[Link] Sr. Sec.

School, Bhopal

Patel Nagar,[Link].16, Piplani P.O. Bhopal

COMPUTER SCIENCE PRACTICAL FILE
FOR
THE PARTIAL FULFILLMENT OF
AISSCE – 2024 – 25

Submitted by: Submitted to:

________________ Ms. Gargee Chattopadhyay
Class – XII PGT (Comp. Sci.)
Section – ______
Board Roll No. _______
 ST. MONTFORT SR. SEC.
SCHOOL
Patel Nagar,[Link].16, Piplani P.O. Bhopal
Affiliated to CBSE Delhi,No.1030149

DEPARTMENT OF COMPUTER SCIENCE

 CERTIFICATE
This is to certify that ___________________, a student of Class XII has

successfully completed all Python and SQL Assignments, under the guidance

of Ms. Gargee Chattopadhyay during the academic session 2024 - 25 in partial

fulfilment of AISSCE 2024-25 practical examination conducted by CBSE, New

Delhi.

Signature of Principal

Signature of Examiner Signature of Teacher

Page | 2
 ACKNOWLEDGMENT

I would like to convey my heartfelt gratitude to Ms. Gargee

Chattopadhyay, my CS teacher for her tremendous support
and assistance in the completion of my project/practical file.
I would also like to thank our Principal, Rev. Bro. Monachan,
for providing me with this wonderful opportunity to work on
a project which covers most aspects of this domain. The
completion of the project would not have been possible
without their help and insights.

Name : ______________________

Page | 3
 TABLE OF CONTENT

Q.1. TEXT FILE WORDS DISPLAYED WITH # ............................................................................. 5

Q.2. TEXT FILE STATISTICS............................................................................................................. 6
Q.3. TEXT FILE STORE ITEM RECORDS ....................................................................................... 7
Q.4. REMOVE ALL LINES THAT CONTAIN ‘A’ ......................................................................... 8
Q.5. BINARY FILE – SEARCH NAME WITH ROLLNO. ........................................................... 10
Q.6. BINARY FILE – ENTER ROLLNO AND UPDATE MARKS ............................................. 12
Q.7. CSV FILE – SEARCH PASSWORD WITH USERID ............................................................ 16
Q.8. ADD LIST ELEMENTS ............................................................................................................ 18
Q.9. STACK OF BOOKS ................................................................................................................... 20
Q.10. PUSH ELEMENTS FROM LIST TO STACK ....................................................................... 22
Q.11. STACK AS LIST ...................................................................................................................... 23
Q.12. PUSH ELEMENTS FROM TUPLE ....................................................................................... 25
Q.13. RANDOM NO. GENERATOR ............................................................................................. 27
Q.14. PY-MYSQL CONNECTIVITY PROGRAM ......................................................................... 28
Q.15. PYTHON-MySQL CONNECTIVITY : EMPLOYEE .......................................................... 31
Q.16. PYTHON-MySQL CONNECTIVITY : PRODUCT ............................................................ 35
Q.17. PYTHON-MySQL CONNECTIVITY : GARMENT ........................................................... 39
Q.18. SQL Queries - COMPANY and CUSTOMER tables.......................................................... 42
Q.19. SQL Queries - ITEMS and TRADERS tables ....................................................................... 46
Q.20 : SQL Queries - SHOP and ACCESSORIES tables .............................................................. 49
Q.21: SQL Queries : VEHICLE and TRAVEL tables .................................................................... 52
Q.22: SQL Queries : SCHOOL and ADMIN tables ...................................................................... 56

Page | 4
Q.1. TEXT FILE WORDS DISPLAYED WITH #
Q.1. Read a text file line by line and display each word
separated by a #.

Program :-

myfile=open("[Link]","r")

line=[Link]() # read first line

while line:

words=[Link]()

for x in words:

print(x,end="#")

line=[Link]() # read next line

Output :-
Aldebaran#is#the#brightest#star#in#the#zodiac#constellatio
n#of#Taurus.#It#is#located#at#a#distance#of#approximately#
65#light-
years#from#the#Sun.#The#star#lies#along#the#line#of#sight#
to#the#nearby#Hyades#cluster.#

Page | 5
Q.2. TEXT FILE STATISTICS
[Link] a text file and display the number of
vowels/consonants/uppercase/lowercase characters in the
file.

Program :-

myfile=open("[Link]","r")
content=[Link]()

up=low=vow=cons=0
for ch in content:
if [Link]():
up+=1
elif [Link]():
low+=1

if [Link]() in 'aeiou':
vow+=1
else:
cons+=1

print("Vowels=",vow)
print("Consonants=",cons)
print("Uppercase=",up)
print("Lowercase=",low)

[Link]()

Output :-

Consonants= 153

Uppercase= 6

Lowercase= 164

Page | 6
Q.3. TEXT FILE STORE ITEM RECORDS
[Link] a text file programmatically which stores the
records (itemid,item_description,price) of 5 items.

Program :-

myfile=open("[Link]","w")
n=int(input("How many items?"))
item_records=""
for i in range(n):
itemid=input("ItemID>>")
item_name=input("Item description>>")
price=float(input("Price>>"))
item_records+=itemid+"\t"+item_name+"\t"+str(price)+"\n"
[Link](item_records)
[Link]()

Output :-

How many items?5 ItemID>>I4

ItemID>>I1 Item description>>Cereals

Item description>>Muffins Price>>65

Price>>45 ItemID>>I5

ItemID>>I2 Item description>>Oil

Item description>>Oats Price>>80

Price>>100 File content:-

I1 Muffins 45.0
ItemID>>I3 I2 Oats 100.0
I3 Rice 150.0
Item description>>Rice I4 Cereals 65.0
I5 Oil 80.0
Price>>150

Page | 7
Q.4. REMOVE ALL LINES THAT CONTAIN ‘A’
Q.4. Remove all the lines that contain the character 'a'
in a file and write it to another file.

Program :-

fin=open("[Link]","r") # read

fout=open("poem_no_a.txt","w") # write

content=[Link]()

no_a_lines=list()

for line in content:

if 'a' not in line:

no_a_lines.append(line)

[Link](no_a_lines)

[Link]()

[Link]()

Output :-

[Link]

I have come to the borders of sleep,

The unfathomable deep

Forest where all must lose

Their way, however straight,

Or winding, soon or late;

They cannot choose.

Many a road and track

That, since the dawn’s first crack,

Up to the forest brink,

Page | 8
Deceived the travellers,

Suddenly now blurs,

And in they sink.

poem_no_a.txt

Up to the forest brink,

Suddenly now blurs,

And in they sink.

Page | 9
Q.5. BINARY FILE – SEARCH NAME WITH ROLLNO.
Q.5. Create a binary file with name and roll number.
Search for a given roll number and display the name, if
not found display appropriate message.

Program :-

import pickle

# write records to binary file

myfile=open("stud_info.dat","ab+")

n=int(input("How many records?"))

for i in range(n):

rollno=int(input("Enter rollno.:"))

sname=input("Enter name of student:")

srecord=[rollno,sname]

[Link](srecord,myfile)

[Link]()

[Link](0)

# searching for name using rollno.

rollno=int(input("What is the rollno?"))

try:

while True:

srec=[Link](myfile)

if srec[0]==rollno:

Page | 10
 print("Rollno.=",srec[0],"\nStudent
name=",srec[1])

break

except EOFError:

print("Record not found!")

[Link]()

Output :-

How many records?2

Enter rollno.:65

Enter name of student:Mani Shankar

Enter rollno.:47

Enter name of student:S Mohan Kumar

What is the rollno?65

Rollno.= 65

Student name= Mani Shankar

Page | 11
Q.6. BINARY FILE – ENTER ROLLNO AND UPDATE MARKS
Q.6. Create a binary file with roll number, name and
marks. Input a roll number and update the marks.

Program :-

import pickle

myfile=open("student_details.dat","ab+")

n=int(input("How many records?"))

for i in range(n):

rollno=int(input("Enter rollno.:"))

sname=input("Enter name :")

marks=float(input("Enter marks:"))

srec=[rollno,sname,marks]

[Link](srec,myfile)

[Link]()

[Link](0)

stable=list()

try:

while True:

srec=[Link](myfile)

[Link](srec)

except EOFError:

pass

Page | 12
[Link]()

# display all records

n=len(stable)

print("Rollno\tStudent name\tMarks")

for i in range(n):

print(stable[i][0],"\t",stable[i][1],"\t",stable[i][2])

# search for record

rollno=int(input("Enter rollno. of student whose marks are

to be updated:"))

found=False

for i in range(n):

if rollno==stable[i][0]:

print("Record found!")

print(stable[i][0],"\t",stable[i][1],"\t",stable[i][2])

new_marks=float(input("Enter new value for

marks:"))

stable[i][2]=new_marks

found=True

break

else:

print("Record not found!")

Page | 13
# Write updated table to file

if found:

myfile=open("student_details.dat","wb")

for i in range(n):

print(stable[i])

[Link](stable[i],myfile)

[Link]()

print("Record updated!")

Output :-

How many records?5

Enter rollno.:1

Enter name :Sameer Sharma

Enter marks:65.5

Enter rollno.:2

Enter name :Raghuvindra K

Enter marks:54

Enter rollno.:3

Enter name :Rama Gupta

Enter marks:87

Enter rollno.:4

Enter name :Mohan Kumar

Enter marks:90.5

Enter rollno.:5

Page | 14
Enter name :Rajesh Kumar

Enter marks:67.3

Rollno Student name Marks

1 Sameer Sharma 65.5

2 Raghuvindra K 54.0

3 Rama Gupta 87.0

4 Mohan Kumar 90.5

5 Rajesh Kumar 67.3

Enter rollno. of student whose marks are to be updated:2

Record found!

2 Raghuvindra K 54.0

Enter new value for marks:56

[1, 'Sameer Sharma', 65.5]

[2, 'Raghuvindra K', 56.0]

[3, 'Rama Gupta', 87.0]

[4, 'Mohan Kumar', 90.5]

[5, 'Rajesh Kumar', 67.3]

Record updated!

Page | 15
Q.7. CSV FILE – SEARCH PASSWORD WITH USERID
Q.7. Create a CSV file by entering user-id and password,
read and search the password for given userid.

Program :-

import csv

header=['user_id','password']

row=list()

with open("login_info.csv","a",newline="") as myfile:

login_writer=[Link](myfile)

login_writer.writerow(header) # writer header row

n=int(input("How many user-ids?"))

for i in range(n):

user_id=input("Enter user-id:")

password=input("Enter password:")

row=[user_id,password]

login_writer.writerow(row)

with open("login_info.csv","r") as myfile:

login_reader=[Link](myfile)

uid=input("Enter user id:")

for row in login_reader:

if uid==row[0]:

print("User_id=",row[0],", password=",row[1])

Output :-

Page | 16
How many user-ids?5

Enter user-id:abigaila407

Enter password:mccabe

Enter user-id:jacobc1041

Enter password:jocy54

Enter user-id:galvang405

Enter password:mocy47

Enter user-id:vixtor57

Enter password:pls74

Enter user-id:melanie78

Enter password:785k

Enter user id:galvang405

User_id= galvang405 , password= mocy47

Page | 17
Q.8. ADD LIST ELEMENTS
Q.8. Write a program that takes any two lists L and M of
the same size and adds their elements together to form a
new list L whose elements are sums of the corresponding
elements in L and M. For instance, if L=[3,1,4] and
M=[1,5,9], then N should equal [4,6,13].

Program :-

n=int(input("How many elements?"))

L=list()

M=list()

N=list()

for i in range(n):

x=int(input("Enter element for list1:"))

y=int(input("Enter element for list2:"))

[Link](x)

[Link](y)

[Link](x+y)

print("L=",L)

print("M=",M)

print("N=",N)

Output :-

How many elements?6

Enter element for list1:5

Page | 18
Enter element for list2:65

Enter element for list1:8

Enter element for list2:4

Enter element for list1:12

Enter element for list2:34

Enter element for list1:78

Enter element for list2:82

Enter element for list1:15

Enter element for list2:16

Enter element for list1:11

Enter element for list2:17

L= [5, 8, 12, 78, 15, 11]

M= [65, 4, 34, 82, 16, 17]

N= [70, 12, 46, 160, 31, 28]

Page | 19
Q.9. STACK OF BOOKS
Q.9. Write a Python program to implement a stack of books
named “book_stack” and push book items to this stack. Each
book item consists of BookID,BookTitle and Price. Also pop the
book elements and display them. Display “Stack Empty” if the
stack is empty.

Program :-

book_stack=list()

book_item=list()

# book_item contains BookID,BookTitle and Price

# pushing book elements

ans='y'

while ans=='y':

bookid=int(input("Enter book-id:"))

btitle=input("Enter book title:")

price=float(input("Enter price of 1 book:"))

book_item=[bookid,btitle,price]

book_stack.append(book_item)

ans=input("Continue pushing books? y/n:")

# popping book elements

print("popping book elements...")

while book_stack:

print(book_stack.pop(),end="\t")

else:

print("Stack Empty")

Output :-
Page | 20
Enter book-id:1

Enter book title:The Great Gatsby

Enter price of 1 book:450

Continue pushing books? y/n:y

Enter book-id:2

Enter book title:Pride and Prejudice

Enter price of 1 book:600

Continue pushing books? y/n:y

Enter book-id:3

Enter book title:1984

Enter price of 1 book:500

Continue pushing books? y/n:y

Enter book-id:4

Enter book title:War and Peace

Enter price of 1 book:550

Continue pushing books? y/n:y

Enter book-id:5

Enter book title:Anna Karennina

Enter price of 1 book:660

Continue pushing books? y/n:n

popping book elements...

[5, 'Anna Karennina', 660.0] [4, 'War and Peace', 550.0]

[3, '1984', 500.0] [2, 'Pride and Prejudice', 600.0]
[1, 'The Great Gatsby', 450.0] Stack Empty

Page | 21
Q.10. PUSH ELEMENTS FROM LIST TO STACK
Q.10. Write a Python program to push all the elements at odd
locations (index) to a stack from the list
colors=['black','cyan','magenta','yellow','purple','green','re
d','blue'].

Program :-

stack=list()

colors=['black','cyan','magenta','yellow','purple','green','re
d','blue']

for i in range(len(colors)):

if i%2!=0:

[Link](colors[i])

print("List=",colors)

print("Stack containing list elements at odd index=",stack)

Output :-

List= ['black', 'cyan', 'magenta', 'yellow', 'purple',

'green', 'red', 'blue']

Stack containing list elements at odd index= ['cyan',

'yellow', 'green', 'blue']

Page | 22
Q.11. STACK AS LIST
[Link] a Python program to push some elements to a stack
implemented as a list and display them. Also pop the elements
from the stack and display them. If no more elements are left,
then display “Stack Empty”.

Program :-

stack=list()

# pushing elements to stack

ans='y'
while ans=='y':
element=input("Enter an element to push:")
[Link](element) # push
ans=input("Do you want to push more? y/n:")

# display stack
print(stack)

# pop elements
print("Elements popped=")
while stack:
print([Link]())
else:
print("Stack empty")

Output :-

Enter an element to push:Frozen Fruit

Do you want to push more? y/n:y

Enter an element to push:Whole Grain Crackers

Do you want to push more? y/n:y

Enter an element to push:Whole Wheat Pasta

Do you want to push more? y/n:y

Enter an element to push:Salsa

Do you want to push more? y/n:y

Page | 23
Enter an element to push:Pasta Sause

Do you want to push more? y/n:n

['Frozen Fruit', 'Whole Grain Crackers', 'Whole Wheat Pasta',

'Salsa', 'Pasta Sause']

Elements popped=

Pasta Sause

Salsa

Whole Wheat Pasta

Whole Grain Crackers

Frozen Fruit

Stack empty

Page | 24
Q.12. PUSH ELEMENTS FROM TUPLE
[Link] a Python program to read a tuple from the user
which contains some numeric elements. Now define a function
named push_even() which will push all the even numbers in this
tuple to a stack. Also define a function pop_even() which will
pop the elements from this stack. For example, if the tuple is
(23,45,12,11,7,5,9,8,34,32,77,78), then the stack should
contain [12,8,34,32,78] and while popping the output should be
78 32 34 8 12 StackEmpty.

Program :-

def push_even(x):
for element in x:
if element%2==0:
stack_even.append(element)

def pop_even():
print("Popping elements from stack=>")
while stack_even:
print(stack_even.pop())
else:
print("StackEmpty")

# _main_
stack_even=list()

tup_elements=eval(input("Enter a tuple containing numeric

values:"))

# pushing even elements to stack

push_even(tup_elements)

# display stack
print(stack_even)

# popping stack elements

pop_even()

Output :-

Enter a tuple containing numeric

values:(45,46,7,8,12,1,36,34,23,14,8,72)

Page | 25
[46, 8, 12, 36, 34, 14, 8, 72]

Popping elements from stack=>

72

14

34

36

12

46

StackEmpty

Page | 26
Q.13. RANDOM NO. GENERATOR
Q.13. Write a random number generator that generates random
numbers between 1 and 6 (simulates a dice).

Program :-

import random

ans='y'

while ans=='y':

num=[Link](1,6)

print("Dice rolled=",num)

ans=input("Roll the dice again? y/n")

Output :-

Dice rolled= 3

Roll the dice again? y/ny

Dice rolled= 5

Roll the dice again? y/ny

Dice rolled= 2

Roll the dice again? y/ny

Dice rolled= 4

Roll the dice again? y/nn

Page | 27
Q.14. PY-MYSQL CONNECTIVITY PROGRAM
[Link] a Python-MySQL connectivity program to do the
following :-
a. Insert new records into the Student table
(rollno,sname,marks).
b. Display records.
c. Search for a student based on rollno.
d. Display the student record with highest marks.

Program :-

import [Link]

con=[Link](host="localhost",user="root",pas
swd="1234",database="cbseexamdb")
scursor=[Link]()

# inserting student records

n=int(input("How many records to be inserted?"))

for i in range(n):
rollno=int(input("Enter rollno.:"))
sname=input("Enter name of student:")
marks=float(input("Enter marks:"))
sql="insert into Student
values({},'{}',{})".format(rollno,sname,marks)
[Link](sql)
[Link]()

# display all records

sql="select * from Student"
[Link](sql)
student_set=[Link]()
print("Rollno\tStudent name\t\tMarks") # header row
for record in student_set:
print(record[0],"\t",record[1],"\t\t",record[2])

# search for a student based on rollno

rollno=int(input("Enter roll no. of student to be
searched:"))
sql="select * from Student where rollno=%s" %(rollno,)
[Link](sql)
student_set=[Link]()
for record in student_set:
print("Rollno\tStudent name\t\tMarks") # header row
print(record[0],"\t",record[1],"\t\t",record[2]) # tuple
break

Page | 28
else:
print("Record not found!")

# display student record with highest marks

max_mark=None
sql="select * from Student where marks=(select max(marks)
from Student)"
[Link](sql)
student_set=[Link]()
print("Students who obtained highest marks=")
print("Rollno\tStudent name\t\tMarks") # header row
for record in student_set:
print(record[0],"\t",record[1],"\t\t",record[2])

# close connection
[Link]()

Output :-

How many records to be inserted?2

Enter rollno.:6

Enter name of student:Perlin

Enter marks:63.5

Enter rollno.:7

Enter name of student:S Jeeva

Enter marks:78

Rollno Student name Marks

1 Seema 45.50

2 Caren 65.50

3 Devansh 70.00

4 Mohan 75.50

5 Reema 80.50

6 Perlin 63.50

Page | 29
7 S Jeeva 78.00

Enter roll no. of student to be searched:5

Rollno Student name Marks

5 Reema 80.50

Students who obtained highest marks=

Rollno Student name Marks

5 Reema 80.50

Page | 30
Q.15. PYTHON-MySQL CONNECTIVITY : EMPLOYEE
Q.15. Write a Python-MySQL connectivity program to do the
following :-
a. Insert new records into the Employee table
(empno,ename,dept,salary).
b. Ask from the user any department and display all
employee records belonging to that department.
c. Search for an employee based on empno and update his
salary.
d. Display all the employee records.

Program :-

import [Link]

con=[Link](host="localhost",user="root",passw
d="1234",database="cbseexamdb")

ecursor=[Link]()

# inserting employee records

n=int(input("How many records to be inserted?"))

for i in range(n):

empno=int(input("Enter employee no.:"))

ename=input("Enter employee name :")

dept=input("Enter department:")

salary=float(input("Enter salary:"))

sql="insert into Employee

values({},'{}','{}',{})".format(empno,ename,dept,salary)

[Link](sql)

[Link]()

# display all records

sql="select * from Employee"

[Link](sql)

student_set=[Link]()
Page | 31
print("Empno\tEmployee name\t\tDepartment\t\tSalary") # header
row

for record in student_set:

print(record[0],"\t",record[1],"\t\t\t",record[2],"\t\t",recor
d[3])

# ask from user any dept and display all employee records in
that department

dept=input("Enter any department:")

sql="select * from Employee where dept='{}'".format(dept)

[Link](sql)

emp_set=[Link]()

print("Empno\tEmployee name\t\tDepartment\t\tSalary") # header

row

for record in emp_set:

print(record[0],"\t",record[1],"\t\t",record[2],"\t\t",record[
3])

if not emp_set:

print("No employees in",dept,"department")

# Search for an employee based on empno and update his salary.

empno=int(input("What is the empno of Employee whose salary is

to be updated?"))

salary=float(input("Enter revised salary:"))

sql="update Employee set salary={} where

empno={}".format(salary,empno)

[Link](sql)

Page | 32
[Link]()

print("Salary updated!")

# close connection

[Link]()

Output :-

How many records to be inserted?3

Enter employee no.:109

Enter employee name :Sameer Sharma

Enter department:IT

Enter salary:50000

Enter employee no.:110

Enter employee name :Zarina Khan

Enter department:Production

Enter salary:40000

Enter employee no.:111

Enter employee name :Pratiksha Kanwar

Enter department:Purchase

Enter salary:23000

Empno Employee name Department Salary

101 Amit Raj Sales 50000

102 Ashish Singh Purchase 45000

103 Dilawar Khan Sales 30000

104 Rehan Khan Finance 40000

Page | 33
105 Milan Singh Purchase 12000

106 Ravi Kumar Finance 15000

107 Jeevan Kumar Sales 15000

108 Neha Bhasin Purchase 16000

109 Sameer Sharma IT 50000

110 Zarina Khan Production 40000

111 Pratiksha Kanwar Purchase 23000

Enter any department:Purchase

Empno Employee name Department Salary

102 Ashish Singh Purchase 45000

105 Milan Singh Purchase 12000

108 Neha Bhasin Purchase 16000

111 Pratiksha Kanwar Purchase 23000

What is the empno of Employee whose salary is to be

updated?108

Enter revised salary:17000

Salary updated!

Page | 34
Q.16. PYTHON-MySQL CONNECTIVITY : PRODUCT
Q.16. Write a Python-MySQL connectivity program to do the
following :-
a. Insert new records into the Product table
(prodID,item_name,price).
b. Display the average price of all items.
c. Display the items whose price is more than 500.
d. Update the price of an item after searching based on
prodID.

Program :-

import [Link]

con=[Link](host="localhost",user="root",passw
d="1234",database="cbseexamdb")

pcursor=[Link]()

# inserting product records

n=int(input("How many records to be inserted?"))

for i in range(n):

prod_id=int(input("Enter product ID:"))

item_name=input("Enter item name:")

price=float(input("Enter price:"))

sql="insert into Product

values({},'{}',{})".format(prod_id,item_name,price)

[Link](sql)

[Link]()

# display all records

sql="select * from Product"

[Link](sql)

prod_set=[Link]()

print("Prod ID\tItem Name\t\tPrice") # header row

Page | 35
for record in prod_set:

print(record[0],"\t",record[1],"\t\t",record[2])

# Display the average price of all items.

sql="select avg(price) from Product"

[Link](sql)

prod_set=[Link]()

print("Average price=",prod_set[0])

# Display the items whose price is more than 500.

sql="select * from Product where price>500"

[Link](sql)

prod_set=[Link]()

print("items whose price is more than 500=")

print("Prod ID\tItem Name\t\tPrice") # header row

for record in prod_set:

print(record[0],"\t",record[1],"\t\t",record[2])

if not prod_set:

print("No products whose price exceeds 500")

# Update the price of an item after searching based on prodID.

prod_id=int(input("Enter prod ID to update price:"))

price=float(input("Enter new price:"))

sql="update product set price={} where

prodID={}".format(price,prod_id)

Page | 36
[Link](sql)

[Link]()

print("Price updated!")

# close connection

[Link]()

Output :-

How many records to be inserted?3

Enter product ID:11

Enter item name:Froot Loops

Enter price:80

Enter product ID:12

Enter item name:Dried oatmeal

Enter price:65

Enter product ID:13

Enter item name:Cajun Pepper

Enter price:60

Prod ID Item Name Price

1 Flour 150.00

2 Olive Oil 550.00

3 Pepper 45.00

4 Seasoning 90.00

5 Cheese 560.00

Page | 37
6 Bread 10.00

7 Exotic fruit 650.00

8 Bread Spread 180.00

9 Jam 120.00

10 Mayonnaise 99.00

11 Froot Loops 80.00

12 Dried oatmeal 65.00

13 Cajun Pepper 60.00

Average price= 204.538462

items whose price is more than 500=

Prod ID Item Name Price

2 Olive Oil 550.00

5 Cheese 560.00

7 Exotic fruit 650.00

Enter prod ID to update price:7

Enter new price:655

Price updated!

Page | 38
Q.17. PYTHON-MySQL CONNECTIVITY : GARMENT
Q.17. Write a Python-MySQL connectivity program to do the
following :-
a. Insert new records into the Garment table
(Gcode,Gname,size,colour,price).
b. Display those garment details whose price is in the
range 1000.00 to 1500.00.
c. Display names of those garments that are available in
‘XL’ size.
d. Update the colour of garment whose code is given. Ask
colour and gcode from user.

Program :-

import [Link]

con=[Link](host="localhost",user="root",passwd="12
34",database="cbseexamdb")
gcursor=[Link]()

# inserting garment records

n=int(input("How many records to be inserted?"))

for i in range(n):
gcode=int(input("Enter Garment code:"))
gname=input("Enter Garment name:")
size=input("Enter size (S/M/L/XL/XXL/UXL) :")
color=input("Enter color:")
price=float(input("Enter price:"))
sql="insert into Garment
values({},'{}','{}','{}',{})".format(gcode,gname,size,color,price)
[Link](sql)
[Link]()

# display all records

sql="select * from Garment"
[Link](sql)
garment_set=[Link]()
print("Gcode\tGarment\t\tSize\tColour\t\tPrice")
for record in garment_set:

print(record[0],"\t",record[1],"\t\t",record[2],"\t",record[3],"\t\
t",record[4])

Page | 39
# Display those garment details whose price is in the range 1000.00
to 1500.00
sql="select * from Garment where price between 1000 and 1500"
[Link](sql)
garment_set=[Link]()
print("garment details whose price is in the range 1000.00 to
1500.00=>")
print("Gcode\tGarment\t\tSize\tColour\t\tPrice")
for record in garment_set:

print(record[0],"\t",record[1],"\t\t",record[2],"\t",record[3],"\t\
t",record[4])

if not garment_set:
print("No record found!")

# Display names of those garments that are available in ‘XL’ size

sql="select gname from Garment where size='XL'"
[Link](sql)
garment_set=[Link]()
print("names of those garments that are available in ‘XL’ size=>")
for record in garment_set:
print(record[0])

# Update the colour of garment whose code is given. Ask colour and
gcode from user
gcode=int(input("Enter garment code to update colour:"))
color=input("Enter new garment color:")
sql="update Garment set colour='{}' where
gcode={}".format(color,gcode)
[Link](sql)
[Link]()
print("Colour of garment updated!")

# close connection
[Link]()

Output :-
How many records to be inserted?3
Enter Garment code:118
Enter Garment name:Skirt
Enter size (S/M/L/XL/XXL/UXL) :XXL

Page | 40
Enter color:Red
Enter price:800
Enter Garment code:119
Enter Garment name:Sweater
Enter size (S/M/L/XL/XXL/UXL) :XL
Enter color:Grey
Enter price:400
Enter Garment code:120
Enter Garment name:Waistcoat
Enter size (S/M/L/XL/XXL/UXL) :L
Enter color:Silver
Enter price:500
Gcode Garment Size Colour Price
111 Tshirt XL Red 1400.00
112 Jeans L Blue 1600.00
113 Skirt M Black 1100.00
114 Ladies Jacket XL Blue 4000.00
115 Trousers L Brown 1500.00
116 Ladies Top L Pink 1200.00
117 Suit XL Maroon 1500.00
118 Skirt XXL Red 800.00
119 Sweater XL Grey 400.00
120 Waistcoat L Silver 500.00
garment details whose price is in the range 1000.00 to 1500.00=>
Gcode Garment Size Colour Price
111 Tshirt XL Red 1400.00
113 Skirt M Black 1100.00
115 Trousers L Brown 1500.00
116 Ladies Top L Pink 1200.00
117 Suit XL Maroon 1500.00
names of those garments that are available in ‘XL’ size=>
Tshirt
Ladies Jacket
Suit
Sweater
Enter garment code to update colour:115
Enter new garment color:Black
Colour of garment updated!

Page | 41
Q.18. SQL Queries - COMPANY and CUSTOMER tables
Q.18. Write SQL queries for (i) to (iv) and find outputs for SQL
queries (v) to (viii), which are based on the tables COMPANY and
CUSTOMER.

1. To display those company name which are having prize less than
30000.

mysql> select [Link],price from company,customer where

[Link]=[Link] and price<30000;

+-------+-------+

| name | price |

+-------+-------+

| Onida | 20000 |

| Sony | 25000 |

+-------+-------+

2 rows in set (0.00 sec)

2. To display the name of the companies in reverse alphabetical

order.

mysql> select name from company order by name desc;

Page | 42
 +------------+

| name |

+------------+

| Sony |

| Sony |

| Onida |

| Nokia |

| Dell |

| Blackberry |

+------------+

6 rows in set (0.00 sec)

3. To increase the prize by 1000 for those customer whose name

starts with ‘S’?

mysql> update customer set price=price+1000 where name like

'S%';

Query OK, 2 rows affected (0.09 sec)

Rows matched: 2 Changed: 2 Warnings: 0

mysql> select * from customer;

+--------+----------------+-------+------+------+

| custid | name | price | qty | cid |

+--------+----------------+-------+------+------+

| 101 | Rohan Sharma | 70000 | 20 | 222 |

| 102 | Deepak Kumar | 50000 | 10 | 666 |

| 103 | Mohan Kumar | 30000 | 5 | 111 |

| 104 | Sahil Bansal | 36000 | 3 | 333 |

| 105 | Neha Soni | 25000 | 7 | 444 |

Page | 43
 | 106 | Sonal Aggarwal | 21000 | 5 | 333 |

| 107 | Arun Singh | 50000 | 15 | 666 |

+--------+----------------+-------+------+------+

7 rows in set (0.00 sec)

4. To add one more column totalprice with decimal(10,2) to the

table customer.

mysql> alter table customer add totalprice decimal(10,2);

Query OK, 0 rows affected (0.75 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc customer;

+------------+---------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+------------+---------------+------+-----+---------+-------+

| custid | smallint(6) | YES | | NULL | |

| name | varchar(30) | YES | | NULL | |

| price | bigint(20) | YES | | NULL | |

| qty | smallint(6) | YES | | NULL | |

| cid | smallint(6) | YES | | NULL | |

| totalprice | decimal(10,2) | YES | | NULL | |

+------------+---------------+------+-----+---------+-------+

6 rows in set (0.00 sec)

5. SELECT COUNT(*),CITY FROM COMPANY GROUP BY CITY;

+----------+--------+

| COUNT(*) | CITY |

+----------+--------+

| 3 | Delhi |

Page | 44
 | 1 | Madras |

| 2 | Mumbai |

+----------+--------+

3 rows in set (0.00 sec)

6. SELECT MIN(PRICE), MAX(PRICE) FROM CUSTOMER WHERE QTY>10;

+------------+------------+

| MIN(PRICE) | MAX(PRICE) |

+------------+------------+

| 50000 | 70000 |

+------------+------------+

1 row in set (0.00 sec)

7. SELECT AVG(QTY) FROM CUSTOMER WHERE NAME LIKE '%r%';

+----------+

| AVG(QTY) |

+----------+

| 11.0000 |

+----------+

1 row in set (0.00 sec)

8. SELECT PRODUCTNAME,CITY, PRICE FROM COMPANY, CUSTOMER WHERE

[Link]=[Link] AND PRODUCTNAME='MOBILE';

+-------------+--------+-------+

| PRODUCTNAME | CITY | PRICE |

+-------------+--------+-------+

| Mobile | Mumbai | 70000 |

| Mobile | Mumbai | 25000 |

+-------------+--------+-------+

2 rows in set (0.00 sec)

Page | 45
Q.19. SQL Queries - ITEMS and TRADERS tables
Write SQL queries for (a) to (g) and write the output for the SQL
queries mentioned shown in (hi) to (h4) parts on the basis of table
ITEMS and TRADERS :

1. To display the details of all the items in ascending

order of item names (i.e., INAME).
mysql> select * from items order by iname;
+------+--------------------+------+-------+-----------+-------+
| code | iname | qty | price | company | tcode |
+------+--------------------+------+-------+-----------+-------+
| 1004 | Car GPS System | 50 | 21500 | Geoknow | T01 |
| 1003 | Digital Camera 12X | 160 | 8000 | Digiclick | T02 |
| 1001 | Digital Pad 12i | 120 | 11000 | Xenita | T01 |
| 1006 | LED Screen 40 | 70 | 38000 | Santora | T02 |
| 1005 | Pen Drive 32 GB | 600 | 1200 | Storehome | T03 |
+------+--------------------+------+-------+-----------+-------+
5 rows in set (0.00 sec)

2. To display item name and price of all those items,

whose price is in the range of 10000 and 22000 (both
values inclusive).

mysql> select iname,price from items where price

between 10000 and 22000;
+-----------------+-------+
| iname | price |
+-----------------+-------+
| Digital Pad 12i | 11000 |
| Car GPS System | 21500 |
+-----------------+-------+
2 rows in set (0.00 sec)
3. To display the number of items, which are traded by
each trader.

mysql> select tcode,count(*) from items group by tcode;

+-------+----------+
| tcode | count(*) |

Page | 46
 +-------+----------+
| T01 | 2 |
| T02 | 2 |
| T03 | 1 |
+-------+----------+
3 rows in set (0.00 sec)
4. To display the price, item name and quantity (i.e.,
qty) of those items which have quantity more than 150.

mysql> select iname,price,qty from items where qty>150;

+--------------------+-------+------+
| iname | price | qty |
+--------------------+-------+------+
| Digital Camera 12X | 8000 | 160 |
| Pen Drive 32 GB | 1200 | 600 |
+--------------------+-------+------+
2 rows in set (0.00 sec)
5. To display the names of those traders, who are either
from DELHI or from MUMBAI.

mysql> select tname,city from traders where city in

('Delhi','Mumbai');
+------------------+--------+
| tname | city |
+------------------+--------+
| Electronic Sales | Mumbai |
| Busy Store Corp | Delhi |
+------------------+--------+
2 rows in set (0.00 sec)
6. To display the names of the companies and the names of
the items in descending order of company names.

mysql> select company,iname from items order by company

desc;
+-----------+--------------------+
| company | iname |
+-----------+--------------------+
| Xenita | Digital Pad 12i |
| Storehome | Pen Drive 32 GB |
| Santora | LED Screen 40 |
| Geoknow | Car GPS System |
| Digiclick | Digital Camera 12X |
+-----------+--------------------+
5 rows in set (0.00 sec)
7. Obtain the outputs of the following SQL queries based on the
data given in tables ITEMS and TRADERS above.
o SELECT MAX (PRICE), MIN (PRICE) FROM ITEMS;

+------------+------------+
| MAX(PRICE) | MIN(PRICE) |
+------------+------------+
| 38000 | 1200 |

Page | 47
+------------+------------+
1 row in set (0.00 sec)

o SELECT PRICE*QTY FROM ITEMS WHERE CODE=1004;

+-----------+
| PRICE*QTY |
+-----------+
| 1075000 |
+-----------+
1 row in set (0.00 sec)

o SELECT DISTINCT TCODE FROM ITEMS;

+-------+
| TCODE |
+-------+
| T01 |
| T02 |
| T03 |
+-------+
3 rows in set (0.00 sec)

o SELECT INAME, TNAME FROM ITEMS I, TRADERS T WHERE

[Link]=[Link] AND QTY< 100;

+----------------+------------------+
| INAME | TNAME |
+----------------+------------------+
| Car GPS System | Electronic Sales |
| LED Screen 40 | Disp House Inc |
+----------------+------------------+
2 rows in set (0.00 sec)

Page | 48
Q.20 : SQL Queries - SHOP and ACCESSORIES tables

Write SQL queries for (i) to (iv) and find outputs for SQL queries
(v) to (viii), which are based on the tables SHOP and ACCESSORIES.

(a) Write the SQL queries:

1. To display Name and Price of all the Accessories in ascending
order of their Price.
mysql> select name,price from accessories order by price;
+--------------+-------+
| name | price |
+--------------+-------+
| Mouse | 300 |
| Mouse | 350 |
| Keyboard | 400 |
| Keyboard | 500 |
| Hard Disk | 4500 |
| Hard Disk | 5000 |
| LCD | 5500 |
| LCD | 6000 |
| Mother Board | 12000 |
| Mother Board | 13000 |
+--------------+-------+

Page | 49
10 rows in set (0.00 sec)

2. To display Id and SName of all Shop located in Nehru Place.

mysql> select id,sname from shop where area='Nehru Place';

+------+-------------------+
| id | sname |
+------+-------------------+
| S004 | Geeks Tecno Soft |
| S005 | Hitech Tech Store |
+------+-------------------+
2 rows in set (0.00 sec)

3. To display Minimum and Maximum Price of each Name of

Accessories.

mysql> select min(price),max(price),name from accessories group by

name;
+------------+------------+--------------+
| min(price) | max(price) | name |
+------------+------------+--------------+
| 4500 | 5000 | Hard Disk |
| 400 | 500 | Keyboard |
| 5500 | 6000 | LCD |
| 12000 | 13000 | Mother Board |
| 300 | 350 | Mouse |
+------------+------------+--------------+
5 rows in set (0.00 sec)

4. To display Name, Price of all Accessories and their respective

SName where they are available.

mysql> select name,price,sname from accessories a,shop s where

[Link]=[Link] ;
+--------------+-------+--------------------+
| name | price | sname |
+--------------+-------+--------------------+
| Keyboard | 500 | All Infotech Media |
| Mother Board | 13000 | All Infotech Media |
| Keyboard | 400 | Tech Shop |
| LCD | 6000 | Geeks Tecno Soft |
| LCD | 5500 | Hitech Tech Store |
| Mouse | 350 | Hitech Tech Store |
| Hard Disk | 4500 | Tech Shop |
+--------------+-------+--------------------+
7 rows in set (0.00 sec)

(b) Write the output of the following SQL

1. SELECT DISTINCT NAME FROM ACCESSORIES WHERE PRICE> =5000;

+--------------+
| NAME |
+--------------+

Page | 50
| Mother Board |
| Hard Disk |
| LCD |
+--------------+
3 rows in set (0.00 sec)

2. SELECT AREA, COUNT(*) FROM SHOP GROUP BY AREA;

+-------------+----------+
| AREA | COUNT(*) |
+-------------+----------+
| CP | 2 |
| GK II | 1 |
| Nehru Place | 2 |
+-------------+----------+
3 rows in set (0.00 sec)

3. SELECT COUNT(distinct area) FROM SHOP;

+----------------------+
| COUNT(distinct area) |
+----------------------+
| 3 |
+----------------------+
1 row in set (0.00 sec)

4. SELECT NAME, PRICE*0.05 as 'DISCOUNT' FROM ACCESSORIES WHERE

id IN ('S02','S03');

+--------------+----------+
| NAME | DISCOUNT |
+--------------+----------+
| Keyboard | 25.00 |
| Mother Board | 650.00 |
| Keyboard | 20.00 |
| Hard Disk | 225.00 |
+--------------+----------+
4 rows in set (0.00 sec)

Page | 51
Q.21: SQL Queries : VEHICLE and TRAVEL tables

Write SQL queries for (i) to (iv) and find outputs for SQL queries
(v) to (viii), which are based on the tables VEHICLE and TRAVEL.

Table : VEHICLE

Note:

• PERKS is Freight Charges per kilometer.

• Km is kilometers Travelled
• NOP is number of passangers travelled in vechicle.

1. To display CNO, CNAME, TRAVELDATE from the table TRAVEL in

descending order of CNO.

SQL >

select cno,cname,traveldate from travel order by cno desc;

OUTPUT >

+------+--------------+------------+

| cno | cname | traveldate |

+------+--------------+------------+

| 107 | John Malina | 2015-02-10 |

Page | 52
 | 106 | Ramesh Jaya | 2016-04-06 |

| 105 | Hitesh Jain | 2016-04-23 |

| 104 | Sahanubhuti | 2016-01-28 |

| 103 | Fredrick Sym | 2016-03-21 |

| 102 | Ravi Anish | 2016-01-13 |

| 101 | [Link] | 2015-12-13 |

+------+--------------+------------+

2. To display the CNAME of all customers from the table TRAVEL who
are travelling by vehicle with code V01 or V02.

SQL >

select cname,vcode from travel where vcode in ('V01','V02');

OUTPUT >

+-------------+-------+

| cname | vcode |

+-------------+-------+

| [Link] | V01 |

| Hitesh Jain | V02 |

| Ravi Anish | V02 |

| Ramesh Jaya | V01 |

+-------------+-------+

3. To display the CNO and CNAME of those customers from the table
TRAVEL who travelled between ‘2015-12-31’ and ‘2015-05-01’.

SQL >

select cno,cname,traveldate from travel where traveldate

between '2015-05-01' and '2015-12-31';

OUTPUT >

+------+---------+------------+

| cno | cname | traveldate |

Page | 53
 +------+---------+------------+

| 101 | [Link] | 2015-12-13 |

+------+---------+------------+

4. To display customer name and vehicle type of those customers

who have travelled distance more than 120 KM in ascending order
of NOP.

SQL >

select cname,vehicletype,km,nop from travel t,vehicle v where

[Link]=[Link] and km>120 order by nop;

OUTPUT >

+-------------+---------------+------+------+

| cname | vehicletype | km | nop |

+-------------+---------------+------+------+

| [Link] | Volvo Bus | 200 | 32 |

| Hitesh Jain | AC Deluxe Bus | 450 | 42 |

+-------------+---------------+------+------+

5. SELECT COUNT(*), VCODE FROM TRAVEL GROUP BY VCODE HAVING

COUNT(*) > 1;

OUTPUT >

+----------+-------+

| COUNT(*) | VCODE |

+----------+-------+

| 2 | V01 |

| 2 | V02 |

+----------+-------+

6. SELECT DISTINCT VCODE FROM TRAVEL;

OUTPUT >

+-------+

Page | 54
 | VCODE |

+-------+

| V01 |

| V03 |

| V02 |

| V04 |

| V05 |

+-------+

7. SELECT [Link], CNAME, VEHICLETYPE FROM TRAVEL A, VEHICLE B

WHERE A. VCODE = B. VCODE and KM < 90;

OUTPUT >

+-------+-------------+---------------+

| VCODE | CNAME | VEHICLETYPE |

+-------+-------------+---------------+

| V02 | Ravi Anish | AC Deluxe Bus |

| V04 | John Malina | SUV |

+-------+-------------+---------------+

8. SELECT CNAME, KM*PERKM FROM TRAVEL A, VEHICLE B WHERE [Link] =

[Link] AND A. VCODE ‘V05’;

OUTPUT >

+-------------+----------+

| CNAME | KM*PERKM |

+-------------+----------+

| Sahanubhuti | 1620 |

+-------------+----------+

Page | 55
Q.22: SQL Queries : SCHOOL and ADMIN tables

Write SQL queries for (i) to (iv) and find outputs for SQL queries
(v) to (viii), which are based on the tables SCHOOL and ADMIN.

1. To display TEACHERNAME, PERIODS of all teachers whose periods

are more than 25.

SQL> select teacher,periods from school where periods>25;

OUTPUT >

+------------+---------+

| teacher | periods |

+------------+---------+

| Priya Rai | 26 |

| Lisa Anand | 27 |

| Ganan | 28 |

| Harish B | 27 |

+------------+---------+
Page | 56
 4 rows in set (0.00 sec)

2. To display all the information from the table SCHOOL in

descending order of experience.

SQL> select * from school order by experience desc;

OUTPUT >

+------+--------------+-----------+------------+---------+------------+

| code | teacher | subject | doj | periods | experience |

+------+--------------+-----------+------------+---------+------------+

| 1215 | Umesh | Physics | 1998-05-11 | 22 | 16 |

| 1045 | Yashraj | Maths | 2000-08-24 | 24 | 15 |

| 1009 | Priya Rai | Physics | 1998-09-03 | 26 | 12 |

| 1001 | Ravi Shankar | English | 2000-03-12 | 24 | 10 |

| 1203 | Lisa Anand | English | 2000-04-09 | 27 | 5 |

| 1167 | Harish B | Chemistry | 1999-10-19 | 27 | 5 |

| 1123 | Ganan | Physics | 1999-07-16 | 28 | 3 |

+------+--------------+-----------+------------+---------+------------+

7 rows in set (0.00 sec)

3. To display DESIGNATION without duplicate entries from the

table ADMIN.

SQL> select distinct designation from admin;

OUTPUT >

+----------------+

| designation |

+----------------+

| Vice Principal |

| Coordinator |

| HOD |

| Senior Teacher |

Page | 57
 +----------------+

4 rows in set (0.00 sec)

4. To display TEACHERNAME, CODE and corresponding DESIGNATION

from tables SCHOOL and ADMIN of Male teachers.

SQL> select teacher,[Link],designation from school s,admin a

where [Link]=[Link] and gender='Male';

OUTPUT >

+--------------+------+----------------+

| teacher | code | designation |

+--------------+------+----------------+

| Ravi Shankar | 1001 | Vice Principal |

| Yashraj | 1045 | HOD |

| Ganan | 1123 | Senior Teacher |

| Harish B | 1167 | Senior Teacher |

| Umesh | 1215 | HOD |

+--------------+------+----------------+

5 rows in set (0.06 sec)

5. Select Designation,Count(*) From Admin Group By Designation

Having Count(*)<2;

OUTPUT >

+----------------+----------+

| Designation | Count(*) |

+----------------+----------+

| Vice Principal | 1 |

+----------------+----------+

1 row in set (0.00 sec)

6. SELECT max(EXPERIENCE) FROM SCHOOL;

OUTPUT >

Page | 58
 +-----------------+

| max(EXPERIENCE) |

+-----------------+

| 16 |

+-----------------+

1 row in set (0.00 sec)

7. SELECT TEACHER FROM SCHOOL WHERE EXPERIENCE >12 ORDER BY

TEACHER;

OUTPUT >

+---------+

| TEACHER |

+---------+

| Umesh |

| Yashraj |

+---------+

2 rows in set (0.00 sec)

8. SELECT COUNT (*), GENDER FROM ADMIN GROUP BY GENDER;

OUTPUT >

+----------+--------+

| COUNT(*) | GENDER |

+----------+--------+

| 2 | Female |

| 5 | Male |

+----------+--------+

2 rows in set (0.00 sec)

----xxx----

Page | 59