Informatic Practices

Practical File
Class 12

SQL/Python
SUBMITTED BY:
Yuvraj Gupta
XII-C
PYTHON
Q1 Write a program to create a series S to store a range
of values where the user gives the upper and the lower
limits. Let the indexes be default values.
Ans1 –
import pandas as pd
lower=int(input("Enter the lower limit"))
upper=int(input("Enter the upper limit"))
S=pd.Series(range(lower,upper))
print(S)
Q2. Create two series S1 and S2 (the way done in Q1).
Perform various
mathematical operations (+, - , *, /) on both the series.
Ans2-
import pandas as pd
lower=int(input("Enter the lower limit of the first
series"))
upper=int(input("Enter the upper limit of the first
series"))
S=pd.Series(range(lower,upper))
print(S)
lower=int(input("Enter the lower limit of the second
series"))
upper=int(input("Enter the upper limit of the second
series"))
S1=pd.Series(range(lower,upper))
print(S1)
while(True):
print("Select the operation:")
print("1. Addition")
print("2. Substraction")
print("3. Multiplication")
print("4. Division")
print("5. Exit")
ch=int(input("Enter choice:"))
if ch==1:
print(S+S1)
elif ch==2:
print(S-S1)
elif ch==3:
print(S*S1)
elif ch==4:
print(S/S1)
elif ch==5:
Break

INPUT-
OUTPUT-
Q3. Write a program to accept the name and salary of
n number of
employees and then create the series to store names
as indexes and salary as
the data. The program must accept the name of the
employee whose salary
needs to be changed and the new salary of the
employee and then update it
in Series.
Ans3:
import pandas as pd
n=int(input("How many employees"))
name=[]
sal=[]
for i in range(n):
a=input("Enter name:")
b=int(input("Enter salary"))
name=name+[a]
sal=sal+[b]
S=pd.Series(sal,index=name)
print(S)
chk=input("Enter the name of employee whose salary
needs to be changed")
new_sal=int(input("Enter the updated salary:"))
S[chk]=new_sal
print("the Series after updating the salry is\n",S)

INPUT-
OUTPUT-

Q4. Write a program to search and display the contents

of a series based on
the input given by the user. Create the series storing
the AQI of various areas
of Delhi. (Take the area name as the index)
Ans4:
import pandas as pd
n=int(input("How many areas?"))
a_name=[]
aqi=[]
for i in range(n):
a=input("Enter area name:")
b=int(input("Enter AQI"))
a_name=a_name+[a]
aqi=aqi+[b]
S=pd.Series(aqi,index=a_name)
print(S)
chk=input("Enter the city whose air quality index you
want to check?")
print("The AQI of ",chk, "is" ,S[chk])

INPUT-

OUTPUT-
 Section B
DataFrames
Q5. Write a program in python to create the following
dataframe named
“DATA”storing the details of CLAT students:

Name City Score Qualify Categor

y
1001 Rakesh Delhi 80 Yes Gen
1002 Angli Noida 20 No SC
1003 Saurabh Gurugram 110 Yes Gen
1004 Kapeel Noida 70 Yes OBC
1005 Pooja Delhi 30 No ST
Ans-
import pandas as pd
d1={'name':['rakesh','angli','saurabh','kapeel','pooja'],
'city':['delhi','noida','gurugram','noida','delhi'],
'score':[80,20,110,70,30],
'qualify':['yes','no','yes','yes','no'],
'category':['gen','sc','gen','obc','st']
}
data=pd.DataFrame(d1,
index=[1001,1002,1003,1004,1005])
print(data)

Q6. Considering the above created dataframe and

write pandas queries to do
the following:
(a) Display names where score >1000
Input:
data.loc[data.score>1000,'name']
Output:

(b) Change the city of the candidate named Kapeel as

Jaipur
Input:
data.loc[data.name=='kapeel','city']="jaipur"
print(data)
Output:

(c) Display the details of SC and ST category students.

Input:
data.loc[data.category.isin(["sc","st"])]
Output:

(d) Increase the score of all the candidates by 25 marks

Input:
data.score=data.score+25
print(data)
Output:

(e)Display the details of candidates with the roll nos

12003 and 12004.
Input:
data[data.index.isin([12003,12004])]
Output:

(f) Display the name and city for all those candidates
where the score is between 800 and 1000
Input:
data.loc[data.score.between(800,1000)]
Output:

Q7. Write a program to consider the dataframe created

in above question
and display a menu to show the following information
regarding the
dataframe.
Transpose, Column names, indexes, datatypes of
individual columns,
size and shape of the dataframe. Your program must
keep on displaying as
per the menu till the user’s choice.
Ans 7.
import pandas as pd
d1={'name':['rakesh','angli','saurabh','kapeel','pooja'],
'city':['delhi','noida','gurugram','noida','delhi'],
'score':[80,20,110,70,30],
'qualify':['yes','no','yes','yes','no'],
'category':['gen','sc','gen','obc','st']
}
data=pd.DataFrame(d1,
index=[1001,1002,1003,1004,1005])
while(True):
print("MENU")
print("1. Display the Transpose")
print("2. Display all column names")
print("3. Display the indexes")
print("4. Display the shape")
print("5. Display the dimension")
print("6. Display the datatypes of all columns")
print("7. Display the size")
print("8. Exit")
n=int(input("Enter choice"))
if n==1:
print(data.T)
elif n==2:
print(data.columns)
elif n==3:
 print(data.index)
elif n==4:
print(data.shape)
elif n==5:
print(data.ndim)
elif n==6:
print(data.dtypes)
elif n==7:
print(data.size)
elif n==8:
print("Thank You")
break
OutPut:
Q9. Considering the given dataframe DATA, accept the
roll number of the
child ( given as index) from the user and display all his
details. The program
must continue executing till the user wants. The
program must give a valid
output if the roll number entered for searching is not
there in the dataframe.
Ans:
import pandas as pd
d1={'name':['rishi kumar','sehdev pal','simy
ghosh','pooja tyagi','kapil arora'],
'city':['delhi','noida','delhi','gurgaon','gurgaon'],
'score':[560,800,1200,900,1060],
'qualify':['no','no','yes','yes','yes'],
'category':['gen','gen','st','sc','gen']
}
data=pd.DataFrame(d1,
index=[12000,12001,12002,12003,12004])
print(data)ch="y"
while(ch=='y'or ch=='Y'):
roll=int(input("Enter the roll number of the child"))
if roll in data.index:
print(data.loc[roll])
else:
print("This roll number doesnt exist")
ch=input("Do you want to search fro more
candidates? [Y/N]")
Output:

Q10. Considering the above given dataframe write

code to insert more
records as per the choice of the user.
Ans 10:
import pandas as pd
d1={'name':['rishi kumar','sehdev pal','simy
ghosh','pooja tyagi','kapil arora'], 'city':
['delhi','noida','delhi','gurgaon','gurgaon'],
'score':[560,800,1200,900,1060],
'qualify':['no','no','yes','yes','yes'],
'category':['gen','gen','st','sc','gen']
}
data=pd.DataFrame(d1,
index=[12000,12001,12002,12003,12004])
print(data)
ch="y"
while(ch=='y'or ch=='Y'):
i=int(input("Enter the roll number of the candidate"))
n=input("Enter name of the cadidate")
c=input("Enter city of the candidate")
s=int(input("Enter the score"))
q=input("Enter qualify status [yes/no]")
cat=input("Enter category")
data.loc[i]=[n,c,s,q,cat]
print("The new dataframe with the added details
are:")
print(data)
ch=input("Do you want to add details of more
candidates? [Y/N]")
Output:
Q11.Write a menu based program to perform the
following operations on
columns of the above defined dataframe based on user
specified inputs:
(a) Insert a new column at the end
(b)Insert a new column at a specified position
(c) Delete a column permanantly
(d)Delete a column temporarily
(e) Display the dataframe
Ans:
import pandas as pd
d1={'name':['Bhidde','Ravinder','Sodhi','Vimal','Harsh'],
'city':
['jaipur','noida','delhi','gurugram','rajasthan'],
'score':[560,800,1200,900,1060],
'qualify':['no','no','yes','yes','yes'],
'category':['obc','gen','st','sc','gen']
}
data=pd.DataFrame(d1, index=[101,102,103,104,105])
print(data)
while True:
print("MENU")
print("1. Insert a new column at the end")
print("2. Insert a new column at a specified position ")
print("3. Delete a column permanantly")
print("4. Delete a column temporarily ")
print("5. Display the dataframe")
print("6. Exit ")
ch=int(input("Enter choice"))
If ch==1:
cn=input("Enter the new column name")
det=eval(input("Enter the data of the column in the
form of list"))
data[cn]=pd.Series(data=det,index=data.index)
print("Column inserted")
elif ch==2:
cn=input("Enter the new column name")
pos=int(input("enter the position where u want to
insert the column"))
det=eval(input("Enter the data of the column in the
form of list"))
data.insert(loc=pos-1,column=cn,value=det)
print("Column inserted")
elif ch==3:
cn=input("Enter the name of the column you want to
permanantly delete")
del data[cn]
print("Column permanantly deleted")
elif ch==4:
 cn=input("Enter the name of the column you want to
temporarily delete")
data.drop(columns=cn)
print("Column temporarily deleted")
elif ch==5:
print("The Status of the current dataframe is")
print(data)
elif ch==6:
Break
Output:
Q13. Write a program to accept the city name and
display all the participant
name and scores from that city using iterrows().
Display an appropriate
message if the city given by the user is missing.
Ans 13:
import pandas as pd
d1={'name':
['Aryan','Saransh','Vedant','Saumili','Saurabh'],
'city':['delhi','noida','delhi','gurgaon','gurgaon'],
'score':[560,1250,500,900,1060],
'qualify':['no','yes','no','yes','yes'],
'category':['obc','gen','st','sc','gen']
}
data=pd.DataFrame(d1,
index=[12000,12001,12002,12003,12004])
print(data)
n=input("Enter city name")
a=0
print("Name of the candidate\t ","Scores")
for i,j in data.iterrows():
if n==j['city']:
print(j['name'],"\t\t",j['score'])
a=1
if a==0:
print("Roll number does not exist")
Output:

Q14. Consider a csv file named “item” from g:\ and

write a menu driven
program to create dataframes from the given csv with
the following
specifications:
(a) Accept a column number from the user and make it
the index of the
data frame.
(b)Accept the column names which the user wants to
include in the
dataframe
(c) Accept the number of rows user wants to skip from
the csv while
creating the dataframe.
Ans 14:
import pandas as pd
while True:
print("MENU")
print("Choose the way you want to create your
Dataframe from the CSV file")
print("1. Create with default setup")
print("2. Accept a column number from the user and
make it the index of the data frame")
print("3. Accept the column names which the user
wants to include in the dataframe")
print("4. Accept the number of rows user wants to
skip from the csv while creating the dataframe")
print("5. Exit")
ch=int(input("Enter choice"))
if ch==1:
cs=input("Enter the csv file name with path")
df=pd.read_csv(cs)
print(df)
 if ch==2:
cs=input("Enter the csv file name with path")
x=int(input("Enter the column number you want to
make as index"))
df=pd.read_csv(cs, index_col=x-1)
print(df)
elif ch==3:
cs=input("Enter the csv file name with path")
y=eval(input("Enter the column names you want to
include in the dataframe"))
df=pd.read_csv(cs,usecols=y)
print(df)
elif ch==4:
cs=input("Enter the csv file name with path")
y=eval(input("Enter the number of rows you want to
exclude from the csv"))
df=pd.read_csv(cs,skiprows=y)
print(df)
elif ch==5:
Break
Output:
 SECTION C: (Data Visualization)

Q15. Bajaj Auto has given his sales figures of his North
and East Region for
the 1 st quarter of the financial year 2020. Present the
same in the form of bar
Graph.

Sales(North Region ) Sales( East Region )

January 110 70
Febuary 170 210
March 280 150
April 360 380

Write a program to represent the above given data as a

dataframe ( consider
month as indexes) and then print the graph. Put
months as x axis and sales (
of both region) as y axis. Format the chart according to
your wishes.
Ans:
import matplotlib.pyplot as plt
import pandas as pd
dict1={
'north':[110,170,280,360],
'east':[200,90,150,350]
}
df=pd.DataFrame(dict1,
index=['January','Febuary','March','April'])
print(df)
df.plot(kind='bar', color=['green','orange'])
plt.title("Comparison of Sales Month
wise",fontsize=14,color="blue")
plt.xlabel("Month",fontsize=14,color="red")
plt.ylabel("Sales",fontsize=14,color="red")
plt.xticks(fontsize=10, rotation=30)
plt.show()
Output:
Q16. Considering the dataframe created in Q1 and
present the sales of the
east region in the horizontal bar graph.
Ans:
import matplotlib.pyplot as plt
import pandas as pd
dict1={
'north':[110,170,280,360],
'east':[200,90,150,350]
}
df=pd.DataFrame(dict1,
index=['January','Febuary','March','April'])
print(df)
df['east'].plot(kind='barh', color=['green'])
plt.title("East Region Sales Month
wise",fontsize=14,color="blue")
plt.xlabel("Sales",fontsize=14,color="red")
plt.ylabel("Month",fontsize=14,color="red")
plt.xticks(fontsize=10, rotation=30)
plt.show()
Otput:

Q17. Number of mistakes done by 20 students in a

maths objective test are
as follows: [7,10,2,1,15,4,0,3,0,8,3,4,12,12,0,0,18,
9,10,11]
To know the class performance in terms of how many
students have done a
particular number of mistakes, draw a histogram with
default number of
bins.
Ans:
import pandas as pd
import matplotlib.pyplot as plt
data=[7,10,2,1,15,4,0,3,0,8,3,4,12,12,0,0,18, 9,10,11]
plt.hist(data,edgecolor="black")
plt.title("Students Performance")
plt.xlabel("Range of mistakes")
plt.ylabel("Number of students(Frequency)")
plt.show()
Output:

Q19. As per the data given in Q3. draw a histogram

with the bins defined as
0 – 10
10 – 15
15 – 18
18 – 20
Add edgecolor and proper title and label to your chart.
Ans:
import pandas as pd
import matplotlib.pyplot as plt
data=[7,10,2,1,15,4,0,3,0,8,3,4,12,12,0,0,18, 9,10,11]
b=[0,10,15,18,20]
plt.hist(data,bins=b,edgecolor="black")
plt.title("Students Performance")
plt.xlabel("Range of mistakes")
plt.ylabel("Number of students(Frequency)")
plt.show()
Output:

Q20. Write a program in Python Pandas to create the

following DataFrame
“Sports” from a Dictionary. Draw line charts to show
the plotting of score1
and score 2 for all batsman. Put legends and titles.
Specify different colours
and line styles of your choice for both the plotted lines.
Change font size of
the titles to 15 and color to green.
S_No Names Points1 Points2 State
1 Pratikshya 92 90 New Delhi
2 Mehul 60 65 Bihar
3 Sharthak 67 50 Haryana
4 Vaishnavi 80 76 Odisha

Ans:
import matplotlib.pyplot as p
import pandas as pd
dict1={'name':['Pratikshya', 'Mehul', 'Sharthak',
'Vaishnavi'],
'Points1':[92,60,67,80],
'Points2':[90,65,50,76],
'location':['New
Delhi','Bihar','Haryana','Odisha']}
df=pd.DataFrame(dict1,index=[1,2,3,4])
print(df)
x=df['name']
y=df['Points1']
z=df['Points2']
p.plot(x,y, linestyle="-",label="Points 1")
p.plot(x,z, linestyle=":",label="Points 2")
p.title('Presenting Cricket scores for every sportssman')
p.xlabel('Name of
Sportsman',fontsize="14",color="green")
p.ylabel('Points',fontsize="14",color="green")
p.legend()
p.show()
Output:
MySQL
Q1. Create a student table with the student id, name, and
marks as attributes where the student id is the primary
key.
Answer:

Q2. In the table ‘student’ created in practical 26, insert

the details of new students.
Answer:
Q3. Write SQL command to Find the min, max, sum,
and average of the marks in a student marks table..
Answer:

Q4. Delete the details of a student table created in Q1.

Answer:

Q5. Write a SQL query to order the (student ID, marks)

table in descending
order of the marks.
Answer:
Q6. To display student id, Name, DOB, Marks, Email of
those male students
in ascending order of their names.
Student ID NAME DOB MAR EMAIL
KS
SCS101101 Rahil Khan 21/04/2000 450 rahil@gmail.com
SCS101102 Jitendal Pal 12/05/2001 499 pal@gmail.com
SCS101103 Suman Lakra 19/05/2002 460 Lak123@gmail.com
SCS101104 Chandumal 24/06/2002 489 jain@gmail.com
Jain

Answer:

Q7. Describe the below table.

Student ID NAME DOB MAR EMAIL
KS
SCS101101 Rahil Khan 21/04/2000 450 rahil@gmail.com
SCS101102 Jitendal Pal 12/05/2001 499 pal@gmail.com
SCS101103 Suman Lakra 19/05/2002 460 Lak123@gmail.com
SCS101104 Chandumal 24/06/2002 489 jain@gmail.com
Jain
Answer:

Q8. To display student id, Name, DOB of those students

who are born between ‘2000- 04-21’ and ‘2002-06-24’
from the in Q7.

Answer:

Q9. To display student id, Gender, Name, DOB, Marks,

Email in descending order of their marks from the table
in Q7.
Answer:
Q2. In database there are two tables “CONSIGNOR” and
“CONSIGNEE” tables.
Table : CONSIGNOR
CNID CNName CNAddress CITY
ND01 R BABU 24,ABC AVENUE CHENNAI
ND02 AMIT GUPTA 12,RAKSHA COLONY NEW DELHI
MU15 RAJ 55, SOUTH MUMBAI
MU50 KARAN KHANNA 27-K, WEST CUBIC MUMBAI

Table : CONSIGNEE
CNEID CNID CNENAME CNEADDRESS CNECITY
MU05 ND01 RAHUL KISHORE 5,PARK MUMBAI
AVENUE
MU08 ND02 P DHIMANT 16/J,MOORE NEW
ENCLAVE DELHI
KO19 MU15 A P ROY 2 A,CENTRAL KOLKATA
AVENUE
MU32 ND02 S MITTAL P 245,AB MUMBAI
COLONY
ND48 MU50 B P JAIN 13,BLOCK D, A NEW
VIHAR DELHI

(1) Give the primary key in both tables and foreign

key in CONSIGNEE table.
(2) Write a query to display number of consignors in
each city

ANS:

(1) Primary key in consignor table is CNORID, and

primary key in consignee table

is CNEEID .Foreign key in consignee table is CNORID.

(2) Select city, count (*) as ‘no. of consignors’ from
teacher group by city;