ASSISI CONVENT SCHOOL

Noida, Uttar Pradesh-201307

2023-24

PRACTICAL FILE

Computer science
with python
--

Submitted By:- Submitted To:-

Name:- Rudra Bhardwaj. Ms Ekta Sahni
Class :- XII-E

1
2
PROGRAM 01:
Consider a database with the following table: TABLE:
LOANS

Answer the following questions.

1. Display the sum of all Loan Amount whose Interest rate is greater
than 10. CODING: Select sum(Loan_Amount)FROM loans where
Interest>10; OUTPUT:

2. Display the Maximum Interest Rate from

Loanstable. CODING: select
Max(Interest)from loans; OUTPUT:

3. Display the count of all loan holders whose name ends with
‘Sharma’. CODING: Select count(*) from loans where Cust_Name
like'%Sharma'; OUTPUT:

3
4. Display the count of all loan holders whose Interest is NULL.

CODING: Select count(*) from loans where Interest is NULL; OUTPUT:

5. Display the Interest-wise details of Loan

Account Holders. CODING: Select*from
loans Group By Interest; OUTPUT:

6. Display the Interest-wise details of Loan Account Holders with at

least 10 installments remaining.
CODING: Select*from loans Group By Interest Having Instalmaents>=10;

OUTPUT:

4
 PROGRAM 02:

Consider the following tables: COMPANY & MODEL

TABLE: COMPANY

 NOTE:
• Comp_ID is the Primary Key.
TABLE:MODEL

 NOTE:
• Model_ID is the Primary Key.
• Comp_ID is the Foreign Key referencing Comp_ID of Company table.

5
1. To display the details of all models in the Model table in
ascending order of DateOfManufacture.

CODING: select*from model order by DateOfManufacture; OUTPUT:

2. To display the details of those models manufactured in 2011 and

whose Cost is below 2000.
CODING: select*from model where year(DateOfManufacture)=2011
and cost<2000; OUTPUT:

3. To decrease the cost of all the models in Model table by 15%.

CODING: update model set cost=cost-0.15*cost; OUTPUT:

4. Select COUNT(Distinct CompHO)from Company; OUTPUT:

5. Select CompName, contact(‘Mr.’,ContactPerson)from Company where

CompName ends with ‘a’;
OUTPUT:

6
 PROGRAM:03
Consider a database with the following table: STOCK and
DEALER.
TABLE: Stock

 NOTE:
• ItemNo is the Primary Key.
• Dcode is the Foreign Key refrencing Dcode of Dealer table.

TABLE:Dealer

 NOTE:
• Dcode is the Primary Key.

❖ a.Write SQL commands for the following statements.

1. To display details of all the items in the Stock table in ascending order of

StockDate. CODING: select*from stock order by stockdate; OUTPUT:

2. To display the ItemNo and Item name of those items from Stock table
whose UnitPrice is more than Rs 10.
CODING: select ItemNo,Item from STOCK where UnitPrice>10; OUTPUT:

3. To display details of those items whose dealer code (Dcode) is 102 or

7
Quantity in Stock(Qty) is more than 100 from the table Stock. CODING:
select*from stock where Dcode=102 or Qty>100; OUTPUT:

8
4. To display Maximum UnitPrice of items for each dealer individually as per
Dcode from the table Stock.
CODING: select Dcode,max(UnitPrice) from stock group by Dcode; OUTPUT:

❖ Give the output of the following

SQL queries:

1. select count(distinct Dcode) from stock;

OUTPUT:

2. select Qty*UnitPrice from stock

where ItemNo=5006; OUTPUT:

3. select min(StockDate) from stock; OUTPUT:

9
 PROGRAM:04
Consider a database with the following table:
PRODUCTS and SUPPLIERS.
TABLE: PRODUCTS

TABLE: SUPPLIERS

 Write SQL commands for the following statements.

1. To arrange and display all the records of table Products on the
basis of product name in the ascending order.

CODING: select*from products order by SNAME; OUTPUT:

2. To display product name and price of all those products whose

price is in the range of 10000 and 15000 (both)
SNAME,PRICE from PRODUCTS where PRICE>=10000 and
PRICE<=15000; OUTPUT:

3. To display the price, product name and quantity of those

10
 products which have quantity more than 100.
CODING: select PRICE,SNAME,QTY from PRODUCTS where QTY>100;

4.To display the names of those suppliers who are either from
DELHI or from CHENNAI. CODING: select SNAME from SUPPLIERS
where CITY="DELHI" or CITY="CHENNAI"; OUTPUT:

5. To display the names of the companies and the names of the

products in descending order of company names.
CODING :select COMPANY,SNAME from PRODUCTS order by COMPANY
desc; OUTPUT:

11
12
  Give the output of the following SQL queries:

 select distinct SUPCODE from PRODUCTS;

OUTPUT:

Select max(PRICE),min(PRICE) from PRODUCTS;

OUTPUT

 select PRICE*QTY AMOUNT from PRODUCTS where PID=104;

OUTPUT:

 PROGRAM:05
Consider a database with the following table: SHOP and
ACCESSORIES.
TABLE: SHOP

13
 TABLE:ACCESSORIES

❖ a.Write SQL commands for the following statements.

1. To display Name and Price of all the Accessories in ascending order
of their Price. CODING select Name,Price from ACCESSORIES ORDER
BY Price; OUTPUT

2. To display Id, SName of all shops located in Nehru Place.

CODING: select ID,SName from SHOP where Area='NehruPlace':

14
3. To display Minimum and Maximum Price of all the
accessories; CODING: select count(distinct AREA) from SHOP;
OUTPUT:

❖ Give the output of the following SQL queries:

4. select distinct NAME from ACCESSORIES where PRICE>=5000; OUTPUT:

5. select AREA,count(*)from SHOP group by AREA; OUTPUT:

6. select count(distinct AREA) from SHOP; OUTPUT:

15
PYTHON WITH
DATABASE
CONNECTIVITY
PROGRAMS

16
 PROGRAM:06

ABC Infotech Pvt. Ltd. needs to store, retrieve and delete the records of its employees.
Develop an interface that provides front-end interaction through Python, and stores and
updates records using MySQL. The operations on MySQL table “emp” involve reading,
searching, updating and deleting records of employees

1) Program to read and fetch all the records from “emp” table having salary is equal
to 62000.

2) Program to delete the records on the basis of inputted salary .

#database name is ABCinfo.

CODING:
#program to read and research record of employees having salary is equal to 62000
import mysql.connector as mycon
db1=mycon.connect(host="localhost",user="root",passw
or d="sanchita",database="ABCinfo")
mycursor=db1.cursor() sql="SELECT * FROM emp WHERE
SALARY=62000;"
mycursor.execute(sql)
myrecords=mycursor.fetchall()
for x in myrecords:
print(x) db1.close()
OUTPUT:

CODING:
import mysql.connector as mycon
con=mycon.connect(host="localhost",user="root",password
="sanchita",database="ABCinfo") if (con.is_connected()):
print("connection done successfully") mycursor=con.cursor()
ns=input("Enter the salary whose record is to be deleted")
try: sql="delete from emp where salary=%s"

17
 mycursor.execute(sql,
(ns,)) con.commit()
print(mycursor.rowcount,"record(s) deleted")
except: print('error') con.rollback()
mycursor.close(
)
OUTPUT:

18
  PROGRAM:07

To create table ‘student’ inside the database ‘school’ using

python script mode as interface CODING:

OUTPUT:

19
  PROGRAM:08
To add new column ‘marks’ in the student table CODING:

OUTPUT:

20
  PROGRAM:09

To insert multiple records into the table student through

python shell. CODING:

OUTPUT:

 PROGRAM:10

To show all records of student using python shell.

CODING:

OUTPUT:

21
  Program:11
Use of WHERE CLAUSE in python shell CODING:

OUTPUT:

 Program:12
Delete record from table using python shell.
CODING:

OUTPUT:

⮚ PROGRAM:13

22
 To update student record using mysql-python connectivity.
CODING:

OUTPUT:

23
PROGRAMMIN
G
IN
PYTHON

24
⮚ PROGRAM:-1

Write a program in python to create four function

calculator.

25
⮚ PROGRAM:-2

Write a program to generate the table of a given number

using loop.

26
⮚ PROGRAM:-3
Write a program to print cubes of number in the range 10 to
18.

27
⮚ PROGRAM:-4
Write a program to calculate factorial of a inputted number
by using function.

28
⮚ PROGRAM:-5
Write a program to check whether number is Armstrong or
not.

29
⮚ PROGRAM:-6
Write a program to check whether a number is Perfect or
not.

30
⮚ PROGRAM:-7
Write a program to generate Fibonacci series in a tuple.

31
⮚ PROGRAM:-8
Write a program to input a string and count the number of
uppercase and lowercase letters.

32
⮚ PROGRAM:-9
Write a program to find the maximum, minimum and mean
value from the inputted list.

33
34
⮚ PROGRAM:-10
Write a program to store student’s information like
admission number, roll number, name and marks in a
dictionary, and display information on the basis of
admission number.

35
⮚ PROGRAM:-11
Write a Python program by using function that prints the
first n rows of Pascal’s triangle.

36
⮚ PROGRAM:-12
Write a program to remove duplicate characters of a given
string.

37
⮚ PROGRAM:-13
Write a python program to find the highest 3 value in a
dictionary..

38
⮚ PROGRAM:-14
Write a python program to count number of items in a
dictionary value that are in a list.

39
⮚ PROGRAM:-15
Write a python program to sort a list alphabetically in a
dictionary.

40
⮚ PROGRAM:-16
Write a python program to sort a dictionary by key.

41
⮚ PROGRAM:-17
Write a python program to sort a dictionary by key.

42
⮚ PROGRAM:-18
Write a python program to print reverse of string and check
whether string is palindrome or not.

43
⮚ PROGRAM:-19

Write a python program by using method to find and

display the prime numbers between 2 to N. Pass N as
argument to the method.

44
⮚ PROGRAM:-20
Write a python program by using function named
zeroending(scores) to add all those values in the list of
scores,which are ending with zero(0) and display the
sum.For eg,score=[200,456,300,100,234,678] The sum
should be displayed as 600.

45
46
⮚ PROGRAM:-21
Write a program to calculate the volume and area of a
sphere inside separate modules and import them in one
complete package.
Volume=4/3𝝅𝒓𝟑 𝒂𝒏𝒅 surfacearea=4πr2

47
48
⮚ PROGRAM:-22
49
Write a program to read a text file line by line and display
each word separated by a #.

50
⮚ PROGRAM:-23
Read a text file and display the number of vowels/
consonants/ uppercase/ lowercase characters in the file.

51
⮚ PROGRAM:-24
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.

52
53
⮚ PROGRAM:-25
Create a binary file with roll number, name and marks.
Input a roll number and update the marks.

54
⮚ PROGRAM:-26
Remove all the lines that contain the character `a' in a file
and write it to another file.

55
56
⮚ PROGRAM:-27
Write a program to read and display content of file from
end to beginning.

57
⮚ PROGRAM:-28
Write a program to read a file ‘story.txt’ and create another
file, storing an index of story.txt ,telling which line of the
file each word appears in.If word appears more than
once,then index should show all the line numbers
containing the word.

58
59
60
61
⮚ PROGRAM:-29
A file sports.dat contains information in following format:
Event-Participants Write a function that would read
contents from the file sports.dat and create a file name
Atheletic.dat copying only those records from sports.dat
where the event name is “Athletics”.

62
⮚ PROGRAM:-30
Write a program that Illustrate the working of deleting the
record from binary file.

63
⮚ PROGRAM:-31
Create a CSV file that store the information of roll num,
name and marks in three subjects of students

64
⮚ PROGRAM:-32
Write a random number generator that generates random
numbers between 1 and 6 simulates a dice.

65
⮚ PROGRAM:-33
Implement a Python program to add, delete and display the
records of an Employee through Stack.

66
⮚ PROGRAM:-34
Write a python program to display unique vowels present
67
in the given word using Stacks
o

68