JAIN INTERNATIONAL RESIDENTIAL SCHOOL

KANAKAPURA, KARNATAKA

PRACTICAL FILE

For
AISSCE 2025 Examination
[As a part of the Computer Science Course (083)]

SUBMITTED BY
KESAR JAIN K
JAIN INTERNATIONAL RESIDENTIAL SCHOOL
KANAKAPURA, KARNATAKA

This is to certify that MISS KESAR JAIN K of class XII D has

satisfactorily completed the course of programs in practical

Session 2024-25 in partial fulfilment of CBSE’s AISSCE

Examination 2025.

Name: KESAR JAIN K

Reg No.: ……………………………

………………………… ……………………………..
Signature of External Examiner Signature of Internal Examiner

Signature of Principal ………………….

Place: JAKKASANDRA
Date: ……………..
 CONTENTS
S.No Date Name Of Program Page Remarks
No.
1 10 – 6 – 23 SUM PRODUCT 1

2 20-6-23 PHONE BOOK USING 2

DICTIONARY

3 30-6-23 INDEX OF NON ZERO 3

NUMBERS

4 6-7-23 AREA OF SHAPES USING 4

MODULES

5 13-7-23 TEXT FILE -I 5-6

6 20-7-23 TEXT FILE -II 7

7 3-8-23 TEXT FILE- III 8-10

8 10-8-23 TEXT FILE- IV 11

9 17-8-23 BINARY FILE - I 12-13

10 24-8-23 BINARY FILE - II 14

11 31-8-23 CSV FILE - I 15

12 7-9-23 CSV FILE - II 16-17

13 14-9-23 STRING PALINDROME - 18-20

USING STACK
14 21-9-23 APPLICATION OF STACK – TO 21-23
STORE NOVOWEL WORDS

15 28-9-23 DATA STRUCTURE - STACK 24-26

16 6-10-23 LINEAR SEARCH 27-29

17 19-10-23 DATABASE CONNECTIVITY-I 30-31

18 26 - 10-23 DATABASE CONNECTIVITY-II 32-33

19 16-11-23 DATABASE CONNECTIVITY-III 34-35

20 23-11-23 DATABASE CONNECTIVITY-IV 36

21 1-12-23 MYSQL SET -I 37-42

22 5-12-23 MYSQL SET -II 43-48

23 8-12-23 MYSQL SET -III 49-53

24 14-12-23 MYSQL SET -IV 53-59

25 15-12-23 MYSQL SET-V 60-66

 PROGRAM 1
AIM: a program to enter two numbers and print the
sum, difference, product, quotient of the two numbers.
a=int(input('enter number1:'))
b=int(input('enter number2:'))
s=a+b
d=a-b
p=a*b
q=a/b
print('the sum is',s)
print('the difference is',d)
print('the product is',p)
print('the quotient is',q)

OUTPUT
enter number1:12
enter number2:11
the sum is 23
the difference is 1
the product is 132
the quotient is 1

PROGRAM 2
AIM: a program to accept a number and print if the
number is an Armstrong number or not.
a=int(input('enter the number:'))
l=len(str(a))
s=0
t=a
while t>0:
j=t%10
s+=j**l
t=t//10
if s==a:
print('It is an Armstrong no')
else:
print('It is not an Armstrong no')

OUTPUT:
enter the number:123
It is not an Armstrong no
 PROGRAM 3
AIM: a program to accept a number and print if the
number is prime or not.
d=int(input('enter a number:'))
s=0
for i in range(2,(d//2+1)):
if (d%i==0):
s=1
break
if(s==0):
print("it is a prime number")
else:
print("it is not a prime number")

OUTPUT:
enter a number:12
it is not a prime number
 PROGRAM 4

AIM: a=int(input('enter the number:'))

p=1
while a>0:
p*=a
a-=1
print('the factorial is',p)

OUTPUT:
enter the number:4
the factorial is 24

PROGRAM 5
AIM: a program to create a function words to accept a
string and print the number of words in the string,
function palindrome to print if the string is palindrome
and function count to count the occurrence of the
letter ‘T’ in the string.

def words():
a=input('enter string:')
y=a.split()
z=len(y)
print('the number of words in the string is:',z)

def palindrome():
b=input('enter string:')
if b[::-1]==b:
print('it is a palindrome')
else:
print('it is not a palindrome')
def count():
s=0
c=input('enter string:')
for i in c:
if i=='t':
s+=1
print('the number of Ts in the string is',s)
words()
palindrome()
count()

OUTPUT:
enter string:kesar jain computer science
the number of words in the string is: 4
enter string:mom
it is a palindrome
enter string:khyati is studying
the number of Ts in the string is 2

PROGRAM 6
AIM: A Program to enter a list of elements and pass
the list to a function and display the elements and
index of the non-zero elements.

def elements(a):
for i in range(0,len(a)):
if(a[i]!=0):
print(i,a[i])
l=eval(input('enter the values'))
elements(l)

OUTPUT:
enter the values12,12,0,0,0,0,0
0 12
1 12

PROGRAM 7
AIM: A program to find area of different shapes using
user defined functions
choice='yes'
def circle(r):
print('the are of the circle is: ',3.14*r**2)
def rectangle(l,b):
print('the area of rectangle is: ',l*b)
def square(s):
print('the area of the square is: ',s*s)
def triangle(b,h):
print('the area of triangle is: ',0.5*b*h)
while choice=='yes':
print('1.circle')
print('2.rectangle')
print('3.square')
print('4.triangle')
choice2=int(input('enter shape choice: '))
if choice2==1:
r=int(input('enter the radius: '))
circle(r)
 elif choice2==2:
l=int(input('enter the length: '))
b=int(input('enter the breadth: '))
rectangle(l,b)
elif choice2==3:
s=int(input('enter the side: '))
square(s)
else:
b=int(input('enter the base: '))
h=int(input('enter the height: '))
triangle(b,h)
choice=input('do you want to continue? yes/no:')

OUTPUT:
1.circle
2.rectangle
3.square
4.triangle
enter shape choice: 1
enter the radius: 1
the are of the circle is: 3.14
do you want to continue? yes/no:yes
1.circle
2.rectangle
3.square
4.triangle
enter shape choice: 2
enter the length: 2
enter the breadth: 2
the area of rectangle is: 4
do you want to continue? yes/no:no

PROGRAM 8
AIM: A program to read a text file line by line and
display each word separated by a ‘#’.

a=open(r'C:\Users\students\Documents\cprogram.txt')
b=a.read()
c=b.split()
for i in c:
print(i,"#",end="")

OUTPUT:
kesar #jain #12 #d #jain #international #residential
#school #bangalore #karnataka #salem #tamilnadu #

PROGRAM 9
AIM: To read a text file and display the number of
vowels/consonants/capital and small letters.

a=open(r'C:\Users\students\Documents\cprogram.txt')
b=a.read()
v=0
c=0
d=0
e=0
for i in b:
if i in ['a','e','i','o','u', 'A','E','I','O','U']:
v+=1
else:
c+=1
a.seek(0)
for i in b:
if i>='A' and i<='Z':
d+=1
elif i>='a' and i<='z':
e+=1
print('the number of vowels are',v)
print('the number of consonants are',c)
print('the number of capital letters are',d)
print('the number of small letters are',e)

OUTPUT:
the number of vowels are 33
the number of consonants are 56
the number of capital letters are 10
the number of small letters are 66

PROGRAM 10
AIM: A program to remove all the lines that contain
the character ‘a’ in a file and write it to another file.

a=open(r'C:\Users\students\Documents\csprogra.txt')
b=open(r'C:\Users\students\Documents\file.txt',"w")
l=[]
c=a.readlines()
for i in c:
if 'a' not in i:
l.append(i)
b.writelines(l)
b.close()

OUTPUT:
computer science
 PROGRAM 11
AIM: A Program to read a text file and count the
number of
occurrence of a word in the text file.

s=0
a=open(r'C:\Users\students\Documents\csprogra.txt')
b=input('enter the word:')
c=a.read()
e=c.split()
for i in e:
if i==b:
s+=1
print(s)

OUTPUT:
enter the word:Jain
2
 PROGRAM 12
AIM: Create a binary file with roll number and name.
Input a roll number and search the student with a
specific roll number and display the details.
import pickle
l=[]
def write():
a=open(r'C:\Users\students\empty2.dat','wb')
b=int(input('enter the number of students:'))
for i in range(1,b+1):
r=int(input('enter the rollno:'))
n=input('enter the name:')
l.append([r,n])
pickle.dump(l,a)
a.close()

def find():
b=open(r'C:\Users\students\empty2.dat','rb+')
roll=int(input('enter the rollno:'))
c=pickle.load(b)
 for i in c:
if(i[0]==roll):
print(i[1])
b.close()
write()
find()

OUTPUT:
enter the number of students:3
enter the rollno:1
enter the name:a
enter the rollno:2
enter the name:b
enter the rollno:3
enter the name:c
enter the rollno:1
a
 PROGRAM 13
AIM:a program to create a binary file with roll
number,name and marks. Input a roll number and
update the marks.

import pickle
l=[]
def enter():
a=open(r'C:\Users\students\empty2.dat','wb')
s=int(input('enter the number of students:'))
for i in range(0,s):
n=input('enter name:')
r=int(input('enter rollno:'))
m=int(input('enter marks:'))
l.append([n,r,m])
pickle.dump(l,a)
a.close()
def search():
a=open(r'C:\Users\students\empty2.dat','rb+')
n1=int(input('enter the rollno'))
 c=pickle.load(a)
m1=int(input('enter marks:'))
for i in c:
if i[1]==n1:
i[2]=m1
a.seek(0)
pickle.dump(c,a)
a.close()
def display():
a=open(r'C:\Users\students\empty2.dat','rb+')
d=pickle.load(a)
for i in d:
print(i)
enter()
search()
display()
OUTPUT:
enter the number of students:3
enter name:a
enter rollno:1
enter marks:100
enter name:b
enter rollno:2
enter marks:100
enter name:c
enter rollno:3
enter marks:100
enter the rollno1
enter marks:95
['a', 1, 95]
['b', 2, 100]
['c', 3, 100]
 PROGRAM 14:
AIM: a program to create the csv file which contains
the employee details and to search the particular
employee based on emp no and display the details.

import pickle
l=[]
def enter():
a=open(r'C:\Users\students\Documents\
empty2.dat','wb')
b=int(input('enter the number of employees:'))
for i in range(0,b):
e=input('enter employee name:')
n=int(input('enter employee number:'))
s=int(input('enter the salary:'))
l.append([e,n,s])
pickle.dump(l,a)
a.close()
def find():
b=open(r'C:\Users\students\Documents\
empty2.dat','rb+')
 e2=input('enter the employee name:')
c=pickle.load(b)
for i in c:
if i[0]==e2:
print(i[1],i[2])
b.close()
enter()
find()

OUTPUT:
enter the number of employees:3
enter employee name:a
enter employee number:1
enter the salary:100
enter employee name:b
enter employee number:2
enter the salary:200
enter employee name:c
enter employee number:3
enter the salary:300
enter the employee name:c
3 300
 PROGRAM 15:
AIM:to create a CSV file by entering user-id and
password, read and search the password for given user-
id.

import csv
def addrec():
with open('login.csv','w',newline='') as f:
b=csv.writer(f)
b.writerow(['UserID','Password'])
ans='y'
while ans in 'Yy':
print('Enter Login Details:')
uID=input("Enter the UserID:")
p=input("Enter the Password :")
b.writerow([uID,p])
ans=input("Do you want to add another
record(y/n):")
print("record stored")
def searchrec():
with open('login.csv','r') as f:
 b=csv.reader(f)
ans='y'
while ans in 'Yy':
found=False
uID=input('Enter the UserID which you want to
search:')
for r in b:
if str(uID) in r:
print("User ID:",r[0],'\nPassword :',r[1])
found=True
break
if found==False:
print('Record not found')
ans=input("Do you want to search another
record(y/n):")
addrec()
searchrec()
OUTPUT:
Enter employ details:
Enter the emp no:1525
Enter emp name :piriyanka attitude
Enter emp salary:10
Do you want to add another record(y/n):y
Enter employ details:
Enter the emp no:1234
Enter emp name :prasun
Enter emp salary:100000000
Do you want to add another record(y/n):n
record stored
Enter the emp no which you want to search:1525
Emp no: 1525
Emp name : piriyanka attitude
Emp salary: 10
Do you want to search another record(y/n):n
 PROGRAM 16

AIM: A program to implement the concept of stack.

l=[]
def push():
n=int(input("enter"))
l.append(n)
print(l)
def pop():
if l==[] or len(l) == 0:
print('stack empty')
else:
l.pop()
def disp():
if l==[]:
print('the stack is empty')
else:
print(l[::-1])
def peak():
if l==[]:
print("stack empty")
 else:
print(l[-1])
choice = 'yes'
while choice == 'yes':
print('1.push')
print('2.pop')
print('3.display')
print('4.peak')
choice1 = input('enter the choice')
if choice1 == '1':
push()
elif choice1 == '2':
pop()
elif choice1 == '3':
disp()
elif choice1 == '4':
peak()
choice = input('do you want to continue')
OUTPUT:
1.push
2.pop
3.display
4.peak
enter the choice1
enter11
[11]
do you want to continueno
 PROGRAM 17

AIM: A Program to create a product table and insert and

display data.

import mysql.connector as dd
y=
dd.connect(host="localhost",user="root",password="jirs",cha
rset="utf8",database="school")
x=y.cursor()
def ins():
for i in range(0,3):
pid=int(input("id of the product "))
pname=input("name of product")
price=input("price")
c="insert into product values(%s,%s,%s);"
t=(pid,pname,price)
x.execute(c,t)
y.commit()
def display():
q="select * from product where pid = 12;"
x.execute(q)
 for i in x :
print(i)
ins()
display()

OUTPUT:
id of the product 12
name of productpen
price100
id of the product 11
name of productpencil
price50
id of the product 13
name of productbook
price300
 PROGRAM 18

AIM: A Program to update and display the data after

modifying table.

import mysql.connector as dd
y=dd.connect(host="localhost",user="root",password="jirs",c
harset="utf8",database="school")
x=y.cursor()
def update1 ():
pid=int(input('pid:'))
pname=input('pname:')
price=input('price:')
q="update product set price = %s,pname = %s where pid
= %s ;"
t=(price,pname,pid)
x.execute(q,t)
y.commit()
def display():
q="select * from product ;"
x.execute(q)
for i in x :
 print(i)
update1()
display()

OUTPUT:
pid:12
pname:pens
price:1000
(12, 'pens', 1000)
(11, 'pencil', 50)
(13, 'book', 300)
 PROGRAM 19

AIM: A Program to display the table then delete a certain

row from the table.

import mysql.connector as dd
y=
dd.connect(host="localhost",user="root",password="jirs",cha
rset="utf8",database="school")
x=y.cursor()
def delete1():
pid=int(input("enter the product id:"))
q="delete from product where pid=%s;"
t=(pid,)
x.execute(q,t)
y.commit()
def display():
q="select * from product;"
x.execute(q)
for i in x :
print(i)
display()
delete1()
display()

OUTPUT:
(12, 'pens', 1000)
(11, 'pencil', 50)
(13, 'book', 300)
enter the product id:11
(12, 'pens', 1000)
(13, 'book', 300)
 PROGRAM 20

AIM: A Program to display the records of the products if the

price is more than 1000.

import mysql.connector as dd
y=dd.connect(host="localhost",user="root",password="jirs",c
harset="utf8",database="school")
x=y.cursor()
def disp2():
q="select * from product where price >= 1000;"
x.execute(q)
for i in x :
print(i)
disp2()

OUTPUT:
(12, 'pens', 1000)
MY SQL QUERIES
 PROGRAM 1

create database employee;

use employee;
create table workers (accno int,cust_name
varchar(25),fd_amount int,months int,int_rate float,fd_date
date);
insert into workers values
(1001,'priyanka',300000,36,6.00,2018-07-01);
insert into workers values
(1002,'prasun',200000,48,6.75,'2018-03-22');
insert into workers values
(1003,'shriram',400000,36,5.00,'2018-03-01');
insert into workers values
(1004,'nayo',100000,40,5.55,'2020-01-01');
select * from workers;

| ACCNO | CUST_NAME | FD_AMOUNT | MONTHS |

INT_RATE | FD_DATE

| 1001 | PRIYANKA | 300000 | 36 | 6 | 2018-07-01

|
| 1002 | PRASUN | 200000 | 48 | 6.75 | 2018-03-
22 |
| 1003 | SHRIRAM | 400000 | 36 | 5 | 2018-03-01
|
| 1004 | NAYO | 100000 | 40 | 5.55 | 2020-01-01
|

1 )TO DISPLAY THE DETAILS ABOUT ALL THE FIXED DEPOSITS

WHOSE INTEREST RATE IS NOT ENTERED.

SELECT * FROM WORKERS WHERE INT_RATE IS NULL;

OUTPUT
EMPTY SET

2. TO DISPLAY THE ACCOUNT NUMBER AND FD AMOUNT OF ALL

THE FIXED DEPOSITS THAT ARE STARTED BEFORE 01-04-2018

SELECT ACCNO,FD_AMOUNT FROM WORKERS WHERE FD_DATE<'2020-01-

01';

OUTPUT
accno fd_amount
1001 300000
1002 200000
1003 400000

3. TO DISPLAY THE CUSTOMER NAME AND FD AMOUNT FOR ALL

THE FIXED DEPOSITS WHICH DO NOT HAVE A NUMBER OF
MONTHS AS 36.

SELECT CUST_NAME,FD_AMOUNT FROM WORKERS WHERE MONTHS!=36;

OUTPUT:

| CUST_NAME | FD_AMOUNT |
| PRASUN | 200000 |
| NAYO | 100000

4. TO DISPLAY THE CUSTOMER NAME AND FD AMOUNT OF ALL

THE FIXED DEPOSITS FORWHICH THE FD AMOUNT IS LESS THAN
500000 AND INT_RATE IS MORE THAN 7.

SELECT CUST_NAME,FD_AMOUNT FROM WORKERS WHERE

FD_AMOUNT<500000 AND INT_RATE>7;

OUTPUT
EMPTY SET

5. TO DISPLAY THE DETAILS ABOUT ALL THE FIXED DEPOSITS

WHICH STARTED IN THE YEAR 2018.

SELECT * FROM WORKERS WHERE YEAR(FD_DATE)=2018;

OUTPUT

accno cust_name fd_amount months int_rate fd_date

1001 priyanka 300000 36 6 2018-07-01
1002 prasun 200000 48 6.75 2018-03-22
1003 shriram 400000 36 5
2018-03-01

6.To display the details of all the Fixed Deposits whose FD Amount is
in the range 40000-50000
select * from workers where fd_amount>=400000 and fd_amount<=500000;
OUTPUT:
accno cust_name fd_amount months int_rate fd_date
1003 shriram 400000 36 5 2018-03-01

7.To display the Customer name and FD amount for all the loans for
which thenumber of months is 24,36 or 48(using in operator)
select cust_name,fd_amount from workers where months=24 or months=36
or months=48;
OUTPUT

cust_name fd_amount
priyanka 300000
prasun 200000
shriram 400000

8. To display the Account number, customer name and fd_amount

for all the FDs for which the customer name ends with “anka’;
select accno,cust_name,fd_amount from workers where cust_name like
"%anka";
OUTPUT
accno cust_name fd_amount
1001 priyanka 300000

9. To display the average FD amount. Label the column as “Average

FD Amount”
select avg(fd_amount) "average fd amount"from workers;
OUTPUT
average fd amount

250000.0000

10.To display the total FD amount which started in the year 2018.
select sum(fd_amount) from workers where year(fd_date)=2018;
OUTPUT
sum(fd_amount)
900000

11.To display the details about all the FDs whose rate of
interest is not in the range 6% to 7%
select * from workers where int_rate>=6.00 and int_rate<=7.00;
OUTPUT
accno cust_name fd_amount months int_rate fd_date
1001 priyanka 300000 36 6 2018-07-01
1002 prasun 200000 48 6.75 2018-03-22

12.To display the details about all the FDs whose customer name
doesn’t contain the character “a”.
select * from workers where cust_name not like "%a%";
OUTPUT
Empty set

13.To update the Interest rate of all the bank Customers from 5.55 to
6.80
OUTPUT
update workers set int_rate=6.80 where int_rate=5.55;
 PROGRAM 2:

create database store;

use store;
create table items(code int,iname varchar(25),price float,qty
int,company varchar(25),tcode int);
insert into items values(1,"coat",600,5,"modi",101);
insert into items values(2,"boat",1000,10,"das",101);
insert into items values(3,"pen",100,50,"orio",103);
insert into items values(4,"book",25,15,"das",104);
select * from items;
| code | iname | price | qty | company | tcode |
| 1 | coat | 600 | 5 | modi | 101 |
| 2 | boat | 1000 | 10 | das | 101 |
| 3 | pen | 100 | 50 | orio | 103 |
| 4 | book | 25 | 15 | das | 104 |

create table traders(tcode int,tname varchar(25),city

varchar(25));
insert into traders values(101,"prasun","angul");
insert into traders values(103,"shriram","halo");
insert into traders values(104,"nayo","hyderabad");
select * from traders;
| tcode | tname | city |
| 101 | prasun | angul |
| 103 | shriram | halo |
| 104 | nayo | hyderabad |

1.To display the natural join of the tables items and traders.
select * from items natural join traders;
OUTPUT
| tcode | code | iname | price | qty | company | tname
| city |
| 101 | 1 | coat | 600 | 5 | modi | prasun | angul
|
| 101 | 2 | boat | 1000 | 10 | das | prasun | angul
|
| 103 | 3 | pen | 100 | 50 | orio | shriram | halo
|
| 104 | 4 | book | 25 | 15 | das | nayo |
hyderabad |

2) To display the number of items traded by every Trader.

select count(*) from items group by tcode;
OUTPUT
| count(*) |
| 2|
| 1|
| 1|
3) To display the Itemname, company, tradername and city
of all the items
select iname,company,tname,city from items natural join
traders;
OUTPUT
| iname | company | tname | city |
| coat | modi | prasun | angul |
| boat | das | prasun | angul |
| pen | orio | shriram | halo |
| book | das | nayo | hyderabad |

4.To display the Item name and Trader name of all the items
that are either from HALO or ANGUL
select iname,tname from items natural join traders where
city in("halo","angul");
OUTPUT
| iname | tname |
| coat | prasun |
| boat | prasun |
| pen | shriram |

5. To display the minimum and maximum price of the item

traded by every trader.
select tcode,max(price),min(price) from items group by
tcode;
OUTPUT
| tcode | max(price) |
min(price) |
| 101 | 1000 | 600 |
| 103 | 100 | 100 |
| 104 | 25 | 25 |

6 .To display the average price of an item traded by every

trader.
select avg(price) from items group by tcode;
OUTPUT
| avg(price) |
| 800 |
| 100 |
| 25

7. To display the Itemname and trader name of all the items

in the alphabetical order of the itemnames.
select iname,tname from items natural join traders order by
iname
asc;
OUTPUT
| iname | tname |
| boat | prasun |
| book | nayo |
| coat | prasun |
| pen | shriram |

8. To display the names of the traders whose average price of

an item is not more than 20000.
select tname from traders natural join items group by iname
having avg(price)<=20000;
OUTPUT
| tname
|
| prasun |
| nayo |
| prasun |
| shriram
|
9.To display the details about all the items whose avg price is
in the range 200 and 1000.
select * from items having avg(price)>=200 and
avg(price)<=1000;
OUTPUT
| code | iname | price | qty | company | tcode |

| 1 | coat | 600 | 5 | modi | 101 |

10. To display the total number of quantity and tname of

items available for every trader.
select sum(qty),tname from items natural join traders group
by tcode;
OUTPUT
| sum(qty) | tname |
| 15 | prasun |
| 50 | shriram |
| 15 | nayo |
 PROGRAM 3:

select * from worker;

| wno | name | doj | dob | gender | dcode |
| 1001 | prasun | 2013-09-02 | 1991-09-01 | male | d01
|
| 1002 | shriram | 2015-07-02 | 1991-09-04 | male | d02
|
| 1003 | priyanka | 2016-08-12 | 1989-10-10 | female |
d03 |
| 1005 | nayo | 2014-01-17 | 1984-10-19 | female | d01
|

select * from dept;

| dcode | department | city |
| d01 | media | delhi |
| d02 | marketing | mumbai |
| d03 | finance | kolkata |
1)To display the department name, code and number of workers in
every department.
select department,dcode,count(*) from worker natural join dept
group by dcode;
OUTPUT
| department | dcode | count(*) |
| media | d01 | 2|
| marketing | d02 | 1|
| finance | d03 | 1|

2)To display the names and date of joining of all the workers in
Kolkata.
select name doj from worker natural join dept where city like
"kolkata";
OUTPUT
| doj |
| priyanka |

3)select count(*),gender from worker group by gender;

OUTPUT
| count(*) | gender |
| 2 | female |
| 2 | male |
4) To display Wno,name ,Gender,department in descending order of
Wno
select wno,name,gender,department from worker natural join dept
order by wno desc;
OUTPUT
| wno | name | gender | department |
| 1005 | nayo | female | media |
| 1003 | priyanka | female | finance |
| 1002 | shriram | male | marketing |
| 1001 | prasun | male | media |

5) To display the names of all the departments where more than one
worker are available.
select department,dcode from dept natural join worker group by
dcode having count(*)>1;
OUTPUT
| department | dcode |
| media | d01 |

6) To display the wno,name,department of all the workers who are

born between “1987-01-01” and “1991-12-01”
select wno,name,department from worker natural join dept where
doj between "2013-01-01" and "2016-12-01";
OUTPUT
| wno | name | department |
| 1001 | prasun | media |
| 1002 | shriram | marketing |
| 1003 | priyanka | finance |
| 1005 | nayo | media |

7)To display the details about all the workers whose Wno is either
1003 , 1005 or 1007
select * from worker where wno in (1003,1005,1007);
OUTPUT
| wno | name | doj | dob | gender | dcode |
| 1003 | priyanka | 2016-08-12 | 1989-10-10 | female | d03 |
| 1005 | nayo | 2014-01-17 | 1984-10-19 | female | d01 |

8) To display wno,name ,department city of all the workers whose

name contains the letter “ p” and who are not in Mumbai and
kolkata
select wno,name,department,city from worker natural join dept
where name like "%p%" and city!="mumbai" and city!="kolkata";
OUTPUT
| wno | name | department | city |
| 1001 | prasun | media | delhi |
9) To count and display the number of male workers who have joined
after “1986-01-01”
select count(*) from worker where gender="male" and doj>"1981-
01-01" group by gender;
OUTPUT
| count(*) |
| 2|

10) To display the details of all the male workers in the finance
department.
select * from worker natural join dept where gender="female" and
department="finance";

OUTPUT
Dcode wno name doj dob gender department city
d03 1003 priyanka 2016-08-12 1989-10-10 female finance kolkata
 PROGRAM 4

select * from hospital;

| pno | name | age | dept | doa | charges | sex |
| 1 | prasun | 65 | surgery | 2018-02-23 | 600 | m |
| 2 | shriram | 24 | ent | 2019-01-01 | 400 | f |
| 3 | nayo | 45 | cardiology | 2018-12-19 | 400 | f |
| 4 | ali | 14 | ortho | 2018-10-01 | 600 | m |

1.To show all the information about the patients of the cardiology
department.
select * from hospital where dept="cardiology";
OUTPUT
| pno | name | age | dept | doa | charges | sex |
| 3 | nayo | 45 | cardiology | 2018-12-19 | 400 | f |

2.To list the names of female patients who are either in the
orthopaedic or surgery department.
select name from hospital where sex="f" and (dept="ortho" or
dept="surgery");
OUTPUT
Empty set
3.To display various departments in the hospital.
select distinct dept from hospital;
OUTPUT
| dept |

| surgery |
| ent |

| cardiology |

| ortho |

4.To display the number of patients in each department.

select count(*) from hospital group by dept;
OUTPUT
| count(*) |
| 1|
| 1|
| 1|
| 1|

5.To display details of all the patients whose name’s second

character is “a”.
select * from hospital where name like "_a%";
OUTPUT
| pno | name | age | dept | doa | charges | sex |
| 3 | nayo | 45 | cardiology | 2018-12-19 | 400 | f |
6.To display the details of all the patients who was admitted in the
year 2019.
select * from hospital where year(doa)=2019;
OUTPUT
| pno | name | age | dept | doa | charges | sex |
| 2 | shriram | 24 | ent | 2019-01-01 | 400 | f |

7.To display the details about all the patients in the reverse
alphabetical order of their names.
select * from hospital order by name desc;
OUTPUT
| pno | name | age | dept | doa | charges | sex |
| 2 | shriram | 24 | ent | 2019-01-01 | 400 | f |
| 1 | prasun | 65 | surgery | 2018-02-23 | 600 | m |
| 3 | nayo | 45 | cardiology | 2018-12-19 | 400 | f |
| 4 | ali | 14 | ortho | 2018-10-01 | 600 | m |

8.To display the average charges collected in every department.

select avg(charges) from hospital group by dept;
OUTPUT
| avg(charges) |
| 400.0000 |
| 400.0000 |
| 600.0000 |
| 600.0000 |
9.To display the patient detail whose age is missing.
select * from hospital where age is null;
OUTPUT
Empty set

10.To display the names of the patients who are charged in the range
300 and 400
select name from hospital where charges between 300 and 400;
OUTPUT
| name |
| shriram |
| nayo |

11.To display the number of patients who are aged above 30.
select count(*) from hospital where age>30;
OUTPUT
| count(*) |
| 2|

12.To display the names of the department and the number of

patients in the department that have more than or equal to one
patient.
select dept,count(*) from hospital group by dept having count(*)>=1;
OUTPUT
| dept | count(*) |
| cardiology | 1|
| ent | 1|
| ortho | 1|
| surgery | 1|

13.To delete the record of the patient “prasun”.

delete from hospital where name like"prasun";
OUTPUT
Query OK
select * from hospital;
| pno | name | age | dept | doa | charges | sex |
| 2 | shriram | 24 | ent | 2019-01-01 | 400 | f |
| 3 | nayo | 45 | cardiology | 2018-12-19 | 400 | f |
| 4 | ali | 14 | ortho | 2018-10-01 | 600 | m |

14.To add another column DOCNAME of datatype varchar(20) into

the table hospital.
alter table hospital add(docname varchar(20));
select * from hospital;
OUTPUT
| pno | name | age | dept | doa | charges | sex |
docname |
| 2 | shriram | 24 | ent | 2019-01-01 | 400 | f | NULL
|
| 3 | nayo | 45 | cardiology | 2018-12-19 | 400 | f | NULL
|
| 4 | ali | 14 | ortho | 2018-10-01 | 600 | m | NULL |

15.To decrease the charges by 5% of all the patients admitted to the

ENT department.
update hospital set charges=charges-(charges*0.05) where
dept="ent";
OUTPUT
Query OK
select * from hospital;
OUTPUT
| pno | name | age | dept | doa | charges | sex |
docname |
| 2 | shriram | 24 | ent | 2019-01-01 | 380 | f | NULL
| 3 | nayo | 45 | cardiology | 2018-12-19 | 400 | f | NULL
| 4 | ali | 14 | ortho | 2018-10-01 | 600 | m | NULL |
 PROGRAM 5:

select * from club;

| coach_id | name | age | sports | doa | pay | sex |
| 1 | prasun | 35 | golf | 1996-03-27 | 1000 | m |
| 2 | nayo | 34 | karate | 1998-01-20 | 1200 | f |
| 3 | shriram | 34 | squash | 1999-02-19 | 2000 | m |
| 4 | priyanka | 30 | football | 1998-01-01 | 1500 | f |
| 5 | pratibha | 35 | golf | 1998-01-12 | 3000 | f |

1.To show all information about the golf coaches in the Club.
select * from club where sports="golf";
OUTPUT
| coach_id | name | age | sports | doa | pay | sex
| 1 | prasun | 35 | golf | 1996-03-27 | 1000 | m |
| 5 | pratibha | 35 | golf | 1998-01-12 | 3000 | f |

2.To list the names of all coaches with their date of appointment in
descending order.
select name from club order by doa desc;
OUTPUT
| name |
| shriram |
| nayo |
| pratibha |
| priyanka |
| prasun |
3.select sports ,min(pay),max(pay),avg(pay) from club group by
sports;
OUTPUT
| sports | min(pay) | max(pay) | avg(pay) |
| football | 1500 | 1500 | 1500.0000 |
| golf | 1000 | 3000 | 2000.0000 |
| karate | 1200 | 1200 | 1200.0000 |
| squash | 2000 | 2000 | 2000.0000 |

4.To display details and bonus(5% of pay).

update club set pay=pay+(pay*0.05);
OUTPUT
Query OK
select * from club;
OUTPUT
| coach_id | name | age | sports | doa | pay | sex |
| 1 | prasun | 35 | golf | 1996-03-27 | 1050 | m |
| 2 | nayo | 34 | karate | 1998-01-20 | 1260 | f |
| 3 | shriram | 34 | squash | 1999-02-19 | 2100 | m |
| 4 | priyanka | 30 | football | 1998-01-01 | 1575 | f |
| 5 | pratibha | 35 | golf | 1998-01-12 | 3150 | f |
5.To display the distinct sports from the club.
select distinct sports from club;
OUTPUT
| sports |
| golf |
| karate |
| squash |
| football |

6.To display the details about the female coaches in the club.
select * from club where sex="f";
OUTPUT
| coach_id | name | age | sports | doa | pay | sex |
| 2 | nayo | 34 | karate | 1998-01-20 | 1260 | f |
| 4 | priyanka | 30 | football | 1998-01-01 | 1575 | f |
| 5 | pratibha | 35 | golf | 1998-01-12 | 3150 | f |

7. To display the coachnames, sports , pay and date of_app of all the
coaches whose name ends with “a”.
select name,sports,pay,doa from club where name like "%a";
OUTPUT
| name | sports | pay | doa |
| priyanka | football | 1575 | 1998-01-01 |
| pratibha | golf | 3150 | 1998-01-12 |
8.To display the coachname, sports ,age and pay of all the coaches
whose pay is in the range 2000-2500
select name,sports,age,pay from club where pay>=1000 and
pay<=2000;
OUTPUT
| name | sports | age | pay |

| prasun | golf | 35 | 1050 |

| nayo | karate | 34 | 1260 |

| priyanka | football | 30 | 1575 |

9.To display the details about all the coaches who are above 30 in
age and coach a sport starting with the letter “g”.
select * from club where age>30 and sports like "g%";
OUTPUT
| coach_id | name | age | sports | doa | pay | sex |
| 1 | prasun | 35 | golf | 1996-03-27 | 1050 | m |
| 5 | pratibha | 35 | golf | 1998-01-12 | 3150 | f |

10.To display the number of coaches available for every sports.

select count(*) from club group by sports;
OUTPUT
| count(*) |
| 1|
| 2|
| 1|
| 1|

11. To display the average Pay given for a coach for every sports
activity.
select avg(pay) from club group by sports;
OUTPUT
| avg(pay) |

| 1575.0000 |

| 2100.0000 |

| 1260.0000 |

| 2100.0000 |

12. To display the details about all the male coaches who joined after
15th February 1998.
select * from club where sex="m" and doa>"1998-02-15";
OUTPUT
| coach_id | name | age | sports | doa | pay | sex |
| 3 | shriram | 34 | squash | 1999-02-19 | 2100 | m |

13. To display the coach id , names and age of all the coaches who
coach neither Swimming nor golf
select coach_id,name,age from club where sports!="swimming" and
sports!="golf";
OUTPUT
| coach_id | name | age |
| 2 | nayo | 34 |
| 3 | shriram | 34 |
| 4 | priyanka | 30 |

14.To display the names of all the sports in the club which have more
than 2 coaches.
select sports from club group by sports having count(*)>1;
OUTPUT
| sports |
| golf |
15.To display the total salary given for male and female coaches in
the club.
select sum(pay) from club group by sex;
OUTPUT
| sum(pay) |
| 5985 |
| 3150 |