5 Assertion(A): A database constraints can be added or removed any time in/from 1

the database tables.

Reason(R): Alter table command is used to change the structure of the table.
6 Identify DDL and DML Commands. 1
CREATE, UPDATE, ALTER and DELETE
Consider the following table and write sql command from QN 7 to 11.

Employee

empid Empname Salary

101 ALOK GUPTA 5000

102 ANITA KANNOJIA Null

103 SANGEETA 4900

104 B L MARODIA 11000

7 Insert a record into the table employee. 1

(105, PANKAJ,5100)
a. insert into table employee values (105,’PANKAJ’,5100);
b. insert into table emp (empid, empname, salary) values
(105,’PANKAJ’,5100);
c. insert into employee (empid, empname, salary) values
(105,’PANKAJ’,5100);
d. All of above
8 Update null values in column salary with 5000. 1
a. update employee set salary = 5000;
b. update employee set salary = 5000 where salary = null;
c. update employee set salary = 5000 where salary is null;
d. None of these

9 Show the structure of the table employee. 1

a. desc employee; b. show employee; c. display employee; d. desc emp;

10 Delete the record of employee with empid 101. 1

a. drop employee where empid = 101;
b. delete from employee where empid = 101;
c. delete from employee;
d. alter table employee drop empid = 101;
11 Change the data type of empname to varchar(50). 1
a. alter table employee modify empname varchar(50);
b. alter table employee change empname varchar(50);
c. alter table employee add empname varchar(50);
d. alter table employee edit empname varchar(50);
Consider the following table and write SQL command from QN 12 to 15 and
output from 16 to 17.

TECH_COURSE

CNAME FEES TID

 Animation 12000 101

Cad 15000 Null

Dca 10000 102

Dtp 9000 104

Mobile app 18000 101

Digital marketing 16000 103

12 Display course name with fees between 10000 to 20000. 1

a. select cname from tech_course where fees between 10000 and 20000;
b. select cname from tech_course where fees between 10000 or 20000;
c. select * from tech_course where fees between 10000 and 20000;
d. select cname from tech_course where fees between 10000 to 20000;
13 Count the number of Records. 1
a. select count(cname) from tech_course;
b. select count(*) from tech_course;
c. select cname, count(*) from tech_course;
d. None of these
14 Display the candidate names in ascending order. 1
a. select * from tech_course;
b. select * from tech_course order by cname;
c. select cname from tech_course;
d. select cname from tech_course order by cname;
15 Show the unique TID. 1
a. select distinct tid from tech_course;
b. select unique tid from tech_course;
c. select primary key tid from tech_course;
d. show distinct tid from tech_course

16 SELECT AVG(FEES) FROM TECH_COURSE WHERE FEES BETWEEN 1

15000 AND 17000;

a. AVG(FEES) b. 15500
15500
c. AVG(FEES) d. All of these
15500.00
17 SELECT TID, COUNT(*), MIN(FEES) FROM TECH_COURSE GROUP BY 1
TID HAVING COUNT(TID) >1;

a. TID COUNT(*) MIN(FEES) b. TID COUNT(*) MIN(FEES)

101 2 12000 101 2 12000
102 1 10000
103 1 16000
104 1 9000
c. TID COUNT(*) MIN(FEES) d. All of these
102 1 10000
103 1 16000
104 1 9000
 18 Foreign Key ensures________. 1
a. Referential Integrity b. Reduction of Data Redundancy
c. Data Security d. All of these
19 Cardinality is the total number of __________ and Degree is Total number of 1
_________.
a. rows, columns b. columns, rows c. non null values d. None of these
20 How many primary keys exist in a table? 1
a.Zero b. One c. Two d. Three

Answer Key
1 A 6 DDL – CREATE, ALTER 11 A 16 C

DML- UPDATE, DELETE

2 A 7 C 12 A 17 B

3 A 8 C 13 B 18 A

4 C 9 A 14 D 19 A

5 B 1 B 15 A 20 B
0

Q.NO. MARKS

1 Write SQL Command for (a) to (d) 4

TABLE: GRADUATE_STUDENTS
S.N NAME STIPEND SUBJECT AVERAGE DIV
O
1 KARAN 400 PHYSICS 68 I
2 DIWAK 450 COMP Sc 68 I
AR
3 DIVYA 300 CHEMISTRY 62 I
4 REKHA 350 PHYSICS 63 I
5 ARJUN 500 MATHS 70 I
6 SABINA 400 CHEMISTRY 55 II
7 JOHN 250 PHYSICS 64 I
8 ROBERT 450 MATHS 68 I
9 RUBINA 500 COMP Sc 62 I
10 VIKAS 400 MATHS 57 II
a. List the names of those students who have obtained DIV I sorted by NAME.
b.Display a report, listing NAME, STIPEND, SUBJECT and amount of stipend
received in a year assuming that the STIPEND is paid every month.
c. To count the number of students who are either PHYSICS or COMPUTER SC
graduates.
d.To insert a new row in the Graduate_students table: 11,”KAJOL”, 300, “computer sc”,
75, 1
2 Consider the table ORDERS as given below 4

TABLE- ORDER

O_Id C_Name Product Quantity Price

1001 Jitendra Laptop 1 12000
1002 Mustafa Smartphone 2 10000
1003 Dhwani Headphone 1 1500
1004 Alice Smartphone 1 9000
1005 David Tablet NULL 7000
A) Write the SQL commands :
a. To display the total Quantity for each Product, excluding Products with total Quantity
less than 5.
b. To display the ORDERS table sorted by total price in descending order.
c. To display the distinct customer names from the ORDERS table.
d. To display the sum of the Price of all the orders for which the quantity is NULL
OR
B) Write the output:
a. SELECT C_Name, SUM(Quantity) AS Total_Quantity
FROM ORDERS GROUP BY C_Name;
b. SELECT * FROM ORDERS WHERE Product LIKE '%phone%';
c. SELECT O_Id, C_Name, Product, Quantity, Price
FROM ORDERS WHERE Price BETWEEN 1500 AND 12000;
d. SELECT MAX(Price) FROM ORDERS;
3 Mayank creates a table RESULT with a set of records to maintain the marks secured by 4
students in sub1, sub2, sub3 and their GRADE. After creation of the table, he has entered
data of 8 students in the table.
Table : RESULT

ROLL_N SNAME sub1 sub sub3 GRADE

O 2
101 KIRAN 366 410 402 I
102 NAYAN 300 350 325 I
103 ISHIKA 400 410 415 I
104 RENU 350 357 415 I
105 ARPITA 100 75 178 IV
106 SABRINA 100 205 217 II
107 NEELIMA 470 450 471 I
103 ISHIKA 400 410 415 I
Write the statements to:

a. Add a column REMARKS in the table with datatype as varchar with 50 characters.
b. Display the name, sub1, sub2, sub3 whose grade is IV.
c. Insert the following record into the table
Roll No- 108, Name- Aaditi, sub1- 470, sub2-444, sub3- 475, Grade– I.
 d. Increase the sub2 marks of the students by 3% whose name begins with ‘N’.
4 Write SQL queries for (a) to (d) which are based on the tables TRANSPORT AND 4
JOURNEY.
Table: TRANSPORT
CODE VTYPE PERKM
101 VOLVO BUS 160
102 AC DELUXE BUS 150
103 ORDINARY BUS 90
105 SUV 40
104 CAR 20
Table: JOURNEY
NO NAME TDATE KM CO NOP
DE
101 Janish Kin 2015-11-13 200 101 32
103 Vedika 2016-04-21 100 103 45
Sahai
105 Tarun Ram 2016-03-23 350 102 42
102 John Fen 2016-02-13 90 102 40
107 Ahmed 2015-01-10 75 104 2
Khan
104 Raveena 2016-05-28 80 105 4

a. To display NO, NAME, TDATE from the table TRANSPORT in descending order
of NO.
b. To display the NAME of all the travellers from the table TRANSPORT who
are travelling by vehicle with code 101 or 102.
c. To display the NO and NAME of those travellers from the table TRAANSPORT
who travelled between ‘2015-12-31’ and ‘2015-04-01’.
d. To display all the details from table TRANSPORT for the travellers, who
have travelled distance more than 100 KM in ascending order of NOP?
5 Give output of the following queries as per given table(s): 4

TABLE- WORKER

WID WNAME JOB SALAR DNO

Y

1001 RAHUL SHARMA CLERK 15000 D03

1002 MUKESH VYAS ELECTRICIAN 11000 D01

1003 SURESH FITTER 9000 D02

1004 ANKUR GUARD 8000 D01

1001 RAHUL SHARMA CLERK 15000 D03

TABLE- DEPT

DNO DNAME LOC MANAGER

 D01 PRODUCTION GROUND FLOOR D K JAIN

D02 ACCOUNTS 1ST FLOOR S ARORA

D03 SECURITY 1ST FLOOR R K SINGH

a. SELECT DISTINCT JOB FROM WORKER;

b. SELECT DNAME, LOC FROM DEPT WHERE SALARY > 10000;
c. SELECT W.WNAME, D.MANAGER FROM WORKER AS W, DEPT
AS D WHERE W.DNO = D.DNO;
d. SELECT WNAME FROM WORKER WHERE WNAME LIKE 'R%';
6 (A) Consider the following tables SCHOOL and ADMIN and answer the following 4
questions:
TABLE- SCHOOL

CO TEACHE SUBJECT DOJ PERIO EXPERIEN

DE R DS CE
1001 RAVI ENGLISH 12/03/20 24 10
00
1009 PRIYA PHYSICS 03/09/19 26 12
98
1203 LISA ENGLISH 09/04/20 27 5
00
1045 YASH MATHS 24/08/20 24 15
RAJ 00
1123 GAGAN PHYSICS 16/07/19 28 3
99
1167 HARISH CHEMISTR 19/10/19 27 5
Y 99
1215 UMESH PHYSICS 11/05/19 22 16
98
TABLE : ADMIN

CODE GENDER DESIGNATION

1001 MALE VICE PRINCIPAL

1009 FEMALE COORDINATOR

1203 FEMALE COORDINATOR

1045 MALE HOD

1123 MALE SENIOR TEACHER

1167 MALE SENIOR TEACHER

1215 MALE HOD

Give the output of the following sql queries:

a. SELECT DESIGNATION, COUNT(*) FROM ADMIN GROUP BY
DESIGNATION HAVING COUNT(*)<2;
 b. SELECT MAX(EXPERIENCE) FROM SCHOOL;
c. SELECT TEACHER FROM SCHOOL WHERE EXPERIENCE >12 ORDER BY
TEACHER;
d. SELECT COUNT(*), GENDER FROM ADMIN GROUP BY GENDER;
7 Consider the following tables – BANK_ACCOUNT and Branch: 1+4
BANK_ACCOUNT

ACode Name Type

A01 Amit Savings
A02 Parth Current
A03 Mira Current
BRANCH

ACode City
A01 Delhi
A02 Jaipur
A01 Ajmer
a. What will be the output of the following statement?
SELECT * FROM BANK_ACCOUNT NATURAL JOIN BRANCH;

(B) Give the output of the following sql statements as per table given above-
TABLE: SPORTS

Student Cl Name Game1 Grad Game2 Grad

No ass e1 e2
10 7 Sammer Cricket B Swimming A
11 8 Sujit Tennis A Skating C
12 7 Kamal Swimmin B Football B
g
13 7 Venna Tennis C Tennis A
14 9 Archana Basketball A Cricket A
15 10 Arpit Cricket A Athletics C

a. SELECT COUNT(*) FROM SPORTS;

b. SELECT DISTINCT Class FROM SPORTS;
c. SELECT MAX(Class) FROM SPORTS;
d. SELECT COUNT(*) FROM SPORTS GROUP BY Game1;
8 (a) consider the following tables STUDENT and FEE and answer the following 4
questions
TABLE – STUDENT
RollNo Name Class Section Marks

101 Anjali 12 A 85

102 Rohan 12 B 92

103 Meera 12 A 78
 104 Aditya 12 C 88

105 Arun 12 C 89

TABLE- FEE
RollNo AmountPaid PaymentDate

101 25000 2024-04-15

102 30000 2024-04-16

104 28000 2024-04-18

a. Display the names of all students along with the amount of fee they have paid.
b. Display the names of students who paid the fee between '2024-04-15' and '2024-04-
17'.
c. List names of students who belong to section 'a' and have paid more than 25000.
d. List the names of students who have not paid the fee yet.

9 consider the following tables EMPLOYEE and PROJECT and answer the following 5
questions
EMPLOYEE
EmpID Name Department Salary

201 Anil HR 50000

202 Seema Finance 60000

203 Ravi IT 55000

204 Pooja HR 52000

205 Karan IT 58000

PROJECT
ProjectID EmpID Project Name HoursWorked ProjectID

P1 201 Recruitment App 35 P1

P2 202 Payroll System 40 P2

P3 203 Inventory Mgmt 30 P3

P4 201 Employee Portal 20 P4

P5 205 Website Redesign 25 P5

a. Display all employees along with the projects they are working on.
 b. List names of employees who are working on more than one project.
c. Show the total hours worked by each employee.
d. List all employees and their project names, including those who are not working on
any project.
e. Display names and departments of employees who have worked more than 30 hours
in any project.
10 consider the following tables EMPLOYEE and DEPARTMENT and answer the 4
following questions-
EMPLOYEE
EmpID Name Salary DeptID

1 Aman 50000 101

2 Priya 60000 102

3 Rakesh 45000 101

4 Sneha 70000 103

DEPARTMENT
DeptID DeptName Location DeptID

101 Sales Delhi 101

102 HR Mumbai 102

a. Show all employee details along with department name.

b. Show the List employees who work in Delhi.
c. Display the names and salaries of employees earning the highest salary.
d. Show average salary for each department
Answer
1 4
a. SELECT NAME from GRADUATE_STUDENT where DIV = ‘I’ order by NAME;
b. SELECT NAME,STIPEND,SUBJECT, STIPEND*12 from
GRADUATE_STUDENT;
c. SELECT SUBJECT,COUNT(*) from GRADUATE_STUDENT
d. group by SUBJECT having SUBJECT=’PHYISCS’ or SUBJECT=’COMPUTER SC’;
e. INSERT INTO GRADUATE_STUDENT values(11,’KAJOL’,300,’COMPUTER
SC’,75,1);
2 Ans : (A)
a. SELECT Product, SUM(Quantity) AS Total_Quantity 4
FROM ORDERS
GROUP BY Product
HAVING SUM(Quantity) >= 5;
b. SELECT O_Id, C_Name, Product, Quantity, Price
FROM ORDERS
ORDER BY Price DESC;
c. SELECT DISTINCT C_Name
FROM ORDERS;

d. SELECT SUM(Price) AS
Total_Price_Null_Quantity
FROM ORDERS
WHERE Quantity IS NULL;

OR
(B)
a. C_Name Total Quantity
Jitendra 1
Mustafa 2
Dhwani 1
Alice 1
David NULL
b.
O_Id C_Name Product Quantity Price
1002 Mustafa Smartphone 2 10000
1004 Alice Smartphone 1 9000

c.

O_Id C_Name Product Quantity Price

1001 Jitendra Laptop 1 12000
1002 Mustafa Smartphone 2 10000
1003 Dhwani Headphone 1 1500
1004 Alice Smartphone 1 9000

4
3 a. New Degree: 8 New Cardinality: 5
b. SELECT name,sub1,sub2,sub3
FROM RESULT
WHERE GRADE=’IV’,
c. A) INSERT INTO RESULT VALUES (108, ‘Aadit’, 470, 444, 475, ‘I’);
d. UPDATE RESULT SET SEM2=SEM2+ (SEM2*0.03)
WHERE SNAME LIKE “N%”;
OR (Option for part iii only)
 a. DELETE FROM RESULT WHERE DIV=’IV’;

ALTER TABLE RESULT ADD (REMARKS VARCHAR(50));

4 4
a. SELECT NO, NAME, TDATE FROM TRAVEL ORDER BY NO DESC;
b. SELECT NAME FROM TRAVEL WHERE CODE=‘101’ OR CODE=’102’; or
c. SELECT NAME FROM TRAVEL WHERE CODE IN (‘101’,’102’)
d. SELECT NO, NAME from TRAVEL WHERE TDATE >= ‘2015−04−01’ AND
TDATE <= ‘2015−12−31’;

OR

SELECT NO, NAME from TRAVEL WHERE TDATE BETWEEN ‘2015-04-01’ AND
‘2015-12-31’;

5 a. JOB 4
CLERK
ELECTRICIA
N FITTER
GUARD
b.

DNAME LOC
PRODUCTION GROUND FLOOR
SECURITY 1ST FLOOR
c.
WNAME MANAGER
RAHUL SHARMA R KSINGH
MUKESH VYAS D K JAIN
SURESH S ARORA
ANKUR D K JAIN
d. WNAME

RAHUL SHARMA

6 a. Vice principal 01 4
b. 16
c. UMESH YASHRAJ
d.
5 Male
2 Female
3
7 (A) a.
ACode Name Type City
A01 Amit Savings Delhi
A01 Amit Savings Ajm
 er
A02 Parth Current Jaip
ur
(B)-a. 6
b.
Class
7
8
9
10

c.10
d.
Game1 Count(*)
Cricket 2
Tennis 2
Swimming 1
8 a. SELECT STUDENT. Name, FEE.AmountPaid 4
FROM STUDENT
LEFT JOIN FEE ON STUDENT.RollNo = FEE.RollNo;
b. SELECT STUDENT. Name
FROM STUDENT
INNER JOIN FEE ON STUDENT.RollNo = FEE.RollNo
WHERE FEE.PaymentDate BETWEEN '2024-04-15' AND '2024-04-17';
c. SELECT STUDENT. Name
FROM STUDENT
INNER JOIN FEE ON STUDENT.RollNo = FEE.RollNo
WHERE STUDENT. Section = 'A' AND FEE.AmountPaid > 25000
d. SELECT STUDENT. Name
FROM STUDENT
LEFT JOIN FEE ON STUDENT.RollNo = FEE.RollNo
WHERE FEE.AmountPaid IS NULL;
a. SELECT EMPLOYEE.Name, PROJECT.ProjectName 5
9 FROM EMPLOYEE
INNER JOIN PROJECT ON EMPLOYEE.EmpID = PROJECT.EmpID;
b. SELECT EMPLOYEE. Name, COUNT(PROJECT.ProjectID) AS ProjectCount
FROM EMPLOYEE
INNER JOIN PROJECT ON EMPLOYEE.EmpID = PROJECT.EmpID
GROUP BY EMPLOYEE. Name
HAVING COUNT(PROJECT.ProjectID) > 1;
c. SELECT EMPLOYEE. Name, SUM(PROJECT.HoursWorked) AS TotalHours
FROM EMPLOYEE
INNER JOIN PROJECT ON EMPLOYEE.EmpID = PROJECT.EmpID
GROUP BY EMPLOYEE.Name;
d. SELECT EMPLOYEE.Name, PROJECT.ProjectName
FROM EMPLOYEE
LEFT JOIN PROJECT ON EMPLOYEE.EmpID = PROJECT.EmpID;
 d. SELECT EMPLOYEE.Name, EMPLOYEE.Department
FROM EMPLOYEE
INNER JOIN PROJECT ON EMPLOYEE.EmpID = PROJECT.EmpID
WHERE PROJECT.HoursWorked > 30;
10 a. SELECT Employee.EmpID, Employee.Name, Employee.Salary, 4
Department.DeptName
FROM Employee
JOIN Department ON Employee.DeptID = Department.DeptID;
b. SELECT Employee.Name
FROM Employee
JOIN Department ON Employee.DeptID = Department.DeptID
WHERE Department. Location = 'Delhi';
c. SELECT Name, Salary
FROM Employee
WHERE Salary = (SELECT MAX(Salary) FROM Employee);
d. SELECT Department.DeptName, AVG(Employee.Salary) AS AvgSalary
FROM Employee
JOIN Department ON Employee.DeptID = Department.DeptID
GROUP BY Department.DeptName;

Python-MySQL Connectivity
The fetchone() method returns results as a dictionary. (Find True or False) 1
(Q1

Q2 MySQL connector must be installed using pip install mysql. (Find True or False) 1

Q3 The …………… method is used to execute SQL queries in Python 1

Q4 To prevent SQL injection, always use ……………instead of string formatting. 1

Q5 Which cursor function is not used to send query to connection? 1

a. query() b. send() c. run() d. All
Q6 Identify the correct statement to create cursor: 1
import mysql.connector as msq
con = msq.connect( #Connection String ) # Assuming all parameter required as passed
mycursor = ________
a. con.cursor() b.con.Cursor() c.con.open_cursor() d. con.get_cursor()
Q7 Which function is used to fetch only one records from cursor? 1
a. fetch() b. fetchone() c. fetchmany() d. fetchall()
Q8 Which function is used to establish a connection between Python and MySQL? 1
a.connect() b. cursor() c. execute() d. commit()
Q9 Which function of connection is used to check whether connection to mysql is 1
successfully done or not?
import mysql.connector as msq
con = msq.connect( #Connection String ) # Assuming all parameter required as passed
if :
print(“Connected!”)
else:
print(“ Error! Not Connected”)
a. con.connected() b. con.isconnected() c. con.is_connected() d . con.is_connect()
Q10. Assertion (A): Parameterized queries prevent SQL injection. 1
Reason (R): They separate SQL code from user input.
a. Both A and R are true, and R explains A.
b. Both A and R are true, but R does not explain A.
c. A is true but R is false.
d. A is false R is True

Answers : COMPETENCY BASED QUESTION: Python-MySQL Connectivity

Ans1 False

Ans2 False

Ans3 execute().

Ans4 Parameterized queries (e.g., %s placeholders)

Ans5 d All

Ans6 a con.cursor()

Ans7 b. fetchone()

Ans8 a) connect()

Ans9 c. con.is_connected()

Ans10 a

Python MySQL Interface and Exception handling

Q. Question Marks
No.
1. State True or False 1
A finally block is always executed, regardless of whether an exception occurred or not.
2. State True or False 1
The except: block without specifying an exception type will catch all exceptions.
3. State whether the following statement is True or False: The finally block in Python is 1
executed only if no exception occurs in the try block.
4. How many except statements can a try-except block have? 1
a. zero b. one c. more than one d. more than zero
5. An exception is said to be caught when 1
a. Error encountered and exception object is created
b. Runtime system searches for appropriate exception handler
c. Code that is designed to handle exception is executed
d. None of these
6. What is the purpose of the cursor() method in Python’s database interaction? 1
a. To create a new database b. To execute SQL queries
c. To close the database connection d. To fetch all records from a table
7. What is the correct order to perform database operations in Python 1
a. Create connection -> Create cursor -> Execute query -> Commit (if
needed) -> close connection
 b. Create cursor -> Create connection -> Execute query -> Commit (if
needed) -> close connection
c. Execute query ->Create cursor -> Create connection -> Commit (if
needed) -> close connection
d. None of these
Q9 and Q10 are Assertion(A) and Reason(R) based questions. Mark
the correct choice as:
a. Both A and R are true and R is the correct explanation for A
b. Both A and R are true and R is not the correct explanation for A
c. A is True but R is False
d. A is False but R is True
9 Assertion : Exception handling handles all types of error and exceptions. 1
Reasoning : Exception handling is responsible for handling anomalous situations
during the execution of a program.
10 Assertion :A database cursor receives all the records retrieved as per the query. 1
Reason : A resultset refers to the records in the database cursor and allows
processing of individual records in it.
11 The code given below inserts the following record in the table Student: 3
RollNo – integer
Name – string
Clas – integer
Marks – integer
Note the following to establish connectivity between Python and MYSQL:
Username is root
Password is tiger
The table exists in a MYSQL database named school.
The details (RollNo, Name, Clas and Marks) are to be
accepted from the user.
Write the following missing statements to complete the code:
Statement 1 – to form the cursor object
Statement 2 – to execute the command that inserts the record in
the table Student.
Statement 3- to add the record permanently in the database
import mysql.connector as mysql
def sql_data():
con1=mysql.connect(host="localhost",user="root", password="tiger",
database="school")
mycursor=_________________ #Statement 1
rno=int(input("Enter Roll Number :: "))
name=input("Enter name :: ")
clas=int(input("Enter class :: "))
marks=int(input("Enter Marks :: "))
querry="insert into student values
({},'{}',{},{})".format(rno,name,clas,marks)
______________________ #Statement 2
______________________ # Statement 3
print("Data Added successfully")
12 The code given below reads the following record from the table named student and 3
displays only those records who have marks greater than 75:
RollNo – integer
Name – string
Clas – integer
Marks – integer
Note the following to establish connectivity between Python and MYSQL:
Username is root
Password is tiger
The table exists in a MYSQL database named school.
Write the following missing statements to complete the code:
Statement 1 – to form the cursor object
Statement 2 – to execute the query that extracts records of
those students whose marks are greater than 75.
Statement 3- to read the complete result of the query (records
whose marks are greater than 75) into the object
named data, from the table student in the database.
import mysql.connector as mysql
def sql_data():
con1=mysql.connect(host="localhost",user="root", password="tiger",
database="school")
mycursor=_______________ #Statement 1
print("Students with marks greater than 75 are : ")
_________________________ #Statement 2
data=__________________ #Statement 3
for i in data:
print(i)
print()
13 Sartaj has created a table named Student in MYSQL database, SCHOOL: 3
rno (Roll number )- integer
name (Name) - string
DOB (Date of birth) – Date
Fee – float
Note the following to establish connectivity between Python and MySQL:
Username - root
Password – tiger
Host – localhost
Sartaj, now wants to display the records of students whose fee is more than 5000. Help
Sartaj to write the program in Python.
14 Kabir wants to write a program in Python to insert the following record in the table 3
named Student in MYSQL database, SCHOOL:
rno (Roll number )- integer
name (Name) - string
DOB (Date of birth) – Date
Fee – float
Note the following to establish connectivity between Python and MySQL:
Username - root
 Password - tiger
Host – localhost
The values of fields rno, name, DOB and fee has to be accepted from the user. Help
Kabir to write the program in Python.
15 A table, named STATIONERY, in ITEMDB database, has the following structure:
Field Type
itemNo Int(11)
itemName Varchar(20)
Price Float
Qty Int(11)

Write the following Python function to perform the specified operation: 4

AddAndDisplay(): To input details of an item and store it in the table STATIONERY.
The function should then retrieve and display all records from the STATIONERY table
where the Price is greater than 120.
Assume the following for Python-Database connectivity:
Host: localhost, User: root, Password: Pencil
16 Rahim wants to write a program in Python to insert the following record in the table 4
named Bank_Account in MySQL database, Bank : ·
Accno – integer
· Cname – string
· Atype – string
· Amount – float
Note the following to establish connectivity between Python and MySQL : ·
Username – admin
· Password – root
· Host – localhost
The values of fields Accno, Cname, Atype and Amount have to be accepted from the
user. Help Rahim to write the program in Python.
17 Sunil wants to write a program in Python to update the quantity to 20 of the records 4
whose item code is 111 in the table named shop in MySQL database named Keeper:
Item_code integer
Item_name String
Qty Integer
Price Integer
Consider the following to establish connectivity between Python and MySQL:
Username admin
Password 123456
Host localhost
18 Sumit wants to write a code in Python to display all the details of the passengers from 4
the table flight in MySQL database , Travel. The tables contains the following
attributes:
F_code String
F_name String
Source String
Destination String
Consider the following to eastablish connectivity between Python and MySQL:
Username admin
 Password 123456
Host localhost
19 The table Bookshop in MySQL contains the following attributes: 3
B_code Integer
B_name String
Qty Integer
Price Integer
Note the following to eastablish connectivity between Python and MySQL on a
‘localhost’:
username is ‘shop’
password is ‘Book’
The table exists in a MySQL database named Bstore
The code given below updates the records from the table Bookshop in MySQL.
Statement 1 – to form the cursor object.
Statement 2 – to execute the query that updates the Qty to 20 of the records whose
B_code is 105 in the table.
Statement 3 – to make the changes permanent in the database.
import mysql.connector as mysql
def update_book ( ):
mydb=mysql.connect (host=”localhost”, user=”shop”, passwd=”Book”,
database=”Bstore”)
mycursor = _______________________ #Statement 1
qry= “update Bookshop set Qty=20 where B_code=105”
________________________ #Statement 2
________________________ #Statement 3

20 Consider the Student table of SCHOOL database with following structure. 3

Rollno Integer
name String
Dob Date
Fee float
The following credentials may be used to connect Python MySQL :
Username root
Password tiger
Host localhost
Write a Python program to display the records of students whose fees is less than
2000.
Answer-
Python MySQL Interface and Exception handling
Section -B
1. True
2. True
3. False
4. d) more than zero
5. a)
6. c. Assertion is True but Reason is False
7. a. Assertion and Reason are True. Reason is correct explanation of Assertion
 8. a)
9. Assertion is False and Reasoning is True
10. d)
11. Statement 1 : con1.cursor()
Statement 2 : mycursor.execute(querry)
Statement 3 : con1.commit()

12. Statement 1 : con1.cursor()

Statement 2 : mycursor.execute (“select * from student where marks > 75”)
Statement 3 : mycursor.fetchall()

13. import mysql.connector as mysql

Con=mysql.connect(host = ‘localhost’, username=’root’, passwd=’tiger’,
database=’SCHOOL’)
Mycursor=Con.cursor()
Query=”select * from Student where Fee > 5000”
Data=Mycursor.fetchall()
for r in Data:
print(r)
14. import mysql.connector as mysql
Con=mysql.connect(host = ‘localhost’, username=’root’, passwd=’tiger’,
database=’SCHOOL’)
Mycursor=Con.cursor()
rno=int(input(“Enter roll no “))
name=input(“Enter name”)
dob=input(“Enter date of birth “)
fee=float(input(“Enter fee”))
Query=”insert into student values ({},{},{},{}).format(rno,name,dob,fee)”
mycursor.execute(query)
con1.close()
15. import mysql.connector as mysql
def AddAndDisplay():
Con=mysql.connect(host = ‘localhost’, username=’root’, passwd=’Pencil’,
database=’SCHOOL’)
Mycursor=Con.cursor()
itemno=int(input(“Enter item no “))
itemname=input(“Enter item name”)
price=float(input(“Enter price “))
Qty=int(input(“Enter quantity”))
Query=”insert into STATIONARY values
({},’{}’,{},{}).format(itemno,itemname,price,Qty)”
mycursor.execute(query)
Query=”select * from STATIONARY where price > 120”
mcursor.execute(Query)
for r in mycursor.fetchall():
print(r)
16. import mysql.connector as mysql
Con=mysql.connect(host = ‘localhost’, username=’admin’, passwd=’root’,
database=’Bank’)
 Mycursor=Con.cursor()
accno=int(input(“Enter account no “))
cname=input(“Enter name”)
atype=input(“Enter account type “)
amount=float(input(“Enter amount”))
Query=”insert into student values ({},’{}’,{},{}).format(accno,cname,atype,amount)”
mycursor.execute(query)
con1.close()

17. import mysql.connector as mysql

Con=mysql.connect(host = ‘localhost’, username=’admin’, passwd=’123456’,
database=’Keeper’)
Mycursor=Con.cursor()
Query=”update shop set qty=20 where item_code=111)”
mycursor.execute(query)
con1.close()
18. import mysql.connector as mysql
Con=mysql.connect(host = ‘localhost’, username=’admin’, passwd=’123456’,
database=’Travel’)
Mycursor=Con.cursor()
Query=”select * from flight ”
mycursor.execute(query)
for r in mycursor.fetchall():
print(r)
con1.close()

19. Statement 1 : mydb.cursor()

Statement 2 : mycursor.execute(mycursor)
Statement 3 : mydb.commit()

20. import mysql.connector as mysql

Con=mysql.connect(host = ‘localhost’, username=’root’, passwd=’tiger’,
database=’SCHOOL’)
Mycursor=Con.cursor()
Query=”select * from student where fee < 2000”
mycursor.execute(query)
Data=mycursor.fetchall()
for r in Data:
print(r)
con1.close()