Revision Test - 1

1. The following table represents information on sales representatives of ABC company with the
following data.
Sales man name, Code , Address, commission , salary.
Write Python code to create the above table in database ‘db’.
2.Write Python mysql Pgm to retrieve all the data from table student which is in database ‘db’.
3. 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

A 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 * from emp"
mycursor.execute(query)
results = mycursor.fetchone()
results = mycursor.fetchone()
results = mycursor.fetchone()
d = int (results[3])
print (d*3)
4.A ------------------ is a special control structure that facilitates the row by row processing of records
in the resultset.
5.After importing mysqlconnector, first of all --------------is established by using connect()
6.-----------------method executes a database query from within Python.
7. Running of sql query through database cursor returns the table records in the form of---------
8.A connectivity package---------------must be imported before running db connection program.
9.Which of the following is not a legal method for fetching records from database.
a)fetchone() b)fetchtwo() c)fetchall() d)fetchmany()
10.To fetch one record from resultset you may use<curor>………… method.
a)fetch() b)fetchone() c)fetchtuple d)none of these.
Answers:
1.
import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",passwd="system",database=
"sales") mycursor=mydb.cursor()
mycursor.execute("CREATE TABLE SALESMAN (NAME VARCHAR(20),CODE INT ,ADDRESS
VARCHAR(20), COMMISSION DEC,SALARY FLOAT);")
2.
import mysql.connector
conn=mysql.connector.connect(host="localhost",user="root",passwd="system",database=
"DB") c=conn.cursor()
c.execute("select * from student")
r=c.fetchone()
while r is not None:
print(r)
r=c.fetchone()
conn.close()

3. 165000
4.database cursor
5.database connection.
6. execute()
7.resultset
8.mysql.connector
9. b)fetchtwo()
10. b)fetchone()
Revision Test - 2

1.Write a python code to delete all the records from employee table whose age >60 and the table has
the following fields. Empid, empname, deptid, age, payscale
2. Consider the following Python code is written to access the details of employee, whose employee
number is passed to function: Complete the missing statements:

def Search(eno):

import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",passwd="system",database="D
B")
mycursor=mydb.cursor()
query="select * from emp where empno=___".format(eno)
mycursor.execute(query)
results = mycursor.
print(results)

3. Consider the following python code for updating the records.

import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",passwd="system",database="stud
ent")
mycursor=mydb.cursor()
mycursor.execute("UPDATE STUDENT SET MARKS=95 WHERE MARKS=50")
print(mycursor.rowcount,"RECORD UPDATED")
Code is running but the record in actual database is not updating, what could be the possible
reason?
4. Which function of connection is used to check whether connection to mysql is successfully
done or not?
5. Write the differences between fetchall() and fetchone()?
6. Write python connectivity program to delete the employee record whose name is read from the
keyboard at execution time.
1.
import mysql.connector as ms
mydb=ms.connect(host="localhost",user="root",passwd="system",database="DB")
mycursor=mydb.cursor()
mycursor.execute("DELETE FROM EMP WHERE AGE>60 ") mydb.commit()
print(mycursor.rowcount,"RECORD DELETED")
mydb.close()

2. .{ } and fetchone()

3. con.commit() function is missing in the python code

4. con.is_connected()

5. fetchall() function is used to fetch all the records from the cursor in the form of tuple.
fetchone() is used to fetch one record at a time. Subsequent fetchone() will fetch next
records. If no more records to fetch, it returns None.
6
import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",passwd="system",database="
DB")
mycursor=mydb.cursor()
s= input(“enter the name”)
mycursor.execute("delete from emp where name =’{}’)”.format(s)
mydb.commit()
 Revision Test - 3
1) ___________ helps in efficient retrieval, inserting and deleting of data.
2) ___________ is a software for creating and managing databases.
3) _________ means that data is accurate and consistent in the database.
4) ______________- ensure that the database properly changes states upon a successfully committed
transaction.
5) RDBMS stands for __________.
6) In RDBMS a database is considered as a collection of ___________
7) Collection of rows and columns are called as ______________
8) Record is also called as a ____________
9) The group of one or more columns used to uniquely identify each row of a relation is called________
10) __________ is data about data.
11) A _________ is a request to a database for obtaining information in a desired way.
12) ___________ is collection of values from which the value is derived for a column.
13) _______ are the columns of a table that points to the primary key of another table.
14) There is a column HOBBY in a Table CONTACTS. The following two statements are giving different
outputs. What may be the possible reason?
a.SELECT COUNT(*) FROM CONTACTS;
b.SELECT COUNT(HOBBY)FROM CONTACTS;
15) What will be the output of the following queries on the basis of Employee table:

a. Select avg(Salary) from Employee;

b. Select Salary+100 from Employee where EmpId='A002';
16)Write the SQL commands for the following questions (i) to (v) based on the relations Car and Customer
given below:
Car
Ccode Cname Make Colour Capacity Charge
201 Triber Renault Yellow 7 s 100
203 Altroz Tata Black 5 150
0
208 Innova Toyota Silver 8 300
0
209 Harrier Tata White 6 200
0
212 Duster Renault Red 6 250
0
217 Ertiga Suzuki Grey 7 230
0
Customer 0
Custcode Custnam Ccode
101 Gopinath 201
102 Ashok 203
103 Harshini 201
104 Vishnu 212
(i) To display the Names and Charges of all the Silver coloured cars.
(ii) To display the non duplicate car codes in the customer table.
(iii) To display the Minimum and Maximum car charges.
(iv) To give a discount of 10% in the car charges for existing customers (who are in the customer
table).
(v) To display Name and Make of cars whose charges is in the range 2000 to 3000 (both inclusive).
Answers Fill in the blanks:
1 Database 2 DBMS 3 Data integrity
4 Consistency 5 Relational Database 6 Interrelated data
Management
System
7 Table 8 Tuple 9 Primary key
10 Meta – data 11 Query 1 Domain
2
13 Foreign key
14 The column hobby may have NULL values, when we give count(colname) it ignores null values
while count(*) will count all duplicate and NULL values. therefore two statements may give different
values.
15 I) 5300
II) NULL

16
(i) Select Cname, Charges from Car where Colour=’silver’;
(ii) Select distinct Ccode from customer;
(iii) Select min(Charges), max(Charges) from Car;
(iv) Update Car set Charges=Charges - Charges*0.1 from Car R, Customer C where
R.Ccode=C.Ccode;
(v) Select Cname, Make from Car where Charges between 2000 and 3000;