CBSE Computer Science Practical –
Complete SQL and Python Solutions
SQL – SUPPLIER & PRODUCT
CREATE TABLE SUPPLIER(SUPCODE VARCHAR(3) PRIMARY KEY,SNAME
VARCHAR(40),CITY VARCHAR(20));
CREATE TABLE PRODUCT(PID INT PRIMARY KEY,PNAME VARCHAR(40),QTY INT,PRICE
INT,COMPANY VARCHAR(30),SUPCODE VARCHAR(3),
FOREIGN KEY(SUPCODE) REFERENCES SUPPLIER(SUPCODE));
INSERT INTO SUPPLIER VALUES('S04','TECH SOURCE','PUNE');
UPDATE PRODUCT SET PRICE=PRICE*1.10 WHERE PNAME LIKE 'P%';
SELECT PNAME,PRICE,SNAME FROM PRODUCT,SUPPLIER WHERE
[Link]=[Link];
SQL – SENDER & RECIPIENT
CREATE TABLE SENDER(SenderID VARCHAR(5) PRIMARY KEY,SenderName
VARCHAR(30),SenderAddress VARCHAR(50),SenderCity VARCHAR(20));
CREATE TABLE RECIPIENT(RecID VARCHAR(5) PRIMARY KEY,SenderID
VARCHAR(5),RecName VARCHAR(30),RecAddress VARCHAR(50),RecCity VARCHAR(20),
FOREIGN KEY(SenderID) REFERENCES SENDER(SenderID));
INSERT INTO SENDER VALUES('ND60','A Mehta','45 Park Lane','New Delhi');
SELECT RecID,SenderName,SenderAddress,RecName,RecAddress
FROM SENDER,RECIPIENT
WHERE [Link]=[Link];
SELECT RecCity,COUNT(*) FROM RECIPIENT GROUP BY RecCity;
SQL – TEACHER & STUDENT
INSERT INTO TEACHER VALUES(400,'Rohit Pal','Science',25000,'2005-05-10');
UPDATE TEACHER SET BASIC=BASIC*1.10 WHERE TEACHERNAME LIKE '%l';
DELETE FROM STUDENT WHERE AGG BETWEEN 60 AND 80;
SELECT TEACHERNAME,NAME,AGG
FROM TEACHER,STUDENT
WHERE [Link]=[Link];
SQL – ROUTE & TICKET
SELECT Source,Destination,Fare FROM ROUTE WHERE TransportType='Train';
SELECT * FROM TICKET WHERE MONTH(BookingDate)=2 AND YEAR(BookingDate)=2024;
SELECT RouteID,AVG(Fare) FROM TICKET GROUP BY RouteID;
SELECT Source,Destination FROM ROUTE WHERE DistanceKM>200;
SQL – SHIPMENT & VEHICLE
SELECT location,item_type,quantity FROM SHIPMENT,VEHICLE
WHERE [Link]=[Link] AND [Link]='Truck';
SELECT location,SUM(quantity) FROM SHIPMENT GROUP BY location;
UPDATE SHIPMENT SET quantity=quantity*1.10 WHERE item_type='Food Kits';
SELECT driver_name,dispatch_date FROM SHIPMENT,VEHICLE
WHERE [Link]=[Link] AND location='Jaipur';
Python – MySQL EMPLOYEE
import [Link] as m
con=[Link](host='localhost',user='root',password='root',database='company')
cur=[Link]()
def insert_emp():
eno=int(input("No: "))
name=input("Name: ")
gen=input("Gender: ")
dept=input("Dept: ")
sal=int(input("Salary: "))
[Link]("insert into EMPLOYEE values(%s,%s,%s,%s,%s)",(eno,name,gen,dept,sal))
[Link]()
def update_emp():
eno=int(input("Emp No: "))
sal=int(input("New Salary: "))
[Link]("update EMPLOYEE set Salary=%s where EmployeeNo=%s",(sal,eno))
[Link]()
def display():
[Link]("select * from EMPLOYEE")
for r in cur:
print(r)
Python – Stack Supply Request
stack=[]
def push():
r=[input("ID: "),input("Zone: "),input("Type: "),input("Qty: ")]
[Link](r)
def pop():
if stack:
print("Handled:",[Link]())
def display():
for r in stack[::-1]:
print(r)
Python – Binary File [Link]
import pickle
def add():
f=open("[Link]","ab")
r=[input("ID: "),input("Name: "),input("Brand: "),input("Cat: "),int(input("Stock:
")),int(input("Price: "))]
[Link](r,f)
[Link]()
def show():
f=open("[Link]","rb")
try:
while True:
print([Link](f))
except:
pass
[Link]()
def search():
name=input("Name: ")
f=open("[Link]","rb")
try:
while True:
r=[Link](f)
if r[1]==name:
print(r)
except:
pass
[Link]()
Python – CSV aqi_log.csv
import csv
def add():
f=open("aqi_log.csv","a",newline='')
w=[Link](f)
r=[input("ID: "),input("City: "),input("Area: "),input("AQI: "),input("Pollutant:
"),input("Week: ")]
[Link](r)
[Link]()
def display():
f=open("aqi_log.csv")
for r in [Link](f):
print(r)
[Link]()
def search():
city=input("City: ")
f=open("aqi_log.csv")
for r in [Link](f):
if r[1]==city:
print(r)
[Link]()