ST.

JOSEPH’S CONVENT HIGH SCHOOL

(C.B.S.E) CHITTARANJAN
SESSION:-2021-22

COMPUTER SCIENCE
SUB. CODE - 083
TERM-II PRACTICAL FILE

NAME:___________________________

STREAM:__________________________

BOARD ROLL NO:-___________________

 Certificate

This is to certify that _______________________,

Roll No:_________, of Class:XII ,Session :2021-22

has prepared the Practical file as per the prescribed

syllabus of Term-II

COMPUTER SCIENCE (SUB. CODE:-083)

under my supervision, I am completely satisfied by

the performance. I wish him/her all the success in
life.

Principal’s Signature Subject Teacher’s Signature

External’s Signature
 TERM-II INDEX PAGE
Section- A:- Python
PROGRAMS DATE SIGN.
Program 1:- Write a menu based program to perform the operation on stack in python.

Program 2:- Write a menu based program to Maintaining Book details like bcode, btitle
and price using stacks in python.
Program 3:- Write a menu based program to add, delete and display the record of hostel
using list as stack data structure in python. Record of hostel contains the fields : Hostel
number, Total Students and Total Rooms.

Section-B :- Mysql
PROGRAMS DATE SIGN.
1. Write SQL query to create a database Employee.
2. Write SQL query to open database Employee.
3. Write SQL query to create following Table name empl.

4. Write SQL query to show the structure of table.

5. Write SQL query to Insert 10 records same as it is in image.
6. Write SQL query to display all the records from table empl.
7. Write SQL query to display EmpNo and EName of all employees from the table empl.
8. Write SQL query to display Ename, Sal, and Sal added with comm from table empl.
9. Write SQL query to display Ename, Sal, and deptno who are not getting commission
from table empl.
10. Write SQL query to display Eno, Ename ,Sal, and Sal*12 as Annual Salary whose
commission is not NULL from table
11. Write SQL query to display the details of employees whose name have only four
letters.
12. Write SQL query to display name, job title and salary of employee who do not have
manager.
13. Write SQL query to display the name of employee whose name contains “A” as third
letter.
14. Write SQL query to display the name of departments. Each department should be
displayed once (DISTINCT).
15. Write SQL query to display the name and salary of those employees whose salary is
between 35000 and 40000. (BETWEEN)
16. Write SQL query to display tables data according to ascending order of sal.
17. Write SQL query to change EName MITA by MIRA.
18. Write SQL query to delete records whose deptno=10.
19. Write SQL query to add a new column Phno.
20. Write SQL query to delete entire table IF EXISTING.
 PROGRAMS DATE SIGN.

21. Queries for Aggregate functions- SUM( ), AVG( ), MIN( ), MAX( ), COUNT( ) .
22. TEXT FUNCTIONS ( TRIM FUNCTION, SUBSTR FUNCTION, LEFT
FUNCTION, RIGHT FUNCTION , MID FUNCTION )
23. MATH FUNCTIONS ( POWER (), ROUND (), MOD () ).
24. DATE FUNCTIONS NOW (), DATE (), MONTH (), MONTHNAME (), YEAR (),
DAY (), DAYNAME ()
25. Querying and manipulating data using Group by, Having, Order by.
26. Create a table DEPT and show Cartesian Product, JOIN
(Cartesian Join, Equi Join, Natural Join)

Section - C (Python MySql Connectivity)

PROGRAMS DATE SIGN.

27. Program to connect with database and store record of employee and display records.
28: Program to connect with database and update the employee record of entered empno.
29. Program to connect with database and search employee number in table employee and
display record, if empno not found display appropriate message.
30. Program to connect with database and delete the employee record of entered empno.

*********************************
 Section-A ( PYTHON)
Program 1:- Write a menu based program to perform the operation on stack in python.
Source Code:-

def isEmpty(S):
if len(S)==0:
return True
else:
return False

def Push(S,item):
S.append(item)
top=len(S)-1

def Pop(S):
if isEmpty(S):
return "Underflow"
else:
val = S.pop()
if len(S)==0:
top=None
else:
top=len(S)-1
return val

def Peek(S):
if isEmpty(S):
return "Underflow"
else:
top=len(S)-1
return S[top]

def Show(S):
if isEmpty(S):
print("Sorry No items in Stack ")
else:
t = len(S)-1
print("(Top)",end=' ')
while(t>=0):
print(S[t],"<==",end=' ')
t-=1
print()

# main begins here

S=[] #Stack
top=None
while True:
print("**** STACK DEMONSTRATION ******")
print("1. PUSH ")
print("2. POP")
print("3. PEEK")
print("4. SHOW STACK ")
print("0. EXIT")
ch = int(input("Enter your choice :"))
if ch==1:
val = int(input("Enter Item to Push :"))
 Push(S,val)
elif ch==2:
val = Pop(S)
if val=="Underflow":
print("Stack is Empty")
else:
print("\nDeleted Item was :",val)
elif ch==3:
val = Peek(S)
if val=="Underflow":
print("Stack Empty")
else:
print("Top Item :",val)
elif ch==4:
Show(S)
elif ch==0:
print("Bye")
break
Program 2:- Write a menu based program to Maintaining Book details like bcode, btitle and
price using stacks in python.
Source Code:-

book=[]
def push():
bcode=input("Enter bcode:- ")
btitle=input("Enter btitle:- ")
price=input("Enter price:- ")
bk=(bcode,btitle,price)
book.append(bk)
def pop():
if(book==[]):
print("Underflow / Book Stack in empty")
else:
bcode,btitle,price=book.pop()
print("poped element is ")
print("bcode:- ",bcode," btitle:- ",btitle," price:- ",price)
def traverse():
if not (book==[]):
n=len(book)
for i in range(n-1,-1,-1):
print(book[i])
else:
print("Empty , No book to display")
while True:
print("Book Stall")
print("*"*40)
print("1. Push")
print("2. Pop")
print("3. Traversal")
print("4. Exit")
ch=int(input("Enter your choice:- "))
if(ch==1):
push()
elif(ch==2):
pop()
elif(ch==3):
traverse()
elif(ch==4):
print("End")
break
else:
print("Invalid choice")
Program 3:- Write a menu based program to add, delete and display the record of hostel using
list as stack data structure in python. Record of hostel contains the fields : Hostel number,
Total Students and Total Rooms.
Source Code:-
host=[ ]
ch='y'
def push(host):
hn=int(input("Enter hostel number:-"))
ts=int(input("Enter Total students:-"))
tr=int(input("Enter total rooms:-"))
temp=[hn,ts,tr]
host.append(temp)

def pop(host):
if(host==[]):
print("No Record")
else:
print("Deleted Record is :",host.pop())

def display(host):
l=len(host)
print("Hostel Number\tTotal Students\tTotal Rooms")
for i in range(l-1,-1,-1):
print(host[i][0],"\t\t",host[i][1],"\t\t",host[i][2])

while(ch=='y' or ch=='Y'):
print("St Joseph's Convent Hostel")
print("*"*40)
print("\n")
print("1. Add Record")
print("2. Delete Record")
print("3. Display Record")
print("4. Exit")
op=int(input("Enter the Choice:-"))
if(op==1):
push(host)
elif(op==2):
pop(host)
elif(op==3):
display(host)
elif(op==4):
break
ch=input("Do you want to enter more(Y/N)")
 Section-B ( MYSQL )
1. Write SQL query to create and show a database Employee.

2. Write SQL query to open database Employee.

3. Write SQL query to create following Table name empl.

create table empl(empno int(4), ename char(20),job char(10), mgr int(4), hiredatedate,sal
int(4), comm int(3), deptno int(2));
4. Write SQL query to show the structure of table.

5. Write SQL query to Insert 10 records same as it is in image.

Write Insert Query for 10 times.

6. Write SQL query to display all the records from table empl.
Ans: select * from empl;
 7. Write SQL query to display EmpNo and EName of all employees from the table empl.
Ans: select EmpNo, EName from empl;

8. Write SQL query to display Ename, Sal, and Sal added with comm from table empl.
Ans: select ename,sal,sal+comm from empl;

9. Write SQL query to display Ename, Sal, and deptno who are not getting commission from table
Ans:- select ename,sal,deptno from empl where comm IS NULL;

10. Write SQL query to display Eno, Ename ,Sal, and Sal*12 as Annual Salary whose commission is not NULL
from table empl.
Ans:- select empno,ename,sal,sal*12 "Annal Salary" from empl where comm IS NOT NULL;
11. Display the details of employees whose name have only four letters.
Ans: select * from empl where EName like “________ ‟;

12. Display name, job title and salary of employee who do not have manager.
Ans: select EName, job, sal from empl where mgr = NULL;

13. Display the name of employee whose name contains “A”as fourth letter.
Ans: select EName from empl where EName like “_ _ _A%”

14. Display the name of departments. Each department should be displayed once.
Ans: SELECT DISTINCT(Dept) FROM EMPLOYEE;

15. Display the details of all employee whose annual salary is between 25000 to 40000.Ans:
select * from empl where sal between 25000 and 40000;
16. Write SQL query to display tables data according to ascending order of sal.
Ans: select * from empl order by sal asc;

17. Write SQL query to change EName MITA by MIRA.

Ans:- update empl set ename="MIRA" where ename="MITA";

18. Write SQL query to delete records whose comm=0.

Ans:- delete from empl where comm=0;
 19. Write SQL query to add a new column Phno.
Ans:- alter table empl add phno int(10);

20. Write SQL query to delete entire table IF EXISTING.

Ans:- drop table IF EXIST empl;

***********************
21. Queries for Aggregate functions- SUM( ), AVG( ), MIN( ), MAX( ), COUNT( )
a. Display total salary of all employee listed in empl.

b. Find the average salary of the employees in empl table.

Solution:- SELECT avg(salary) FROM EMPL;

c. Find the minimum salary of job SALEMAN in EMPL table.

Solution:- SELECT min(sal) FROM EMPL WHERE job=’SALESMAN’;

d. Find the maximum salary of job SALEMAN in EMPL table.

Solution:- SELECT max(sal) FROM EMPL WHERE job=’SALESMAN’;

e. Find the total number of Employee from empl table.

Solution:- SELECT count(empno) from empl;
22. TEXT FUNCTIONS
Display name of employee in lower case and upper case;

Display the position of the string ‘LE’ in field TRIM FUNCTION

job of table EMPL.

SUBSTR FUNCTION LENGTH FUNCTION

LEFT FUNCTION RIGHT FUNCTION MID FUNCTION

23. MATH FUNCTIONS

MOD Function Power Function Round Function

Square Root Function

24. DATE / TIME FUNCTION

25. Querying and manipulating data using Group by, Having, Order by.
a) Display department no, job and no. of employee in particular job Group by department no,
job from empl.

b) Display the jobs where the number of employee is less than 3.

26. Create a table DEPT and show Cartesian Product, JOIN (Cartesian Join, Equi Join, Natural
Join)

a) Cartesian Product
b) Write query to join two tables empl and dept on the basis of deptno field. (Equi join)

c) Natural Join

d) Left Join

e) Right join
27: Program to connect with database and store record of employee and
display records.
Source Code:-
import mysql.connector as mycon
con = mycon.connect(host='127.0.0.1',user='root',password="root")
cur = con.cursor()
cur.execute("create database if not exists company")
cur.execute("use company")
cur.execute("create table if not exists employee(empno int, name varchar(20), dept varchar(20),salary
int)")
con.commit()
choice=None
while choice!=0:
print("1. ADD RECORD ")
print("2. DISPLAY RECORD ")
print("0. EXIT")
choice = int(input("Enter Choice :"))
if choice == 1:
e = int(input("Enter Employee Number :"))
n = input("Enter Name :")
d = input("Enter Department :")
s = int(input("Enter Salary :"))
query="insert into employee values({},'{}','{}',{})".format(e,n,d,s)
cur.execute(query)
con.commit()
print("## Data Saved ##")
elif choice == 2:
query="select * from employee"
cur.execute(query)
result = cur.fetchall()
print("%10s"%"EMPNO","%20s"%"NAME","%15s"%"DEPARTMENT", "%10s"%"SALARY")
for row in result:
print("%10s"%row[0],"%20s"%row[1],"%15s"%row[2],"%10s"%row[3])
elif choice==0:
con.close()
print("## Bye!! ##")
else:
print("## INVALID CHOICE ##")
 28: Program to connect with database and update the employeerecord of entered
empno.
Source Code:-
import mysql.connector as mycon
con = mycon.connect(host='127.0.0.1',user='root',password="root", database="company")
cur = con.cursor()
print("#"*40)
print("EMPLOYEE UPDATION FORM")
print("#"*40)
print("\n\n")
ans='y'
while ans.lower()=='y':
eno = int(input("ENTER EMPNO TO UPDATE :"))
query="select * from employee where empno={}".format(eno)
cur.execute(query)
result = cur.fetchall()
if cur.rowcount==0:
print("Sorry! Empno not found ")
else:
print("%10s"%"EMPNO","%20s"%"NAME", "%15s"%"DEPARTMENT", "%10s"%"SALARY")
for row in result:
print("%10s"%row[0],"%20s"%row[1],"%15s"%row[2],"%10s"%row[3])
choice=input("\n## ARE YOUR SURE TO UPDATE ? (Y) :")
if choice.lower()=='y':
print("== YOU CAN UPDATE ONLY DEPT AND SALARY ==")
print("== FOR EMPNO AND NAME CONTACT ADMIN ==")
d = input("ENTER NEW DEPARTMENT,(LEAVE BLANK IF NOT WANT TO CHANGE )")
if d=="":
d=row[2]
try:
s = int(input("ENTER NEW SALARY,(LEAVE BLANK IF NOT WANT TO CHANGE ) "))
except:
s=row[3]
query="update employee set dept='{}',salary={} where empno={}".format(d,s,eno)
cur.execute(query)
con.commit()
print("## RECORD UPDATED ## ")
ans=input("UPDATE MORE (Y) :")
 29. Program to connect with database and search employee number in table
employee and display record, if empno not found display appropriate message.
Source Code:-
import mysql.connector as mycon
con = mycon.connect(host='127.0.0.1',user='root',password="root", database="company")
cur = con.cursor()
print("#"*40)
print("EMPLOYEE SEARCHING FORM")
print("#"*40)
print("\n\n")
ans='y'
while ans.lower()=='y':
eno = int(input("ENTER EMPNO TO SEARCH :"))
query="select * from employee where empno={}".format(eno)
cur.execute(query)
result = cur.fetchall()
if cur.rowcount==0:
print("Sorry! Empno not found ")
else:
print("%10s"%"EMPNO", "%20s"%"NAME","%15s"%"DEPARTMENT", "%10s"%"SALARY")
for row in result:
print("%10s"%row[0],"%20s"%row[1],"%15s"%row[2],"%10s"%row[3])
ans=input("SEARCH MORE (Y) :")
 30. Program to connect with database and delete the employeerecord of entered empno.
Source Code:-
import mysql.connector as mycon
con = mycon.connect(host='127.0.0.1',user='root',password="root", database="company")
cur = con.cursor()
print("#"*40)
print("EMPLOYEE DELETION FORM")
print("#"*40)
print("\n\n")
ans='y'
while ans.lower()=='y':
eno = int(input("ENTER EMPNO TO DELETE :"))
query="select * from employee where empno={}".format(eno)
cur.execute(query)
result = cur.fetchall()
if cur.rowcount==0:
print("Sorry! Empno not found ")
else:
print("%10s"%"EMPNO","%20s"%"NAME", "%15s"%"DEPARTMENT", "%10s"%"SALARY")
for row in result:
print("%10s"%row[0],"%20s"%row[1],"%15s"%row[2],"%10s"%row[3])
choice=input("\n## ARE YOUR SURE TO DELETE ? (Y) :")
if choice.lower()=='y':
query="delete from employee where empno={}".format(eno)
cur.execute(query)
con.commit()
print("=== RECORD DELETED SUCCESSFULLY! ===")
ans=input("DELETE MORE ? (Y) :")

*********************************