SHIV NADAR SCHOOL GURUGRAM

COMPUTER SCIENCE
PRACTICAL FILE
SUB. CODE - 083

Student Name: Aryan Sethi

Roll No: 17628172
 CERTIFICATE

This is to certify that Aryan Sethi,

Roll No: 17628172, of Class: XII - Commerce, Session :2021-22

has prepared the Practical file as per the prescribed syllabus.

COMPUTER SCIENCE (SUB. CODE:-083)

Under my supervision, I am completely satisfied by the performance.

I wish him all the success in life.

Principal’s Signature Subject Teacher’s Signature

External’s Signature
 ACKNOWLEDGEMENT

I would like to express my special thanks of gratitude to my teacher Ms. Ekta Kaper,

PGT(CS) as well as our Principal Ms. Monica Sagar who gave me the opportunity to

study Computer Science, which helped me develop my computational thinking skills.

Finally, yet importantly, I would like to express my heartfelt thanks to my beloved

parents for their blessings, my friends/classmates for their help and wishes for the

successful completion of this curriculum.

Aryan Sethi
 TERM 1
INDEX

S. No. Programs Date

1 Write a program in python to check a number whether it is prime or not

2 Write a program to check a number whether it is palindrome or not.

3 Write a program to calculate compound interest.

4 Write a program to display ASCII code of a character and vice versa. The user is
given the choice ‘1’ for to find the ordinal value of a character and ‘2’ to find a
character of a value, in case any other value is entered, display appropriate
message.

5 Write a program to generate random numbers between 1 to 10 (both numbers

included). A clue is given to the user and check whether the user guesses the
correct number or not

6 Write a python function sin(x,n) to calculate the value of sin(x) using its taylor
series expansion up to n terms.

Where value of ‘n’ and ‘x’ are given by user

7 Input a list of numbers and using ‘TEST’ function check if a number is equal to the
sum of the cubes of its digits. create another function ‘BIG_SMALL’ to find the
smallest and largest such number from the given list of numbers.

8 Write a program to input a tuple of elements, using function ‘FIND’ search for a
given element in the tuple entered by the user.

9 Create a dictionary containing names of Competition winner students as keys and

number of their wins as values. Also display the record of the candidate entered by
the user.

10 Read a text file line by line and display each word separated by a #.

11 Write a program to write those lines which have the character 'P' from one text file
to another text file

12 Write a program to count number of words in a text file.

13 Write a program to find the most common words in a file.

14 Read a text file and display the number of vowels/ consonants/ uppercase/
lowercase characters in the file.
 15 Write a program to create a CSV file “user.csv” which will contain user name and
password for some entries. Also display the data stored in the file

16 Write a program to create a binary file with name and roll number. Search for a
given roll number and display the name, if not found display appropriate message.

17 Write a program to create a binary file with roll number, name and marks. Input a
roll number and update the marks.

18
Write a program to create a binary file “Employee” that stores record of employee
(Name , Salary) and display only records of those employees who are getting
between 25000 to 30000.

PROGRAMS
1

2
3

5
6

8
9

10

11

12
13

14
15

16
17

18
 TERM 2
INDEX

S. No. Programs Date

Section- A:- Python (Stack)

1 Write a menu based program to perform the operation on stack in

python.

2 Write a menu based program to Maintaining Book details like bcode,

btitle

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

4 Write SQL query to create a database Employee.

5 Write SQL query to open database Employee.

6 Write SQL query to create following Table name empl.

7 Write SQL query to show the structure of table.

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

9 Write SQL query to display all the records from table empl.

10 Write SQL query to display EmpNo and EName of all employees from
the table empl.

11 Write SQL query to display Ename, Sal, and Sal added with comm from
table empl.

12 Write SQL query to display Ename, Sal, and deptno who are not getting
commission from table empl.

13 Write SQL query to display Eno, Ename ,Sal, and Sal*12 as Annual
Salary whose commission is not NULL from table

14 Write SQL query to display the details of employees whose name have
only four letters.
 15 Write SQL query to display name, job title and salary of employee who
do not have manager.

16 Write SQL query to display the name of employee whose name contains
“A” as third letter

17 Write SQL query to display the name of departments. Each department

should be displayed once (DISTINCT)

18 Write SQL query to display the name and salary of those employees
whose salary is between 35000 and 40000. (BETWEEN)

19 Write SQL query to display tables data according to ascending order of

sal.

20 Write SQL query to change EName MITA by MIRA.

21 Write SQL query to delete records whose deptno=10.

22 Write SQL query to add a new column Phno.

23 W rite SQL query to delete entire table.

24 Queries for Aggregate functions- SUM( ), AVG( ), MIN( ), MAX( ),

COUNT( ) .

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

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(Stack)

1 def isEmpty(stk):
if stk==[]:
return True
else:
return False
def Push(stk, item):
stk.append(item)
top=len(stk)-1

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

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

def Display(stk):
if isEmpty(stk):
print("Stack empty")
else:
top=len(stk)-1
print(stk[top], "<-top")
for a in range(top-1, -1, -1):
print(stk[a])
#__main__
Stack=[ ]
top=None
while True:
print("STACK OPERATIONS")
print("1.PUSH\t2. POP\t3.PEEK\t4. DISPLAY STACK\t5. EXIT")
ch=int(input("Enter your choice(1-5) : "))
if ch==1:
item=int(input("Enter item : "))
Push(Stack, item)
elif ch==2:
item=Pop(Stack)
if item=="Underflow":
print("Underflow! Stack is empty!")
else:
print("Popped item is ",item)
elif ch==3:
item=Peek(Stack)
if item=="Underflow":
print("Underflow! Stack is empty!")
else:
print("Topmost item is ",item)
elif ch==4:
Display(Stack)
elif ch==5:
print("\n-----------------Exiting-----------------\n")
break
else:
print("\n-----------------Invalid choice!!!-----------------\n")
2 book=[ ]
choice=int(0)
def Badd():
bcode=input("Enter book code : ")
btitle=input("Enter book title : ")
bprice=int(input("Enter book price : "))
btemp=[bcode,btitle,bprice]
book.append(btemp)

def Bdel():
if(book==[ ]):
print("Underflow or Book Stack is empty")
else:
bcode, btitle,price = book.pop()
print("Book Record is : ")
print("Book Code : ",bcode,"\tBook Title : ",btitle,"\tPrice : ",price)

def Bdisp():
if not(book==[]):
n=len(book)
print("-"*80)
print("Book Name\tBook Number\tBook Price")
print("-"*80)
for i in range(n-1,-1,-1):
print(book[i][0],"\t\t",book[i][1],"\t\t",book[i][2])
else:
print("Empty, No book to display")

while(choice!=4):
print("---------------------Book Stall---------------------")
print("-"*80)
print("1.Add Book\t2.Delete Book\t3.Display Books\t4. Exit")
op=int(input("Enter the Choice : "))
print("-"*80)
if(op==1):
Badd()
elif(op==2):
Bdel()
elif(op==3):
Bdisp()
elif(op==4):
print("Exiting.....")
break
else:
print("Wrong choice")
3 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("-"*80)
print("Hostel Number\tTotal Students\tTotal Rooms")
print("-"*80)
for i in range(l-1,-1,-1):
print(host[i][0],"\t\t",host[i][1],"\t\t",host[i][2])
choice=int(0)
while(choice!=4):
print("-"*80)
print("1. Add Record\t2.Delete Record\t3.Display Record\t4. Exit")
op=int(input("Enter the Choice : "))
print("-"*80)
if(op==1):
push(host)
elif(op==2):
pop(host)
elif(op==3):
display(host)
elif(op==4):
print("Exiting.....")
break
else:
print("Wrong choice")
 Section B:- MySQL
4 CREATE DATABASE EMPLOYEE;
5 USE EMPLOYEE;

6 CREATE TABLE DEPT1

(
DEPTNO INTEGER(2) PRIMARY KEY,
DNAME VARCHAR(12),
LOC VARCHAR(12)
);

--------------------------------------------------------------
CREATE TABLE EMPL
(
EMPNO INTEGER(4) PRIMARY KEY,
ENAME VARCHAR(20),
JOB VARCHAR(10),
MGR INTEGER(4),
HIREDATE DATE,
SAL INTEGER(6),
COMM INTEGER(6),
DEPTNO INTEGER(2) REFERENCES DEPT(DEPTNO)
);

7 DESC DEPT1;
DESC EMPL;
8 INSERT INTO DEPT1 VALUES(10, "ACCOUNTING", "DELHI");
INSERT INTO DEPT1 VALUES(20, "RESEARCH", "MUMBAI");
INSERT INTO DEPT1 VALUES(30, "SALES", "GURGAON");
INSERT INTO DEPT1 VALUES(40, "OPERATION", "NOIDA");

----------------------------------------------------------------------------------

INSERT INTO EMPL VALUES(8369,"SMITH","CLERK",8902,"1990-12-18",800,NULL,20);

INSERT INTO EMPL VALUES(8499,"ANYA","SALESMAN",8698,"1991-02-20",1600,300,30);
INSERT INTO EMPL VALUES(8521,"BETHI","SALESMAN",8698,"1991-02-22",1250,500,30);
INSERT INTO EMPL VALUES(8566,"MAHADEVAN","MANAGER",8839,"1991-04-02",2985,NULL,20);
INSERT INTO EMPL VALUES(8654,"MOMIN","SALESMAN",8698,"1991-09-28",1250,1400,30);
INSERT INTO EMPL VALUES(8698,"BINA","MANAGER",8839,"1991-05-01",2850,NULL,30);
INSERT INTO EMPL VALUES(8882,"SHIAVNSH","MANAGER",8839,"1991-06-09",2450,NULL,10);
INSERT INTO EMPL VALUES(8888,"SCOTT","ANALYST",8566,"1992-12-09",3000,NULL,20);
INSERT INTO EMPL VALUES(8839,"AMAR","PRESIDENT",NULL,"1991-11-18",5000,NULL,10);
INSERT INTO EMPL VALUES(8844,"KULDEEP","SALESMAN",8698,"1991-09-08",1500,0,30);
INSERT INTO EMPL VALUES(8886,"ANOOP","CLERK",8888,"1997-01-12",1100,NULL,20);
INSERT INTO EMPL VALUES(8900,"JATIN","CLERK",8698,"1991-12-03",950,NULL,30);
INSERT INTO EMPL VALUES(8902,"FAKIR","ANALYST",8566,"1991-12-03",3000,NULL,20);
INSERT INTO EMPL VALUES(8934,"MITA","CLERK",8882,"1992-01-23",1300,NULL,10);
9 SELECT * FROM DEPT1;
10 SELECT EMPNO, ENAME FROM EMPL;
11 SELECT ENAME, SAL, SAL+COMM "TOTAL SALARY" FROM EMPL;

12 SELECT EMPNO, SAL, DEPTNO FROM EMPL WHERE COMM IS NULL;

13 SELECT EMPNO, ENAME,SAL,SAL*12 AS "ANNUAL SALARY" FROM EMPL WHERE COMM IS NOT NULL;
14 SELECT ENAME FROM EMPL WHERE ENAME LIKE '____';

15 SELECT ENAME, JOB, SAL FROM EMPL WHERE MGR IS NULL;

16 SELECT ENAME FROM EMPL WHERE ENAME LIKE '__A%';

17 SELECT DISTINCT DNAME FROM DEPT1;

18 SELECT ENAME, SAL FROM EMPL
WHERE SAL BETWEEN 35000 AND 40000;

19 SELECT * FROM EMPL

ORDER BY SAL ASC;

20 UPDATE EMPL SET ENAME="MIRA"

WHERE ENAME="MITA";
21 DELETE FROM EMPL
WHERE DEPTNO=10;

22 ALTER TABLE EMPL ADD PHNO VARCHAR(15);

23 DROP TABLE IF EXISTS EMPL;

24 SELECT JOB, SUM(SAL) "TOTAL SALARY", MAX(SAL) "MAXIMUM SALARY",

MIN(SAL) "MINIMUM SALARY", COUNT(*) "NO OF EMPLOYEES",
AVG(SAL) "AVERAGE SALARY"
FROM EMPL
WHERE DEPTNO=20
GROUP BY JOB
HAVING AVG(SAL)>1000
ORDER BY SUM(SAL);

25 (a) Display department no, job and no. of employee in particular job group by department no,job
 from EMPL

SELECT DEPTNO, JOB, COUNT (*) FROM EMPL

GROUP BY DEPTNO, JOB ORDER BY DEPTNO;

(b) Display the jobs where the number of employees is less than 3

SELECT JOB, COUNT (*) FROM EMPL

GROUP BY JOB HAVING COUNT (*) <3;

26 (a) Equi Join:

Write query to join two tables EMPL AND DEPT1 on the basis of deptno field

SELECT *
FROM EMPL E, DEPT1 D
WHERE E. DEPTNO=D.DEPTNO;

(b) Cartesian Join:

 Show the join of EMPL and DEPT1 tables.

SELECT * FROM EMPL,DEPT1;

(c) Natural Join

Show the natural join of EMPL and DEPT1 tables.

SELECT EMPL.*,DNAME,LOC FROM EMPL,DEPT1 WHERE EMPL.DEPTNO = DEPT.DEPTNO;

(d) LEFT JOIN

 Show the left join of EMPL and DEPT1 tables.

SELECT DEPT1.*,ENAME,MGR FROM DEPT1 LEFT JOIN ON DEPT1.DEPTNO = EMPL.DEPTNO;

(e) RIGHT JOIN

Show the right join of EMPL AND DEPT1 tables

SELECT DEPT1.*,ENAME,MGR FROM DEPT1 RIGHT JOIN ON DEPT1.DEPTNO = EMPL.DEPTNO;

Section C:- Python MySQL Connectivity

27 import mysql.connector as mycon
con = mycon.connect(user='root', password='ayaan@123')
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("3. EXIT")
choice = int(input("Enter choice number:"))
if choice==1:
e = int(input("Enter employee number:"))
n = input("Enter employee name:")
d = input("Enter department name:")
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 == 3:
con.close()
print("## Bye!! ##")
break
else:
print("## INVALID CHOICE ##")
28 import mysql.connector as mycon
con = mycon.connect(user='root',password='ayaan@123',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 YOU SURE TO UPDATE ? (Y/N):")
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 YOU DONT WISH TO CHANGE): ")
if d == "":
d=row[2]
try:
s=int(input("ENTER NEW SALARY,(LEAVE BLANK IF YOU DONT WISH 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/N):")
29 import mysql.connector as mycon
con = mycon.connect(host='127.0.0.1',user='root',password='ayaan@123',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/N):")

30 import mysql.connector as mycon

con = mycon.connect(host='127.0.0.1',user='root',password='ayaan@123',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 YOU SURE TO DELETE ? (Y/N):")
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/N):")