SMS DUTTA MEMORIAL NOSEGAY

PUBLIC SCHOOL, KHATIMA

COMPUTER SCIENCE PRACTICAL

FILE
FOR
THE PARTIAL
FULFILLMENT OF AISSCE–
2025–26

Submitted by: Submitted to:

[Link]
BHATNAGAR
Class – XII PGT(CS)
Section –
Board RollNo.
 SMS DUTTA MEMORIAL NOSEGAY PUBLIC SCHOOL
KHATIMA

Affiliated to CBSE Delhi,No.3530125

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

successfully completed all Python and SQL Assignments,

under the guidance of [Link] Bhatnagar during the

academic session2025-26 inpartial fulfilment of AISSCE

2024-25 practical examination conducted by CBSE, New

Delhi.

Signature of Principal

Signature of Examiner Signature of Teacher

 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.
IwouldalsoliketothankourPrincipal,[Link],
forprovidingmewiththiswonderfulopportunitytoworkon 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
 TABLEOFCONTENT

TEXTFILEWORDSDISPLAYEDWITH#.............................................................................................................. 5
TEXTFILESTATISTICS................................................................................................................................................ 6
TEXTFILESTOREITEMRECORDS........................................................................................................................... 7
REMOVEALLLINESTHATCONTAIN‘A’..................................................................................8
BINARYFILE–SEARCHNAMEWITHROLLNO........................................................................................10
BINARYFILE–ENTERROLLNOANDUPDATEMARKS........................................................................12
CSVFILE–SEARCHPASSWORDWITHUSERID.......................................................................................16
ADDLISTELEMENTS............................................................................................................................................ 18
STACKOFBOOKS..................................................................................................................................................... 20
PUSHELEMENTSFROMLISTTOSTACK.....................................................................................................22
STACKASLIST........................................................................................................................................................... 23
PUSHELEMENTSFROMTUPLE...................................................................................................................... 25
[Link]............................................................................................................................... 27
PY-MYSQLCONNECTIVITYPROGRAM......................................................................................................28
PYTHON-MySQLCONNECTIVITY:EMPLOYEE.....................................................................................31
PYTHON-MySQLCONNECTIVITY:PRODUCT........................................................................................35
PYTHON-MySQLCONNECTIVITY:GARMENT.......................................................................................39
SQLQueries-COMPANYandCUSTOMERtables.............................................................................................42
SQLQueries-ITEMSandTRADERStables...........................................................................................................46
Q.20:SQLQueries-SHOPandACCESSORIEStables...............................................................................................49
Q.21:SQLQueries:VEHICLEandTRAVELtables....................................................................................................52
Q. 2:SQLQueries:SCHOOLandADMINtables.......................................................................................................56

Page|4
 TEXTFILEWORDSDISPLAYEDWITH#
Readatextfilelinebylineanddisplayeachword separated by a
#.

Program:-

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

line=[Link]()#readfirstline while

line:

words=[Link]() for

x in words:

print(x,end="#")

line=[Link]()#readnextline

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
 TEXTFILESTATISTICS
Read a text file and display the number of
vowels/consonants/uppercase/lowercasecharactersinthe
file.

Program:-

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

up=low=vow=cons=0
forchincontent:
[Link]():
up+=1
[Link]():
low+=1

[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
 TEXTFILESTOREITEMRECORDS
Createatextfileprogrammaticallywhichstoresthe records
(itemid,item_description,price) of 5 items.

Program:-

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

Output:-

Howmanyitems?5 ItemID>>I1 ItemID>>I4

Itemdescription>>Muffins Itemdescription>>Cereals
Price>>45 Price>>65
ItemID>>I2 ItemID>>I5
Itemdescription>>Oats Itemdescription>>Oil
Price>>100 Price>>80
ItemID>>I3 Filecontent:-
Itemdescription>>Rice I1Muffins 45.0
I2Oats 100.0
Price>>150 I3Rice 150.0
I4Cereals 65.0
I5Oil 80.0

Page|7
 REMOVEALLLINESTHATCONTAIN‘A’
Removeallthelinesthatcontainthecharacter'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()

forlineincontent:

if 'a' not in line:

no_a_lines.append(line)

[Link](no_a_lines)

[Link]()

[Link]()

Output :-

[Link]

Ihavecometothebordersofsleep, The

unfathomable deep

Forest where all must lose

Theirway,howeverstraight, Or

winding, soon or late; They

cannot choose.

Manyaroadandtrack

That,sincethedawn’sfirstcrack,

Uptotheforestbrink,
Page|8
Deceivedthetravellers,

Suddenly now blurs,

Andintheysink.

poem_no_a.txt

Uptotheforestbrink,

Suddenly now blurs,

Andintheysink.

Page|9
 BINARYFILE– SEARCHNAMEWITHROLLNO.
Create a binary file with name and roll number.
Searchforagivenrollnumberanddisplaythename,if not found
display appropriate message.

Program :-

importpickle

# 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("Enternameofstudent:")

srecord=[rollno,sname]

[Link](srecord,myfile)

[Link]()

[Link](0)

# searching for name using rollno.

rollno=int(input("Whatistherollno?")) try:

whileTrue:

srec=[Link](myfile)

if srec[0]==rollno:

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

break

except EOFError:

print("Recordnotfound!") [Link]()

Output:-

Howmanyrecords?2

Enter rollno.:65

Enternameofstudent:ManiShankar Enter

rollno.:47

Enternameofstudent:SMohanKumar What is

the rollno?65

Rollno.=65

Studentname=ManiShankar

Page|11
 BINARYFILE–ENTERROLLNO ANDUPDATEMARKS
Createabinaryfilewithrollnumber,nameand 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("Enterrollno.:"))

sname=input("Enter name :")

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

srec=[rollno,sname,marks]

[Link](srec,myfile)

[Link]()

[Link](0)

stable=list()

try:

whileTrue:

srec=[Link](myfile)

[Link](srec)

exceptEOFError:

pass

Page|12
[Link]()

#displayallrecords

n=len(stable)

print("Rollno\tStudentname\tMarks")

for i in range(n):

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

#searchforrecord

rollno=int(input("[Link] to be
updated:"))

found=False

foriin range(n):

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

print("Recordfound!")

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

new_marks=float(input("Enternewvaluefor
marks:"))

stable[i][2]=new_marks

found=True

break

else:

print("Recordnotfound!")

Page|13
#Writeupdatedtabletofile if

found:

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

for i in range(n):

print(stable[i])

[Link](stable[i],myfile)

[Link]()

print("Recordupdated!")

Output:-

Howmanyrecords?5

Enter rollno.:1

Entername:SameerSharma Enter

marks:65.5

Enterrollno.:2

Entername:RaghuvindraK Enter

marks:54

Enterrollno.:3

Entername:RamaGupta

Enter marks:87

Enterrollno.:4

Entername:MohanKumar

Enter marks:90.5

Enterrollno.:5

Page|14
Entername:RajeshKumar Enter

marks:67.3

Rollno Studentname Marks

1 SameerSharma 65.5

2 RaghuvindraK 54.0

3 RamaGupta 87.0

4 MohanKumar 90.5

5 RajeshKumar 67.3

[Link] Record

found!

2 RaghuvindraK 54.0

Enternewvalueformarks:56 [1,

'Sameer Sharma', 65.5]

[2,'RaghuvindraK',56.0]

[3,'RamaGupta',87.0]

[4,'MohanKumar',90.5]

[5,'RajeshKumar',67.3]

Record updated!

Page|15
 CSVFILE–SEARCHPASSWORDWITHUSERID
CreateaCSVfilebyenteringuser-idandpassword, read and
search the password for given userid.

Program:-

import csv

header=['user_id','password']

row=list()

withopen("login_info.csv","a",newline="")asmyfile:

login_writer=[Link](myfile)

login_writer.writerow(header)#writerheaderrow

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

for i in range(n):

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

password=input("Enterpassword:")

row=[user_id,password]

login_writer.writerow(row)

withopen("login_info.csv","r")asmyfile:

login_reader=[Link](myfile)

uid=input("Enter user id:")

forrowinlogin_reader: if

uid==row[0]:

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

Output:-

Page|16
Howmanyuser-ids?5

Enteruser-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

Enteruserid:galvang405

User_id=galvang405,password=mocy47

Page|17
 ADDLISTELEMENTS
WriteaprogramthattakesanytwolistsLandMof
thesamesizeandaddstheirelementstogethertoforma 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("Howmanyelements?"))

L=list()

M=list()

N=list()

foriin 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:-

Howmanyelements?6

Enterelementforlist1:5
Page|18
Enterelement for list2:65

Enterelement for list1:8

Enterelement for list2:4

Enterelement for list1:12

Enterelement for list2:34

Enterelement for list1:78

Enterelement for list2:82

Enterelement for list1:15

Enterelement for list2:16

Enterelement for list1:11

Enterelement 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
 STACKOFBOOKS
Write a Python program to implement a stack of books named
“book_stack” and push book items to this stack. Each
bookitemconsistsofBookID,[Link] book
elements and display them. Display “Stack Empty” if the stack
is empty.

Program :-

book_stack=list()

book_item=list()

#book_itemcontainsBookID,BookTitleandPrice #

pushing book elements

ans='y'

whileans=='y':

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

btitle=input("Enter book title:")

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

book_item=[bookid,btitle,price]

book_stack.append(book_item)

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

# popping book elements

print("poppingbookelements...")

while book_stack:

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

else:

print("StackEmpty")

Output:-
Page|20
Enterbook-id:1

Enterbooktitle:TheGreatGatsby

Enter price of 1 book:450

Continue pushing books? y/n:y

Enter book-id:2

Enterbooktitle:PrideandPrejudice

Enter price of 1 book:600

Continuepushingbooks?y/n:y

Enter book-id:3

Enter book title:1984

Enterpriceof1book:500

Continuepushingbooks?y/n:y

Enter book-id:4

Enterbooktitle:WarandPeace

Enter price of 1 book:550

Continue pushing books? y/n:y

Enter book-id:5

Enterbooktitle:AnnaKarennina

Enter price of 1 book:660

Continue pushing books? y/n:n

popping book elements...

[5,'AnnaKarennina',660.0] [4,'WarandPeace',550.0]
[3,'1984',500.0][2,'PrideandPrejudice',600.0] [1,
'The Great Gatsby', 450.0]Stack Empty

Page|21
 PUSHELEMENTSFROMLISTTOSTACK
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']

foriinrange(len(colors)): if

i%2!=0:

[Link](colors[i])

print("List=",colors)

print("Stackcontaininglistelementsatoddindex=",stack)

Output:-

List=['black','cyan','magenta','yellow','purple', 'green',
'red', 'blue']

Stackcontaininglistelementsatoddindex=['cyan', 'yellow',
'green', 'blue']

Page|22
 STACKASLIST
Write a Python program to push some elements to a stack
implemented as a list and display them. Also pop the elements
[Link], then
display “Stack Empty”.

Program:-

stack=list()

#pushingelementstostack
ans='y'
whileans=='y':
element=input("Enteranelementtopush:")
[Link](element) # push
ans=input("Doyouwanttopushmore?y/n:")

#displaystack
print(stack)

# pop elements
print("Elementspopped=")
while stack:
print([Link]())
else:
print("Stackempty")

Output:-

Enteranelementtopush:FrozenFruit Do

you want to push more? y/n:y

Enteranelementtopush:WholeGrainCrackers Do you

want to push more? y/n:y

Enteranelementtopush:WholeWheatPasta Do you

want to push more? y/n:y

Enter an element to push:Salsa

Doyouwanttopushmore?y/n:y

Page|23
Enteranelementtopush:PastaSause Do

you want to push more? y/n:n

['FrozenFruit','WholeGrainCrackers','WholeWheatPasta', 'Salsa',
'Pasta Sause']

Elementspopped=

Pasta Sause

Salsa

Whole Wheat Pasta

WholeGrainCrackers

Frozen Fruit

Stackempty

Page|24
 PUSHELEMENTSFROMTUPLE
Write a Python program to read a tuple from the user which
contains some numeric elements. Now define a function
namedpush_even()whichwillpushalltheevennumbersinthis
tupletoastack.Alsodefineafunctionpop_even()whichwill
[Link],ifthetupleis
(23,45,12,11,7,5,9,8,34,32,77,78), then the stack should
contain[12,8,34,32,78]andwhilepoppingtheoutputshouldbe
783234812StackEmpty.

Program:-

defpush_even(x):forel
ementinx:
if element%2==0:
stack_even.append(element)

defpop_even():
print("Poppingelementsfromstack=>")
while stack_even:
print(stack_even.pop())
else:
print("StackEmpty")

#_main_
stack_even=list()

tup_elements=eval(input("Enteratuplecontainingnumeric
values:"))

#pushingevenelementstostack
push_even(tup_elements)

#displaystack
print(stack_even)

#poppingstackelements
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]

Poppingelementsfromstack=>72

14

34

36

12

46

StackEmpty

Page|26
 [Link]
Writearandomnumbergeneratorthatgeneratesrandom numbers
between 1 and 6 (simulates a dice).

Program :-

importrandom

ans='y'

while ans=='y':

num=[Link](1,6)

print("Dicerolled=",num)

ans=input("Rollthediceagain?y/n")

Output:-

Dicerolled=3

Rollthediceagain?y/ny

Dice rolled= 5

Rollthediceagain?y/ny

Dice rolled= 2

Rollthediceagain?y/ny

Dice rolled= 4

Rollthediceagain?y/nn

Page|27
 PY-MYSQLCONNECTIVITYPROGRAM
WriteaPython-MySQLconnectivityprogramtodothe following
:-
a. InsertnewrecordsintotheStudenttable
(rollno,sname,marks).
b. Displayrecords.
c. Searchforastudentbasedonrollno.
d. Displaythestudentrecordwithhighestmarks.

Program:-

[Link]

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

#insertingstudentrecords
n=int(input("Howmanyrecordstobeinserted?"))

for i in range(n):
rollno=int(input("Enter rollno.:"))
sname=input("Enternameofstudent:")
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\tStudentname\t\tMarks")#headerrow
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("[Link] searched:"))
sql="select*fromStudentwhererollno=%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("Recordnotfound!")

#displaystudentrecordwithhighestmarks
max_mark=None
sql="select*fromStudentwheremarks=(selectmax(marks) from
Student)"
[Link](sql)
student_set=[Link]()
print("Studentswhoobtainedhighestmarks=")
print("Rollno\tStudentname\t\tMarks")#headerrow
for record in student_set: print(record[0],"\t",record[1],"\t\
t",record[2])

#closeconnection
[Link]()

Output:-

Howmanyrecordstobeinserted?2 Enter

rollno.:6

Enternameofstudent:Perlin

Enter marks:63.5

Enterrollno.:7

Enternameofstudent:SJeeva

Enter marks:78

Rollno Studentname 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

[Link]

Rollno Studentname Marks

5 Reema 80.50

Students whoobtained highestmarks=

Rollno Studentname Marks

5 Reema 80.50

Page|30
 PYTHON-MySQLCONNECTIVITY:EMPLOYEE
WriteaPython-MySQLconnectivityprogramtodothe
following :-
a. InsertnewrecordsintotheEmployeetable
(empno,ename,dept,salary).
b. Askfromtheuseranydepartmentanddisplayall
employee records belonging to that department.
c. Searchforanemployeebasedonempnoandupdatehis salary.
d. Displayalltheemployeerecords.

Program:-

[Link]

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

ecursor=[Link]()

#insertingemployeerecords

n=int(input("Howmanyrecordstobeinserted?")) for i

in range(n):

empno=int(input("Enteremployeeno.:"))

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*fromEmployee"ecurs

[Link](sql)

student_set=[Link]()

Page|31
print("Empno\tEmployeename\t\tDepartment\t\tSalary")#header
row

forrecordinstudent_set:

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

#askfromuseranydeptanddisplayallemployeerecordsin that
department

dept=input("Enteranydepartment:")

sql="select*fromEmployeewheredept='{}'".format(dept)

[Link](sql)

emp_set=[Link]()

print("Empno\tEmployeename\t\tDepartment\t\tSalary")#header
row

forrecordinemp_set:

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

ifnotemp_set:

print("Noemployeesin",dept,"department")

#Searchforanemployeebasedonempnoandupdatehissalary.

empno=int(input("WhatistheempnoofEmployeewhosesalaryis to be
updated?"))

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

sql="updateEmployeesetsalary={}where
empno={}".format(salary,empno)

[Link](sql)

Page|32
[Link]()

print("Salaryupdated!")

#closeconnection

[Link]()

Output:-

Howmanyrecordstobeinserted?3

Enter employeeno.:109

Enter employeename:SameerSharma

Enter department:IT

Enter salary:50000

Enter employeeno.:110

Enter employeename:ZarinaKhan

Enter department:Production

Enter salary:40000

Enter employeeno.:111

Enter employeename:PratikshaKanwar

Enter department:Purchase

Enter salary:23000

Empno Employeename Department Salary

101 AmitRaj Sales 50000

102 AshishSingh Purchase 45000

103 DilawarKhan Sales 30000

104 RehanKhan Finance 40000

Page|33
105 MilanSingh Purchase 12000

106 RaviKumar Finance 15000

107 JeevanKumar Sales 15000

108 NehaBhasin Purchase 16000

109 SameerSharma IT 50000

110 ZarinaKhan Production 40000

111 PratikshaKanwar Purchase 23000

Enteranydepartment:Purchase

Empno Employeename Department Salary

102 AshishSingh Purchase 45000

105 MilanSingh Purchase 12000

108 NehaBhasin Purchase 16000

111 PratikshaKanwar Purchase 23000

WhatistheempnoofEmployeewhosesalaryistobe updated?108

Enterrevisedsalary:17000

Salary updated!

Page|34
 PYTHON-MySQLCONNECTIVITY:PRODUCT
WriteaPython-MySQLconnectivityprogramtodothe
following :-
a. InsertnewrecordsintotheProducttable
(prodID,item_name,price).
b. Displaytheaveragepriceofallitems.
c. Displaytheitemswhosepriceismorethan500.
d. Updatethepriceofanitemaftersearchingbasedon prodID.

Program:-

[Link]

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

pcursor=[Link]()

#insertingproductrecords

n=int(input("Howmanyrecordstobeinserted?")) for i

in range(n):

prod_id=int(input("EnterproductID:"))

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*fromProduct"pcurso

[Link](sql)

prod_set=[Link]()

print("ProdID\tItemName\t\tPrice")#headerrow

Page|35
for record in prod_set: print(record[0],"\t",record[1],"\t\

t",record[2])

#Displaytheaveragepriceofallitems.

sql="select avg(price) from

Product"[Link](sql)

prod_set=[Link]()

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

#Displaytheitemswhosepriceismorethan500.

sql="select * from Product where

price>500"[Link](sql)

prod_set=[Link]()

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

print("ProdID\tItemName\t\tPrice")#headerrow for

record in prod_set:

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

ifnotprod_set:

print("Noproductswhosepriceexceeds500")

#UpdatethepriceofanitemaftersearchingbasedonprodID.

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

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

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

Page|36
[Link](sql)

[Link]()

print("Priceupdated!")

#closeconnection

[Link]()

Output:-

Howmanyrecordstobeinserted?3

Enter productID:11

Enter itemname:Froot Loops

Enter price:80

Enter productID:12

Enter itemname:Dried oatmeal

Enter price:65

Enter productID:13

Enter itemname:Cajun Pepper

Enter price:60

Prod IDItemName Price

1 Flour 150.00

2 OliveOil 550.00

3 Pepper 45.00

4 Seasoning 90.00

5 Cheese 560.00

Page|37
6 Bread 10.00

7 Exoticfruit 650.00

8 BreadSpread 180.00

9 Jam 120.00

10 Mayonnaise 99.00

11 FrootLoops 80.00

12 Driedoatmeal65.00

13 CajunPepper 60.00

Averageprice=204.538462

itemswhosepriceismorethan500=

Prod ID ItemName Price

2 OliveOil 550.00

5 Cheese 560.00

7 Exoticfruit 650.00

EnterprodIDtoupdateprice:7

Enter new price:655

Priceupdated!

Page|38
 PYTHON-MySQLCONNECTIVITY:GARMENT
WriteaPython-MySQLconnectivityprogramtodothe
following :-
a. InsertnewrecordsintotheGarmenttable
(Gcode,Gname,size,colour,price).
b. Displaythosegarmentdetailswhosepriceisinthe range
1000.00 to 1500.00.
c. Displaynamesofthosegarmentsthatareavailablein
‘XL’size.
d. [Link] colour
and gcode from user.

Program:-

[Link]

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

#insertinggarmentrecords
n=int(input("Howmanyrecordstobeinserted?"))

foriin range(n):
gcode=int(input("Enter Garment code:"))
gname=input("Enter Garment name:")
size=input("Entersize(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
#Displaythosegarmentdetailswhosepriceisintherange1000.00 to 1500.00
sql="select*fromGarmentwherepricebetween1000and1500"[Link](sq
l)
garment_set=[Link]()
print("garmentdetailswhosepriceisintherange1000.00to 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])

ifnot garment_set:
print("Norecordfound!")

#Displaynamesofthosegarmentsthatareavailablein‘XL’size
sql="select gname from Garment where
size='XL'"[Link](sql)
garment_set=[Link]()
print("namesofthosegarmentsthatareavailablein‘XL’size=>")
forrecordingarment_set:
print(record[0])

#[Link] gcode from user

gcode=int(input("Entergarmentcodetoupdatecolour:")) color=input("Enter
new garment color:")
sql="updateGarmentsetcolour='{}'where
gcode={}".format(color,gcode)
[Link](sql)
[Link]()
print("Colourofgarmentupdated!")

#closeconnection
[Link]()

Output:-
Howmanyrecordstobeinserted?3
Enter Garment code:118
EnterGarmentname:Skirt
Entersize(S/M/L/XL/XXL/UXL):XXL

Page|40
Entercolor:Red
Enterprice:800
Enter Garment code:119
EnterGarmentname:Sweater
Entersize(S/M/L/XL/XXL/UXL):XL
Enter color:Grey
Enterprice:400
EnterGarmentcode:120
Enter Garment name:Waistcoat
Entersize(S/M/L/XL/XXL/UXL):L
Enter color:Silver
Enterprice:500
Gcode Garment SizeColour Price
111 Tshirt XL Red 1400.00
112 Jeans L Blue 1600.00
113 Skirt M Black 1100.00
114 LadiesJacket XL Blue 4000.00
115 Trousers L Brown 1500.00
116 LadiesTop 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
garmentdetailswhosepriceisinthe range1000.00to1500.00=>
Gcode Garment SizeColour Price
111 Tshirt XL Red 1400.00
113 Skirt M Black 1100.00
115 Trousers L Brown 1500.00
116 LadiesTop L Pink 1200.00
117 Suit XL Maroon 1500.00
namesofthosegarmentsthatareavailablein‘XL’size=>
Tshirt
LadiesJacket
Suit
Sweater
Entergarmentcodetoupdatecolour:115 Enter
new garment color:Black
Colourofgarmentupdated!

Page|41
 SQLQueries-COMPANYandCUSTOMERtables
WriteSQLqueriesfor(i)to(iv)andfindoutputsforSQL
queries(v)to(viii),whicharebasedonthetablesCOMPANYand CUSTOMER.

1. Todisplaythosecompanynamewhicharehavingprizelessthan 30000.

mysql>[Link],pricefromcompany,customerwhere
[Link]=[Link] and price<30000;

+ + +

|name|price|

+ + +

|Onida|20000|

|Sony|25000|

+ + +

2rowsinset(0.00sec)

2. Todisplaythenameofthecompaniesinreversealphabetical order.

mysql>selectnamefromcompanyorderbynamedesc;

Page|42
 + +

|name |

+ +

|Sony |

|Sony |

|Onida |

|Nokia |

|Dell |

|Blackberry|

+ +

6rowsinset(0.00sec)

3. Toincreasetheprizeby1000forthosecustomerwhose name
startswith‘S’?

mysql>updatecustomersetprice=price+1000wherenamelike 'S%';

Query OK, 2 rows affected (0.09 sec)

Rowsmatched:2Changed:2Warnings:0

mysql>select*fromcustomer;

+ + + + + +

| custid | name | price | qty | cid |

+ + + + + +

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

| 102 | DeepakKumar | 50000 | 10 | 666 |

| 103 | MohanKumar | 30000 | 5 | 111 |

| 104 | SahilBansal | 36000 | 3 | 333 |

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

Page|43
 | 106|SonalAggarwal|21000| 5|333|

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

+ + + + + +

7rowsinset(0.00sec)

4. Toaddonemorecolumntotalpricewithdecimal(10,2)tothe table
customer.

mysql>altertablecustomeraddtotalpricedecimal(10,2); Query OK,

0 rows affected (0.75 sec)

Records:0Duplicates:0Warnings:0

mysql>desccustomer;

+ + + + + + +

|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 | |

+ + + + + + +

6rowsinset(0.00sec)

5. SELECTCOUNT(*),CITYFROMCOMPANYGROUPBYCITY;

+ + +

|COUNT(*)|CITY |

+ + +

| 3|Delhi|

Page|44
 | 1|Madras|

| 2|Mumbai|

+ + +

3rowsinset(0.00sec)

6. SELECTMIN(PRICE),MAX(PRICE)FROMCUSTOMERWHEREQTY>10;

+ + +

|MIN(PRICE)|MAX(PRICE)|

+ + +

| 50000| 70000|

+ + +

1rowinset(0.00sec)

7. SELECTAVG(QTY)FROMCUSTOMERWHERENAMELIKE'%r%';

+ +

|AVG(QTY)|

+ +

|11.0000|

+ +

1rowinset(0.00sec)

8. SELECTPRODUCTNAME,CITY,PRICEFROMCOMPANY,CUSTOMERWHERE
[Link]=[Link] AND PRODUCTNAME='MOBILE';

+ + + +

|PRODUCTNAME| CITY |PRICE|

+ + + +

|Mobile |Mumbai|70000|

|Mobile |Mumbai|25000|

+ + + +

2rowsinset(0.00sec)

Page|45
 SQLQueries-ITEMSandTRADERStables
Write SQL queries for (a) to (g) and write the output for the SQL
queriesmentionedshownin(hi)to(h4)partsonthebasisoftable ITEMS and
TRADERS :

1. Todisplaythedetailsofalltheitemsinascending order of
item names (i.e., INAME).
mysql>select*fromitemsorderbyiname;
+ + + + + + +
|code| iname |qty|price| company |tcode|
+ + + + + + +
|1004|CarGPS System | 50|21500| Geoknow |T01 |
|1003|DigitalCamera12X|160|8000|Digiclick|T02 |
|1001|DigitalPad12i |120|11000| Xenita |T01 |
|1006|LEDScreen40 | 70|38000| Santora |T02 |
|1005|PenDrive32GB |600|1200|Storehome|T03 |
+ + + + + + +
5rowsinset(0.00sec)

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

whosepriceisintherangeof10000and22000(both values
inclusive).

mysql>selectiname,pricefromitemswhereprice between
10000 and 22000;
+ + +
|iname |price|
+ + +
|DigitalPad12i|11000|
|CarGPSSystem|21500|
+ + +
2rowsinset(0.00sec)
3. Todisplaythenumberofitems,whicharetradedby each
trader.

mysql>selecttcode,count(*)fromitemsgroupbytcode;
+ + +
|tcode|count(*)|

Page|46
 + + +
|T01 | 2|
|T02 | 2|
|T03 | 1|
+ + +
3rowsinset(0.00sec)
4. To display the price, item name and quantity (i.e.,
qty)ofthoseitemswhichhavequantitymorethan150.

mysql>selectiname,price,qtyfromitemswhereqty>150;
+ + + +
|iname |price|qty|
+ + + +
|DigitalCamera12X|8000|160|
|PenDrive32GB |1200|600|
+ + + +
2rowsinset(0.00sec)
5. Todisplaythenamesofthosetraders,whoareeither from
DELHI or from MUMBAI.

mysql>selecttname,cityfromtraderswherecityin
('Delhi','Mumbai');
+ + +
|tname |city |
+ + +
|ElectronicSales|Mumbai|
|BusyStoreCorp|Delhi|
+ + +
2rowsinset(0.00sec)
6. Todisplaythenamesofthecompaniesandthenamesof the items
in descending order of company names.

mysql>selectcompany,inamefromitemsorderbycompany desc;
+ + +
|company |iname |
+ + +
|Xenita |DigitalPad12i |
|Storehome|PenDrive32GB |
|Santora |LEDScreen40 |
|Geoknow |CarGPS System |
|Digiclick|DigitalCamera12X|
+ + +
5rowsinset(0.00sec)
7. ObtaintheoutputsofthefollowingSQLqueriesbasedonthe data
given in tables ITEMS and TRADERS above.
o SELECTMAX(PRICE),MIN(PRICE)FROMITEMS;

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

Page|47
+ + +
1rowinset(0.00sec)

o SELECTPRICE*QTYFROMITEMSWHERECODE=1004;

+ +
|PRICE*QTY|
+ +
| 1075000|
+ +
1rowinset(0.00sec)

o SELECTDISTINCTTCODEFROMITEMS;

+ +
|TCODE|
+ +
|T01 |
|T02 |
|T03 |
+ +
3rowsinset(0.00sec)

o SELECTINAME,TNAMEFROMITEMSI,TRADERSTWHERE
[Link]=[Link] AND QTY< 100;

+ + +
|INAME |TNAME |
+ + +
|CarGPSSystem|ElectronicSales|
|LEDScreen40|DispHouseInc |
+ + +
2rowsinset(0.00sec)

Page|48
Q.20:SQLQueries-SHOPandACCESSORIEStables

WriteSQLqueriesfor(i)to(iv)andfindoutputsforSQLqueries
(v) to(viii),whicharebasedonthetablesSHOPandACCESSORIES.

(a) WritetheSQLqueries:
1. TodisplayNameandPriceofalltheAccessoriesinascending order of
their Price.
mysql>selectname,pricefromaccessoriesorderbyprice;
+ + +
|name |price|
+ + +
|Mouse | 300|
|Mouse | 350|
|Keyboard | 400|
|Keyboard | 500|
|HardDisk |4500|
|HardDisk |5000|
|LCD |5500|
|LCD |6000|
|MotherBoard|12000|
|MotherBoard|13000|
+ + +

Page|49
10rowsinset(0.00sec)

2. TodisplayIdandSNameofallShoplocatedinNehruPlace. mysql>

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

+ + +
|id |sname |
+ + +
|S004|GeeksTecnoSoft|
|S005|HitechTechStore|
+ + +
2rowsinset(0.00sec)

3. TodisplayMinimumandMaximumPriceofeachNameof
Accessories.

mysql>selectmin(price),max(price),namefromaccessoriesgroupby name;
+ + + +
|min(price)|max(price)| name |
+ + + +
| 4500| 5000|HardDisk |
| 400| 500| Keyboard |
| 5500| 6000|LCD |
| 12000| 13000|MotherBoard|
| 300| 350| Mouse |
+ + + +
5rowsinset(0.00sec)

4. TodisplayName,PriceofallAccessoriesandtheirrespective SName
where they are available.

mysql>selectname,price,snamefromaccessoriesa,shopswhere [Link]=[Link] ;
+ + + +
|name |price| sname |
+ + + +
|Keyboard | 500|AllInfotechMedia|
|MotherBoard|13000|AllInfotechMedia|
|Keyboard | 400|TechShop |
|LCD |6000|GeeksTecnoSoft |
|LCD |5500|HitechTechStore|
|Mouse | 350|HitechTechStore|
|HardDisk |4500|TechShop |
+ + + +
7rowsinset(0.00sec)

(b) WritetheoutputofthefollowingSQL
1. SELECTDISTINCTNAMEFROMACCESSORIESWHEREPRICE>=5000;

+ +
|NAME |
+ +

Page|50
|MotherBoard|
|HardDisk |
|LCD |
+ +
3rowsinset(0.00sec)

2. SELECTAREA,COUNT(*)FROMSHOPGROUPBYAREA;

+ + +
|AREA |COUNT(*)|
+ + +
|CP | 2|
|GKII | 1|
|NehruPlace| 2|
+ + +
3rowsinset(0.00sec)

3. SELECTCOUNT(distinctarea)FROMSHOP;

+ +
|COUNT(distinctarea)|
+ +
| 3|
+ +
1rowinset(0.00sec)

4. SELECTNAME,PRICE*0.05as'DISCOUNT'FROMACCESSORIESWHERE id IN
('S02','S03');

+ + +
|NAME |DISCOUNT|
+ + +
|Keyboard | 25.00|
|MotherBoard| 650.00|
|Keyboard | 20.00|
|HardDisk | 225.00|
+ + +
4rowsinset(0.00sec)

Page|51
Q.21:SQLQueries:VEHICLEandTRAVELtables

WriteSQLqueriesfor(i)to(iv)andfindoutputsforSQLqueries
(v) to(viii),whicharebasedonthetablesVEHICLEandTRAVEL.

Table:VEHICLE

Note:

 PERKSisFreightChargesperkilometer.
 KmiskilometersTravelled
 NOPisnumberofpassangerstravelledinvechicle.

1. TodisplayCNO,CNAME,TRAVELDATEfromthetableTRAVELin
descending order of CNO.

SQL>

selectcno,cname,traveldatefromtravelorderbycnodesc; OUTPUT >

+ + + +

|cno| cname |traveldate|

+ + + +

|107|JohnMalina|2015-02-10|

Page|52
 |106|RameshJaya|2016-04-06|

|105|HiteshJain|2016-04-23|

|104|Sahanubhuti|2016-01-28|

|103|FredrickSym|2016-03-21|

|102|RaviAnish |2016-01-13|

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

+ + + +

2. TodisplaytheCNAMEofallcustomersfromthetableTRAVELwho are
travelling by vehicle with code V01 or V02.

SQL>

selectcname,vcodefromtravelwherevcodein('V01','V02'); OUTPUT

>

+ + +

|cname |vcode|

+ + +

|[Link] |V01 |

|HiteshJain|V02 |

|RaviAnish|V02 |

|RameshJaya|V01 |

+ + +

3. TodisplaytheCNOandCNAMEofthosecustomersfromthetable TRAVEL who

travelled between ‘2015-12-31’ and ‘2015-05-01’.

SQL>

selectcno,cname,traveldatefromtravelwheretraveldate 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

whohavetravelleddistancemorethan120KMinascendingorder of NOP.

SQL>

selectcname,vehicletype,km,nopfromtravelt,vehiclevwhere
[Link]=[Link] and km>120 order by nop;

OUTPUT>

+ + + + +

|cname |vehicletype |km |nop|

+ + + + +

|[Link] |VolvoBus |200| 32|

|HiteshJain|ACDeluxeBus|450| 42|

+ + + + +

5. SELECTCOUNT(*),VCODEFROMTRAVELGROUPBYVCODEHAVING
COUNT(*) > 1;

OUTPUT>

+ + +

|COUNT(*)|VCODE|

+ + +

| 2|V01 |

| 2|V02 |

+ + +

6. SELECTDISTINCTVCODEFROMTRAVEL;

OUTPUT >

+ +

Page|54
 |VCODE|

+ +

|V01 |

|V03 |

|V02 |

|V04 |

|V05 |

+ +

7. [Link],CNAME,VEHICLETYPEFROMTRAVELA,VEHICLEB WHERE
A. VCODE = B. VCODE and KM < 90;

OUTPUT>

+ + + +

|VCODE|CNAME |VEHICLETYPE |

+ + + +

|V02 |RaviAnish|ACDeluxeBus|

|V04 |JohnMalina|SUV |

+ + + +

8. SELECTCNAME,KM*PERKMFROMTRAVELA,[Link]=
[Link]‘V05’;

OUTPUT>

+ + +

|CNAME |KM*PERKM|

+ + +

|Sahanubhuti| 1620|

+ + +

Page|55
Q.22:SQLQueries:SCHOOLandADMINtables

WriteSQLqueriesfor(i)to(iv)andfindoutputsforSQLqueries
(v) to(viii),whicharebasedonthetablesSCHOOLandADMIN.

1. TodisplayTEACHERNAME,PERIODSofallteacherswhoseperiods are
more than 25.

SQL>selectteacher,periodsfromschoolwhereperiods>25; OUTPUT >

+ + +

|teacher |periods|

+ + +

|PriyaRai| 26|

|LisaAnand| 27|

|Ganan | 28|

|HarishB | 27|

+ + +

Page|56
 4rowsinset(0.00sec)

2. TodisplayalltheinformationfromthetableSCHOOLin
descending order of experience.

SQL>select*fromschoolorderbyexperiencedesc; OUTPUT >

+ + + + + + +

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

+ + + + + + +

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

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

|1009|PriyaRai | Physics |1998-09-03| 26| 12|

|1001|RaviShankar|English |2000-03-12| 24| 10|

|1203|LisaAnand | English |2000-04-09| 27| 5|

|1167|HarishB |Chemistry|1999-10-19| 27| 5|

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

+ + + + + + +

7rowsinset(0.00sec)

3. TodisplayDESIGNATIONwithoutduplicateentriesfromthe table
ADMIN.

SQL>selectdistinctdesignationfromadmin;

OUTPUT >

+ +

|designation |

+ +

|VicePrincipal|

|Coordinator |

|HOD |

|SeniorTeacher|

Page|57
 + +

4rowsinset(0.00sec)

4. TodisplayTEACHERNAME,CODEandcorrespondingDESIGNATION from
tables SCHOOL and ADMIN of Male teachers.

SQL>selectteacher,[Link],designationfromschools,admina where
[Link]=[Link] and gender='Male';

OUTPUT>

+ + + +

|teacher |code| designation |

+ + + +

|RaviShankar|1001|VicePrincipal|

|Yashraj |1045|HOD |

|Ganan |1123|SeniorTeacher|

|HarishB |1167|SeniorTeacher|

|Umesh |1215|HOD |

+ + + +

5rowsinset(0.06sec)

5. SelectDesignation,Count(*)FromAdminGroupByDesignation
Having Count(*)<2;

OUTPUT>

+ + +

|Designation |Count(*)|

+ + +

|VicePrincipal| 1|

+ + +

1rowinset(0.00sec)

6. SELECTmax(EXPERIENCE)FROMSCHOOL;

OUTPUT >

Page|58
 + +

|max(EXPERIENCE)|

+ +

| 16|

+ +

1rowinset(0.00sec)

7. SELECTTEACHERFROMSCHOOLWHEREEXPERIENCE>12ORDERBY
TEACHER;

OUTPUT>

+ +

|TEACHER|

+ +

|Umesh |

|Yashraj|

+ +

2rowsinset(0.00sec)

8. SELECTCOUNT(*),GENDERFROMADMINGROUPBYGENDER; OUTPUT

>

+ + +

|COUNT(*)|GENDER|

+ + +

| 2|Female|

| 5|Male |

+ + +

2rowsinset(0.00sec)

xxx

Page|59