JAIN INTERNATIONAL RESIDENTIAL

SCHOOL

KANAKAPURA, KARNATAKA

PRACTICAL FILE

For

AISSCE 2023 Examination

[As a part of the Computer Science
Course (083)]

SUBMITTED BY
KHYATI KEDIA
JAIN INTERNATIONAL RESIDENTIAL SCHOOL
KANAKAPURA, KARNATAKA

This is to certify that MISS KHYATI KEDIA of class XII D has

satisfactorily completed the course of programs in practical

Session 2023-24 in partial fulfilment of CBSE’s AISSCE

Examination 2024.

Name: KHYATI KEDIA

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

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

Signature of Principal ………………….

Place: JAKKASANDRA
Date: ……………..
 CONTENTS

S.No Date Name Of Program Page No. Remarks

1 10 – 6 – 24 MATH OPERATIONS 1

2 20-6-24 ARMSTRONG NUMBER 2

3 30-6-24 PRIME NUMBER 3

4 6-7-24 FACTORIAL OF A NUMBER 4

5 13-7-24 PALINDROME 5-6

6 20-7-24 INDEX OF NON-ZERO 7

ELEMENTS

7 3-8-24 AREA OF SHAPES 8-9

8 10-8-24 TEXT FILE-I 10

9 17-8-24 TEXT FILE-II 11

10 24-8-24 TEXT FILE-III 12

11 31-8-24 TEXT FILE-IV 13

12 7-9-24 BINARY FILE-I 14-15

13 14-9-24 BINRY FILE-II 16-17

14 21-9-24 CSV FILE-I 18-19

15 28-9-24 CSV FILE-II 20-21

16 6-10-24 STACK 22-24

17 19-10-24 DATABASE CONNECTIVITY- 25-26

I

18 26 - 10-24 DATABASE CONNECTIVITY- 27-28

II
19 16-11-24 DATABASE CONNECTIVITY- 29-30
III

20 23-11-24 DATABASE CONNECTIVITY- 31-32

IV

21 1-12-24 MYSQL SET -I 33-38

22 5-12-24 MYSQL SET -II 39-45

23 8-12-24 MYSQL SET -III 46-52

24 14-12-24 MYSQL SET -IV 53-57

25 15-12-24 MYSQL SET-V 58-62

 PROGRAM -1
Aim:
# Write a program to enter two numbers and print the sum, difference,
product and quotient of the two numbers.

PROGRAM CODING:
a=int(input('enter number='))

b=int(input('enter number='))

c=a+b

d=a-b

e=a*b

f=a%b

print('the sum is',c)

print('the difference is',d)

print('the product is',e)

print('the quotient is',f)

output:
enter number=15

enter number=23

the sum is 8

the difference is 2

the product is 15
the quotient is 2

PROGRAM -2
Aim:
#Write a program to accept a number and print if the number is an
Armstrong number or not.

PROGRAM CODING:
p=int(input('enter the number='))

l=len(str(a))

k=0

t=p

while t>0:

j=t%10

k+=j**l

t=t//10

if k==p:

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:
#Write a program to accept a number and print if the number is a prime
number or not.

PROGRAM CODING:
output: a=int(input("enter a number="))

y=0

for i in range(2,(a//2+1)):

if (a%i==0):

y=1

break

if(y==0):

print("it is a prime number")

else:

print("it is not a prime number"

OUTPUT:
enter a number=5

it is a prime number
 PROGRAM -4
Aim:
#write a program to create a function factorial to accept a number and
print the factorial of the number.

PROGRAM CODING:
num =int(input("Enter a number: "))

f=1

if num<0:

print(" Factorial does not exist for negative numbers")

elif num==0:

print("The factorial of 0 is 1")

else:

for i in range(1,num + 1):

f=f*i

print("The factorial of",num,"is",f)

OUTPUT:
Enter a number: 54
The factorial of 54 is
23084369733924138047209274268302758108327856
4571807941132288000000000000
 PROGRAM -5
Aim:
#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.

PROGRAM CODING:
def words():

a=input("enter str:")

b=a.split()

print(len(b))

def palindrome():

a=input("enter word:")

if (a[::-1]==a):

print("string is palindrome")

else:

print("string is not a palindrome")

def count():

b=0

a=input("enter the string=")

for i in a:

if(i=="t"):

b+=1
 print(b)

words()

palindrome()

count()

OUTPUT:
enter str:i love studying computer science

enter word:mom

string is palindrome

enter the string=i love studying computer science

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.

PROGRAM CODING:
def elements(a):

for i in range(0,len(a)):

if(a[i]!=0):

print(i)

l=eval(input("values??"))

elements(l)

OUTPUT:
values??[1,4,7,0,70,0,5,8]

7
 PROGRAM -7
Aim:
# A program to find area of different shapes using user defined functions

PROGRAM CODING:
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: 2

enter the length: 3

enter the breadth: 4

the area of rectangle is: 12

do you want to continue? yes/no:yes

1.circle

2.rectangle

3.square

4.triangle

enter shape choice: 4

enter the base: 3

enter the height: 5

the area of triangle is: 7.5

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 #

PROGRAM CODING:
a=open(r"C:\Users\students\Desktop/kesar.txt")

b=a.read()

c=b.split()

for i in c:

print(i,"#")

OUTPUT:
prime #minister #of #india #is #mr #modi #
 PROGRAM -9
Aim:
# Read a text file and display the number of

vowels/consonants/capital and small letters.

PROGRAM CODING:
y=open(r'C:\Users\students\Desktop\kesar.txt',"r")

x=y.read()

v=0

c=0

l=0

u=0

for i in x :

if i >= 'a' and i<= 'z':

l=l+1

elif i>= 'A' and i<='Z':

u=u+1

for i in x :

if i in ['a','e','i','o','u']:

v=v+1

else:

c=c+1
print(l)

print(v)

print(u)

print(c)

OUTPUT:
36

15

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

PROGRAM CODING:
y=open(r'C:\Users\students\Desktop\kesar.txt',"r")

z=open(r"C:\Users\students\Desktop\cs.txt","w")

x=y.readlines()

k=[]

for i in x :

if 'a' not in i :

k.append(i)

z.writelines(k)

z.close()

OUTPUT:
 PROGRAM -11
Aim:
# A Program to read a text file and count the number of occurrence of a
word in the text file.

PROGRAM CODING:
a=open(r"C:\Users\students\Desktop/kesar.txt")

k=input("enter the word?")

f=0

b=a.read()

c=b.split()

for i in c:

if (i==k):

f=f+1

print(f)

OUTPUT:
enter the word?prime

1
 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.

PROGRAM CODING:
import pickle

l=[]

def writing():

y=open(“stu.dat”,”wb”)

for i in range(1,3):

n=int(input”roll no”))

na=input(“name”)

l.append([n,na])

pickle.dump(l,y)

y.close()

def reading():

y=open(“stu.dat”,”rb+”)

n=int(input(“roll no”))

p=y.tell()

a=pickle.load(y)

na=input(“name”)

for i in a:
if(i[0]==n):

print i[0]

print i[1]

y.close()

def display():

y=open(“stu.dat”,”rb+”)

a1=pickle.load(y)

print(a1)

writing()

reading()

display()

OUTPUT:
roll no1234

namekhyati

roll no2345

namearyan

roll no1235

nameashka

[[1234, ”khyati”], [2345, “Aryan”]]

 PROGRAM -13
Aim:
# Write a program to create a binary file with roll number ,name and
marks. Input a roll number and update the marks

PROGRAM CODING:
import pickle

l=[]

def wr():

y=open(“binary13.dat”,”wb”)

for i in range(0,2):

na = input(“enter the name:”)

roll=int(input(“enter the roll no:”))

ma=int(input(“enter the marks:”))

l.append([na,roll,ma])

pickle.dump(l,y)

y.close()

def read():

y=open(“binary13.dat”,”rb+”)

roll=int(input(“enter the roll no of the person whose marks is to

be

edited:"))
a=pickle.load(y)

ma=int(input(“enter the marks”))

for i in a:

if i[0]==roll:

i[2]=ma

y.close()

def display():

y=open(“binary13.dat”,”rb+”)

a1=pickle.load(y)

print(a1)

wr()

read()

display()

OUTPUT:
enter the name:pri

enter the roll no:123

enter the marks55

enter the name:pra

enter the roll no:124

enter the marks60

enter the roll no of the person whose marks is to be edited:123

enter the marks70

[“pri”, 123, 70], [“pra”, 124, 60]]

 PROGRAM -14
Aim:
# A Program to create the csv file which should contains the employee
details and to search the particular employee based on emp no and
display the details.

PROGRAM CODING:
import csv

def addrec():

with open(“cs.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(“cs.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)==str(r[0]):

print(“User ID:&quot;,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 Login Details:

Enter the UserID:3333

Enter the Password :1234

Do you want to add another record(y/n):n

record stored

Enter the UserID which you want to search:3333

User ID: 3333

Password : 1234

Do you want to search another record(y/n):n

 PROGRAM -15
Aim:
# A Program to create a CSV file by entering user-id and password, read
and search the password for given user-id.

PROGRAM CODING:
import csv

found=0

a=open(r'C:\Users\students\Desktop\kesar.csv',"w",newline="")

b=csv.writer(a)

c=int(input("number of people"))

l=[]

for i in range(0,c):

d=int(input("user id?"))

e=input("pass?")

l.append([d,e])

b.writerows(l)

a.close()

a=open(r'C:\Users\students\Desktop\kesar.csv',"r")

k=csv.reader(a)

a.seek(0)

y=a.tell()

h=int(input("enter user id "))

for i in k:

if(str(i[0])==str(h)):

print(i[1])

found=1

pa=i[1]

break

if(found==1):

print("password=",pa)

else:

print("not found")

OUTPUT:
number of people2
user id?1
pass?gg
user id?2
pass?hh
enter user id 1
gg
password= gg
 PROGRAM -16
Aim:
# A program to implement the concept of stack.

PROGRAM CODING:
l=[ ]

def push():

k=int(input("enter a number:"))

l.append(K)

print(l)

def pop1():

if l==[]or len(l)==0:

print("stack is empty")

else:

l.pop()

def disp():

if l==[]:

print("stack 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.

PROGRAM CODING:
import mysql.connector

con=mysql.connector.connect(host="localhost" , user="root" ,
password="jirs" ,charset="utf8", database="productdetails");

cur=con.cursor()

def adddata():

for i in range(0,3):

productid=int(input("enter pid="))

productname=input("enter pname=")

price=int(input("price=$"))

quantity=int(input("qty="))

dop=input("date of purchase=")

q="insert into product values (%s,%s,%s,%s,%s);"

t=(productid,productname,price,quantity,dop)

cur.execute(q,t)

con.commit()

adddata()

def display():

q="select * from product;"

 cur.execute(q)

for i in cur:

print(i)

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.

PROGRAM CODING:
import mysql.connector

con=mysql.connector.connect(host="localhost" , user="root" ,
password="jirs" ,charset="utf8", database="productdetails");

cur=con.cursor()

def update():

productid=int(input("enter pid="))

productname=input("enter pname=")

price=int(input("price=$"))

quantity=int(input("qty="))

dop=input("date of purchase=")

q= "update product set pname=%s,price=%s,qty=%s,dop=

%s where pid=%s;"

t=(productname,price,quantity,dop,productid)

cur.execute(q,t)

con.commit()

def display():

q="select * from product;"

cur.execute(q)
 for i in cur:

print(i)

display()

update()

display()

OUTPUT:

(9855, 'masala lays', 10.0, 7, datetime.date(2024, 5, 3))

(874984, 'dark green lays', 10.0, 4, datetime.date(2024, 2, 4))
(8230990, 'yelllow lays ', 10.0, 2, datetime.date(2024, 5, 7))
(78045, 'dehg', 8.0, 7, datetime.date(2024, 8, 9))
(843670, '740', 9.0, 74, datetime.date(2024, 9, 8))
(6343, '874', 7.0, 8, datetime.date(2024, 5, 8))
(436, 'nutella', 250.0, 2, datetime.date(2024, 8, 9))
(346749, 'kinderjoy', 76.0, 9, datetime.date(2024, 9, 3))
(7480879, 'mousse', 150.0, 4, datetime.date(2024, 7, 5))
(163493, 'oreo silk', 150.0, 5, datetime.date(2024, 6, 4))
(763492, 'oreo silk', 150.0, 3, datetime.date(2024, 7, 5))
(68432, 'oreo silk', 150.0, 5, datetime.date(2024, 8, 9))
(4267803, 'oreo silk', 150.0, 3, datetime.date(2024, 7, 8))
(7303, 'silk', 24.0, 9, datetime.date(2024, 5, 6))
(238648, 'ferero rocher', 500.0, 1, datetime.date(2024, 7, 7))
enter pid=436
enter pname=nutella
price=$300
qty=6
date of purchase=2024-07-12
(9855, 'masala lays', 10.0, 7, datetime.date(2024, 5, 3))
(874984, 'dark green lays', 10.0, 4, datetime.date(2024, 2, 4))
(8230990, 'yelllow lays ', 10.0, 2, datetime.date(2024, 5, 7))
(78045, 'dehg', 8.0, 7, datetime.date(2024, 8, 9))
(843670, '740', 9.0, 74, datetime.date(2024, 9, 8))
(6343, '874', 7.0, 8, datetime.date(2024, 5, 8))
(436, 'nutella', 300.0, 6, datetime.date(2024, 7, 12))
(346749, 'kinderjoy', 76.0, 9, datetime.date(2024, 9, 3))
(7480879, 'mousse', 150.0, 4, datetime.date(2024, 7, 5))
(163493, 'oreo silk', 150.0, 5, datetime.date(2024, 6, 4))
(763492, 'oreo silk', 150.0, 3, datetime.date(2024, 7, 5))
(68432, 'oreo silk', 150.0, 5, datetime.date(2024, 8, 9))
(4267803, 'oreo silk', 150.0, 3, datetime.date(2024, 7, 8))
(7303, 'silk', 24.0, 9, datetime.date(2024, 5, 6))
(238648, 'ferero rocher', 500.0, 1, datetime.date(2024, 7, 7))#
 PROGRAM -19
Aim:
# A Program to display the table then delete a certain row from the table.

PROGRAM CODING:
import mysql.connector

con=mysql.connector.connect(host="localhost",user="root",pa
ssword="jirs",charset="utf8",database="students")

cur=con.cursor()

def add():

for i in range(0,3):

rollno=int(input("rollno="))

name=input("name=")

class2=input("class=")

q="insert into school values(%s,%s,%s);"

t=(rollno,name,class2)

cur.execute(q,t)

con.commit()

def update():

rollno=int(input("rollno="))

name=input("name=")

class2=input("class")
 q="update school set name=%s,class1=%s where rollno=
%s;"

t=(name,class2,rollno)

cur.execute(q,t)

con.commit()

def delete():

rollno=int(input("enter"))

p="delete from school where rollno=%s;"

t=(rollno,)

cur.execute(p,t)

con.commit()

add()

update()

delete()

OUTPUT:
rollno=1

name=aryan

class=12

rollno=2

name=tej

class=12

rollno=3

name=heet
class=12

rollno=4

name=aayush

class12

enter4

PROGRAM -20
Aim:
# A Program to display the records of the products if the price is more
than 1000.

PROGRAM CODING:
import mysql.connector

con=mysql.connector.connect(host="localhost" , user="root" ,
password="jirs" ,charset="utf8", database="productdetails");

cur=con.cursor()

def adddata():

for i in range(0,3):

productid=int(input("enter pid="))

productname=input("enter pname=")

price=int(input("price=$"))

quantity=int(input("qty="))

dop=input("date of purchase=")

q="insert into product values (%s,%s,%s,%s,%s);"

t=(productid,productname,price,quantity,dop)

cur.execute(q,t)

con.commit()

def display():

q="select * from product where price>1000;"

cur.execute(q)

for i in cur:
 print(i)

adddata()

display()

OUTPUT:
(9855, 'masala lays', 10.0, 7, datetime.date(2024, 5, 3))
(874984, 'dark green lays', 10.0, 4, datetime.date(2024, 2, 4))
(8230990, 'yelllow lays ', 10.0, 2, datetime.date(2024, 5, 7))
(78045, 'dehg', 8.0, 7, datetime.date(2024, 8, 9))
(843670, '740', 9.0, 74, datetime.date(2024, 9, 8))
(6343, '874', 7.0, 8, datetime.date(2024, 5, 8))
(436, 'nutella', 300.0, 6, datetime.date(2024, 7, 12))
(346749, 'kinderjoy', 76.0, 9, datetime.date(2024, 9, 3))
(7480879, 'mousse', 150.0, 4, datetime.date(2024, 7, 5))
(163493, 'oreo silk', 150.0, 5, datetime.date(2024, 6, 4))
(763492, 'oreo silk', 150.0, 3, datetime.date(2024, 7, 5))
(68432, 'oreo silk', 150.0, 5, datetime.date(2024, 8, 9))
(4267803, 'oreo silk', 150.0, 3, datetime.date(2024, 7, 8))
(7303, 'silk', 24.0, 9, datetime.date(2024, 5, 6))
(238648, 'ferero rocher', 500.0, 1, datetime.date(2024, 7, 7))
(630, 'blue lays', 10.0, 6, datetime.date(2024, 9, 13))
(872, 'kinder joy', 36.0, 4, datetime.date(2024, 3, 2))
(730, 'scezwan', 150.0, 2, datetime.date(2024, 7, 26))

MYSQL-1:
 Considering the table bank given below write queries for the
following:
questions:
1.To display the details about all the

Fixed Deposits whose interest rate is not

entered.
Ans: Select * from Fixed_Deposits where Int_rate is null;

Output:
2. To display the Account number and
FD amount of all the Fixed Deposits that
are started before
01-04-2018.

Ans: . Select Accno, FD_amount from Fixed_Deposits where FD_Date < “01-04-2018”;

Output:

3. To display the customer name and FD

amount for all the Fixed deposits which
do not have a number of months as 36.
Ans: 3. Select CUST_Name from Fixed_Deposits where Months <> 36;

Output:
4. To display the customer name and FD
Amount of all the Fixed deposits for
which the FD amount is less than
500000 and int_rate is more than 7.

Ans: Select CUST_Name, FD_amount from Fixed_Deposits where FD_Amount<500000 and

Int_Rate>7;

Output:

5. To display the details about all the

Fixed deposits which started in the year
2018.

Ans: Select * from Fixed_Deposits where year(FD_Date)=2016;

Output:
6.To display the details of all the Fixed Deposits whose FD Amount is in the range 40000-

50000.

Ans: Select * from Fixed_Deposits where FD_Amount between 40000 and 50000;

Output:

7.To display the Customer name and FD amount for all the
loans for which the number of months is 24,36 or 48(using in
operator).
Ans: . Select CUST_Name, FFD_Amount from Fixed_Deposits where Months in (24,36,48);

Output:
8. To display the Account number, customer name and
fd_amount for all the FDs for which the customer name ends
with “Sharma”;
Ans: 8. Select ACCNo, CUST_Name, FD_amount from Fixed_Deposits where CUST_Name like
“%Sharma”;

Output:

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

“Average FD Amount”
Ans: 9. Select avg(FD_Amount) “Average FD Amount” from Fixed_Deposits;

Output:

10.To display the total FD amount which started in the year

2018.
Ans: Select sum(FD_Amount) from Fixed_Deposits where year(FD_Date)=2018;
Output:
11.To display the details about all the FDs whose rate of
interest is not in the range 6% to 7%
Ans: . Select * from Fixed_Deposits where Int_Rate not between 6 and 7;
Output:

12.To display the details about all the FDs whose customer
name doesn’t contain the character “a”.
Ans: . Select * from Fixed_Deposits where CUST_Name not like “%a%”;
Output:
13.To update the Interest rate of all the bank Customers from
6.75 to 6.80
Ans: Update Fixed_Deposits set Int_Rate 6.80 where Int_Rate=6.75;

Output:
 MYSQL-2:
AIM :

To manipulate the table CLUB using MySQL Queries.

1. To show all information about the Swimming coaches in the

Club
Ans: Select * from club where sports = "swimming";
Output:

2.To list the names of all coaches with their date of

appointment in descending order.
Ans: Select * from club where sports = "swimming"; 2.
3.To display coach name, pay ,age and bonus(15% of pay)

ANS: Select coachname, pay, age, 0.15*pay as bonus from club;

OUTPUT:

4.To display the distinct sports from the club.

ANS: Select distinct sports from club;

OUTPUT:

5. To display the details about the female coaches in the club

 ANS: Select * from club where sex = "f";

OUTPUT:

6. To display the coach names, sports , pay and date_of_app of all the coaches whosename ends with
“n”.

ANS: Select coachname, sports, pay, date_of_app from club where coachname like '%n';

OUTPUT:

7. To display the coach name, sports , age and pay of all the coaches whose pay is in

the range 2000-2500.

ANS: Select coachname, sports, age, pay from club where pay between 2000 and 2500;

OUTPUT:

8. To display the details about all the coaches who are above 30 in age and coach a

sport starting with the letter “s”.

ANS: Select * from club where age > 30 and sports like 's%';
OUTPUT:

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

ANS: Select sports, count(*) as num_coaches from club group by sports;

OUTPUT:

10. To display the average Pay given for a coach for every sports activity.

ANS: Select sports, avg(pay) as avg_pay from club group by sports;

OUTPUT:

11. To display the details about all the male coaches who joined after 15th February

ANS: Select * from club where sex = "m" and date_of_app > '2022-02-15';

Output:
12. To display the coach id , names and age of all the coaches who coach neither

Swimming nor Basketball

Ans: Select coach_id, coachname, age from club where sports not in ("swimming", "basketball");

Output:

13. To display the names of all the sports in the club which have more than 2 coaches.

Ans: Select sports from club group by sports having count(*) > 2;

Output:

14. To display the total salary given for male and female coaches in the club.

Ans: Select sex, sum(pay) as total_salary from club group by sex;

Output:
 MYSQL-3:

1. To show all the information about the patients of the cardiology department.

ANS: Select *from patients where department = 'cardiology';

OUTPUT:

2. To list the names of female patients who are either in the orthopaedic or surgery

department.

ANS: Select name from patients where sex = 'f' and (department = 'orthopaedic' or department =
'surgery');

OUTPUT:

3. To display various departments in the hospital.

ANS:

Select distinct department from patients;

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

ANS: Select department, count(*) as num_patients from patients group by department;

OUTPUT:

5. To display details of all the patients whose name’s second character is “a”.

ANS: Select *from patients where name like '_a%';

OUTPUT:

6. To display the details of all the patients who was admitted in the year 2019.

ANS: Select *from patients where year(dateofadm) = 2019;

OUTPUT:
7. To display the details about all the patients in the reverse alphabetical order of their names.

Ans: SELECT * FROM patients ORDER BY name DESC;

Output:

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

Ans: SELECT department, AVG(charges) AS avg_charges FROM patients GROUP BY department;

Output.

9. To display the patient detail whose age is missing.

Ans: SELECT * FROM patients WHERE age IS NULL;

Output:
10. To display the names of the patients who are charged in the range 300 and 400 (both inclusive).

Ans: SELECT name FROM patients WHERE charges BETWEEN 300 AND 400;

Output:

11. To display the number of patients who are aged above 30.

Ans: SELECT COUNT(*) AS num_patients FROM patients WHERE age > 30;

Output:

12. To display the names of the department and the number of patients in the department that
have more than one patient.

Ans:SELECT department, COUNT(*) AS num_patients FROM patients GROUP BY department HAVING

COUNT(*) > 1;

Output
13. To delete the record of the patient "Kush".

Ans:DELETE FROM patients WHERE name = 'Kush';

Output:

14. To decrease the charges by 5% of all the patients admitted to the ENT department.

Ans: UPDATE patients SET charges = charges * 0.95 WHERE department = 'ent';

Output:

15. To add another column DOCNAME of datatype varchar(20) into the table hospital.

Ans:ALTER TABLE patients ADD COLUMN docname VARCHAR(20);

Output:
MYSQL-4:

1. Query:
To display the natural join of the tables items and traders.
ANS: Select * from items natural join traders;
OUTPUT:

2. Query:
To display the number of items traded by every Trader.
ANS: Select tname, count(*) as num_items from items natural join
traders group by tname;
OUTPUT:
3. Query:
To display the Item name, company, trader name, and city of all the
items.
ANS: Select iname, company, tname, city from items natural join
traders;
OUTPUT:

4. Query:
To display the Item name and Trader name of all the items that are
either from Delhi or Mumbai.
ANS: Select iname, tname from items natural join traders where city in
('delhi', 'mumbai');
OUTPUT:

5. Query:
To display the minimum and maximum price of the item traded by
every trader.
ANS: Select tname, min(price) as min_price, max(price) as max_price
from items natural join traders group by tname;
OUTPUT:
6. Query:
To display the average price of an item traded by every trader.
ANS: Select tname, avg(price) as avg_price from items natural join
traders group by tname;
OUTPUT:

7. Query:
To display the Item name and trader name of all the items in the
alphabetical order of the item names.
ANS: Select iname, tname from items natural join traders order by
iname asc;
OUTPUT:

8. Query:
To display the names of the traders whose average price of an item is
not more than 20000.
ANS: Select tname from traders natural join items where price <
20000;
OUTPUT:
9. Query:
To display the details about all the items whose price is in the range
between 10000 and 30000.
ANS: Select * from items natural join traders where price between
10000 and 30000;
OUTPUT:

10. Query:
To display the total number of quantity of items available for every
trader.
ANS: Select tname, sum(qty) as total_qty from items natural join
traders group by tname;
OUTPUT:
MYSQL-5:

TABLE:DEPT

Q1. To display the department name, code, and number of workers in

every department.
ANS: Select department, dcode, count(wno) from workers natural join
dept group by dcode;
OUTPUT:

Query 2:
Q2. To display the details about all the workers whose Wno is either
1003, 1005, or 1007.
ANS: Select * from workers natural join dept where wno in (1003,
1005, 1007);
OUTPUT:

Query 3:
Q3. To display the names and date of joining of all the workers in
Kolkata.
ANS: Select name, doj from workers natural join dept where city like
"Kolkata";
OUTPUT:

Query 4:
Q4. To display the number of male and female workers.
ANS: Select count(gender) "no.of workers", gender from workers
group by gender;
OUTPUT:

Query 5:
Q5. To display Wno, name, gender, department in descending order of
Wno.
ANS: Select wno, name, gender, department from workers natural join
dept order by wno desc;
OUTPUT:
Query 6:
Q6. To display the names of all the departments where more than one
worker is available.
ANS: Select department from workers natural join dept group by
dcode having count(wno) > 1;
OUTPUT:

Query 7:
Q7. To display Wno, name, department of all the workers who are born
between “1987-01-01” and “1991-12-01”.
ANS: Select wno, name, department from workers natural join dept
where dob >= "1987-01-01" and dob <= "1991-12-01";
OUTPUT:

Query 8:
Q8. To display Wno, name, department, city of all the workers whose
name contains the letter “s” and who are not in Mumbai and Delhi.
ANS: Select wno, name, department, city from workers natural join
dept where name like "%s" and city not in ("Mumbai", "Delhi");
OUTPUT:

Query 9:
Q9. To count and display the number of male workers who have joined
after “1986-01-01”.
ANS: Select count(wno) from workers where doj >= "1986-01-01" and
gender like "male";
OUTPUT:
Query 10:
Q10. To display the details of all the male workers in the finance
department.
ANS: Select * from workers natural join dept where gender like "male"
and department like "finance";
OUTPUT: