CBSE 12th Computer Science Interface Python with MySQL - New Previous year Question Papers Study Material - QB365 Set A

Download CBSE Class 12th Standard CBSE Computer Science question papers, sample papers, important questions, and previous year solved papers in PDF format. Get free study materials, NCERT solutions, and exam preparation resources for Class 12th Standard CBSE Computer Science

Questions:

Short Answer Type Questions

18 x 2 = 36

A table, named THEATRE, in CINEMA database, has the following structure:

Field	Type
Th_ID	Char (5)
Name	Varchar (15)
City	Varchar (15)
Location	Varchar (15)
Seats	int

Write a function Delete Theatre ( ), to input the value of Th_ID from the user and permanently delete the corresponding record from the table. Assume the following for Python-Database connectivity:

Host: localhost, User: root, Password: Ex2025

Consider the table Product with following structure.

P_ID, ProductName, Manufacturer, Price. Write Python code to increase the price of all products by 50.

Sunil wants to write a program in Python to update the quantity to 20 of the records whose item code is 111 in the table named shop in MySQL database named Keeper.

The table shop in MySQL contains the following attributes :

• Item_code: Item code (Integer)

•Item_name: Name of item (String)

•Qty: Quantity of item (Integer)

•Price: Price of item (Integer)

Consider the following to establish connectivity between Python and MySQL:

•Username: admin

•Password: Shopping

•Host: localhost

Sumit wants to write a code in Python to display all the details of the passengers from the table flight in MySQL database, Travel. The table contains the following attributes:

F_code: Flight code (String)

F_name: Name of flight (String)

Source: Departure city of flight (String)

Destination: Destination city offlight (String)
Consider the following to establish connectivity
between Python and MySQL:

Username: root

Password: airplane

Host : localhost

Write the following Python function to perform the specified operation:

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

Consider the following table Order Details of database sales

Table: Sales

ORDNUMB	PARTNUMB	NUMBORD	QUOTPRIC
12494	CB03	4	175.00
12495	CX11	2	57.95
12498	AZ52	2	22.95
12500	BT04	1	402.99

Write Python code to create a table Faculty with following fields.Id, LastName, FirstName, Locationld, phone, rank, stardate Rank should be one of the following

ASSO, FULL, ASST, INST

Following table represents information on sales representatives for Premiere Products and contains the following data fields for sales representations.

Sales representative's number →SLSRNUMB

Sales representative's name → SLSRNAME

Sales representative's address → SLSRADDR

Total commission paid to the sales representative → COMMRATE

Write Python code to create the above table

A result set is extracted from the database using the cursor object (that has been already created) by giving the following statement.

Mydat = Cursor.fetchone( )

(i) How many records will be returned by fetchone ( ) method?

(ii) What will be the datatype of Mydata object after the given command is executed?

Differentiate between fetchone( ) and fetchall( ) methods with suitable examples for each.

10.

The code given below inserts the following record in the table Student:

RollNo - integer

Name - string

Class - 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, Class 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=mysq1.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(1),'{ )',( ),(1)".format

(rno,name,clas,marks)

_________ #Statement 2

_________ # Statement 3

print(" Data Added successfully" )

The code given below reads the following record from the table named student and
displays only those records who have marks greater than 75:

RollNo - integer

Name - string

Class - 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( ):

conl=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( )

11.

The code given below deletes the record from. the table employee which contains the following record structure:

E_code - String

E_name - String

Sal - Integer

City - String

Note the following to establish connectivity between Python and MySQL

• Username is root

• Password is root

The table exists in a MySQL database named emp.

The details (E_code, E_ name, Sal, City) are the attributes of the table.

Write the following statements to complete the code:

Statement 1 to import the desired library.

Statement 2 to execute the command that deletes the record with E code as E101:

Statement 3 to delete the record permanently from the database.

import

def delete( ) :

mydb-mysql.connect (host-"localhost".

user="root".passwd="root".database="emp")

mycursor=mydb.cursor( )

_________ # Statement 2

________# Statement 3

print ("Record deleted")

The code given below reads the following records from the table employee and displays only those records who have employees coming from city 'Delhi'

E_code-String

E_ name-String

Sal - Integer

City - String

Note the following to establish connectivity between Python and MySQL:

• Username is root

• Password is root

• The table exists in a MySQL database named emp.

• The details (E_code, E_name, Sal, City) are the.attributes of the table.

Write the following statements to complete the code

Statement 1 to import the desired library.

Statement 2 to execute the query that fetches records of the employees coming from city Delhi.

Statement 3 to read the complete data of the query (rows whose city is Delhi) into the object named details, from the table employee in the database.

import

as mysql \# Statement 1

def display( ) :

mydb-mysql.connect (host="localhost".

user="root", passwd="root", database="emp")

mycursor=mydb.cursor( )

12.

Consider the following structure of table

Hospital.

Hospital_id(p)

Hopital_name

Bed_Count

Write Python code to create the above table structure.

13.

Given a database Inventory, userid - store manager password- welcometostore 123

Write Python code to connect to the above database.

14.

To fetch all records from database PythonDB

15.

To fetch many records from database PythonDB

16.

To fetch only one record

17.

In Student table, we will delete the record of that student whose Roll No is 33.

18.

In Student table, we will update the class of those student whose Roll No is 22.

Long Answer Type Questions

6 x 5 = 30

19.

Consider the table MobileMaster

M_Id	M_Company	M_Name	M_Price	M_Mf_Date
MB001	Samsung	Galaxy	4500	2014-02-12
MB002	Nokia	N1100	2500	2011-04-15
MB003	Sony	Experia M	9500	2017-11-20
MB004	Oppo	SelfieEx	8500	2010-08-21

Write the Python code for the following

(i) To display details of those mobiles whose price is greater than 8000.

(ii) To increase the price of mobile Samsung by 2000.

20.

MySQL database named WarehouseDB has a product_inventory table in MySQL which contains the following attributes:

• Item_code:Item code (Integer)

• Product_name: Name of product (String)

• Quantity: Quantity of product (Integer)

• Cost Cost of product (Integer)

Consider the following details to establish Python-MySQL connectivity:

• Username: admin_user

• Password: warehouse2024

• Host localhost

Write a Python program to change the Quantity of the product to 91 whoseltem_code is 208 in the product_inventory table.

21.

Create following table using Python code where

Database - Test

Table - Watches

User name - Root

Password - System

Watchid	Watchname	Price	Type	Qty_store
W001	High Time	10000	Unisex	100
W002	Life Time	15000	Ladies	150
W003	Wave	20000	Gents	200
W004	High Fashion	7000	Unisex	250
W005	Golden Time	25000	Gents	100

22.

Write the Python code for (i) and (ii) on the basis of table College.

No	Name	Age	Department	Dateofjoin	Basic	Sex
1	Aren	51	Mathematics	22/0/91	8500	M
2	Reeta	27	Chemistry	14/02/94	9000	F
3	Viren	49	Mathematics	01/01/88	9000	M
4	Prakash	22	Physics	17/02/92	8000	M
5	Shalaz	45	Biology	12/02/88	10500	M
6	Urvashi	29	Biology	10/02/93	8500	F

(i) To insert a new row in the table College with the following data

15, "Atin 27, "Physics"; "15/05/02 8500,"M"

(ii) To delete a row fron table in which name is Viren.

23.

Kabir wants to write a program in Python to insert the following record in the table 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.

24.

Peter has created a table named Account in MySQL database, SCHOOL, having following structure:

• Stud_id integer

• Sname string

• Class string

• Fees float

Help him in writing a Python program to display records of those students whose fees is less than 5000.

Note the following to establish connectivity between Python and MySQL:

• Username admin

• Password root

• Host localhost

Case Study Questions

3 x 4 = 12

25.

Consider the following table STUDENT:

Stud	Name	Course	Result
1101	Anubhav Singh	BCom	Awaited
1102	Riya Sharma	BSc	Passed
1103	Karthikey Rana	BSc	Passed
1104	Archana Kapoor	BBA	Awaited
1105	Aarti Sharma	BCom	Awaited
1106	Sanchit Kumar	BCom	Passed

(i) What will be the query statement to insert a new record with Student id = 1107, name = Sumit Saxena, Course = BBA and result = passed

A sql = "INSERT INTO student (stuid. name,course, result) VALUES ('1107, 'Sumit Saxena' 'BBA, 'Passed')"

B sql = "INSERT INTO student VALUES ('1107''Sumit Saxena', 'BBA', 'Passed')"

C Both (A) and (B)

D None of these

(ii) The following sql command has been given:

Sql = "UPDATE Student SET result ''

'Passed' WHERE course -'BCom' "

The result of which of the following STUID will be changed to "Passed"?

A 1101

B 1101, 1104

C 1101, 1105

D 1101, 1104, 1105

(iii) Consider the following code:

sql = '''SELECT * from STUDENT'.'

cursor.execute(sql)

result = cursor.fetchone();

print(result)

What will be the output of the above code in reference to the STUDENT table?

A 1101, Anubhav Singh, BCom, Awaited

B 1106, Sanchit Kumar, BCom, Passed

C 1103, Kartikey Rana, BSc, Passed

D Both a) and b)

(iv) Consider the following code:

result = Cursor.fetchmany(2);

print(result)

What will be the result if it is executed after the code given in (iii)?

A 1101, Anubhav Singh, BCom, Awaited 1102, Riya Sharma, BSc, Passed

B 1102, Riya Sharma, BSc, Passed 1103, Kartikey Rana, BSc, Passed

C 1103, Kartikey Rana, BSc, Passed 1104, Archana Kapoor, BBA, Awaited

D None of these

(v) Consider the following code:

cursor=db.cursor()

query = "DELETE FROM student WHERE course =

'BCom' AND result = 'Awaited' "

cursor.execute(query)

del_rows= cursor.rowcount

print(del_rows)

What will be the output of the above code?

A 1 B 2 C 3 D 4

26.

Consider the following code:

import MySQLdb

db=MySQLdb.connect('localhost'.

'LibMan','isspwd','Library')

cursor=db.cursor()

sql="UPDATE BOOK set Status ='ISSUE'

where BookId='1021'''

try:

cursor.execute(sql)

db.commit()

except:

db.rollback( )

db.close( )

(i) What is "Library in the statement MySQLdb.connect('localhostLibMan,isspwd'Library')

A User

B Password

C Table

D Database

(ii) What will be the result of the above code?

A A new record is added

B A record with book id 1021' is updated

C A record with status 'Issue is updated

D All of these

(iii) What is the use of try in above code?

A Handles the errors

B Try the logic of code

C test a block of code for errors

D executes the block

(iv) Which of the following method closes the database connection?

A db.commit( )

B db.rollback( )

C db.close( )

D None of these

(v) What is the function of db.rollbback( ) in the code?

A Returns the database to some previous state

B Returns cursor to the previous row

C Review the records updated

D All of these

27.

Consider the following code:

import MySQLdb

db-MySQLdb.connect('localhost', 'LIBMAN',

'Admin@pwd','library')

cursor=db.cursor(prepared-True)

sq1 = """INSERT INTO Members('MemId',

'MemName','MemAdd',)values('%s','%s',

insert_value=[('1021', 'Ronit', '%s,

Block A, Kaveri Vihar' ), ('1022',

'Smriti', '12/9 Indira Puram')]

try:

cursor.executemany(sq1,insert_value)

print(cursor.rowcount,'Records inserted')

db.commit( )

except:

db.rollback( )

cursor.close( )

db.close( )

(i) What of the following statement is connecting database server?

A importMySQLdb B MySQLdb.connect C db.cursor D None of these

(ii) The role of the statement 'cursor=db.cursor' is:

A Create an instance of a cursor

B Moves cursor row wise

C Connects cursor to database

D Prepares cursor to move

(iii) Which of the following statement will add the record in the table?

A Insert Into

B Insert Value

C cursor.executemany(sql,insert_value)

D None of these

(iv) What will be the output of following statement in the given code?

print(cursor. rowcount, "Records inserted")

A 0 B 1 C 2 D 3

(v) What will be the result of cursor.close( )' ?

A Close the cursor

B Reset all results

C Deletes all references to the connection

D All of these

Visit QB365-SLMS - https://www.qb365.in/slms/

Answers Key:

Short Answer Type Questions

18 x 2 = 36

import mysql.connector

def Delete_Theatre( ):

th_id = input("Enter Th_ID: ")

conn = mysql.connector.connect(

host="localhost".

user="root",

password="Ex2025".

database="CINEMA"

)

cursor = conn.cursor( )

query = "DELETE FROM THEATRE WHERE

Th_ID = %s"

cursor.execute(query, (th_id,))

conn.commit( )

print("Theatre deleted successfully.")

conn.close( )

Import MySOLdb

db = MySQLdb.connect('localhost'. 'PId'.

"PID@pwd', 'sales')

cursor = db.cursor()

sq1 = """Update Product set Price =

Price+50"""

try:

cursor.execute(sq1)

db.commit( )

except:

db.rollback( )

db.close( )

import mysql.connector

# Establishing the connection to the database

conn = mysql.connector.connect(

host="localhost".

user="admin".

password="Shopping".

database="Keeper"

)

# Creating a cursor object using the

cursor() method

cursor = conn.cursor( )

# SOL query to update the quantity of the

records with item code 111

update_query = "UPDATE shop SET Qty = 20

WHERE Item_code = 111"

try:

# Executing the SQL command

cursor.execute(update_query)

# Commit the transaction

conn.commit()

# Print the number of rows affected

print(cursor,rowcount, "record(s)

updated successfully")

cept mysql.connector.Error as err:

# Rollback the transaction in case of

an error

conn.rollback( )

print("Error:". err)

finally:

conn.close( )

import mysql.connector

# Establish connection to the MySQL database

conn = mysql.connector.connect(

host="localhost",

user="root",

password="airplane",

database="Travel"

)

# Create a cursor object

cursor = conn.cursor()

# Execute the SQL query to fetch all

details from the flight table

query = "SELECT * FROM flight"

cursor.execute(query)

# Fetch all rows from the executed query

rows = cursor.fetchall( )

# Print each row

for row in rows:

print(row)

# Close connection

conn.close( )

import mysql.connector as mycon

def Add_Item( ):

mycon=mycon.connect(host="localhost".

user="root", passwd="Pencil",

database="ITEMOB")

mycur= mycon.cursor()

itmno-input("Enter Item Number: ")

itmnm=input("Enter Item Name: ")

itmpr=input("Enter price: ")

itmqty=input("Enter qty: ")

query="INSERT INTO stationery VALUES

({}.(1'.{ ).( } )"

query=query.format(itmno, itmnm, itmpr,

itmqty)

mycur.execute(query)

mycon.commit( )

mycur.execute("SELECT * FROM stationery

WHERE price>120")

for rec in mycur:

print(rec)

mycon.close( )

import mysql.connector

# Establish the connection

connection = mysql.connector.connect(

host="your_host", # e.g., "localhost"

user-"your_user", # e.g.. "root"

password="your_password", # your MySQL

password

database="your_database" # your

database name

)

cursor = connection.cursor()

# SQL query to insert data

insert_query = ""

INSERT INTO Sales (ORDNUMB, PARTNUMB,

NUMBORD, QUOTPRIC)

VALUES (%s, %s, %s, %s)

# Data to be inserted

data = [

(12494, "СВОЗ", 4, 175.00).

(12495, "CX11", 2. 57.95).

(12498, "AZ52", 2, 22.95),

(12500, "ВТО4", 1, 402.99)

]

# Inserting data

cursor.executemany(insert_query, data)

# Commit changes to the database

connection.commit()

# Close the connection

cursor.close( )

connection.close( )

print("Data inserted successfully.")

import mysql.connector

# Establish the connection

conn = mysql.connector.connect(

host="localhost",

user="your_username",

password="your_password",

database="your_database"

)

# Create a cursor object

cursor = conn.cursor()

# Create the Faculty table

cursor.execute("""

CREATE TABLE Faculty (

Id INT PRIMARY KEY,

LastName VARCHAR(50),

FirstName VARCHAR(50),

LocationId INT,

Phone VARCHAR(15),

Rank ENUM('ASSO', 'FULL', 'ASST', ' INST'),

StartDate DATE

)

""")

# Commit and close the connection

conn.commit ( )

cursor.close( )

conn.close( )

import mysql.connector

# Establishing the connection to the MySOL

database

connection = mysql.connector.connect(

host="localhost".

# Replace with your MySQL host

user="root",

# Replace with your MySQL username

password="password".

# Replace with your MySQL password

database="your_database"

# Replace with your database name

)

cursor = connection.cursor()

# SQL query to create the table

create_table_query = ""

CREATE TABLE SalesRepresentatives (

SLSRNUMB INT PRIMARY KEY,

SLSRNAME VARCHAR(100),

SLSRADDR VARCHAR(255),

TOTCOMM DECIMAL(10, 2),

COMMRATE DECIMAL(5, 2)

)

# Executing the query

cursor.execute(create_table_query)

# Committing changes and closing the

connection

connection.commit( )

cursor.close( )

connection.close( )

print("Table 'SalesRepresentatives'

created successfully.")

(i) One record

(ii) tuple

The difference between fetchone( ) and fetchall( ) methods:

fetchone( )	fetchall( )
Fetches a single record from the result set.	Fetches all records from the result set.
Returns a tuple representing a single row.	Returns a list of tuples, each representing a row.
Example Cursor.execute("SELECT * FROM students") row = Cursor. fetchone( )print(row)	Example cursor.execute("SELECT * FROM students") rows = cursor.fetchall ( ) print(rows)

10.

Statement1 conl.cursor( )

Statement2 mycursor.execute(querry)

Statement3 conl.commit( )

Statement1 con1.cursor( )

Statement2 mycursor.execute("SELECT * FROM student WHERE marks>75")

Statement3 mycursor.fetchall( )

11.

Statement 1→mysql .connector

Statement 2→mycursor.execute ("DELETE FROM Employee WHERE E code= 'E101' ")

Statement 3→ mydb. commit( ) Or

Statement 1→mysql. connector

Statement 2→mycursor.execute ("SELECT * FROM Employee WHERE City 'Delhi' ")

Statement 3 → mycursor. fetchal]( )

12.

import mysql.connector

# Establish connection to MySQL

conn= mysql.connector.connect

(

host="localhost",

user="your_username",

password="your_password".

database="your_database"

)

cursor = conn.cursor()

# Create table

cursor.execute

(""" CREATE TABLE Hospital

(

Hospital_id INT PRIMARY KEY,

Hospital_name VARCHAR(255),

Bed_Count INT

) """)

# Commit changes and close connection

conn.commit( )

cursor.close( )

conn.close( )

13.

import mysql.connector

# Connect to the database

conn = mysql.connector.connect

(

host="localhost".

# Replace with your host

user="manager",

# Replace with your username

password="wel cometostore123".

# Replace with your password

database="Inventory"

# Database name

)

# Check connection

if conn.is_connected( ) :

print("Connected to the database")

# Close the connection

conn.close( )

14.

import mysql.connector

con = mysql.connector.connect

(host = "localhost", user = "root".

passwd = "arihant".

database = "PythonDB")

cursor = con.cursor( )

try :

cursor.execute ("SELECT * FROM Student")

display = cursor.fetchall( )

for i in display :

print(i)

except :

con.rollback ( )

con.close( )

Output

(Riya', 27, 12, 'Delhi, 73, 15)
(Kiran', 22, 12, 'Pune, 88, 15)
('Nisha', 28, 12, Meerut', 95, 16)

15.

import mysql.connector

con = mysql.connector.connect

(host = "localhost", user = "root",

passwd = "arihant",database = "PythonDB")

cursor = con.cursor()

try:

size = int(input("Enter the value of

numbers of rows."))

cursor.execute("SELECT*FROM Student")

display = cursor.fetchmany(size)

for i in display:

print (i)

except:

con.roll'back( )

con.close( )

Output

Enter the value of no. of rows :2

(Riya', 27, 12, 'Delhi, 73, 15)

('Kiran', 22, 12, 'Pune', 88, 15)

16.

import mysql.connector

con = mysql.connector.connect

(host = "localhost",

user = "root".

passwd = "arihant",database = "PythonDB")

cursor = con.cursor()

try:

cursor.execute ("SELECT Name,

RollNo. Address. Percentage FROM

Student")

display = cursor.fetchone( )

print(display)

except:

con.rollback( )

con.close( )

Output

(Riya', 27, 'Delhi', 73)

17.

import mysql.connector

con = mysql.connector.connect

(host = "localhost", user = "root".

passwd = "arihant",

database = "PythonDB")

cursor = con.cursor()

try:

cursor.execute ("DELETE FROM Student

WHERE RollNo - 33")

con.commit( )

except:

con.rollback( )

con.close( )

In Python Shell,

>>>SELECT *FROM Student;

Name	RollNo	Class	Address	Percentage	Age
Riya	27	12	Delhi	73	15
Kiran	22	12	Pune	88	15
Nisha	28	12	Meerut	95	16

18.

import mysq1.connector

con = mysql.connector.connect

(host = "localhost", user = "root".

passwd = "arihant".

database = "PythonDB")

cursor = con.cursor()

try :

cursor.execute ("UPDATE Student SET

Class = 12 WHERE RollNo = 22")

con.commit( )

except :

con.rollback( )

con.close( )

In Python Shell,

>>>>SELECT *FROM Student;

Name	RollNo	Class	Address	Percentage	Age
Riya	27	12	Delhi	73	15
Ayush	33	11	Delhi	97	14
Kiran	22	12	Pune	88	15
Nisha	28	12	Meerut	95	16

Long Answer Type Questions

6 x 5 = 30

19.

(i) import mysql.connector as mydb

mycon = mydb.connect

(host = "localhost",

user = "root".

passwd = "system",

database = "Admin")

cursor = mycon.cursor ( )

sql = SELECT FROM MobileMaster

WHERE M_Price > 8000"

try:

cursor.execute (sql)

display = cursor. fetchall ( )

for i in display:

print(i)

mycon.close ( )

(ii) import mysql.connector as mydb

mycon = mydb.connect

(host = "localhost",

user = "root",

passwd = "system".

database = "Admin")

cursor = mycon.cursor ()

sql = "UPDATE MobileMaster SET M_Price

= M_Price + 2000

WHERE M_Company = 'Samsung'"

cursor.execute (sql)

mycon.commit ()

print("Price updated successfully,")

mycon.close( )

20.

import mysql.connector

connection =

mysql.connector.connect(host='localhost',

user='admin_user',

password='warehouse2024',database='Wareho

useDB')

cursor = connection.cursor( )

update_query = "UPDATE product_inventory

SET Quantity = 91 WHERE Item_code = 208"

cursor.execute(update_query)

connection.commit( )

print("Data updated successfully.")

cursor.close( )

connection.close( )

21.

import mysql.connector

my = mysql.connector.connect

host = "localhost", user = "Root",

passwd = "System", database = "Test")

cursor = my.cursor( )

db=cursor.execute ( ""CREATE TABLE Watches

(Watch_Id varchar(5) Primary Key,

WatchName char(20) NOT NULL,

Price float, Type varchar (20),

Qty_store Int(5) NOT NULL)""")

sq1 = "INSERT INTO Watches

(WatchId, WatchName, Price, Type,

Qty_store) VALUES (%s, %s, %s, %s, %s)"

val = [("W001", "High Time", 10000,

"Unisex", 100),

("W002". "Life Time", 15000,

"Ladies", 150),

("W003", "Wave", 20000,

"Gents", 200),

("W004", "HighFashion", 7000,

"Unisex", 250),

("W005", "Golden Time", 25000,

"Gents", 100)]

try:

cursor. executemany (sql, val)

my.commit ( )

except :

my.rollback ( )

my.close ( )

22.

(i) import mysql.connector as mydb

con = mydb.connect (

host = "localhost".

user = "root".

passwd = "admin@123".

database = "Management")

cursor = con.cursor ( )

sql =" INSERT INTO College ( No, Name, Age, Department, DateofJoin,

Basic, Sex) VALUES (15, 'Atin', 27, 'Physics', '15/05/02, 8500, 'M' )"cursor.execute (sql) con.close( )

(ii) import mysql.connector as mydb

Con = mydb.connect

(host = "localhost" ,

user = "root",

passwd = "admin@123",

database = "Management")

Cursor = con.cursor( )

try:

cursor.execute ("DELETE FROM College WHERE Name = Viren' " )

con.commit ( )

except:

con.roll back ( )

Con.close ( )

23.

import mysql.connector as mysql

con-mysql .connect(host="localhost",user="root",

password="tiger". database="SCHOOL")

cursor = con.cursor( )

rno = int(input("Enter the Roll Number: "))

name = input("Enter the name: ")

DOB = input("Enter date of birth: ")

fee - float(input("Enter Fee: "))

query="INSERT INTO Student VALUES

({ }.'{ }'.'{ }'.{ })".format(rno,name,DOB,fee)

cursor.execute(query)

con.commit( )

print("Data entered successfully !!!")

con.close( )

24.

import mysql.connector

# Establish connection

con = mysql.connector.connect(

host="localhost",

user="admin",

password="root".

database="SCHOOL"

)

# Create cursor object

cursor = con.cursor()

# Execute query

query = "SELECT * FROM Account WHERE

Fees < 5000*

cursor.execute(query)

# Fetch and display records

records = cursor.fetchall( )

for row in records:

print(row)

# Close connection

con.close( )

Case Study Questions

3 x 4 = 12

25.

(i) (C) INSERT INTO statement is used to add new records in a table. For this you need to specify table name, column names and the values. But if you pass values of a record in the same order of the columns of the table, then you don't need to give column names.

(ii) (C) The rows having STUID 1101 and 1105, both match the condition given with WHERE clause.

(iii) (A) The fetchone( ) method fetches next row from the result set and returns it as a tuple.

(iv) (B) The fetchmany(n) method fetches the given number of records from the cursor position.

(v) (B) The records of students with Stuld 1101 and 1105 are the two rows that meets the criteria given with WHERE clause.

26.

(i) (D) The syntax of connect function is as follows:

connect(host=<host-name>. user=<username>. passwd=<password>. database=<database>)

(ii) (B) The sql query "UPDATE BOOK set Status =ISSUE where Bookld="%s' "I(1021") will execute and the status of record with bookld=%s' " /("1021") will be updated as 'Issue.

(iii) (C) The try block lets you test a block of code for errors and the except block lets you handle the error.

(iv) (C) If the connection to a database is closed by the user with the close() method, any outstanding transactions are rolled back by the database.

(v) (A) When the ROLLBACK command is executed, the changes made to database files, updates, additions, or deletions since the commitment boundary are rolled back and the original entries are put back in the files.

27.

(i) (B) The connect( ) is Constructor creates a connection to the MySQL server and returns a MySQLConnection object.

(ii) (A) Cursor is a special control structure that created to get access of all the records retrieved as per query.

(ii) (C) The executemany( ) method is used to insert multiple rows in a table.

Syntax cursor object.executemany(statement. arguments)

Where statement refers to the string containing the query to execute and arguments refers to the sequence containing values to use within insert statement.

(iv) (C) The rowcount( ) method is used to get number of records affected by query involving any Delete, update & insert command.

(v) (D) close() is used when you are done using a cursor, This method closes the cursor, resets all results, and ensures that the cursor object has no reference to its original connection object.