SARDAR PATEL PUBLIC SCHOOL, MISROD

LIST OF PRACTICALS – INFORMATICS PRACTICES (065)

SESSION 2023-24 - CLASS XII
General Note:
1. PRACTICAL FILE WILL BE HANDWRITTEN.
2. Make a practical file (one side rule and one side blank)
3. Use only blue and black pen for writing.
4. Output will be written on the left side (blank side) inside a box with pencil only. Heading output can be
written with black pen.
5. Few outputs are shared in this file rest of the outputs you will write after performing the practical in the lab.
6. Take precaution about indentation while writing python codes.
7. For each question write Objective with black pen and code or SQL command with blue pen. Leave dates
now.
8. Prepare the index also.
9. Start writing each practical from separate page.
For SQL Questions
1. Make the table(s) on left side with pencil.
2. Write the SQL question with black pen and its query with blue pen.
3. Make output of the query with pencil on left side.
4. Make sure that query and its output should come on the same pair of pages.
I BELIEVE THAT WHILE WRITING THESE PRACTICALS IN YOUR FILE, YOU WILL DO IT UNDERSTANDING
EACH AND EVERY LINE AND COMMAND.
PRACTICAL 1
Objective: Write SQL Commands for (a) to (e) and output of (f) to (i) based upon the given table CLUB.
TABLE : CLUB
Coach_ID CoachName Age Sports DateofApp Pay Sex
1. KUKREJA 35 KARATE 1996-03-27 1000 M
2. RAVINA 34 KARATE 1998-01-20 1200 F
3. KARAN 34 SQUASH 1998-02-19 2000 M
4. TARUN 33 BASKETBALL 1998-01-01 1500 M
5. ZUBIN 36 SWIMMING 1998-01-12 750 M
6. KETAKI 36 SWIMMING 1998-02-24 800 F
7. ANKITA 39 SQUASH 1998-02-20 2200 F
8. ZAREEN 37 KARATE 1998-02-22 1100 F
9. KUSH 41 SWIMMING 1998-01-13 900 M
10. SHAILYA 37 BASKETBALL 1998-02-19 1700 M
(a) Display CoachName in lowercase.
(b) Display Month name of appointment of all coaches.
(c) Display the position of occurrence of ‘A’ in CoachName.
(d) Display sportwise average of Pay of the coaches.
(e) Display average age of Female coaches.
(f) Select right (Sports, 3) from Club where pay >1000;
(g) Select sum (pay) “Total Pay” from club where CoachName like ‘%U%’;
(h) Select year(min(DateofApp) from Club;
(i) Select count(Distinct Sports) from club;
 PRACTICAL 2
Objective: Write SQL Commands for (a) to (e) and output for (f) to (i) based upon the given table STUDENT.
TABLE : STUDENT
SN NAME STIPEND STREAM AVGMARK GRADE CLASS
1. KARAN 450.00 MEDICAL 78.5 B 12B
2. DIVAKAR 450.00 COMMERCE 89.2 A 11C
3. DIVYA 300.00 COMMERCE 68.6 C 12C
4. ARUN 350.00 HUMANITIES 73.1 B 12C
5. SABINA 500.00 NONMEDICAL 90.6 A 11A
6. JOHN 400.00 MEDICAL 75.4 B 12B
7. ROBERT 250.00 HUMANITIES 64.4 C 11A
8. RUBINA 450.00 NONMEDICAL 88.5 A 12A
9. VIKAS 500.00 NONMEDICAL 92.0 A 12A
10. MOHAN 300.00 COMMERCE 67.5 C 12C
(a) Select all the Nonmedical stream students from Student table.
(b) Change the stream of Sabina to Medical.
(c) List all students sorted by AvgMarks in descending order.
(d) Display a report listing, Name, Stipend, Stream and amount of stipend received in a year assuming
that the stipend is paid every month.
(e) Display, Minimum, Maximum and Average marks of students of class 11th.
(f) Select round(AvgMark) from Student where grade = ‘A’;
(g) Select substr(Stream, 3, 2) from student;
(h) Select Stream, sum(Stipend) from student group by stream;
(i) Select name from stream where length(name) <=4;

PRACTICAL 3
Objective: Write SQL Commands for (a) to (e) and output for (f) to (i) based upon the given table LIBRARY.
TABLE : LIBRARY
NO TITLE AUTHOR TYPE PUB QTY PRICE
1. Data Structure Lipschutz DS McGraw 4 217
2. Computer Studies French FND Galgotia 2 75
3. Advanced Pascal Schildt Prog McGraw 4 350
4. Dbase dummies Palmer DBMS PustakM 5 130
5. Mastering C++ Gurewich Prog BPB 3 295
6. Guide Network Freed Net ZPress 3 200
7. Mastering Foxpro Seigal DBMS BPB 2 135
8. DOS Guide Norton OS PHI 3 175
9. Basic for beginners Morton Prog BPB 3 40
10. Mastering Window Cowart OS BPB 1 225
a) Select all the PROG type books published by BPB from Library.
b) Display a list of books with price more than 130 and sorted by Qty.
c) Display all the books sorted by Price in descending order.
d) Display the list of Type of books available in the Library.
e) Delete the record of the book titled DOS Guide.
f) Select ucase(Title) from Library where type = ‘Prog’;
g) Select min(Qty) from Library where Pub = ‘BPB’;
h) Select author from library where price <100;
i) Select author, pub from library where price between 150 and 300 and qty >=3;
 PRACTICAL 4
Objective: Write SQL Commands for (a) to (e) and output for (f) to (i) based upon the given tables WATCHES
AND SALE.
TABLE : WATCHES
Watchid Watch_Name Price Type Qty_Store
W001 HighTime 10000 Unisex 100
W002 LifeTime 15000 Ladies 150
W003 Wave 20000 Gents 200
W004 HighFashion 7000 Unisex 250
W005 GoldenTime 25000 Gents 100

TABLE : SALE
Watchid Qty_Sold Quarter
W001 10 1
W003 5 1
W002 20 2
W003 10 2
W001 15 3
W002 20 3
W005 10 3
W003 15 4
a) Display the details of those watches whose name end with ‘Time’.
b) Display the name and price of those watches, which have a price range in between 5000 – 15000
c) Display the total quantity in store of ‘Unisex’ type watches.
d) Display watch name and quantity sold in first quarter.
e) Display Watch Name and its Sum of the Qty_sold of it.
f) Select max(price), min(qty_store) from watches;
g) Select quarter, sum(Qty_Sold) from Sale group by Quarter;
h) Select W.WatchName, W.price*S.Qty_Sold “Total Sales” from Watches W, Sale S where
W.Watchid=S.Watchid;
i) Select sum(price) from Watches where Type = ‘Gents’;

PRACTICAL 5
Objective: Write SQL Commands for the following
a) Display the position of occurrence of string ‘Come’ in the string “Welcome to my world”.
b) Display 7 characters extracted from 7th left character onwards from the string ‘SHINING STARS’ and display
them in lower case.
c) Display 4 characters extracted from left and right side of the string “Informatics Practices”.
d) Remove blank spaces from left side of the string “ Programming Passion “.
e) Remove blank spaces from right side of the string “ Programming Passion “.
f) Remove all the leading and trailing spaces from the string “ Programming Passion “.
g) Round off the value 23.78562 to 2 decimal places.
h) Display the remainder of 100 divided by 9
i) Calculate the exponent for 4 raised to power of 5.
j) To display current date and time.
k) To display month name from current date in capital letters.
l) Display length of the string “I can do it on my own”.
6 Objective : WAP to generate a Pandas Series using range function between user given values and
increment.
Code :
import pandas as pd
s = int (input ("Enter starting value of series : "))
e = int (input ("Enter ending value of series : "))
j = int(input ("Enter skip value of series : "))
NP = pd.Series (range(s, e, j))
print (NP)
Output :
Enter starting value of series : 40
Enter ending value of series : 60
Enter skip value of series : 4
0 40
1 44
2 48
3 52
4 56
dtype: int64
7 Objective : WAP to create a Panda’s series from a dictionary containing weekdays and
weeknames and a ndarray.
Code :
import pandas as pd
import numpy as np
# creating a dictionary of weekdays and weeknames and then series
d = {1:"Monday", 2:"Tuesday", 3:"Wednesday", 4:"Thursady", 5:"Friday", 6:"Saturday",
7:"Sunday"}
s = pd.Series (d)
print ("Series from dictionary")
print ("~~~~~~~~~~")
print (s)
# creating a numpy array and then series
n = np.array (range(11,15))
s1 = pd.Series (n)
print ("Series from ndarray")
print ("~~~~~~~~~~~~")
print (s1)
Output :
Series from dictionary
~~~~~~~~~~
1 Monday
2 Tuesday
3 Wednesday
4 Thursady
5 Friday
6 Saturday
7 Sunday
dtype: object
Series from ndarray
~~~~~~~~~~~~
0 11
1 12
2 13
 3 14
dtype: int32
8. Objective : Write a program to generate a series of 10 numbers with a scalar value as entered
by user.
Code :
import pandas as pd
n = int (input("Enter a scalar value : "))
print (pd.Series(n,range(1,11)))
9. Objective : Create a Series of marks, print all the elements that are above 75th percentile.
Code:
import pandas as pd
stu_marks=[]
rollno=[]
for i in range (1,6):
rollno.append(i+1200)
m = int(input ("Enter percentile marks :"))
stu_marks.append(m)

#creating series
s = pd.Series(index = rollno, data = stu_marks)
print ("\nValues above 75 percentile are")
print (s[s>=75])
10. Write a program to display the square of all the values of a Series object.
Code :
import pandas as pd
A = [5,2,1,3.5,3]
X = pd.Series (A,index=["a","b","c","d","e"])
print (X**2)
11. Write a program to perform following functions on a Series containing name of students as
index numbers and their percentile marks as values.
 Sorting on values
 Sorting on index
 Printing top 3 rows
 Printing bottom 2 rows
Code :
import pandas as pd
#creating series
stu_marks=[]
stu_name=[]
for i in range (1,6):
n = input ("Enter Student name : ")
m = float(input ("Enter percentile marks :"))
stu_marks.append(m)
stu_name. append (n)
S = pd.Series (index = stu_name, data = stu_marks)
print (S)
#Sorting on Values
print ("\nSeries Sorted on Values")
print (S.sort_values())
print ("\nSeries Sorted on index")
print (S.sort_index())
print ("\n Top 3 rows of the series ")
 print (S.head(3))
print ("\n Last 2 rows of the series")
print (S.tail(2))
print ("\n user defined rows printed")
x = int (input ("Enter start index number : "))
y = int (input ("Enter end index number : "))
print (S[x:y])
print ("\n Series in reverse order")
print (S.sort_values(ascending = False))
12 Create a DataFrame for examination results and display row labels, column labels data types of
each column and the dimensions.
Code:
import pandas as pd
res={'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(res)
print("Prinitng row labels in a list:")
print("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~")
idx=df.index
l=list(idx)
print(l)
print("Prinitng row labels in a list:")
print("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~")
print("[",end=" ")
for col in df.columns:
print(col,end=" ")
print("]")
print("Printing Data Types of each column")
print("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~")
print(df.dtypes)
print("Printing dimensions of Data Frame")
print("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~")
print(df.ndim)
13 Write a program to print user defined rows printed out of a DataFrame.
Code:
import pandas as pd
#creating series
country=[]
capital=[]
for i in range (1,6):
n = input ("Enter Country Name : ")
m = input ("Enter its Capital :")
country.append(n)
capital. append (m)
S = pd.Series (index = country, data = capital)
print (S)
print ("\n user defined rows printed")
x = int (input ("Enter start index number : "))
y = int (input ("Enter end index number : "))
 print (S[x:y])
14 Create a DataFrame from a 2D dictionary, Sales which stores the quarter wise sales as inner
dictionary for two years, as shown below:
Sales = {'yr1' : {'Qtr1' : 34500, 'Qtr2': 56000, 'Qtr3':47000, 'Qtr4': 49000},
'yr2' : {'Qtr1' : 44900, 'Qtr2': 46100, 'Qtr3':57000, 'Qtr4': 59000}}
Code:
import pandas as pd
Sales = {'yr1' : {'Qtr1' : 34500, 'Qtr2': 56000, 'Qtr3':47000, 'Qtr4': 49000},
'yr2' : {'Qtr1' : 44900, 'Qtr2': 46100, 'Qtr3':57000, 'Qtr4': 59000}}
DF = pd.DataFrame(Sales)
print (DF)

15 Create a dataframe and iterate them over rows.

Code :
import pandas as pd
data = [["Virat",55,66,31],["Rohit",88,66,43],[ "Hardik",99,101,68]]
players = pd.DataFrame(data, columns = ["Name","Match-1","Match-2","Match-3"])
print("Iterating by rows:")
print("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~")
for index, row in players.iterrows():
print(index, row.values)
print("Iterating by columns:")
print("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~")
for index, row in players.iterrows():
print(index, row["Name"],row["Match-1"], row["Match-2"],row["Match-3"])
16 Create a DataFrame and print it along with their index using items() method.
Code:
import pandas as pd

sc_4yrs={2016:{'Virat Kohli':2595,'Rohit Sharma':2406,'Shikhar Dhawan':2378},

2017:{'Virat Kohli':2818,'Rohit Sharma':2613,'Shikhar Dhawan':2295},
2018:{'Virat Kohli':2735,'Rohit Sharma':2406,'Shikhar Dhawan':2378},
2019:{'Virat Kohli':2455,'Rohit Sharma':2310,'Shikhar Dhawan':1844}}
df=pd.DataFrame(sc_4yrs)
print(df)
print("------------------------------------------------------------------------")
for (year,runs) in df.items():
print("Year:",year)
print(runs)
17. Create a DataFrame from a 2D dictionary, Sales which stores the quarter wise sales as inner
dictionary for two years, as shown below:
Sales = {'yr1' : {'Qtr1' : 34500, 'Qtr2': 56000, 'Qtr3':47000, 'Qtr4': 49000},
'yr2' : {'Qtr1' : 44900, 'Qtr2': 46100, 'Qtr3':57000, 'Qtr4': 59000}}
Using this DataFrame do the following
1. Change the DataFrame Sales such that it becomes its transpose
2. Modify yr1 to 2021 and Yr2 to 2022
3. Modify Qtr1 sales of 2022 to 45950
4. Add data of 2023 as {'Qtr1' : 47500, 'Qtr2': 47600, 'Qtr3':58050, 'Qtr4': 59800}
Code:
import pandas as pd
Sales = {'yr1' : {'Qtr1' : 34500, 'Qtr2': 56000, 'Qtr3':47000, 'Qtr4': 49000},
'yr2' : {'Qtr1' : 44900, 'Qtr2': 46100, 'Qtr3':57000, 'Qtr4': 59000}}
DF = pd.DataFrame(Sales)
 print (DF)
print("\nTranspose:")
print("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~")
print(DF.T)
print("\nRenaming Column Labels")
print("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~")
DF=DF.rename({"yr1":"2021","yr2":"2022"},axis="columns")
print(DF)

print("\nModifying Data Value")

print("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~")

DF['2022':'Qtr1']=45950
print(DF)

print("\nAdd Column")
print("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~")

DF['2023']=47500,47600,58050,59800
print(DF)
18. Write a program in Python Pandas to create the following DataFrame batsman from a
Dictionary:
B_No Name Score1 Score2
1 Sunil Pillai 90 80
2 Gaurav Sharma 65 45
3 Piyush Goel 70 90
4 Kartik Thakur 80 76
Perform the following operations on the DataFrame:
(a) Add both the scores of a batsman and assign to column “Total”
(b) Display the highest score in both Score1 and Score2 of the DataFrame
Code:
import pandas as pd
d1={'B_NO':[1,2,3,4],
'Name':["Sunil Pillai","Gaurav Sharma","Piyush Goel","Kartik Thakur"],
'Score1':[90,65,70,80],
'Score2':[80,45,95,76]
}
df=pd.DataFrame(d1)
print(df)
df['Total'] = df['Score1']+ df['Score2']
print(df)
print("Maximum scores are : " ,
max(df['Score1']), max(df['Score2']))
19. Write a program to plot following data on line chart:
Day Monday Tuesday Wednesday Thursday Friday
Income 510 350 475 580 600
1. Write a title for the chart “The Weekly Income Report”.
2. Write the appropriate titles of both the axes.
3. Write code to Display legends.
4. Display red color for the line.
5. Use the line style – dashed
 6. Display diamond style markers on data points.
Code :
import matplotlib.pyplot as pp
day =['Monday','Tuesday','Wednesday','Thursday','Friday']
inc = [510,350,475,580,600]
pp.plot(day,inc,label='Income',color='r',linestyle='dashed',marker='D')
pp.title("The Weekly Income Report")
pp.xlabel("Days")
pp.ylabel("Income")
pp.show()
20. Pratyush Garments has recorded the following data into their register for their income from
cotton clothes and jeans. Plot them on the line chart.
Day Monday Tuesday Wednesday Thursday Friday
Cotton 450 560 400 605 580
Jeans 490 600 425 610 625
Apply following customization to the line chart.
1. Write a title for the chart “The Weekly Garment Orders”.
2. Write the appropriate titles of both the axes.
3. Write code to Display legends.
4. Display your choice of colors for both the lines cotton and jeans.
5. Use the line style – dotted for cotton and dashdot for jeans.
6. Display plus markers on cotton and x markers of jeans.
Code:
import matplotlib.pyplot as pp
day =['Monday','Tuesday','Wednesday','Thursday','Friday']
ct = [450,560,400,605,580]
js = [490,600,425,610,625]
pp.plot(day,ct,label='Cotton',color='g',linestyle='dotted',marker='+')
pp.plot(day,js,label='Food',color='m',linestyle='dashdot',marker='x')
pp.title("The Weekly Garment Orders")
pp.xlabel("Days")
pp.ylabel("Orders")
pp.legend()
pp.show()
21. Write a program to plot a bar chart for the medals won by four countries based on the data
given below:
Country Gold Silver Bronze Total
Australia 80 59 59 198
England 45 45 46 136
India 26 20 20 66
Canada 15 40 27 82
1. The title of the chart will be “Diagrammatic representation of Medal Talley”.
2. X axis title is “Countries”
3. Y axis title will be “Medals Won”
4. Show legends at upper left corner.

import matplotlib.pyplot as plt

import numpy as np
Info = ['Gold','Silver','Bronze','Total']
Australia = [80,59,59,198]
England=[45,45,46,136]
 India = [26,20,20,66]
Canada = [15,40,27,82]
X = np.arange(len(Info))
plt.bar(Info,Australia, width = 0.15, label ="Australia")
plt.bar(X+0.15, England, width = 0.15, label = "England")
plt.bar(X+0.30, India, width = 0.15, label ="India")
plt.bar(X+0.45,Canada, width = 0.15, label = "Canada")
plt.title ("Diagrammatic representation of Medal Talley")
plt.xlabel("Countries")
plt.ylabel("Medals won")
plt.legend(loc = 'upper left')
plt.show()
22. In an engineering college, number of admissions stream wise in the current year are:
Civil = 15, Electrical = 35, Mechanical = 40, Chemical = 20, CS = 50
Write a program to print above information on a circular pic chart, creating a wedge for CS
stream. Save the figure in D drive as stream.png
Code:
import matplotlib.pyplot as plt
streams = ['Civil','Electrical','Mechanical','Chemical','CS']
adm = [15,35,40,20,50]
plt.title("Admission Streamwise")
plt.axis("equal")
expl = [0,0,0,0,0.2]
plt.pie(adm,labels = streams, explode = expl, autopct = "%5.1f%%")
plt.savefig("D:\\stream.png")