GRADE : XII

COMPUTER SCIENCE
PRACTICAL FILE
Index

No. Practical Name Date Signature

1 Program to read and display file content line by
line with each word separated by “ #”
2 Program to read the content of file and display the total
numberof consonants, uppercase, vowels and lower
case characters.
3 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.
4 Program to create binary file to store Rollno and
Name, Search any Rollno and display name if
Rollno found otherwise “Rollno not found”
5 Program to create binary file to store Rollno,
Name and Marksand update marks of entered
Rollno.
6 Program to generate random number 1-6, simulating a
dice.
7 Program to implement Stack in Python using List.
8 Create a CSV file by entering user-id and password, read and
search the password for given user- id.
9 Consider the following MOVIE table and write the SQL queries
based on it.
10 Write following queries.
11 Consider the following matches, teams table and write SQL
queries based on it.
12 Consider the following table and write the queries:

13 Perform create database and Write a MySQL connectivity

program in Python.
14 Perform all the operations with reference to table ‘students
through MySQL-Pythonconnectivity.
Program 1: Program to read and display file content line by line with each
word separated by “ #”

#Program to read content of file line by

line#and display each word separated by
'#'

f = open("file1.txt")
for line in f:
words = line.split()
for w in words:
print(w+'#',end=” “)
print()
f.close()

NOTE : if the original content of file is:

India is my
countryI love
python
Python learning is fun

OUTPUT
India#is#my#country#
I#love#python#
Python#learning#is#fun
#
Program 2: Program to read the content of file and display the total number of
consonants, uppercase, vowels and lower case characters.

#Program to read content of file

#and display total number of vowels, consonants, lowercase and uppercase characters

f = open("file1.txt")
v=0
c=0
u=0
l=0
o=0
data = f.read()
vowels=['a','e','i','o','u']
for ch in data:
if ch.isalpha():
if ch.lower() in vowels:
v+=1
else:
c+=1
if ch.isupper():
u+=1
elif ch.islower():
l+=1
elif ch!=' ' and ch!='\n':
o+=1
print("Total Vowels in file :",v)
print("Total Consonants in file :",c)
print("Total Capital letters in file :",u)
print("Total Small letters in file :",l)
print("Total Other than letters :",o)
f.close()
NOTE : if the original content of file is:
India is my country I
love python
Python learning is fun123@

OUTPUT
Total Vowels in file : 16
Total Consonants in file n : 30
Total Capital letters in :2
file
Total Small letters in file : 44
Total Other than letters :4
Program 3: 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.

#Program to read line from file and write it to another line#Except

for those line which contains letter 'a'

f1 = open("file2.txt")
f2 = open("file2copy.txt","w")

for line in f1:

if 'a' not in line:
f2.write(line)
print(“## File Copied Successfully! ##”)
f1.close()
f2.close()

NOTE: Content of file2.txt

a quick brown fox
one two three four
five six seven
India is my country
eight nine ten
bye!

OUTPUT

## File Copied Successfully! ##

NOTE: After copy content of file2copy.txt

one two three four
five six seven eight
nine ten bye!
Program 4: Program to create binary file to store Rollno and Name, Search any Rollno
and display name if Rollno found otherwise “Rollno not found”

import pickle
def write():
D={}
f=open("Studentdetails.dat","wb")
while True:
r = int(input ("Enter Roll no : "))
n = input("Enter Name : ")
D['Roll No'] = r
D['Name'] = n
pickle.dump(D,f)
ch = input("More ? (Y/N)")
if ch in 'Nn':
break
f.close()

def Search() :
found = 0
rollno= int(input("Enter Roll no Whose name you want to display :"))
f = open("Studentdetails.dat", "rb")
try:
while True:
rec = pickle.load(f)
if rec['Roll No']==rollno:
print(rec['Name'])
found = 1
break
except EOFError:
if found == 0:
print("Sorry not Found....")
f.close()
write()
Search()

OUTPUT
Enter Roll no : 1
Enter Name : jayna
More ? (Y/N)y
Enter Roll no : 2
Enter Name : Daksh
More ? (Y/N)y
Enter Roll no : 3
Enter Name : vyom
More ? (Y/N)N
Enter Roll no Whose name you want to
display :3
vyom
Program 5: Program to create binary file to store Rollno, Name and Marksand
update marks of entered Rollno.

#Program to create a binary file to store Rollno and name

#Search for Rollno and display record if found #otherwise
"Roll no. not found"

import pickle
def Write():
f = open("Studentdetails.dat", 'wb')
while True:
r =int(input ("Enter Roll no : "))
n = input("Enter Name : ")
m = int(input ("Enter Marks : "))
record = [r,n,m]
pickle.dump(record, f)
ch = input("Do you want to enter more ?(Y/N)")
if ch in 'Nn':
break

f.close()

def Read():
f = open("Studentdetails.dat",'rb')
try:
while True:
rec=pickle.load(f)
print(rec)
except EOFError:
f.close()

def Update():
f = open("Studentdetails.dat", 'rb+')
rollno = int(input("Enter roll no whoes marks you want to update"))
try:
while True:
pos=f.tell()
rec = pickle.load(f)
if rec[0]==rollno:
um = int(input("Enter Update Marks:"))
rec[2]=um
f.seek(pos)
pickle.dump(rec,f)
#print(rec)
except EOFError:
f.close()

Write()
Read()
Update()
Read()
OUTPUT
Enter Roll no : 1
Enter Name : jiya
Enter Marks : 56
Do you want to enter more ?(Y/N)y
Enter Roll no : 2
Enter Name : henil
Enter Marks : 67
Do you want to enter more ?(Y/N)n
[1, 'jiya', 56]
[2, 'henil', 67]
Enter roll no whose marks you want to update : 2
Enter Update Marks:76
[1, 'jiya', 56]
[2, 'henil', 76]
Program 6: Program to generate random number 1-6, simulating a dice.

# Program to generate random number between 1 - 6 # To

simulate the dice
import random
while True:
print("="*55)
print("***********************Roling Dice****************************")
print("="*55)
num = random.randint(1,6)
if num ==6:
print("Hey.....You got",num,"........Congratulations!!!!")
elif num ==1:
print("Well tried.... But you got",num)
else:
print("You got:",num)
ch=input("Roll again? (Y/N)")
if ch in "Nn":
break
print("Thank for playing!!!!!!!!")

OUTPUT

=======================================================
***********************Roling Dice****************************
=======================================================
You got: 2
Roll again? (Y/N)y
Thank for playing!!!!!!!!
=======================================================
***********************Roling Dice****************************
=======================================================
Hey.....You got 6 ........Congratulations!!!!
=======================================================
***********************Roling Dice****************************
=======================================================
Well tried.... But you got 1
=======================================================
***********************Roling Dice****************************
=======================================================
Well tried.... But you got 1
=======================================================
***********************Roling Dice****************************
=======================================================
Well tried.... But you got 1
=======================================================
***********************Roling Dice****************************
=======================================================
You got: 2
Roll again? (Y/N)n
Program 7: Write a program to implement a stack for the employee details (empno,name).Code:

#stack implementation using functions

#program to create a stack of employee(empno,name,sal).

employee=[]
def push():
empno=input("Enter empno ")
name=input("Enter name ")
sal=input("Enter sal ")
emp=(empno,name,sal)
employee.append(emp)
def pop():
if(employee==[]):
print("Underflow / Employee Stack in empty")
else:
empno,name,sal=employee.pop()
print("poped element is ")
print("empno ",empno," name ",name," salary ",sal)
def traverse():
if not (employee==[]):
n=len(employee)
for i in range(n-1,-1,-1):
print(employee[i])
else:
print("Empty , No employee to display")
while True:
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")
Output:
Program 8: Create a CSV file by entering user-id and password, read and search the
password for given user- id.

import csv
with open("7.csv", "w") as obj:
fileobj = csv.writer(obj)
fileobj.writerow(["User Id", "password"])
while(True):
user_id = input("enter id: ")
password = input("enter password: ")
record = [user_id, password]
fileobj.writerow(record)
x = input("press Y/y to continue and N/n to terminate the program\n")
if x in "Nn":
break
elif x in "Yy":
continue
with open("7.csv", "r") as obj2:
fileobj2 = csv.reader(obj2)
given = input("enter the user id to be searched\n")

for i in fileobj2:
next(fileobj2)

# print(i,given)
if i[0] == given:
print(i[1])

break
 SQL
QUERIEs
Practical
Program 9 : Consider the following MOVIE table and write the SQL queries based on it.

Movie_ID MovieName Type ReleaseDate Production BusinessC

Cost ost
M001 The Kashmir Action 2022/01/26 1245000 1300000
Files
M002 Attack Action 2022/01/28 1120000 1250000
M003 Looop Lapeta Thriller 2022/02/01 250000 300000
M004 Badhai Do Drama 2022/02/04 720000 68000
M005 Shabaash Mithu Biography 2022/02/04 1000000 800000
M006 Gehraiyaan Romance 2022/02/11 150000 120000

a) Display all information from movie.

b) Display the type of movies.
c) Display movieid, moviename, total_eraning by showing the business done by the
movies. Claculate the business done by movie using the sum of productioncost
andbusinesscost.
d) Display movieid, moviename and productioncost for all movies
withproductioncost greater thatn 150000 and less than 1000000.
e) Display the movie of type action and romance.
f) Display the list of movies which are going to release in February, 2022.
Answers:
a) select * from movie;

b) select distinct from a movie;

c) select movieid, moviename, productioncost + businesscost"total earning" from
movie;

d) select movie_id,moviename, productioncost from moviewhere

productcost is >150000 and <1000000;

e) select moviename from movie where type ='action' ortype='romance';

f) select moviename from movie where month(releasedate)=2;

Program 10: Write following queries.
a. Write a query to display cube of 5.
b. Write a query to display the number 563.854741 rounding off to the next
hundred.
c. Write a query to display "put" from the word "Computer".
d. Write a query to display today's date into DD.MM.YYYY format.
e. Write a query to display 'DIA' from the word "MEDIA".
f. Write a query to display moviename - type from the table movie.
g. Write a query to display first four digits of productioncost.
h. Write a query to display last four digits of businesscost.
i. Write a query to display weekday of release dates.
j. Write a query to display dayname on which movies are going to be released.

Answers:
a) select pow(5,3);

b) select round(563.854741,-2);

c) select mid(“Computer”,4,3);
d) select concat(day(now()), concat('.',month(now()),concat('.',year(now()))))
"Date";

e) select right("Media",3);

f) select concat(moviename,concat(' - ',type)) from movie;

g) select left(productioncost,4) from movie;

h)select right (businesscost,4)from movie;

i)select weekday(releasedate) from movie;

j)select dayname(releasedate) from movie;

 Program 11 : Suppose your school management has decided to conduct cricket
matches betweenstudents of Class XI and Class XII. Students of each class are
asked to join any oneof the four teams – Team Titan, Team Rockers, Team Magnet
and Team Hurricane. During summer vacations, various matches will be conducted
between these teams. Help your sports teacher to do the following:
A. Create a database “Sports”.
B. Create a table “TEAM” with following considerations:

a) It should have a column TeamID for storing an integer value between 1 to 9,which
refers to unique identification of a team.
b) Each TeamID should have its associated name (TeamName), which should be astring
of length not less than 10 characters.
c) Using table level constraint, make TeamID as the primary key.
a) Show the structure of the table TEAM using a SQL statement.
b) As per the preferences of the students four teams were formed as given
below. Insertthese four rows in TEAM table:
d) Row 1: (1, Tehlka)
e) Row 2: (2, Toofan)
f) Row 3: (3, Aandhi)
g) Row 3: (4, Shailab)
a) Show the contents of the table TEAM using a DML statement.
b) Now create another table MATCH_DETAILS and insert data as shown below.
Chooseappropriate data types and constraints for each attribute.

MatchI MatchDate FirstTe SecondTeam FirstTeamSc SecondTeamSc

D amID ID ore ore
M1 2021/12/2 1 2 107 93
0
M2 2021/12/2 3 4 156 158
1
M3 2021/12/2 1 3 86 81
2
M4 2021/12/2 2 4 65 67
3
M5 2021/12/2 1 4 52 88
4
M6 2021/12/2 2 3 97 68
5
Answers:

a) create database sports;

b) Creating table with the given specification
create table team -> (teamid int(1), -> teamnamevarchar(10), primary key(teamid));

c) desc team;

Inserting data:

mqsql> insert into team -> values(1,'Tehlka');

Show the content of table - team:

select * from team;
 Creating another table:
create table match_details
-> (matchid varchar(2) primary key,
-> matchdate date,
-> firstteamid int(1) references team(teamid),
-> secondteamid int(1) references team(teamid),
-> firstteamscore int(3),
-> secondteamscore int(3));

 Write following queries according to program no 11.

 a) Display the matchid, teamid, teamscore whoscored more than 70 in first iningalong
with team name.
b) Display matchid, teamname and secondteamscore between 100 to 160.
c) Display matchid, teamnames along with matchdates.
d) Display unique team names
e) Display matchid and matchdate played by Anadhi and Shailab.

Answers:
a) select match_details.matchid, match_details.firstteamid,
team.teamname,match_details.firstteamscore from match_details, team where
match_details.firstteamid = team.teamid and match_details.first

b) selectmatch_details.matchid,match_details.firstteamid,team.teamname,match_d
etails.firstteamscore from match_details, team where
match_details.firstteamid= team.teamid and match_details.firstteamscore>70;

c) mselect matchid, teamname, firstteamid, secondteamid,matchdate from

match_details, team where match_details.firstteamid = team.teamid;

d) select distinct(teamname) from match_details, team where

match_details.firstteamid = team.teamid;
 e) select matchid,matchdate from match_details, team wherematch_details.firstteamid
= team.teamid and team.teamname in ('Aandhi','Shailab');

Program 12: Consider the following table and write the queries:

itemno item dcode qty unitpric stockdate

e
S005 Ballpen 102 100 10 2018/04/22
S003 Gel Pen 101 150 15 2018/03/18
S002 Pencil 102 125 5 2018/02/25
S006 Eraser 101 200 3 2018/01/12
S001 Sharpner 103 210 5 2018/06/11
S004 Compass 102 60 35 2018/05/10
S009 A4 Papers 102 160 5 2018/07/17

a) Display all the items in the ascending order of stockdate.

b) Display maximum price of items for each dealer individually as per dcode from
stock.
c) Display all the items in descending orders of itemnames.
 d) Display average price of items for each dealer individually as per doce from stock
which avergae price is more than 5.
e) Diisplay the sum of quantity for each dcode.

Answers:
a) select * from stock order by stockdate;

b) Select dcode, max(unitprice) from stock group by code;

c) select * from stock order by item desc;

d) select dcode,avg(unitprice) from stock group by dcodehaving

avg(unitprice)>5;
e) select dcode,sum(qty) from stock group by dcode;

PYTHON
DATABASE
CONNECTIVITY
Program 13. Write a MySQL connectivity program in Python to
 Create a database school
 Create a table students with the specifications - ROLLNO integer, STNAME
character(10) in MySQL and perform the following operations:
o Insert two records in it
o Display the contents of the table

Answer:

import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="root",
port="3306",
#database="my_first_db"
)
def c_database():
try:
dn=input("Enter Database Name=")
c.execute("create database {}".format(dn))
c.execute("use {}".format(dn))
print("Database created successfully")
except Exception as a:
print("Database Error",a)
#Function to Drop Database as per users choice
def d_database():
try:
dn=input("Enter Database Name to be dropped=")
c.execute("drop database {}".format(dn))
print("Database deleted sucessfully")
except Exception as a:
print("Database Drop Error",a)

#Function to create Table

def c_table():
 try:
c.execute("create table students(rollno int(3),stname varchar(20));")
#mycursor.execute("CREATE TABLE customers (name VARCHAR(255), address
VARCHAR(255))")
print("Table created successfully")
except Exception as a:
print("Create Table Error",a)

#Function to Insert Data

def e_data():
try:
while True:
rno=int(input("Enter student rollno="))
name=input("Enter student name=")
c.execute("use {}".format('STD12'))
c.execute("insert into students values({},'{}');".format(rno,name))
mydb.commit()
choice=input("Do you want to add more record<y/n>=")
if choice in "Nn":
break
except Exception as a:
print("Insert Record Error",a)

#Function to Display Data

def d_data():
try:
c.execute("select * from students")
data=c.fetchall()
for i in data:
print(i)
except Exception as a:
print("Display Record Error",a)

mydb = mysql.connector.connect(
host="localhost",
user="root",
password="root",
port="3306",
#database="my_first_db"
)
c=mydb.cursor()
while True:
print("MENU\n1. Create Database\n2. Drop Database \n3. Create Table\n4. Insert Record \n5.
Display Entire Data\n6. Exit")
choice=int(input("Enter your choice<1-6>="))
if choice==1:
c_database()
elif choice==2:
 d_database()
elif choice==3:
c_table()
elif choice==4:
e_data()
elif choice==5:
d_data()
elif choice==6:
break
else:
print("Wrong option selected"
 Program 14: Perform all the operations with reference to table ‘students’ through
MySQL-Pythonconnectivity.

import mysql.connector as ms
db=ms.connect(
host="localhost",
user="root",
passwd="root",
port = 3306,
database="daksh"
)
#cn=db.cursor()

def insert_rec():
try:
while True:
rn=int(input("Enter roll number:"))
sname=input("Enter name:")
marks=float(input("Enter marks:"))
gr=input("Enter grade:")
cn.execute("insert into student values({},'{}',{},'{}')".format(rn,sname,marks,gr))
db.commit()
ch=input("Want more records? Press (N/n) to stop entry:")
if ch in 'Nn':
print("Record Inserted.....")
break

except Exception as e:
print("Error", e)
def update_rec():
try:
rn=int(input("Enter rollno to update:"))
marks=float(input("Enter new marks:"))
gr=input("Enter Grade:")
cn.execute("update student set marks={},gr='{}' where rn={}".format(marks,gr,rn))
db.commit()
 print("Record Updated.....")
except Exception as e:
print("Error",e)
def delete_rec():
try:

rn=int(input("Enter rollno to delete:"))

cn.execute("delete from student where rn={}".format(rn))
db.commit()
print("Record Deleted.....")
except Exception as e:
print("Error",e)
def view_rec():
try:
cn.execute("select * from student")
records = cn.fetchall()
for record in records:
print(record)
#db.commit()
#print("Record...")
except Exception as e:
print("Error",e)
db = ms.connect(
host="localhost",
user="root",
passwd="root",
port=3306,
database="daksh"
)
cn = db.cursor()
while True:
print("MENU\n1. Insert Record\n2. Update Record \n3. Delete Record\n4. Display Record \n5.
Exit")
ch=int(input("Enter your choice<1-4>="))
if ch==1:
insert_rec()
elif ch==2:
update_rec()
elif ch==3:
delete_rec()
elif ch==4:
view_rec()
elif ch==5:
break
else:
print("Wrong option selected")
Output: