Scribd
Upload
16 views5 pages

s-1 dbqa

The document explains the concept of Cartesian product in SQL, detailing how it combines two tables and provides examples of SQL queries to demonstrate this. It also includes a series of questions and answers related to database connectivity in Python, covering connection creation, data retrieval methods, and basic SQL operations. Additionally, it provides code snippets for establishing connections and executing queries in Python using the mysql.connector package.

Uploaded by

sweetheartjanani0
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
16 views5 pages

s-1 dbqa

The document explains the concept of Cartesian product in SQL, detailing how it combines two tables and provides examples of SQL queries to demonstrate this. It also includes a series of questions and answers related to database connectivity in Python, covering connection creation, data retrieval methods, and basic SQL operations. Additionally, it provides code snippets for establishing connections and executing queries in Python using the mysql.connector package.

Uploaded by

sweetheartjanani0
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 5

e.g.

SELECT * FROM FOODNATURAL JOIN COMPANY;


Output

Cartesian Product
The cartesian product is a binary operation and is denoted by (x). The degree of new relation is the
sum of the degrees of two relations on which Cartesian product is operated. The number of
tuples, of the new relation is equal to the product of the number of tuples, of the two relations
on which Cartesian product is performed.
e.g. if A = {1, 2, 3} and B = {a, b, c}, find A x B.
A x B = ((1, a), (1, b), (1, c), (2, a), (2, b), (2, c), (3, a), (3, b), (3, c))
In SQL the CROSS JOIN or CARTESIAN JOIN is used to produce the cartesian product of two
tables.
The Cartesian product is a basic type of join that matches each row from one table to every row
from another table.
e.g. Consider the following EMPLOYEES and DEPARTMENTS tables:

To get the cartesian product, the following sql statement can be used:

SELECT EMP_name, EMPjd FROM EMPLOYEESCROSS JOIN DEPARTMENTS:


Output

1 MARK QUESTIONS

1. What is connection? What is its role?


Ans. A Connection (represented through a connection object) is the session
between the application program and the database. To do anything with database,
one must have a connection object
2. Which package must be imported in Python to create a database
connectivity application?
Ans. There are multiple packages available through which database connectivity
applications can be created in Python. One such package is mysql.connector.
3. Which function is used to get multiple records retrieved as the result of
SQL query executed?
143
Ans. fetchmany()
4. Which method is used to close the open database connection?
Ans. <connectionobject>.close()
5. To make the changes made by any SQL Queries permanently in
database, which function is used after execution of the query ?
Ans. <connectionobject>.commit()

6. Which function is used to execute an SQL query from within a Python


program?
Ans. execute()
7. Identify the name of connector to establish bridge between Python and MySQL
a) mysql.connection
b) connector
c) mysql.connect
d) mysql.connector
Ans. d. mysql.connector
8. In the following connection string: Identify the elements:
connect(<<1>> =127.0.0.1, <<2>> =‟root‟, <<3>> =„admin‟)
a. <<1>> = User, <<2>> = password, <<3> =host
b. <<1>> = host, <<2>> = user, <<3> =password
c. <<1>> = host, <<2>> = password, <<3> =user
d. <<1>> = IP, <<2>> = user, <<3> = password
Ans. b. <<1>> = host, <<2>> = user, <<3> = password
connect(host= 127.0.0.1, user= ‘root’, password= ‘admin’)
9. Which of the following component act as a container to hold all the data returned from the
query and from there we can fetch data one at a time?
a) ResultSet c) Container
b) Cursor d) Table
Ans. b. Cursor
10.Which attribute of cursor is used to get number of records stored in cursor (Assuming cursor
name ismycursor)?
a) mycursor.count
b) mycursor.row_count
c) mycursor.records
d) mycursor.rowcount

144
Ans. d. mycursor.rowcount
2 MARKS QUESTIONS
1. Write the code to create the connection in which database’s name is Python,
name of host, user and password can taken by user. Also, print that connection?
Ans.import mysql.connector
mycon = mysql.connector.connect( host = “localhost”, user = “root”, passwd = “tiger”,
database = “Python”)
print(mycon)
3. Explain the following results retrieval methods with examples-
fetchone () rowcount ()
Ans. fetchone() :- The fetchone() method will return only one row from the result set in the
form of tuple containing a record.
rowcount() :- cursor.rowcount() that always return how many records have been retrieved
so for using any of the fetch..() methods.
3. Write a small python program to insert a record in the table books with
attributes (title ,isbn).
Ans. import mysql.connector as Sqlator
conn =sqlator.connect(host=”localhost”,user=”root”,passwd=””,database=”test”)
cursor=con.cursor()
query=”INSERT into books(title,isbn) values(‘{}’{})”.format(‘Neelesh’,’5143’)
cursor.execute(query)
con.close()
4. Differentiate between fetchone() and fetchall() methods.
Ans. fetch(): It returns the next row from the result set as tuple. If there are no more
rows to retrieve, None is returned.
fetchall():It fetches all the rows of a query result. It returns all the rows as a list of tuples.
An empty list is returned if there is no record to fetch.
5. Consider the following Python code for updating the records:
def Update(eno):
#Assume basic setup import, connection(con) and cursor(mycursor) is created query="update emp set
salary=90000 where empno=” + str(eno) mycursor.execute(query)

Codeisrunningbuttherecordinactualdatabaseisnotupdating,whatcouldbethepossible reason?
a) save() function ismissing
b) con.save() function ismissing
c) con.commit() function ismissing
d) commit() function ismissing

Ans. c. con.commit() function is missing


3 MARKS QUESTIONS
1. Avni is trying to connect Python with MySQL for her project. Help her to write
the python statement on the following:-
(i) Name the library, which should be imported to connect MySQL with Python.
(ii)Name the function, used to run SQL query in Python.
(iii) Write Python statement of connect function having the arguments values
as :Host name :192.168.11.111
User : root
Password: Admin
Database : MYPROJECT

Ans. (i) import mysql.connector


(ii) execute (<sql query >)
(iii)mysql.connector.connect(host=”192.168.11.111”,user=”root”,passwd=”Admin”,databa

145
se=”MYPROJECT”)
2. The given program is used to connect with MySQL abd show the name of the all
the record from the table “stmaster” from the database “oraclenk”. You are
required to complete the statements so that the code can be executed properly.
import _____.connector__pymysql
dbcon=pymysql._____________(host=”localhost”, user=”root”,
________=”sia@1928”)
if dbcon.isconnected()==False
print(“Error in establishing connection:”)
cur=dbcon.______________()
query=”select * from stmaster”
cur.execute(_________)
resultset=cur.fetchmany(3)
for row in resultset:
print(row)
dbcon.______()

Ans. import mysql.connectoraspymysql


dbcon=pymysql.connect(host=”localhost”, user=”root”, passwd=”sia@1928”)
if dbcon.isconnected()==False
print(“Error in establishing connection:”)
cur=dbcon.cursor()
query=”select * from stmaster”
cur.execute(query)
resultset=cur.fetchmany(3)
for row in resultset:
print(row)
dbcon.close()

3. Explain various database operations one can perform using MySQL-Python


connectivity.
Ans. INSERT, READ, UPDATE, DELETE, ROLLBACK (explain in detail )
4. Consider the information stored in the table: EMP

EMPNO ENAME DEPT SALARY


1 ALEX MUSIC 60000
2 PETER ART 67000
3 JOHNY WE 55000
4 RAMBO P&HE 48000

Following python code is written to access the records of table : EMP, What will be the output of following
code?

# Assume All basic setup related to connection and cursor creation is already done
query="select * fromemp"
mycursor.execute(query)
results = mycursor.fetchone()
results = mycursor.fetchone()
results = mycursor.fetchone()
d = int(results[3])
print(d*3)
Ans. 165000
5. Find the output:

146
Ans. Total records fetched so far are 1
Total records fetched so far are2
Total records fetched so far are4

147

You might also like