PRACTICAL FILE

COMPUTER SCIENCE

NAME: Sahil Saxena

CLASS: 12 E

CERTIFICATE 
 
DELHI PRIVATE SCHOOL, DUBAI 

  

CERTIFICATE 
 

Certified that the Practical file is the bona fide

work of Master/Miss Sahil Saxena  Class  XII 
E Roll no D4592 recorded in the School
laboratory during academic year 2022 to 2023. 
 
 
 
 
____________________                ______________________                _____________________ 
Ms. Richi Rehlan    Ms. Rose Nimmi                                                  
Teacher in-Charge               HOD        External Examiner    
 INDEX
S. No Chapter
1. Review Of Python
2. User Defined Functions
3. File Handling
4. Data Structure(Stack)
5. SQL Group Functions
6. Python Interface with MySQL
 INDEX
REVISION TOUR

S. No. PROGRAMS Teachers

Signature
1. Write a function transferFS(All,Even,Odd) to create a list
All that contains all elements of first in even position and all
elements of second in Odd position
2. Write a function FixSalary(Salary,N) to do the following
If less than 1,00,000 Add 35% more
If >=1,00,000 and Add 30% more
<2,00,000
If >=2,00,000 Add 20% more
3. Write a function AddEnd2() to find and display the sum of
all values ending with 2.
4. Write a program to perform sum of the following series: 
x-(x^2/3!)+(x^3/5!)-(x^4/7!)+(x^5/9!)-…..upto N terms
5. Write a function to find the sum of series. 
(1) +(1+2) +(1+2+3) +(1+2+3+4) …........up to N terms
 CODE

1. Write a function transferFS(All,Even,Odd) to create a list All that

contains all elements of first in even position and all elements of second
in Odd position

Code:
def transferFS(All, Even, Odd):
Even = All[0::2]
Odd = All[1::2]
return Even, Odd

All = eval(input("Enter the list: "))

print ('MAIN LIST: ', All)
Odd = []
Even = []
print('EVEN: ', transferFS(All, Even, Odd)[0])
print('ODD: ', transferFS(All, Even, Odd)[1])

Output:
 2. Write a function FixSalary(Salary,N) to do the following

If less than 1,00,000 Add 35% more

If >=1,00,000 and <2,00,000 Add 30% more
If >=2,00,000 Add 20% more

Code:
def FixSalary(Salary):
if Salary >= 200000:
Salary += (Salary * 0.35)
elif 100000 <= Salary < 200000:
Salary += (Salary * 0.30)
else:
Salary += (Salary * 0.20)
return Salary

Sal = int(input("Enter your Current Salary: "))

print("New Salary: ", FixSalary(Sal))

Output:
 3. Write a function AddEnd2() to find and display the sum of all values
ending with 2.

Code:
def AddEnd2(Sum, List):
for i in List:
if (i % 10) == 2:
Sum += i
return Sum

range1 = int(input("Enter the Range of Numbers:"))

List = []
Sum = 0
for i in range(range1):
num = int(input("Enter number to enter: "))
List += [num]
print('LIST of numbers:', List)
print("Sum: ", AddEnd2(Sum, List))

Output:
 4. Write a program to perform sum of the following series:  x-(x^2/3!) +
(x^3/5!) -(x^4/7!) +(x^5/9!)-…. upto N terms

Code:
def Series(X, N):
output, a, b, c, d = 1, 1, 1, 1, 1
for i in range(1, N):
b = b * d * (d + 1)
c = c * X * X
term = (-1) * a
d += 2
output = output + (a * c) / b
return output

X = int(input("Enter the Value of X: "))

N = int(input("Enter the number of Values"))
print ("Output: ",Series(X,N))

Output:
 5. Write a function to find the sum of series.  (1) +(1+2) +(1+2+3)
+(1+2+3+4) …........up to N terms
Code:
def Series1(N):
Sum = 0
for i in range(1, N + 1):
for j in range(1, i + 1):
Sum += j

return Sum

N = int(input("Enter the RANGE: "))

print ("Sum: ",Series1(N))

Output:
 INDEX
USER DEFINED FUNCTIONS

S. No. PROGRAMS TEACHER’S

SIGNATURE
1. Write a function to find the sum of series:
(2) + (2+4) + (2+4+ 6) + ( 2 + 4 + 6 + 8) ……. Up to N
terms
2. Write a program that inputs a series of integers and
passes them on one at a time to function called even(),
which uses the modulus operator to determine if an
integer is even. The function should take an integer
argument and return true if an integer is even, otherwise
false
3. A large chemical company pays its salespeople on a
common basis. The salespeople receive Rs. 200 per
week plus 9% of their gross sales for that week. For
example, a salesperson who sells Rs. 5000 worth of
chemicals in a week receives Rs.200 plus 9% of 5000, or
a total of Rs.650. Develop a program that will input each
salespersons gross sales for last week and calculate and
display the salespersons earnings.
4. Write a function to find the sum of the series:
x- x^2/2! + x^4/4! + x^6/6! + x^8/8! + ……. + x^n/n!
5. Write a function to find the sum of the series:
1 + 1/3^2 + 1/5^2 + 1/7^2 + 1/9^2 + ….. + 1/N^2
 CODE
1. Write a function to find the sum of series: (2) + (2+4) + (2+4+ 6) + ( 2 + 4
+ 6 + 8) ……. Up to N terms
Code:
def Series2(N):
Sum = 0
for i in range(1, N + 1):
for j in range(1, i + 1):
Sum += (j*2)

return Sum

N = int(input("Enter the RANGE: "))

print("Sum: ", Series2(N))

Output:
 2. Write a program that inputs a series of integers and passes them on one
at a time to function called even(), which uses the modulus operator to
determine if an integer is even. The function should take an integer
argument and return true if an integer is even, otherwise false
Code:
def Even(number):
val = False
if number >= 0:
if number % 2 == 0:
val = True
else:
if (-number) % 2 == 0:
val = True
return val

range1 = int(input("Enter the range of numbers: "))

for i in range(range1):
n=int(input("Enter an integer to check if it is even: "))
v = Even(n)
if v:
print ("This is Even")
else:
print ('This is ODD')

Output:
 3. A large chemical company pays its salespeople on a common basis. The
salespeople receive Rs. 200 per week plus 9% of their gross sales for
that week. For example, a salesperson who sells Rs. 5000 worth of
chemicals in a week receives Rs.200 plus 9% of 5000, or a total of
Rs.650. Develop a program that will input each salespersons gross sales
for last week and calculate and display the salespersons earnings.
Code:
def Sale(sales):
salary = 200 + (sales * 0.09)
return salary

sales = int(input("Enter the amount sold in Rs: "))

print("Your salary for this week is :", Sale(sales))

Output:
 4. Write a function to find the sum of the series: x+ x^2/2! + x^4/4! +
x^6/6! + x^8/8! + ……. + x^n/n!
Code:
def Series3(N, X):
SUM = 0
for i in range(1, N + 1):
fact = 1
for j in range(1, i + 1):
fact *= j
SUM = SUM + ((X ** i) / fact)
return SUM

N = int(input("Enter the range(n) : "))

X = int(input("Enter the value of x: "))
print ('The sum of the Series is: ',Series3(N, X))

Output:
 5. Write a function to find the sum of the series:1 + 1/3^2 + 1/5^2 + 1/7^2
+ 1/9^2 + ….. + 1/N^2

Code:
def Series4(N):
Sum = 0
for i in range(1, N + 1):
Sum = Sum + (1 / ((i + 2) ** 2))
return Sum

N = int(input("Enter the range(n) : "))

print(Series4(N))

Output:
 INDEX
FILE HANDLING

S. No. PROGRAMS TEACHER’S

SIGNATURE
1. Write a program to read the content of file line by line
and write it to another file except for the lines contains
“a” letter in it.
2. Write a program to read the content of file and display the
total number of consonants, uppercase, vowels and lower
case characters.
3. Write a program to Program to create binary file to store
Rollno,Name and Marks and update marks of entered
Rollno.
4. Write a program to create binary file to store Rollno
and Name, Search any Rollno and display name if Rollno
found otherwise “Rollno not found”.
5. Write a program to create CSV file and store empno,
name, salary and search for all names starting with ‘A’
and display name, salary and if not found display
appropriate message
6. Write a program to create CSV file and store empno,
name, salary and search for all names starting with ‘S’
with max 20 characters and display name, salary and if
not found display appropriate message.
 1. Write a program to read the content of file line by line and write it to
another file except for the lines contains “a” letter in it.

Code:
with open('Q1.txt','w') as f:
n='My name is Sahil Saxena\n Welcome to the jungle\n This is Python'
f.write(n)
f.close()
with open('Q1.txt','r') as f:
with open('N1.txt','w') as n:
r=f.readlines()
for i in r:
for j in i:
if 'a' or 'A' not in j:
n.write(j)

Output:
 2. Write a program to read the content of file and display the total number
of consonants, uppercase, vowels and lower-case characters.
CODE:
with open('Prac.txt','r') as f:
n = f.read()
t_vow=0
t_con=0
up_c=0
low_c=0
for i in n:
if i in 'aeiouAEIOU':
t_vow+=1
if i not in 'aeiouAEIOU':
t_con+=1
if i.isupper()==True:
up_c+=1
if i.islower()==True:
low_c+=1
print("No of Vowels: ", t_vow)
print("No of Consonants: ", t_con)
print("No of Uppercase : ", up_c)
print("No of Lowercase: ", low_c)
Output:
 3. Write a program to Program to create binary file to store Rollno,Name
and Marks and update marks of entered Rollno.

CODE:
import pickle
def insert():
record=[]

while True:
roll=int(input("Enter Student Roll No "))
name=input("Enter Student name: ")
d=[roll,name]
record.append(d)
val=input("Do you want to add more data (Y/N): ")
if val.lower()=='n':
break
with open('Prac','wb') as f:
pickle.dump(record,f)
def search():
with open('Prac', 'rb') as f:
record1=pickle.load(f)
found=0
rno=int(input("Enter Roll No to be searched: "))
for i in record1:
if i[0]==rno:
print(i[1],"Found!")
found=1
break
if found==0:
print("Record not found!")
while True:
print ('Choose one of the following options:')
print ('1. insert')
print ('2. Search')
print ('3. Exit')
val = int(input('Enter one of the above options:'))
if val == 1:
insert()

elif val == 2:
search()
else:
break
OUTPUT:
 4. Write a program to create binary file to store Rollno and Name,
Search any Rollno and display name if Rollno found otherwise “Rollno
not found”.
CODE:
import pickle
with open("employee.dat", "ab") as n:
rec = 1
print("Please Enter Records!")
print()
while True:
print("Record No.", rec)
e_no = int(input("Roll No: "))
e_name = input("Name: ")
data = [e_no, e_name]
pickle.dump(data, n)
ans = input("Do you wish to enter more records (y/n)? ")
rec = rec + 1
if ans.lower() == 'n':
print("Record entry OVER ")
print()
break

with open("employee.dat", "rb") as val:

r_no = int(input("Enter roll number to be found: "))
flag = 0
while True:
try:
emp_info = pickle.load(val)
if emp_info[0] == r_no:
print("Employee ID:", emp_info[0])
print("Employee Name:", emp_info[1])
flag = True
if flag == True:
print('Record found!')
else:
print('Record not found!')
except EOFError:
break

OUTPUT:
 5. Write a program to create CSV file and store empno, name, salary and
search for all names starting with ‘A’ and display name, salary and if
not found display appropriate message.

CODE:
import csv
with open('data1.csv','w',newline='') as f:
r=csv.writer(f)
r.writerow(['Emp_no','Name','Salary'])
list1=[]
while True:
emp_no=int(input("Enter Employee No: "))
emp_name=input("Enter Employee Name: ")
sal=int(input("Enter Salary of Employee: "))
list2=[emp_no,emp_name,sal]
list1.append(list2)
ch = input("Do you want to continue(Y/N): ")
if ch == 'N' or ch == 'n':
break
for i in list1:
r.writerow(i)
with open('data1.csv','r') as file:
read=csv.reader(file)
for i in read:
if i[1][0]=='A'or i[1][0]=='a':
print('name:',i[1])
print('salary:',i[2])
else:
print ("Not found")
OUTPUT:
 6. Write a program to create CSV file and store empno, name, salary and
search for all names starting with ‘S’ with max 20 characters and
display name, salary and if not found display appropriate message.

CODE:

import csv
with open('Data2.csv','w',newline='') as file1:
r=csv.writer(file1)
r.writerow(['Emp_no','Name','Salary'])
list1=[]
while True:
emp_no = int(input("Enter Employee No: "))
emp_name = input("Enter Employee Name: ")
sal = int(input("Enter Salary of Employee: "))
list2 = [emp_no, emp_name, sal]
list1.append(list2)
ch=input("Do you want to continue(Y/N): ")
if ch=='N' or ch=='n':
break
for i in list1:
r.writerow(i)

with open('Data2.csv','r') as file:

r=csv.reader(file)
for i in r:
if i[1][0]=='S'or i[1][0]=='s':
print('name:',i[1])
print('salary:',i[2])

else:
print ("Not found")
OUTPUT:
 INDEX
DATA STRUCTURE (STACKS)
S. PROGRAMS TEACHER’
No. S

SIGNATUR
E
1. Write a program to create a stack called Student containing
data values roll number and name. Create functions push() for
pushing data values, pop() for removing data values and show()
for displaying data values.
2. Write a menu driven program stack using list containing
Employee data:Employee Id(Eid) and Employee Name(Ename).
Push()
Pop()
Display()
3. Write a menu driven program implementing stack using list
containing book record:Book Number (Bno) and Book
Name(Bname) .
Push()
Pop()
Display()
4. Write two functions push() to add and pop() to remove elements
in Directory using dictionary with fields pin code and name of
city using dictionary.
 OUTPUT

1. Write a program to create a stack called Student containing data values

roll number and name. Create functions push() for pushing data values,
pop() for removing data values and show() for displaying data values.
Code: def PUSH(Stack):
n = int(input("Enter the Number of inputs: "))
for i in range(n):
Roll = int(input("Enter your roll number: "))
name = input("Enter your name: ")
record = (Roll, name)
Stack.append(record)

def POP(Stack):
if len(Stack) == 0:
print("UNDERFLOW")
else:
val = Stack.pop()
print(val)
if len(Stack) == 0:
top_val = None
else:
top_val = len(Stack) - 1
print("Top VAL: ", top_val)

def Display(Stack):
if len(Stack) == 0:
print("UNDERFLOW")
else:
for i in Stack:
print(i[0], ":", i[1])

Stack = []
while True:
print("Choose following options:")
print('''1. Push 2. Pop 3. Display 4. Exit''')
val = int(input("Enter the chosen value : "))
if val == 1:
PUSH(Stack)
elif val == 2:
POP(Stack)
elif val == 3:
Display(Stack)
else:
break
OUTPUT:
 2. Write a menu driven program stack using list containing Employee
data:Employee Id(Eid) and Employee Name(Ename). Push() Pop()
Display()

Code:
def PUSH(EMPDATA):
n = int(input("Enter the Number of inputs: "))
for i in range(n):
Eid = int(input("Enter your Employee ID: "))
Name = input("Enter your name: ")
record = (Eid, Name)
EMPDATA.append(record)

def POP(EMPDATA):
if len(EMPDATA) == 0:
print("UNDERFLOW")
else:
val = EMPDATA.pop()
print(val)
if len(EMPDATA) == 0:
top_val = None
else:
top_val = len(EMPDATA) - 1
print("Top VAL: ", top_val)

def Display(EMPDATA):
if len(EMPDATA) == 0:
print("UNDERFLOW")
else:
for i in EMPDATA:
print(i[0], ":", i[1])

EMPDATA = []
while True:
print("Choose following options:")
print('''1. Push 2. Pop 3. Display 4. Exit''')
val = int(input("Enter the chosen value : "))
if val == 1:
PUSH(EMPDATA)
elif val == 2:
POP(EMPDATA)
elif val == 3:
Display(EMPDATA)
else:
break
OUTPUT:
 3. Write a menu driven program implementing stack using list containing
book record:Book Number (Bno) and Book Name(Bname) . Push() Pop()
Display()

CODE:
def PUSH(BOOKS):
n = int(input("Enter the Number of inputs: "))
for i in range(n):
Bno = int(input("Enter your Book ID: "))
Bname = input("Enter Book name: ")
record = (Bno, Bname)
BOOKS.append(record)

def POP(BOOKS):
if len(BOOKS) == 0:
print("UNDERFLOW")
else:
val = BOOKS.pop()
print(val)
if len(BOOKS) == 0:
top_val = None
else:
top_val = len(BOOKS) - 1
print("Top VAL: ", top_val)

def Display(BOOKS):
if len(BOOKS) == 0:
print("UNDERFLOW")
else:
for i in BOOKS:
print(i[0], ":", i[1])

BOOKS = []
while True:
print("Choose following options:")
print('''1. Push 2. Pop 3. Display 4. Exit''')
val = int(input("Enter the chosen value : "))
if val == 1:
PUSH(BOOKS)
elif val == 2:
POP(BOOKS)
elif val == 3:
Display(BOOKS)
else:
break
OUTPUT:
 4. Write two functions push() to add and pop() to remove elements in
Directory using dictionary with fields pin code and name of city using
dictionary.

CODE:
def PUSH(City):
n = int(input("Enter the Number of inputs: "))
for i in range(n):
Bno = int(input("Enter your pin number: "))
Bname = input("Enter City: ")
City[Bno] = Bname

def POP(City):
if len(City) == 0:
print("UNDERFLOW")
else:
val = City.popitem()
print(val)
if len(City) == 0:
top_val = None
else:
top_val = len(City) - 1
print("Top VAL: ", top_val)

def Display(City):
if len(City) == 0:
print("UNDERFLOW")
else:
for i in City:
print(i, ":", City[i])

City = {}
while True:
print("Choose following options:")
print('''1. Push 2. Pop 3. Display 4. Exit''')
val = int(input("Enter the chosen value : "))
if val == 1:
PUSH(City)
elif val == 2:
POP(City)
elif val == 3:
Display(City)
else:
break
OUTPUT:
 INDEX
SQL GROUP FUNCTION
S. No. PROGRAMS TEACHER’S
SIGNATURE
1. Consider the table ITEM and CUSTOMER given below. Write SQL
commands in MySql for (1) to (4) and give output for SQL queries (5) to
(8) 
TABLE: ITEM 

 
TABLE: CUSTOMER 

 
   
A. SQL Commands: 
1. To display the details of those customers whose city is
Delhi 
1.  To display the details of those items whose Price is in the
range of 35000 to 55000. 
1. To display the CustomerName and City from table
Customer and Itemname and Price from table Item, with their
corresponding matching I_Id. 
1. To increase the Price of all items by 1000 in the table item 
A. Output Queries: 
1. SELECT DISTINCT(City) FROM Customer; 
1. SELECT ItemName, MAX(Price), count(*) FROM item
GROUP BY ItemName; 
1. SELECT CustomerName, Manufacturer  
FROM Item,Customer WHERE Item.I_ID=Customer.I_ID; 
1. SELECT ItemName, Price*100 FROM Item WHERE
Manufacturer='ABC'; 
2. Consider the following tables DOCTOR and SALARY and write MySql
 commands for the questions (1) to (4) and give output for SQL queries (5)
to (6) 
TABLE: DOCTORS 

 
 
TABLE: SALARY 

 
A. SQL Commands: 
1. Display NAME of all doctors who are in MEDICINE and
having more than 10 years from the table DOCTOR  
1. Display the average salary of all doctors Working in “ENT”
department using the tables DOCTORS and SALARY.BASIC
= SALARY.BASIC + SALARY.ALLOWANCE 
1. Display the minimum ALLOWANCE of female doctors 
1. Display the highest consultation fee for Male doctors 
A. Output Queries: 
1. SELECT count(*) from DOCTORS where SEX= “F”; 
1. SELECT NAME, DEPT, BASIC from DOCTORS,
SALARY where DEPT = “ENT” and DOCTORS.ID=
SALARY.ID 
3. Consider the following tables GARMENT and FABRIC and write MySql
commands for the questions (1) to (4) and give outputs for SQL queries (5)
to (8). 
TABLE: GARMENT 
  
TABLE: FABRIC 

 
A. SQL Commands: 
1. To display GCODE and DESCRIPTION of each
GARMENT in descending order of GCODE. 
1. To display the details of all the GARMENTs, which have
READYDATE in between 08-DEC-07 and 16-JUN-08
(inclusive of both dates). 
1.  To display the average PRICE of all the GARMENTs,
which are made up of FABRIC with FCODE as FO3. 
1. To display FABRIC wise highest and lowest price of
GARMENTs from GARMENT table. (Display FCODE of each
GARMENT along with highest and lowest Price). 
A. Output Queries: 
1. SELECT SUM(PRICE) FROM GARMENT WHERE
FCODE = ‘F0l’; 
1. SELECT DESCRIPTION, TYPE FROM GARMENT,
FABRIC WHERE GARMENT.FCODE = FABRIC.FCODE
AND GARMENT.PRICE >= 1260; 
1. SELECT MAX(FCODE) FROM FABRIC; 
1. SELECT COUNT(DISTINCT PRICE) FROM
GARMENT; 
4. Consider the following tables worker and paylevel and write MySql
commands for the questions (1) to (4) and give outputs for SQL queries (5)
to (8). 
TABLE: worker 
  
TABLE: paylevel 

 
A. SQL Commands: 
1. To display the details of all WORKERs in descending order
of DOB. 
1. To display NAME and DESIGN of those WORKERs
whose PLEVEL is either P001 or P002.  
1. To display the content of all the WORKERs table whose
DOB is in between ‘19-Jan-1984 and ’18-Jan-1987'. 
1. To add a new row in WORKER table with the following
data: 
19, ‘Daya Kishore’, ‘Operator’, ‘P003’, 19-Jun-2008’, ’11-Jul-
1984' 
A. Output Queries: 
1.  SELECT COUNT (PLEVEL), PLEVEL FROM WORKER
GROUP BY PLEVEL; 
1.  SELECT MAX(DOB), MIN(DOJ) FROM WORKER; 
1. SELECT Name, Pay FROM WORKER W, PAYLEVEL P 
WHERE W.PLEVEL = P.PLEVEL AND W.ECODE<13;  
1. SELECT PLEVEL, PAY+ALLOWANCE FROM PAYLEVEL
WHERE PLEVEL = 'POO3'; 

5. Consider the following tables cabhub and customer and write MySql
commands for the questions (1) to (4) and give outputs for SQL queries (5)
to (8). 
TABLE: cabhub 
  
TABLE: customer 

 
A. SQL Commands: 
1. To display the names of all the white colored vehicles. 
1. To display name of vehicle, make and capacity of all the
vehicles in ascending order of their sitting Capacity. 
1. To display the highest charges at which a vehicle can be
hired from CABHUB. 
1. To display all the Customer name and the corresponding
name of the vehicle hired by them. 
A. Output Queries: 
1. SELECT COUNT(DISTINCT Make) From CABHUB; 
1. SELECT MAX(Charges), MIN(Charges) FROM
CABHUB; 
1. SELECT COUNT(*), Make FROM CABHUB; 
1. SELECT Vehicle FROM CABHUB WHERE Capacity=4; 
1. Consider the table ITEM and CUSTOMER given below. Write SQL
commands in MySql for (1) to (4) and give output for SQL queries (5) to (8)

1. To display the details of those customers whose city is Delhi

select * from CUSTOMER where city='delhi';

2. To display the details of those items whose Price is in the range of 35000 to
55000.

select * from ITEM where Price>=35000 and Price<=55000;

3. To display the CustomerName and City from table Customer and Itemname
and Price from table Item, with their corresponding matching I_Id.

select CustomerName,City,ItemName,Price from CUSTOMER,ITEM where

Item.I_ID=Customer.I_ID;

4. To increase the Price of all items by 1000 in the table item

Update Item set Price=Price+1000;

2. SELECT DISTINCT(City) FROM Customer;

3. SELECT ItemName, MAX(Price), count(*) FROM item GROUP BY

ItemName;
7. SELECT CustomerName, Manufacturer FROM Item,Customer WHERE
Item.I_ID=Customer.I_ID;

8. SELECT ItemName, Price*100 FROM Item WHERE Manufacturer='ABC';

 2. Consider the following tables DOCTOR and SALARY and write
MySql commands for the questions (1) to (4) and give output for
SQL queries (5) to (6)

1. Display NAME of all doctors who are in MEDICINE and having more than 10
years from the table DOCTOR

select Name from Doctor where Dept=”Medicine” and Experience>10

2. Display the average salary of all doctors Working in “ENT” department using
the tables DOCTORS and SALARY.BASIC = SALARY.BASIC +
SALARY.ALLOWANCE

select avg(basic+allowance) from Doctor,Salary where Dept=”Ent” and

Doctor.Id=Salary.Id

3. Display the minimum ALLOWANCE of female doctors

select min(Allowance) from Doctro,Salary where Sex=”F” and Doctor.Id=Salary.Id

4. Display the highest consultation fee for Male doctors

select max(Consulation) from Doctor,Salary where Sex=”M” and Doctor.Id=Salary.Id

5. SELECT count(*) from DOCTORS where SEX= “F”;

6. SELECT NAME, DEPT, BASIC from DOCTORS, SALARY where DEPT =

“ENT” and DOCTORS.ID= SALARY.ID
 3. Consider the following tables GARMENT and FABRIC and write
MySql commands for the questions (1) to (4) and give outputs for
SQL queries (5) to (8).

1. To display GCODE and DESCRIPTION of each GARMENT in descending

order of GCODE.

select GCODE,DESCRIPTION from GARMENT order by GCODE desc

2. To display the details of all the GARMENTs, which have READYDATE in

between 08-DEC-07 and 16-JUN-08 (inclusive of both dates).

select * from where READYDATE between '08-DEC-07' and '16-JUN-08'

3. To display the average PRICE of all the GARMENTs, which are made up of
FABRIC with FCODE as FO3.

select PRICE from GARMENT where FCODE='FO3'

4. To display FABRIC wise highest and lowest price of GARMENTs from

GARMENT table. (Display FCODE of each GARMENT along with highest and
lowest Price).

select FCODE, max(PRICE), min(PRICE) FROM GARMENT GROUP BY FCODE;

5. SELECT SUM(PRICE) FROM GARMENT WHERE FCODE = ‘F02’;

6. SELECT DESCRIPTION, TYPE FROM GARMENT, FABRIC WHERE

GARMENT.FCODE = FABRIC.FCODE AND GARMENT.PRICE >= 1260;
7. SELECT MAX(FCODE) FROM FABRIC;

8. SELECT COUNT(DISTINCT PRICE) FROM GARMENT;

 4. Consider the following tables worker and paylevel and write MySql
commands for the questions (1) to (4) and give outputs for SQL
queries (5) to (8).

1. To display the details of all WORKERs in descending order of DOB.

select NAME from WORKER order by DOBDESC

2. To display NAME and DESIGN of those WORKERs whose PLEVEL is either

P001 or P002.

select NAME, DESIGN from WORKER where PLEVEL='POO1' or PLEVEL='POO2'

3. To display the content of all the WORKERs table whose DOB is in between
‘19-Jan-1984 and ’18-Jan-1987'.

select * FROM WORKER where DOB '19-JAN-1984' and '18-JAN-1987'

4. To add a new row in WORKER table with the following data: 19, ‘Daya
Kishore’, ‘Operator’, ‘P003’, 19-Jun-2008’, ’11-Jul-1984'

insert into WORKER values (19,"Daya Kishore", "Operator", "P003",'19-Jun-

2008','11-Jul-1984')

5. SELECT COUNT2(PLEVEL), PLEVEL FROM WORKER GROUP BY

PLEVEL;

6. SELECT MAX(DOB), MIN(DOJ) FROM WORKER;

7. SELECT Name, Pay FROM WORKER W, PAYLEVEL P WHERE
W.PLEVEL = P.PLEVEL AND W.ECODE<13;

8. SELECT PLEVEL, PAY+ALLOWANCE FROM PAYLEVEL WHERE

PLEVEL = 'POO3';
 5. Consider the following tables cabhub and customer and write MySql
commands for the questions (1) to (4) and give outputs for SQL
queries (5) to (8).

1. To display the names of all the white colored vehicles.

select vehicle_name from cabhub where colour = "WHITE"

2. To display name of vehicle, make and capacity of all the vehicles in ascending
order of their sitting Capacity.

select vehicle_name, make, capacity from cabhub order by Capacity asc

3. To display the highest charges at which a vehicle can be hired from CABHUB.

select max(Charges) from cabhub

4. To display all the Customer name and the corresponding name of the vehicle
hired by them.

select name1,vehicle_name from cabhub, CUSTOMER where CUSTOMER.vcode =

cabhub.vcode

(5). SELECT COUNT(DISTINCT Make) From CABHUB;

6. SELECT MAX(Charges), MIN(Charges) FROM CABHUB;

kk
PYTHON INTERFACE WITH MYSQL
S. No. PROGRAMS TEACHER’S
SIGNATURE

1. Write a program to create and insert the data into the following table:
Field Name Data Type Constraints

Roll no Number Primary key

Name Char(20)

Sclass Varchar(5)

Age Number

Gender Char(10)

Date of Date DEFAULT

Joining SYSDATE

Percentage Float

2. Write a python interface programs using MySQL module using following

specifications of Student table. Use appropriate connection to perform SQL
query connections.

Field Name Data Type Constraints

Roll no Number Primary key

Name Char(20)

Sclass Varchar(5)

Age Number

Gender Char(10)

Date of Joining Date DEFAULT

SYSDATE

Percentage Float

Write a menu driven program

a)to insert n records from user
 b) to delete record based on rollno
c)to fetch the records of student whose Date of joining is in the
month of June.
d) to display all records

3. Write a python interface programs using MySQL module using following

specifications of Employee table. Use appropriate connection to perform
SQL query connections.

Field Name Data Type Constraints

Emp No Number Primary key

Name Char(20)

Date of Joining Date DEFAULT

SYSDATE

Age Number

Address Char(10)

Salary Float DEFAULT 10000

Commission Float

Write a menu driven program

a) to insert data in the above table.

b) to update the address based on Emp No
c) to show name and total salary (salary + commission).
d) To display all records
4. Write a python interface programs using MySQL module using following
specifications of Employee table. Use approprate connection to perform
SQL query connections.

Field Name Data Type Constraints

Emp id Number Primary key

Name Char(20)

ESalary float

EAge Number

EGender Char(1)

Date of Joining Date DEFAULT

SYSDATE

Designation Char(40)
 Write a menu driven program
a)to insert n records from user
b) to search all employee whose salary is above 25000

5. Write a python interface programs using MySQL module using following

specifications of Employee table. Use approprate connection to perform
SQL query connections.

Field Name Data Type Constraints

Emp id Number Primary key

Name Char(20)

ESalary float

EAge Number

EGender Char(1)

Date of Joining Date DEFAULT

SYSDATE

Designation Char(40)

Write a menu driven program

a)to insert n records from user
b) to display all employee whose designation is “Planning Engineer”
 1. Write a program to create and insert the data into the
following table:
Field Name Data Type Constraints

Roll no Number Primary key

Name Char(20)

Sclass Varchar(5)

Age Number

Gender Char(10)

Date of Date DEFAULT

Joining SYSDATE

Percentage Float

Code:

Output:

2. Write a python interface programs using MySQL module

using following specifications of Student table. Use
appropriate connection to perform SQL query connections.
 Field Name Data Type Constraints

Roll no Number Primary key

Name Char(20)

Sclass Varchar(5)

Age Number

Gender Char(10)

Date of Joining Date DEFAULT

SYSDATE

Percentage Float

Write a menu driven program

a)to insert n records from user
b) to delete record based on rollno
c)to fetch the records of student whose Date of joining is in the month of
Novembor.
d) to display all records
CODE:

def Insert():
import mysql.connector as mys
mycon = mys.connect(host="localhost", username='root',
password='Soumil008$',database = 'PRACTICAL_FILE')
mycursor = mycon.cursor()
n = int(input("Enter the number of records: "))
for i in range(n):
Roll_no = int(input("Enter roll number: "))
name = input("Enter name: ")
Class = input("Enter your class: ")
Age = int(input("Enter your age please: "))
Gender = input("Enter your gender please (MALE/FEMAlE): ")
percentage = float(input("Enter your percentage: "))
sql = "INSERT INTO STUDENTS
(Roll_no,Name,Sclass,Age,Gender,percentage) VALUES ('%d','%s','%s','%d','%s',"
\
"'%d') " % (Roll_no,name,Class,Age,Gender,percentage)
mycursor.execute(sql)
mycon.commit()
mycursor.close()
mycon.close()
 print ("Values Added")
def Delete():
import mysql.connector as mys
mycon = mys.connect(host="localhost", username='root',
password='Soumil008$', database='PRACTICAL_FILE')
mycursor = mycon.cursor()
roll = int(input("Enter roll number to delete"))
mycursor.execute(f"DElETE FROM students where Roll_no = {roll} ")
print ("Deletion done! ")
mycon.commit()
def fetch():
import mysql.connector as mys
mycon = mys.connect(host="localhost", username='root',
password='Soumil008$', database='PRACTICAL_FILE')
mycursor = mycon.cursor()
mycursor.execute("Select * from students where Date_of_joining like '%-06-
%' ")
record = mycursor.fetchall()
for i in record:
print (i[0],":",i[1],":",i[2],":",i[3],":",i[4],":",i[5],":",i[6])

def display():
import mysql.connector as mys
mycon = mys.connect(host="localhost", username='root',
password='Soumil008$', database='PRACTICAL_FILE')
mycursor = mycon.cursor()
mycursor.execute("Select * from students ")
record = mycursor.fetchall()
for i in record:
print(i[0], ":", i[1], ":", i[2], ":", i[3], ":", i[4], ":", i[5],
":", i[6])

import mysql.connector as mys

mycon = mys. connect(host="localhost", username='root', password='Soumil008$')
mycursor = mycon.cursor()
mycursor.execute('''CREATE DATABASE IF NOT EXISTS PRACTICAL_FILE;''')
mycursor.execute('''use PRACTICAL_FILE;''')
mycursor.execute('''create table if not exists students(Roll_no int primary
key, Name char(20),Sclass varchar(50),
Age int, Gender char(10),Date_of_joining Date Default(sysdate()),percentage
float) ''')

while True:
print ("Choose one of the following options")
print ("1. Insert N records from the user")
print ("2. Delete record on the basis of Roll number")
print ("3. To fetch records of the Student whose date of Joining is in the
month of june")
print ("4. To display all the records")
print ("5. Exit")
val = int(input("Enter option: "))
if val == 1:
Insert()

elif val == 2:
Delete()
 elif val == 3:
fetch()

elif val == 4:
display()

elif val == 5:
break

Output:

INDEX
 3. Write a python interface programs using MySQL module
using following specifications of Employee table. Use
appropriate connection to perform SQL query connections.
Field Name Data Type Constraints

Emp No Number Primary key

Name Char(20)

Date of Joining Date DEFAULT

SYSDATE

Age Number

Address Char(10)

Salary Float DEFAULT 10000

Commission Float

Write a menu driven program

a) to insert data in the above table.
b) to update the address based on Emp No
c) to show name and total salary (salary + commission).
d) To display all records
Code:
def Insert():
import mysql.connector as mys
mycon = mys.connect(host="localhost", username='root',
password='Soumil008$',database = 'PRACTICAL_FILE')
mycursor = mycon.cursor()
n = int(input("Enter the number of records: "))
for i in range(n):
Roll_no = int(input("Enter roll number: "))
name = input("Enter name: ")
Class = input("Enter your class: ")
Age = int(input("Enter your age please: "))
Gender = input("Enter your gender please (MALE/FEMAlE): ")
percentage = float(input("Enter your percentage: "))
sql = "INSERT INTO STUDENTS
(Roll_no,Name,Sclass,Age,Gender,percentage) VALUES ('%d','%s','%s','%d','%s',"
\
"'%d') " % (Roll_no,name,Class,Age,Gender,percentage)
mycursor.execute(sql)
mycon.commit()
mycursor.close()
mycon.close()
print ("Values Added")
def Delete():
import mysql.connector as mys
mycon = mys.connect(host="localhost", username='root',
password='Soumil008$', database='PRACTICAL_FILE')
mycursor = mycon.cursor()
roll = int(input("Enter roll number to delete"))
mycursor.execute(f"DElETE FROM students where Roll_no = {roll} ")
print ("Deletion done! ")
mycon.commit()
def fetch():
import mysql.connector as mys
mycon = mys.connect(host="localhost", username='root',
password='Soumil008$', database='PRACTICAL_FILE')
mycursor = mycon.cursor()
mycursor.execute("Select * from students where Date_of_joining like '%-06-
%' ")
record = mycursor.fetchall()
for i in record:
print (i[0],":",i[1],":",i[2],":",i[3],":",i[4],":",i[5],":",i[6])

def display():
import mysql.connector as mys
mycon = mys.connect(host="localhost", username='root',
password='Soumil008$', database='PRACTICAL_FILE')
mycursor = mycon.cursor()
mycursor.execute("Select * from students ")
record = mycursor.fetchall()
for i in record:
print(i[0], ":", i[1], ":", i[2], ":", i[3], ":", i[4], ":", i[5],
":", i[6])

import mysql.connector as mys

mycon = mys. connect(host="localhost", username='root', password='Soumil008$')
mycursor = mycon.cursor()
mycursor.execute('''CREATE DATABASE IF NOT EXISTS PRACTICAL_FILE;''')
mycursor.execute('''use PRACTICAL_FILE;''')
mycursor.execute('''create table if not exists students(Roll_no int primary
key, Name char(20),Sclass varchar(50),
Age int, Gender char(10),Date_of_joining Date Default(sysdate()),percentage
float) ''')

while True:
print ("Choose one of the following options")
print ("1. Insert N records from the user")
print ("2. Delete record on the basis of Roll number")
print ("3. To fetch records of the Student whose date of Joining is in the
month of june")
print ("4. To display all the records")
print ("5. Exit")
val = int(input("Enter option: "))
if val == 1:
Insert()

elif val == 2:
Delete()
 elif val == 3:
fetch()

elif val == 4:
display()

elif val == 5:
break

Output:
 4. Write a python interface programs using MySQL module
using following specifications of Employee table. Use
appropriate connection to perform SQL query connections.

Field Name Data Type Constraints

Emp id Number Primary key
Name Char(20)
ESalary float
EAge Number
EGender Char(1)
Date of Joining Date DEFAULT
SYSDATE
Designation Char(40)

Write a menu driven program

a) to insert n records from user
b) to search all employee whose salary is above 25000
CODE:
def Insert():
print()
import mysql.connector as mys
mycon = mys.connect(host="localhost", username='root',
password='Soumil008$', database='PRACTICAL_FILE')
mycursor = mycon.cursor()
n = int(input("Enter the number of records: "))
for i in range(n):
print()
emp_id = int(input("Enter emp number: "))
name = input("Enter name: ")
salary = float(input("Enter your salary: "))
eage = int(input("Enter your age: "))
egender = input('Enter your gender: ')
designation = input("Enter your designation: ")
sql = "INSERT INTO emp1 (Emp_id,Name,Esalary,Eage,Egender,Designation)
VALUES ('%d','%s','%d','%d','%s'," \
"'%s') " % (emp_id,name,salary,eage,egender,designation)
mycursor.execute(sql)
mycon.commit()
mycursor.close()
mycon.close()
print("Values Added")

def Search():
print()
import mysql.connector as mys
mycon = mys.connect(host="localhost", username='root',
password='Soumil008$', database='PRACTICAL_FILE')
mycursor = mycon.cursor()
mycursor.execute("Select * from emp1 where Esalary>25000 ")
record = mycursor.fetchall()
for i in record:
print(i[0], ":", i[1], ":", i[2], ":", i[3], ":", i[4], ":", i[5],
":", i[6])

def Display():
print()
import mysql.connector as mys
mycon = mys.connect(host="localhost", username='root',
password='Soumil008$', database='PRACTICAL_FILE')
mycursor = mycon.cursor()
mycursor.execute("Select * from emp1 ")
record = mycursor.fetchall()
for i in record:
print(i[0], ":", i[1], ":", i[2], ":", i[3], ":", i[4], ":", i[5],
":", i[6])

import mysql.connector as mys

mycon = mys.connect(host="localhost", username='root', password='Soumil008$')
mycursor = mycon.cursor()
mycursor.execute('''CREATE DATABASE IF NOT EXISTS PRACTICAL_FILE;''')
mycursor.execute('''use PRACTICAL_FILE;''')
mycursor.execute('''create table if not exists emp1(Emp_id int primary key,
Name char(50),Esalary float,Eage int,
Egender varchar(50),Date_of_joining Date Default(sysdate()), Designation
Char(40) )''')
while True:
print("Choose one of the following options")
print("1. Insert N records from the user")
print("2. to search all employees with salary above 25000")
print("3. To display all the records")
print("4. Exit")
val = int(input("Enter option: "))
if val == 1:
Insert()
elif val == 2:
Search()
elif val == 3:
Display()
else:
break

OUTPUT:
5. Write a python interface programs using MySQL module using following
specifications of Employee table. Use approprate connection to perform SQL
query connections.

Field Name Data Type Constraints

Emp id Number Primary key
Name Char(20)
ESalary float
EAge Number
EGender Char(1)
Date of Joining Date DEFAULT
SYSDATE
Designation Char(40)

Write a menu driven program

a) to insert n records from user
b) to display all employee whose designation is “Planning Engineer”
Code:
def Insert():
print()
import mysql.connector as mys
mycon = mys.connect(host="localhost", username='root',
password='Soumil008$', database='PRACTICAL_FILE')
mycursor = mycon.cursor()
n = int(input("Enter the number of records: "))
for i in range(n):
print()
emp_id = int(input("Enter emp number: "))
name = input("Enter name: ")
salary = float(input("Enter your salary: "))
eage = int(input("Enter your age: "))
egender = input('Enter your gender: ')
designation = input("Enter your designation: ")
sql = "INSERT INTO emp2 (Emp_id,Name,Esalary,Eage,Egender,Designation)
VALUES ('%d','%s','%d','%d','%s'," \
"'%s') " % (emp_id,name,salary,eage,egender,designation)
mycursor.execute(sql)
mycon.commit()
mycursor.close()
mycon.close()
print("Values Added")

def Search():
print()
import mysql.connector as mys
mycon = mys.connect(host="localhost", username='root',
password='Soumil008$', database='PRACTICAL_FILE')
mycursor = mycon.cursor()
mycursor.execute("Select * from emp2 where Designation = 'engineer' ")
record = mycursor.fetchall()
for i in record:
print(i[0], ":", i[1], ":", i[2], ":", i[3], ":", i[4], ":", i[5],
":", i[6])

def Display():
print()
import mysql.connector as mys
mycon = mys.connect(host="localhost", username='root',
password='Soumil008$', database='PRACTICAL_FILE')
mycursor = mycon.cursor()
mycursor.execute("Select * from emp2 ")
record = mycursor.fetchall()
for i in record:
print(i[0], ":", i[1], ":", i[2], ":", i[3], ":", i[4], ":", i[5],
":", i[6])

import mysql.connector as mys

mycon = mys.connect(host="localhost", username='root', password='Soumil008$')
mycursor = mycon.cursor()
mycursor.execute('''CREATE DATABASE IF NOT EXISTS PRACTICAL_FILE;''')
mycursor.execute('''use PRACTICAL_FILE;''')
mycursor.execute('''create table if not exists emp2(Emp_id int primary key,
Name char(50),Esalary float,Eage int,
Egender varchar(50),Date_of_joining Date Default(sysdate()), Designation
Char(40) )''')
while True:
print("Choose one of the following options")
print("1. Insert N records from the user")
print("2. to search all employees whose designation is engineer")
print("3. To display all the records")
print("4. Exit")
val = int(input("Enter option: "))
if val == 1:
Insert()
elif val == 2:
Search()
elif val == 3:
Display()
else:
break

Output: