INDIA INTERNATIONAL SCHOOL

SHARJAH

INFORMATICS PRACTICES RECORD

Submitted by

(Reg. No. )

Under the guidance of

Mrs. Janathul Firthouse M

2022-2023
 INDIA INTERNATIONAL SCHOOL
SHARJAH

Certificate
Department of Computer Science

This is to certify that of class 12 has

carried out the necessary Record Work under my supervision for the
academic year 2022-2023.

Seal Teacher in-charge

Janathul Firthouse M
(Computer Science Dept.)

Date:
Year: 2022-2023

Seal Date: (Examiner)

S.NO. TOPIC PAGE NO.
PYTHON PROGRAMS
1 Write a menu driven program to perform binary operations using
the following two data frames. – marks obtained by two students 08
in a class test:
2 Create a data frame df for examination results and write a menu
driven program to display row labels, column labels data types of 12
each column and the dimensions.
3 Write a program to iterate over a data frame containing names and
marks, which then calculates grades as per marks (as per
15
guidelines below) and adds them to the grade column.
4 Consider the given automobile data set for performing following
operations using Pandas data frames and CSV files. Write a menu 17
driven program
5 Write a menu driven program i) to count the number of rows and
columns of a dataframe. ii) to select the rows where the score is
missing ie. NaN.iii) to count the mean score for each student in the 21
dataframe. iv) to sort the dataframe first by ‘name’ in descending
order, then by ‘score’ in ascending order.
6 Write a menu driven program a) to select the ‘name’ and ‘score’
columns from a dataframe. b) to select ‘name’ and ‘score’ columns
in rows 1,3,5,7 from the dataframe. c) to select the rows where the
number of attempts in the examination is greater than 2 from 25
dataframe. Draw a line chart for the result. Customize the chart
according to the below-given instructions:
7 Write a menu driven program perform all aggregate and statistical
functions column wise and row wise. Draw a bar graph using the
columns Day and Temperature. Customize the bar according to the 29
below-given instructions.
8 Write a menu driven program to shows the attributes of a
series.(index, values, dtype, shape, nbytes, ndim, size, itemsize, 35
hasnans, empty)
 9 Write a menu driven program a) to select the ‘name’ and ‘score’
columns from a dataframe. b) to select ‘name’ and ‘score’ columns
in rows 1,3,5,6 from the dataframe. c) to select the rows where the 41
number of attempts in the examination is greater than 2 from
dataframe.
10 Write a menu driven program to create a series using a) list b)
range() and for loop. c) missing values(NaN) d) scalar or constant 43
values e) dictionary.
11 Write a menu driven program to create a dataframe Books using
lists and perform the following operations. i) to display books for
class XII. ii) to display the books whose price is more than 250.
iii) delete 3 rd and 5 th rows. iv) to add a column called 45
‘Publisher’ with the following
data[‘NCERT’,’BPB’,’Kips’,’Dhanpat Rai’,‘Oswal Book’]. v) to
add a new record as 3 rd record.
12 Consider the given class12.csv file for performing following
operations using Pandas dataframes and CSV files. Draw a line 49
chart for the result.
13 Write a menu driven program to create a dataframe named
Temperature df and perform the following operations. i)to
compute average Min Temp, MaxTemp, Rainfall for first 3 rows. 52
ii) to replace missing values with 0. iii) to replace missing values
with constant value. iv) to fills the missing values by copying the
value from above adjacent cell.
14 Write a program to plot the following data on a line chart and
customize the chart according to the below-given instructions:
a) Write a title for the chart “The Monthly Sales Report”
b) Write the appropriate titles of both the axes
55
c) Write code to display legends
d) Display red color for the line
e) Use the line style-dashed
f) Display diamond style markers on data points
 15 Write a program to create a bar plot of scores by group and
gender. Use multiple x values on the same chart for men and
women. Customize the bar according to the below-given
instructions.
a) Write a suitable title.
b) Write the appropriate titles of both the axes
56
c) Write code to display legends
d) Display bars in different colours.
Sample data:
Means(men) =(22,30,35,35,26)
Means(women) =(25,32,30,35,29)
MYSQL
16 Create the following table STUDENT in SQL. Insert all records.
Write SQL statements for the following PRACTICALs. 58
17 Create the following table STORE in SQL. Insert all records.Write
SQL statements for the following PRACTICALs. 64
18 Create the following table SALESMAN in SQL. Insert all records.
Write SQL statements for the following PRACTICALs. 70
19 Create the following table Employee in SQL. Insert all records.
Write SQL statements for the following PRACTICALs. 76
20 Create the following table HOSPITAL in SQL. Insert all records.
Write SQL statements for the following question 82
21 Create the following table LOAN in SQL. Insert all records. Write
SQL statements for the following PRACTICALs. 88
PYTHON
Practical No: 1
Write a menu driven program to perform binary operations using the following
two dataframes. – marks obtained by two students in a class test:
Student1 = {‘Unit Test-1’:[50,46,35,49,43], ‘Unit Test-
2’:[34,47,41,20,39]}
Student2 = {‘Unit Test-1’:[33,37,48,50,36], ‘Unit Test-
2’:[49,41,38,31,40]}
Program
import pandas as pd
Student1 = {'Unit Test-1':[50,46,35,49,43], 'Unit Test-2':[34,47,41,20,39]}
Student2 = {'Unit Test-1':[33,37,48,50,36], 'Unit Test-2':[49,41,38,31,40]}
df1=pd.DataFrame(Student1)
df2=pd.DataFrame(Student2)
print(df1,'\n',df2)
choice='yes'
while choice=='yes':
print("Press 1 for addition")
print("Press 2 for subtraction")
print("press 3 for multiplication")
print("press 4 for division")
option=int(input("enter the choice"))
if option==1:
print("Addition using + operator")
result=df1+df2
print(result)
print("Addition using add() method")
print(df1.add(df2))
elif option==2:
print("Subtraction using - operator")

Page 8 of 93
 print(df1-df2)
print("Subtraction using sub() method")
print(df1.sub(df2))
elif option==3:
print("Multiplication using * operator")
print(df1*df2)
print("Multiplication using mul() method")
print(df1.mul(df2))
elif option==4:
print("Division using / operator")
print(df1/df2)
print("Division using div() method")
print(df1.div(df2))
else:
print("invalid option")
choice=input("do u want to continue or not, if u want to continue press yes or
press no")

Output

Page 9 of 93
Page 10 of 93
Page 11 of 93
Practical No: 2
Create a data frame df for examination results and write a menu driven program
to display row labels, column labels data types of each column and the
dimensions.
result={‘Amit’:[76,78,75,66,68],’Shialesh’:[78,56,77,49,55],’Rani’:[90,9
1,93,97,99],’Madan’:[55,48,59,60,66],’Radhika’:[78,79,85,88,86]}
Program
import pandas as pd
result={'Amit':[76,78,75,66,68],
'Shialesh':[78,56,77,49,55],
'Rani':[90,91,93,97,99],
'Madan':[55,48,59,60,66],
'Radhika':[78,79,85,88,86]}
df=pd.DataFrame(result)
print("Actual Dataframe \n",df)
choice='yes'
while choice=='yes':
print("Press 1 for row labels")
print("Press 2 for column labels")
print("press 3 for data types of each column")
print("press 4 for dimension")
option=int(input("enter the choice"))
if option==1:
print("Printing row labels in a list:")
print("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~")
idx=df.index
l=list(idx)
print(l)

Page 12 of 93
 elif option==2:
print("Printing column labels in a list:")
print("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~")
for col in df.columns:
print(col,end=" ")
elif option==3:
print("\n Printing Data Types of each column")
print("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~")
print(df.dtypes)
elif option==4:
print("Printing dimensions of Data Frame")
print("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~")
print(df.ndim)
else:
print("invalid option")
choice=input("do u want to continue or not, if u want to continue press yes or
press no")
Output

Page 13 of 93
Page 14 of 93
Practical No: 3
Write a program to iterate over a dataframe containing names and marks, which
then calculates grades as per marks(as per guidelines below) and adds them to
the grade column.
Marks>=90 grade A+
Marks 70-90 grade A
Marks 60-70 grade B
Marks 50-60 grade C
Marks 40-50 grade D
Marks <40 grade F
Program
import pandas as pd
import numpy as np
Name=pd.Series(['Jitan','Manu','Ritika','Ajay'])
Marks=pd.Series([76.0,56.0,91.0,67.0])
Stud={'Name':Name,'Marks':Marks}
df1=pd.DataFrame(Stud,columns=['Name','Marks'])
df1['Grade']=np.NaN
print("Initial values in dataframe")
print(df1)
for (col,colSeries) in df1.iteritems():
length=len(colSeries)
if col=='Marks':
lstMrks=[]
for row in range(length):
mrks=colSeries[row]
if mrks>=90:
lstMrks.append('A+')
elif mrks>=70 and mrks<90:
lstMrks.append('A')

Page 15 of 93
 elif mrks>=60 and mrks<70:
lstMrks.append('B')
elif mrks>=50 and mrks<60:
lstMrks.append('C')
elif mrks>=40 and mrks1<50:
lstMrks.append('D')
else:
lstMrks.append('F')
df1['Grade']=lstMrks
print("\nDataframe after calculating grades")
print(df1)

Output

Page 16 of 93
Practical No: 4
Consider the given automobile data set for performing following operations
using Pandas dataframes and CSV files. Write a menu driven program
a) To print the most expensive car’s company name and price.
b) To sort all cars on the basis of the ascending order of Price column.
c) To select company and mileage where the mileage of car is greater
than 20.
Draw a bar graph for the result. Customize the bar according to the
below-given instructions.
a) Write a suitable title.
b) Write the appropriate titles of both the axes
c) Write code to display legends
d) Display bars in different width and colours.

The automobile data set has different characteristics of an automobile

such as body-style, wheel-base, engine-type, price, mileage, horsepower
etc.

Program
import pandas as pd
import matplotlib.pyplot as plt
df=pd.read_csv('automobiledata.csv')
print(df)
choice='yes'
while choice=='yes':
print("Press 1 to print the most expensive car’s company name and price.")

Page 17 of 93
 print("Press 2 to sort all cars on the basis of the ascending order of Price
column.")
print("press 3 to select company and mileage where the mileage of car is
greater than 20.")
option=int(input("enter the choice"))
if option==1:
print(df[['company','price']][df.price==df['price'].max()])
elif option==2:
print(df.sort_values(by=['price']))
x=df['company']
y=df['price']
plt.title("Automobile_data")
plt.xlabel("company")
plt.ylabel("price")
plt.bar(x,y,width=0.3,color='orange',label='automobile')
plt.legend()
plt.show()
elif option==3:
print(df[['company','mileage']][df['mileage']>20])
x=df['company']
y=df['mileage']
plt.title("Automobile_data")
plt.xlabel("company")
plt.ylabel("mileage")
plt.bar(x,y,width=0.3,color='yellow',label='automobile')
plt.legend()
plt.show()
else:

Page 18 of 93
 print("invalid option")
choice=input("do u want to continue or not, if u want to continue press yes or
press no")

Output

Page 19 of 93
Page 20 of 93
Practical No: 5
Write a menu driven program i) to count the number of rows and columns of a
dataframe. ii) to select the rows where the score is missing ie. NaN. iii) to count
the mean score for each student in the dataframe. iv) to sort the dataframe first
by ‘name’ in descending order, then by ‘score’ in ascending order.
Sample data:
exam_data={'name':['Anastasia','Dima','Katherine','James','Emily','Micha
el','Matthew','Laura','Kevin','Jonas'],
'score':[12,9,16.5,np.nan,9,20,14.5,np.nan,8,19],'attempts':[1,3,2,3,2,3,1,1,
2,1],'qualify':['yes','no','yes','no','no','yes','yes','no','no','yes']}
labels=[‘a’,’b’,’c’,’d’,’e’,’f’,’g’,’h’,’i’,’j’]
Draw a bar graph using the columns name and score. Customize the
bar according to the below-given instructions.
a) Write a suitable title.
b) Write the appropriate titles of both the axes
c) Write code to display legends
d) Display bars in different colours.

Program
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
exam_data={'name':['Anastasia','Dima','Katherine','James','Emily','Micha
el','Matthew','Laura','Kevin','Jonas'],'score':[12,9,16.5,np.nan,9,20,14.5,np
.nan,8,19],

'attempts':[1,3,2,3,2,3,1,1,2,1],'qualify':['yes','no','yes','no','no','yes','yes','n
o','no','yes']}
labels=['a','b','c','d','e','f','g','h','i','j']
df = pd.DataFrame(exam_data,index=labels)
print(df)
choice="yes"
while choice =="yes":
print("Enter 1 to find to the count of rows and columns of a dataframe
Exam Data")
print("Enter 2 to select the rows where the score is missing (NaN) in
dataframe Exam Data")
print("Enter 3 to count the mean score for each student in dataframe
Exam Data")

Page 21 of 93
 print("Enter 4 to sort the dataframe first by ‘name’ in descending order,
then by ‘score’ in ascending order in dataframe Exam Data")
option=int(input("Enter the Choice"))
if option==1 :
print("[Output Is :]")
columns = len(df.axes[1])
row = len(df.axes[0])
print(" ")
print("Number of rows in dataframe Exam Data
is","\n",row,"\n","Number of columns in dataframe Exam Data
is","\n",columns)
elif option==2 :
print("Output Is :")
print(" ")
print("To select the rows where the score is missing in dataframe
Exam Data is")
print(df[df['score'].isnull()])
elif option==3 :
print("Output Is :")
print(" ")
print("To count the mean score for each student in dataframe Exam
Data is")
print(df['score'].mean())
elif option==4 :
print("Output Is :")
print(" ")
print("To sort the dataframe first by name in descending order, then
by score in ascending order")
print(df.sort_values(by=['name', 'score'], ascending=[False, True]))
print(" ")
df.plot.bar(x='name',y='score',color=['orange'],label='Score')
plt.title('Bar graph using the columns name and score')
plt.xlabel('Names')
plt.ylabel('Score')
plt.legend()
plt.show()
else:
print("------ ","\n","Oops! You Have Entered Incorrect
Option","\n"," ----")
choice=input("If you want to continue Enter yes or type no")

Page 22 of 93
Output

Page 23 of 93
Page 24 of 93
Practical No: 6
Write a menu driven program a) to select the ‘name’ and ‘score’ columns from
a dataframe. b) to select ‘name’ and ‘score’ columns in rows 1,3,5,7 from the
dataframe. c) to select the rows where the number of attempts in the
examination is greater than 2 from dataframe. Draw a line chart for the
result. Customize the chart according to the below-given instructions:
a) Write a title for the chart.
b) Write the appropriate titles of both the axes
c) Display green color for the line
d) Use the line style-dotted
e) Display markers on data points.
Sample data:
exam_data={'name':['Anastasia','Dima','Katherine','James','Emily','Michael','Mat
thew','Laura','Kevin','Jonas'],'score':[12,9,16.5,np.nan,9,20,14.5,np.nan,8,19],'att
empts':[1,3,2,3,2,3,1,1,2,1],'qualify':['yes','no','yes','no','no','yes','yes','no','no','yes
']}
labels=[‘a’,’b’,’c’,’d’,’e’,’f’,’g’,’h’,’i’,’j’]

Program
import pandas as pd
import matplotlib.pyplot as plt
import numpy as np
exam_data={'name':['Anastasia','Dima','Katherine','James','Emily','Michael','Mat
thew','Laura','Kevin','Jonas'],
'score':[12,9,16.5,np.nan,9,20,14.5,np.nan,8,19],
'attempts':[1,3,2,3,2,3,1,1,2,1],
'qualify':['yes','no','yes','no','no','yes','yes','no','no','yes']}
df=pd.DataFrame(exam_data,index=['a','b','c','d','e','f','g','h','i','j'])
print(df)
choice='yes'
while choice=='yes':

Page 25 of 93
 print("Press 1 to select the name and score columns from a dataframe")
print("Press 2 to select the name and score columns in rows 1,3,5,7 from the
dataframe")
print("press 3 to select the rows where the number of attempts in the
examination is greater than 2 from dataframe")
option=int(input("enter the choice"))
if option==1:
print(df[['name','score']])
x=df['name']
y=df['score']
plt.title('exam details')
plt.xlabel('Name')
plt.ylabel('Score')
plt.plot(x,y,':',color='g',label='score details',marker="*")
plt.show()
elif option==2:
df1=df.iloc[1:8:2,0:2]
print(df1)
x=df1['name']
y=df1['score']
plt.title('Name & Score of selected records')
plt.xlabel('Name')
plt.ylabel('Score')
plt.plot(x,y,':',color='g',label='score details',marker="*")
plt.show()
elif option==3:
df1=df[df['attempts']>2]
print(df1)

Page 26 of 93
 x=df1['name']
y=df1['attempts']
plt.title('Name & Attempts')
plt.xlabel('Name')
plt.ylabel('Attempts')
plt.plot(x,y,':',color='g',label='score details',marker="*")
plt.show()
else:
print("invalid option")
choice=input("do u want to continue or not, if u want to continue press yes or
press no")
Output

Page 27 of 93
Page 28 of 93
Practical No:7
Given the data set for weather forecast:
Day:[‘01/01/2018’,’01/02/2018’,’01/03/2018’,’01/04/2018’,’01/05/2018’,
’01/06/2018’]
Temperature:[42,41,43,42,41,40]
Windspeed:[6,7,2,4,7,2]
Event:[‘Sunny’,’Rain’,’Sunny’,’Sunny’,’Rain’,’Sunny’]
Write a menu driven program perform all aggregate and statistical
functions column wise and row wise. Draw a bar graph using the
columns Day and Temperature. Customize the bar according to the
below-given instructions.
a) Write a suitable title.
b) Write the appropriate titles of both the axes
c) Write code to display legends
d) Display bars in different colours.
Program
import pandas as pd
import matplotlib.pyplot as plt
data={'day':['01/01/2018','01/02/2018','01/03/2018','01/04/2018','01/05/2018','01
/06/2018'],
'temperature':[42,41,43,42,41,40],'windspeed':[6,7,2,4,7,2],'event':['Sunny','Rain'
,'Sunny','Sunny','Rain','Sunny']}
df=pd.DataFrame(data)
print(df)
choice='yes'
while choice=='yes':
print('choose a for max()')
print('choose b for min()')
print('choose c for sum()')
print('choose d for count()')
print('choose e for mean()')
print('choose f for median()')

Page 29 of 93
print('choose g for mode()')
print('choose h for bar graph')
operation=input('enter the desired operation')
if operation=='a':
print('row wise')
print(df.max())
print('column wise')
print(df.max(axis=1))
elif operation=='b':
print('row wise')
print(df.min())
print('column wise')
print(df.min(axis=1))
elif operation=='c':
print('row wise')
print(df.sum())
print('column wise')
print(df.sum(axis=1))
elif operation=='d':
print('row wise')
print(df.count())
print('column wise')
print(df.count(axis=1))
elif operation=='e':
print('row wise')
print(df.mean())
print('column wise')

Page 30 of 93
 print(df.mean(axis=1))
elif operation=='f':
print('row wise')
print(df.median())
print('column wise')
print(df.median(axis=1))
elif operation=='g':
print('row wise')
print(df.mode())
print('column wise')
print(df.mode(axis=1))
elif operation=='h':
print('bar graph')
x=df['day']
y=df['temperature']
plt.bar(x,y,color=['y','r','g','b','c','m'],label='weather')
plt.title('weather')
plt.xlabel('day')
plt.ylabel('temperature')
plt.legend()
plt.show()

else:
print('operation chosen is invalid')
choice=input('choose yes to continue')

Output

Page 31 of 93
Page 32 of 93
Page 33 of 93
Page 34 of 93
Practical No: 8
Write a menu driven program to shows the attributes of a series.(index, values,
dtype, shape, nbytes, ndim, size, itemsize, hasnans, empty)
Sample data:
Series 1
A 10
B 20
C 30
D 40
E 50
F 60

Program
import pandas as pd
data=[10,20,30,40,50,60]
s1=pd.Series(data,index=['a','b','c','d','e','f'])
print(s1)
choice='yes'
while choice=='yes':
print('choose a for index')
print('choose b for values')
print('choose c for dtype')
print('choose d for shape')
print('choose e for nbytes')
print('choose f for ndim')
print('choose g for size')
print('choose h for itemsize')
print('choose i for hasnans')
print('choose j for empty')
attribute=input('enter the desired attribute')

Page 35 of 93
if attribute=='a':
print('index')
print(s1.index)
elif attribute=='b':
print('values')
print(s1.values)
elif attribute=='c':
print('dtype')
print(s1.dtype)
elif attribute=='d':
print('shape')
print(s1.shape)
elif attribute=='e':
print('nbytes')
print(s1.nbytes)
elif attribute=='f':
print('ndim')
print(s1.ndim)
elif attribute=='g':
print('size')
print(s1.size)
elif attribute=='h':
print('itemsize')
print(s1.itemsize)
elif attribute=='i':
print('hasnans')
print(s1.empty)

Page 36 of 93
 elif attribute=='j':
print('empty')
print(s1.empty)
else:
print('attribute chosen is invalid')
choice=input('choose yes to continue')

Output
import pandas as pd
data=[10,20,30,40,50,60]
s1=pd.Series(data,index=['a','b','c','d','e','f'])
print(s1)
choice='yes'
while choice=='yes':
print('choose a for index')
print('choose b for values')
print('choose c for dtype')
print('choose d for shape')
print('choose e for nbytes')
print('choose f for ndim')
print('choose g for size')
print('choose h for hasnans')
print('choose i for empty')
attribute=input('enter the desired attribute')
if attribute=='a':
print('index')
print(s1.index)

Page 37 of 93
elif attribute=='b':
print('values')
print(s1.values)
elif attribute=='c':
print('dtype')
print(s1.dtype)
elif attribute=='d':
print('shape')
print(s1.shape)
elif attribute=='e':
print('nbytes')
print(s1.nbytes)
elif attribute=='f':
print('ndim')
print(s1.ndim)
elif attribute=='g':
print('size')
print(s1.size)
elif attribute=='h':
print('hasnans')
print(s1.empty)
elif attribute=='i':
print('empty')
print(s1.empty)
else:
print('attribute chosen is invalid')
choice=input('choose yes to continue')

Page 38 of 93
Output

Page 39 of 93
Page 40 of 93
Practical No: 9
Write a menu driven program to create the dataframe and perform head() and
tail() function on it.
Sample data:
Classes Country Quarter Tutor
28 USA 1 Tahira
36 UK 1 Jacob
41 Japan 2 Venkat
32 USA 2 Tahira
40 USA 3 Venkat
40 UK 3 Tahira

Program
import pandas as pd
data={'classes':[28,36,41,32,40,40],'country':['usa','uk','japan','usa','usa','uk'],'qua
rter':[1,1,2,2,3,3],'tutor':['tahira','jacob','venkat','tahira','venkat','tahira']}
df=pd.DataFrame(data)
print(df)
choice='yes'
while choice=='yes':
print('choose a for head()')
print('choose b for tail()')
function=input('enter the desired function')
if function=='a':
print('head function without argument')
print(df.head())
print('First two rows of the dataframe')
print(df.head(2))

elif function=='b':
print('tail function without argument')

Page 41 of 93
 print(df.tail())
print('last two rows of the dataframe')
print(df.tail(2))
else:
print('function chosen is invalid')
choice=input('choose yes to continue')
Output

Page 42 of 93
Practical No:10
Write a menu driven program to create a series using a) list b) range() and for
loop. c) missing values(NaN) d) scalar or constant values e) dictionary.
Program
import pandas as pd
import numpy as np
choice="yes"
while choice =="yes":
print("press 1 to create a series using list ")
print("press 2 to create a series using using range() and for loop")
print("press 3 to create a series using missing values(NaN) ")
print("press 4 to create a series using scalar or constant values ")
print("press 5 to create a series using dictionary")
option=int(input("Enter the Choice"))
if option==1:
series=pd.Series([10,20,30,40,50])
print(series)
elif option==2:
series=pd.Series(range(1,15,3),index=[x for x in 'abcde'])
print(series)
elif option==3:
series=pd.Series([7.5,5.4,np.NaN,-34.5])
print(series)
elif option==4:
series=pd.Series(55,index=['a','b','c','d','e'])
print(series)
elif option==5:
series=pd.Series({'Jan':31,'Feb':28,'Mar':31,'Apr':30})

Page 43 of 93
 print(series)
else:
print(" ---------------------------------------","\n","Oops! You Have Entered
Incorrect Option","\n","--------------------------------------- ")
choice=input("If you want to continue Enter yes or type no")
Output

Page 44 of 93
Practical No: 11
Write a menu driven program to create a dataframe Books using lists and
perform the following operations. i) to display books for class XII. ii) to display
the books whose price is more than 250. iii) delete 3rd and 5th rows. iv) to add a
column called ‘Publisher’ with the following
data[‘NCERT’,’BPB’,’Kips’,’Dhanpat Rai’,‘Oswal Book’]. v) to add a new
record as 3rd record.
Draw a line chart for the result using the columns Booktitle and
Price. Customize the chart according to the below-given instructions:
a) Write a title for the chart.
b) Write the appropriate titles of both the axes
c) Display green color for the line
d) Use the line style-dash-dot
e) Display markers on data points.

Sample data:
BookID Subject BookTitle Class Price
B0001 Computer NCERT Computer XII 270
Science Science
B0002 Computer Move fast with XII 340
Science computer science
B0003 Computer Sample Papers X 120
Applications
B0004 Informatics NCERT Computer XII 270
Practices Science
B0005 Artificial Artificial Intelligence IX 340
Intelligence
B0006 Informatics CBSE Questions Bank XII 299
Practices

Program
import pandas as pd
import matplotlib.pyplot as plt
Dict1={'BookID':['B0001','B0002','B0003','B0004','B0005','B0006'],
'Subject':['Computer Science','Computer Science','Computer
Application','Informative Practise','Artificial Intelligence','Informative Practise'],

Page 45 of 93
 'BookTitle':['NCERT Computer Science','Move fast with computer
science','Sample Papers','Ncert Computer Science','Artificial Intelligence','Cbse
Question Bank'],
'Class':[12,12,10,12,11,12],'Price':[270,340,120,270,340,299]}
df=pd.DataFrame(Dict1,index=[1,2,3,4,5,6])
print(df)
choice='yes'
while choice=='yes':
print('''
1. To display books for grade 12
2. To display books whos price is greater than 250
3. Delete 3rd and 5th row
4. Add column Publisher
5. New record on 3rd position
''')
choice=int(input('Enter the correct option'))
if choice==1:
print('To display books for grade 12')
print(df[df['Class']==12])
elif choice==2:
print('To display books whose price is greater than 250')
print(df[df['Price']>250])
x=df['BookTitle']
y=df['Price']
plt.title("Book Details")
plt.xlabel("Book Name")
plt.ylabel("Price")
plt.plot(x,y,'-.',color='g',label='score details',marker="*")

Page 46 of 93
 plt.legend()
plt.show()
elif choice==3:
print('Delete 3rd and 5th row')
print(df.drop([3,5]))
elif choice==4:
print('Add column named Publisher')
df['Publisher']=['Ncert','BPB','Kips','Dhanpat Rai','Oswal Book','Ncert']
print(df)
elif choice==5:
print('To add new record as 3rd')
df.loc[3]=['B0009','Computer Application','NCERT Computer
Science',12,560]
print(df)
else:
print('Wrong Command')
choice=input("If you want to continue Enter yes or type no")
Output

Page 47 of 93
Page 48 of 93
Practical No:12
Consider the given class12.csv file for performing following operations using
Pandas dataframes and CSV files. Draw a line chart for the result.
i) To read the CSV file into a dataframe df.
ii) To find the total marks (Total_marks) for each student and add it to
the newly-created dataframe.
iii) To calculate the average obtained by each student under a new
column “average” in the dataframe.
RollNO Name Accounts Maths BSt IP Eco
10 Ritu Jain 88 67 87 97 56
11 Mridul Mehta 67 78 77 87 90
12 Divij 87 89 78 82 92
13 Yashvi Verma 67 82.3 76.5 98.2 78.6
14 Deepak Virmani 56.7 76.5 88 78 67
15 Jatin Malik 76 66 77 87.5 67.5

Program
import pandas as pd
import matplotlib.pyplot as plt
choice='yes'
while choice=='yes':
print("To read the CSV file into a dataframe df")
print("To find the total marks")
print("To calculate the percentage")
option=int(input("enter the choice"))
if option==1:
df=pd.read_csv('mark.csv')
print(df)
elif option==2:
df=pd.read_csv('mark.csv')
df['Total_Marks']=df['Accounts']+df['Maths']+df['Bst']+df['IP']+df['Eco']
print(df)

Page 49 of 93
 elif option==3:
df=pd.read_csv('mark.csv')
df['Average']=df['Accounts']+df['Maths']+df['Bst']+df['IP']+df['Eco']/5
print(df)
x=df['Name']
y=df['Average']
plt.title("Mark List")
plt.xlabel("Student Name")
plt.ylabel("Average")
plt.plot(x,y,'-.',color='b',label='Marklist',marker="*")
plt.legend()
plt.show()
else:
print('operation chosen is invalid')
choice=input('do u want to continue or not, if u want to continue press yes or
press no')
Output

Page 50 of 93
Page 51 of 93
Practical No: 13
Write a menu driven program to create a dataframe named Temperature df and
perform the following operations. i)to compute average Min Temp, MaxTemp,
Rainfall for first 3 rows. ii) to replace missing values with 0. iii) to replace
missing values with constant value. iv) to fills the missing values by copying the
value from above adjacent cell.
City MaxTemp MinTemp Rainfall
Delhi 40 32 36.5
Bengaluru 36 25 36.2
Chennai 35 27 40.8
Mumbai NaN 27 NaN
NaN 39 NaN 41.8

Program
import pandas as pd
import numpy as np
Temp={'City':['Delhi','Bengaluru','Chennai','Mumbai',np.nan],'MaxTemp':[40,3
6,35,np.nan,39],'MinTemp':[32,25,27,27,np.nan],'Rainfall':[36.5,36.2,40.8,np.na
n,41.8]}
Temperature_df=pd.DataFrame(Temp)
print(Temperature_df)
choice="yes"
while choice =="yes":
print("Enter 1 to compute average MaxTemp, Rainfall for first 3 rows ")
print("Enter 2 to replace missing values with 0 ")
print("Enter 3 to replace missing values with constant value ")
print("Enter 4 to fills the missing values by copying the value from above
adjacent cell ")
option=int(input("Enter the Choice"))
if option==1 :
print("[Output Is :]")
print(" ")

Page 52 of 93
 avg = Temperature_df.loc[:3, 'MaxTemp':'Rainfall'].mean( )
print(avg)
print(" ")
elif option==2 :
print("[Output Is :]")
print(" ")
nAn = Temperature_df.fillna(0)
print(nAn)
print(" ")
elif option==3 :
print("[Output Is :]")
print(" ")
con = int(input("Enter the Constant Value"))
con_fill = Temperature_df.fillna(con)
print(con_fill)
print(" ")
elif option==4 :
print("[Output Is :]")
print(" ")
adj_fill = Temperature_df.fillna(method = 'ffill')
print(adj_fill)
print(" ")
else:
print(" ---------------------------------------","\n","Oops! You Have Entered
Incorrect Option","\n","--------------------------------------- ")
choice=input("If you want to continue Enter yes or type no")

Page 53 of 93
Output

Page 54 of 93
Practical No: 14
Write a program to plot the following data on a line chart and customize the
chart according to the below-given instructions:
a) Write a title for the chart “The Monthly Sales Report”
b) Write the appropriate titles of both the axes
c) Write code to display legends
d) Display red color for the line
e) Use the line style-dashed
f) Display diamond style markers on data points
Month January February March April May
Sales 510 350 475 580 600

Program
import matplotlib.pyplot as plt
month=['january','february','march','april','may']
sales=[510,350,475,580,600]
plt.plot(month,sales,'--',color='red',label='Line-1',marker="*")
plt.xlabel('month')
plt.ylabel('sales')
plt.title('The Monthly Sales Report')
plt.legend()
plt.show()

Output

Page 55 of 93
Practical No: 15
Write a program to create a bar plot of scores by group and gender. Use
multiple x values on the same chart for men and women. Customize the bar
according to the below-given instructions.
a) Write a suitable title.
b) Write the appropriate titles of both the axes
c) Write code to display legends
d) Display bars in different colours.
Sample data:
Means(men) =(22,30,35,35,26)
Means(women) =(25,32,30,35,29)
Program
import numpy as np

import matplotlib pyplot as plt

n_groups=5
men_means=(22,30,35,35,26)
women_means=(25,32,30,35,29)
y_pos=np.arange(len(men_means))
plt.bar(2*y_pos,women_means,color='g',label="women's score")
plt.bar(2*y_pos+1,men_means,color='b',label="Men's score")
plt.xlabel('person')
plt.ylabel('scores')
plt.title('score by person')
plt.legend()
plt.show()

Output

Page 56 of 93
MySQL
Q1) Create the following table STUDENT in SQL. Insert all records.
Write SQL statements for the following questions.

Stud_i Name Marks Gender DOB

d
1 Raj Kumar 93.5 M 17-07-2000
2 Deep Singh 98.3 M 22-03-2004
3 Ankit Sharma NULL M 15-09-2003
4 Radhika 78.8 F 25-10-2001
Gupta
5 Payal Goel 80.0 F 19-05-2002

Page 58 of 93
1) Write SQL commands to get the details of the students with marks more
Than 80.

SOURCE CODE:
SELECT * from Student where Marks >80 ;
OUTPUT:

2) Write SQL commands To find The minimum and maximum marks of

female students.

SOURCE CODE:
SELECT max(marks),min(marks) from Student where gender = ' F ' ;
OUTPUT:

Page 59 of 93
3) Write SQL query to order The Table in descending order of The marks.

SOURCE CODE:
SELECT * from Student order by Marks desc ;
OUTPUT:

4) Write SQL query to display The marks without decimal places, displayThe
reminder after diving marks by 3.

SOURCE CODE:
SELECT ROUND (marks,0), MOD(marks,3) from Student ;
OUTPUT:

Page 60 of 93
5) Write SQL query to display names into small letters and display first 3letters
of name.

SOURCE CODE:
SELECT LCASE(name), LEFT(name,3) from Student ;
OUTPUT:

6) Write SQL commands To display day name and year from Date of
birth.

SOURCE CODE:
SELECT DAYNAME (DOB),year(DOB) from Student ;
OUTPUT:

Page 61 of 93
7) Write SQL query to display names into capital letters and display last3
letters of name.

SOURCE CODE:
SELECT UPPER(name), RIGHT(name,3) from Student ;
OUTPUT:

8) Write SQL commands To increase The marks of male students by 5%.

SOURCE CODE:
UPDATE Student set Marks= Marks + (Marks * 0.05) where Gender= 'M' ;
OUTPUT:

Page 62 of 93
9) Write SQL commands To add one more column sTream in Table
Student.

SOURCE CODE:
ALTER Table Student add column sTream varchar(35) ;
OUTPUT:

10) Write SQL commands To remove The details of students Those who
didn’T write The exam.

SOURCE CODE :
DELETE from STUDENT where Marks is null;
OUTPUT:

************************************************

Page 63 of 93
 Q2) Create the following table STORE in SQL. Insert all records.
Write SQL statements for the following questions.

Storeid Name Location City NoOfEmp DateOpen SalesAmt

S101 Planet Fashion Bandra Mumbai 7 2015-10-16 40000

S102 Vogue Karol Bagh Delhi 8 2015-07-14 120000

S103 Trends Powai Mumbai 10 2015-06-24 30000

S104 Superfashion Thane Mumbai 11 2015-02-06 45000

S105 Annabelle South Extn. Delhi 8 2015-04-09 60000

S106 Rage Defence Delhi 5 2015-03-01 20000

Colony

Page 64 of 93
1) To display names of sTores along wiTh SalesAmount of ThoseThaT
have ‘fashion’ anywhere in Their sTore names.

SOURCE CODE:
SELECT Name ,SalesAmt from STORE where Name like '%fashion%' ;
OUTPUT:

2) To display name of The ciTies and The ToTal amount of sales on The
basis of ciTy.

SOURCE CODE:
SELECT Name ,SalesAmt from STORE group by CiTy;
OUTPUT:

Page 65 of 93
3) To display daTe of opening of The oldesT sTore.

SOURCE CODE:
SELECT MIN (DateOpen) from STORE ;
OUTPUT:

4) To display The ciTy and The number of sTores locaTed in ThaT ciTy, onlyif
number of sTores is more Than 2.

SOURCE CODE:
SELECT CiTy,CounT(*) from STORE group by ciTy having counT(*) > 2 ;
OUTPUT:

5) To find The average of sales amount.

SOURCE CODE:
SELECT AVG (SalesAmt) from STORE;
OUTPUT:

Page 66 of 93
6) Write a SQL query to display The square of number of employees.

SOURCE CODE:
SELECT POW(NoOfEmp,2) from STORE;
OUTPUT:

7) Write a SQL query to display names into capital letters and display last 4
letters of name.

SOURCE CODE:
SELECT UCASE(Name),Right(Name,4) from STORE;
OUTPUT:

Page 67 of 93
8) Write SQL commands To display month name and month of
DateOpen.

SOURCE CODE:
SELECT monthname(DateOpen), month(DateOpen) from STORE;
OUTPUT:

9) Write SQL query to display name, location, sales amount and numberof
employees in ascending order of sales amount.

SOURCE CODE:
SELECT Name, Location, SalesAmt, NoOfEmp from STORE order bySalesAmt ;
OUTPUT:

Page 68 of 93
10) Write SQL commands To decrease The sales amount of Trends by 10%.

SOURCE CODE:
UPDATE STORE set SalesAmt = SalesAmt – (SalesAmt* 0.1) where
Name='Trends';
OUTPUT:

************************************************

Page 69 of 93
Q3) Create the following table SALESMAN in SQL. Insert all records.
Write SQL statements for the following questions.

Sno Sname Salary Bonus DOJ

A01 Beena Mehta 30000 45.23 29-102019

A02 K.L.Sahay 50000 25.34 13-032018

B03 Nisha Thakkar 30000 35.00 18-032017

B04 Leela Yadhav 80000 NULL 31-122018

C05 Gautam Gola 20000 NULL 23-011989

C06 Trapti Garg 70000 12.37 15-061987

C07 Neena Sharma 50000 27.89 18-031999

Page 70 of 93
1) Display salesman name and bonus after rounding off To zero decimal places.

SOURCE CODE:
SELECT Sname, ROUND(Bonus,0) from SALESMAN;
OUTPUT:

2) Display the position of occurrence of the string “Ta” in salesman names.

SOURCE CODE:
SELECT INSTR(Sname, ' Ta ') from SALESMAN;
OUTPUT:

Page 71 of 93
3) Display the four characters from salesman name starting from second
character and last five characters of salesman.

SOURCE CODE:
SELECT MID(Sname,2,4),Right(Sname,5) from SALESMAN;
OUTPUT

4) Display the month name and day for the date of join of salesman.

SOURCE CODE:
SELECT MONTHNAME (DOJ),DAYNAME (DOJ) from SALESMAN;
OUTPUT:

Page 72 of 93
5) Display salary wise bonus of salesman those who have bonus more than 25.

SOURCE CODE:
SELECT Bonus from SALESMAN group by SALARY having BONUS > 25 ;
OUTPUT

6) To display the sname and salary in the salesman table in descending orderof
salary.

SOURCE CODE:
SELECT Sname ,Salary FROM SALESMAN order by Salary DESC;
OUTPUT:

Page 73 of 93
7) Write SQL commands to remove the details of sales man those who have bonus.

SOURCE CODE:
DELETE FROM Salesman WHERE Bonus is not null;
OUTPUT:

8) Write SQL commands to display unique salary of salesman who joined after 2017.

SOURCE CODE:
SELECT DISTINCT Salary FROM Salesman WHERE YEAR (DOJ) > 2017;
OUTPUT:

Page 74 of 93
9) To display the total of minimum and maximum salary.

SOURCE CODE:
SELECT MIN (SALARY) + MAX (SALARY) from Salesman;
OUTPUT:

10) Write SQL commands to decrease the bonus of salesman who

got salary more Than 50000.

SOURCE CODE:
UPDATE SALESMAN set BONUS=BONUS-10 WHERE
SALARY>50000;
OUTPUT:

************************************************

Page 75 of 93
 Q4) Create the following table Employee in SQL. Insert all records.
Write SQL statements for the following questions.

ID NAME AGE ADDRESS SALARY PHONE

1 Siddharth 25 A-4, Ashok Vihar, Delhi 62000 98110766656
2 Chavi 23 B-21, Model Town, Mumbai 71000 99113423989
3 Karan 26 KC-24, North Avenue, Bhopal 65000 98105393578
4 Raunaq 22 A-152, Gomti Nagar, Lucknow 89000 99101393576
5 Kunal 27 B-5/45, Uday Park, Delhi 80000 97653455654
6 Karan 25 A-Block,AMT Vihar,Chennai 94000 945287653241
7 Paul 25 S-25,BLB Appts, Bangalore 75000 988765442312

Page 76 of 93
1) To display list of all employees below 25 years old.

SOURCE CODE:
SELECT * from Employee where AGE < 25;
OUTPUT:

2) To list names and respective salaries in descending order ofsalary.

SOURCE CODE:
SELECT NAME, SALARY FROM Employee ORDER BY SALARY
DESC;
OUTPUT:

Page 77 of 93
3) To count The number of employees with names starting
with ‘K’.

SOURCE CODE:
SELECT COUNT(name) FROM Employee where NAME like 'K%' ;
OUTPUT:

4) To list names and addresses of those persons who have

‘Delhi’ in their address.

SOURCE CODE:
SELECT NAME, ADDRESS from Employee where ADDRESS like
‘%Delhi%’;
OUTPUT:

Page 78 of 93
5) To list out all the employees whose salary is in between
70000 and 80000.

SOURCE CODE:
SELECT * from Employee where SALARY between 70000 and
80000;
OUTPUT:

6) Count number of employees based on their age group.

SOURCE CODE:
SELECT COUNT(name) from Employee group by AGE;
OUTPUT:

Page 79 of 93
7) To list out all the employee names whose phone number starts
with ‘99’.

SOURCE CODE:
SELECT Name FROM Employee where PHONE like '%99%' ;
OUTPUT:

8) To display all the details of employee whose salary is above

85000.

SOURCE CODE:
SELECT * FROM Employee WHERE SALARY > 85000 ;
OUTPUT:

Page 80 of 93
9) To display all the employee names whose name contains
the character ‘a’ anywhere.

SOURCE CODE:
SELECT NAME FROM Employee WHERE NAME LIKE '%a%' ;
OUTPUT:

10) Display the position of occurrence of the string “na” in

employees names.

SOURCE CODE:
SELECT * , insTr( Name, 'na' ) FROM EMPLOYEE ;
OUTPUT:

************************************************

Page 81 of 93
Q5) Create the following table HOSPITAL in SQL. Insert all records
Write SQL statements for the following questions.

Page 82 of 93
1) To list the names of all the patients admitted after 1998.

SOURCE CODE:
SELECT Name FROM HOSPITAL WHERE Year(Date Of Adm) > 1998;
OUTPUT:

2) To list the unique age of male patients.

SOURCE CODE:
SELECT DISTINCT Age FROM HOSPITAL WHERE SEX = ’M’ ;
OUTPUT:

Page 83 of 93
3) To list the names of all patients with their date of admission in
ascending order.

SOURCE CODE:
SELECT Name, Date Of Adm FROM HOSPITAL ORDER BY Date Of Adm;
OUTPUT:

4) Display name and charges after rounding off to two decimal

places.

SOURCE CODE:
SELECT Name, ROUND(Charges, 2) FROM HOSPITAL;
OUTPUT:

Page 84 of 93
5) To display the department wise details of patients with chargesmore
than 300.

SOURCE CODE:
SELECT * FROM HOSPITAL GROUP BY Department HAVING
Charges>300;
OUTPUT:

6) Write SQL query to display names into small letters and displaylast 3
letters of name.

SOURCE CODE:
SELECT LCASE(Name), RIGHT(Name, 3) FROM HOSPITAL;
OUTPUT:

Page 85 of 93
7) To display month and day name of date of admin.

SOURCE CODE:
SELECT MONTH(Date Of Adm),DAYNAME(Date Of Adm)
where HOSPITAL;
OUTPUT:

8) To remove the details of female patients those who are in ENT

department.

SOURCE CODE:
DELETE from HOSPITAL Where Sex = ’F’ and Department = ’ENT’;
OUTPUT:
9) To decrease the charges of cardiology department by 100.

SOURCE CODE:
UPDATE HOSPITAL set Charges = Charges - 100 where
Department=’Cardiology’;
OUTPUT:

10) To remove The Table Hospital from database.

SOURCE CODE:
DROP Table HOSPITAL;
OUTPUT:

************************************************

Page 87 of 93
 Q6) Create the following table LOAN in SQL. Insert all records.
Write SQL statements for the following questions.

AccNo Cust_Name Loan_Amount Instalments Int_Rate Start_Date Interest

1 R.K.Gupta 300000 36 12.00 19-07-2009 1200
2 S.P.Sharma 500000 48 10.00 22-03-2008 1800
3 K.P.Jain 300000 36 NULL 08-03-2007 1600
4 M.P.Yadav 800000 60 10.00 06-12-2008 2250
5 S.P.Sinha 200000 36 12.50 03-01-2010 4500
6 P.Sharma 700000 60 12.50 05-06-2008 3500
7 K.S.Dhall 500000 48 NULL 05-03-2008 3800

Page 88 of 93
1) Display the sum of all Loan Amounts whose interest rate is greater than
10.

SOURCE CODE:
SELECT sum(Loan_Amount) from LOAN Where Int_Rate > 10;
OUTPUT:

2) Display the position of occurrence of the string “ma” in customer

names.

SOURCE CODE:
Select instr(CusT_Name,’ma’) from LOAN;
OUTPUT:

Page 89 of 93
3) Display the count of all loan holders whose name ends with
‘Sharma’.
SOURCE CODE:
SELECT count(Cust_Name)from LOAN where Cust_Name like
‘%Sharma’;
OUTPUT:

4) Display the count of all loan holders whose interest rate is Null.

SOURCE CODE:
SELECT count(Cust_Name) from LOAN where int_rate is null;
OUTPUT:

Page 90 of 93
5) Display the month name, year and day of start date.

SOURCE CODE:
Select monthname(Start_Date), year(Start_Date), day(Start_Date)from LOAN;
OUTPUT:

6) Display the interest –wise details of Loan Account Holders with atleast
10 instalments remaining.

SOURCE CODE:
SELECT * FROM LOAN GROUP BY int_rate HAVING Instalments>=10 ;
OUTPUT:

Page 91 of 93
 7) To display details of all loan holders in descending order of Loan
Amount.
SOURCE CODE:
SELECT * FROM LOAN ORDER BY Loan_Amount DESC;
OUTPUT:

8) To decrease the interest of all the loan holders by 15%.

SOURCE CODE:
UPDATE LOAN SET interest = interest - ( interest *0.15);
OUTPUT:

9) To display the total of minimum and maximum Loan Amount.

SOURCE CODE:
SELECT min(Loan_Amount)+ Max(Loan_Amount) from LOAN ;
OUTPUT:

Page 92 of 93
10) To remove the details of all loan holders whose interest is less than 2000.

SOURCE CODE:
DELETE FROM LOAN WHERE interest < 2000;
OUTPUT:

************************************

Page 93 of 93