Megha IP File Complete

Class XII
Informatics Practices
Practical File
SQL/Python
Submitted By:
Megha Singh, XII C
INDEX
S.No
Questions Signature
Python
1 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.
2 Create two series S1 and S2 (the way done in Q1). Perform various mathematical
operations (+, - , *, /) on both the series.
3 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.
4 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)
5 Write a program in python to create the following dataframe named “DATA”storing the
details of NEET students:
6 Considering the above created dataframe and write pandas queries to do the
following:
(a) Display names where score >1000
(b)Change the city of the candidate named Pooja Tyagi as Jaipur
(c) Display the details of SC and ST category students
(d) Increase the score of all the candidates by 25 marks
(e) Display the details of candidates with the roll nos 12003 and 12004
(f) Display the name and city for all those candidates where the score is between 800 and
1000
7 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.
8 Write a program to create a menu driven program to display various sets of records from
the dataframe depending upon the choice of the user. ( Consider the dataframe named
DATA)
1. display the first five rows
2. display the last five rows
3. Display any number of records from the top
4. Display any number of records from the bottom
9 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.
10 Considering the above given dataframe write code to insert more records as per the
choice of the user.
11 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
12 Write a program to write a menu driven program to do the following based on user
inputs: (a) Display a particular row of a dataframe (b)Display a column of a dataframe (c)
Display a combination of rows and columns from a dataframe
13 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.
14 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.
15 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.
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.
16 Considering the dataframe created in Q1 and present the sales of the east region in the
horizontal bar graph.
17 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.
18 As per the data given in Q3. draw a histogram with 3 bins.

19 As per the data given in Q3. draw a histogram with the bins defined as 0 – 5 5 – 10 10 –
18 18 --20 Add edgecolor and proper title and label to your chart.
20 Write a program in Python Pandas to create the following DataFrame “batsman” 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.
MY SQL
21 Questions on table named TEACHER
22 Questions based on tables “CONSIGNOR” and “CONSIGNEE” .

INFORMATICS PRACTICES CLASS XII
CBSE PRACTICAL FILE QUESTIONS:
SECTION A: (Series)
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.
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
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.
Ans: 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)
Output Sample:
How many employees3
Enter name:sushmita
Enter salary45000
Enter name:mayank
Enter salary340
Enter name:parth
Enter salary56000
sushmita 45000
mayank 340
parth 56000
dtype: int64
Enter the name of employee whose salary needs to be changedmayank
Enter the updated salary:34000
the Series after updating the salry is
sushmita 45000
mayank 34000
parth 56000
dtype: int64
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])
Output Sample:
How many areas?4
Enter area name:dwarka
Enter AQI350
Enter area name:janakpuri
Enter AQI380
Enter area name:delhi cantt
Enter AQI276
Enter area name:chandni chowk
Enter AQI400
dwarka 350
janakpuri 380
delhi cantt 276
chandni chowk 400
dtype: int64
Enter the city whose air quality index you want to check?janakpuri
The AQI of janakpuri is 380
>>>
SECTION B:(Dataframes)
Q5. Write a program in python to create the following dataframe named

“DATA”storing the details of NEET students:
Name City Score Qualify Category
12000 Rishi Kumar Delhi 560 No Gen
12001 Sehdev Pal Noida 800 No Gen
12002 Simy Ghosh Delhi 1200 Yes ST
12003 Pooja Tyagi Gurgaon 900 Yes SC
12004 Kapil Arora Gurgaon 1060 Yes Gen
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)
Q6. Considering the above created dataframe and write pandas queries to do
the following:
(a) Display names where score >1000
>>>
data.loc[data.score>1000,'name']
12002 simy ghosh
12004 kapil arora
Name: name, dtype: object
(b) Change the city of the candidate named Pooja Tyagi as Jaipur
>>> data.loc[data.name=='pooja tyagi','city']="jaipur"
>>> print(data)
name city score qualify category
12000 rishi kumar delhi 560 no gen
12001 sehdev pal noida 800 no gen
12002 simy ghosh delhi 1200 yes st
12003 pooja tyagi jaipur 900 yes sc
12004 kapil arora gurgaon 1060 yes gen
(c) Display the details of SC and ST category students.
>>> data.loc[data.category.isin(["sc","st"])]
>>>
(d) Increase the score of all the candidates by 25 marks

>>> data.score=data.score+25
>>> print(data)
12000 rishi kumar delhi 585 no gen
(e) Display the details of candidates with the roll nos 12003 and 12004
>>> data[data.index.isin([12003,12004])]
(f) Display the name and city for all those candidates where the score is
between 800 and 1000
>>> data.loc[data.score.between(800,1000)]
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
'score':[560,800,1200,900,1060],
}
print(data)
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("Good bye")
break
Q8 Write a program to create a menu driven program to display various sets
of records from the dataframe depending upon the choice of the user. (
Consider the dataframe named DATA)
1. display the first five rows
2. display the last five rows
3. Display any number of records from the top
4. Display any number of records from the bottom
Ans8:
import pandas as pd
'score':[560,800,1200,900,1060],
}
print(data)
while(True):
print("MENU")
print("1. Display the First five rows")
print("2. Display the last five rows")
print("3. Display specific number of records from the top")
print("4. Display specific number of records from the top")
print("5. Exit")
n=int(input("Enter choice"))
if n==1:
print(data.head())
elif n==2:
print(data.tail())
elif n==3:
r=int(input("Enter the number of rows from the top you want to see"))
print(data.head(r))
elif n==4:
r=int(input("Enter the number of rows from the bottom you want to
see"))
print(data.tail(r))
elif n==5:
print("Good bye")
break
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
'score':[560,800,1200,900,1060],
}
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
'score':[560,800,1200,900,1060],
}
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
'score':[560,800,1200,900,1060],
}
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:
Q12. Write a program to write a menu driven program to do the following

based on user inputs:
(a) Display a particular row of a dataframe
(b) Display a column of a dataframe
(c) Display a combination of rows and columns from a dataframe
Ans 12:
import pandas as pd
'score':[560,800,1200,900,1060],
}
print(data)
while True:
print("MENU")
print("1. Display a particular row of a dataframe")
print("2. Display a column of a dataframe ")
print("3. Display a combination of rows and columns from a dataframe")
print("4. Exit ")
if ch==1:
rowl=int(input("Enter the row label "))
print(data.loc[rowl])
elif ch==2:
cn=input("Enter the column name")
print(data[cn])
elif ch==3:
rowl=eval(input("Enter the row labels in a list form"))
cn=eval(input("Enter the column names in a list form"))
print(data.loc[rowl,cn])
elif ch==4:
break
Sample 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
'score':[560,800,1200,900,1060],
}
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")
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")
if ch==1:
cs=input("Enter the csv file name with path")
df=pd.read_csv(cs)
print(df)
if ch==2:
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:
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:
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
SECTION C: (Data Visualization)
Q15. Bajaj Auto has given his sales figures of his North and East Region for
the 1st quarter of the financial year 2020. Present the same in the form of bar
graph.
Sales( North Region) Sales ( East Region)
April 110 200
May 150 90
June 230 150
July 400 350
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,150,230,400],
'east':[200,90,150,350]
}
df=pd.DataFrame(dict1, index=['April','May','june','July'])
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()
Q16. Considering the dataframe created in Q1 and present the sales of the
east region in the horizontal bar graph.
Ans:
import pandas as pd
dict1={
'north':[110,150,230,400],
'east':[200,90,150,350]
}
df=pd.DataFrame(dict1, index=['April','May','june','July'])
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()
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
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()
Q18. As per the data given in Q3. draw a histogram with 3 bins.
Ans:
import pandas as pd
data=[7,10,2,1,15,4,0,3,0,8,3,4,12,12,0,0,18, 9,10,11]
plt.hist(data,bins=3,edgecolor="black")
plt.show()
Q19. As per the data given in Q3. draw a histogram with the bins defined as
0–5
5 – 10
10 – 18
18 --20
Add edgecolor and proper title and label to your chart.
Ans:
import pandas as pd
data=[7,10,2,1,15,4,0,3,0,8,3,4,12,12,0,0,18, 9,10,11]
b=[0,5,10,18,20]
plt.hist(data,bins=b,edgecolor="black")
plt.show()
Q20. Write a program in Python Pandas to create the following DataFrame
“batsman” 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.
B_NO Name Score1 Score2 Location
1 Sunil Pillai 90 80 Delhi
2 Gaurav Sharma 65 45 Mumbai
3 Piyush Goel 70 90 Hyderabad
4 Kartik Thakur 80 76 Delhi
Ans:
import matplotlib.pyplot as p
import pandas as pd
dict1={'name':['sunil pillai','gaurav sharma','piyush goel','kartik thakur'],
'score1':[90,65,70,80],
'score2':[80,45,90,76],
'location':['delhi','mumbai','hyderabad','delhi']}
df=pd.DataFrame(dict1,index=[1,2,3,4])
print(df)
x=df['name']
y=df['score1']
z=df['score2']
p.plot(x,y, linestyle="-",label="Score 1")
p.plot(x,z, linestyle=":",label="Score 2")
p.title('Presenting Cricket scores for every batsman')
p.xlabel('Name of Batsman',fontsize="14",color="green")
p.ylabel('Scores',fontsize="14",color="green")
p.legend()
p.show()
CLASS 12 INFORMATICS PRACTICES 2020-21
SQL PRACTICAL FILE WORK
Q1. Consider the following table named TEACHER and answer the following questions
based on it.
Field name Type Width Constraint
Tno Number 5 Primary Key
Name Char 30 NOT NULL
Age Number 2
Dept Char 20
DateofJoin Date
Salary decimal 10,2
Bonus integer 12
Sex Char 1
1. Create the above given table.
2. Insert 5 record
3. List the names in capital of the male teachers who are in Science or commerce
Department in the descending order of their salary.
4. Calculate the total salary given department wise.
5. Increase the salary of all those teachers who have joined before 10-jan-2007 by 20%
6. Display the average salary given for computer dept.

7. Display the department names where the total salary disbursed is more than 500000.
8. Display the teacher name, age and salary rounded off to hundredth place.
9. Display the teacher name along with the length of each name.
10. Display those department names where the number of teacher’s working is more
than 5.
11. Display the position of the occurrence of ’a’ in all the teacher names.
12. Display the highest and lowest salary value for each department.
13. Display the total number of records in the table.
14. Display those department names where the maximum bonus given is more than
20000.
15. Display a report showing the average bonus given to every department.
16. Create a table named Computers storing the details of computer department
teachers.
17. Display the number of years every teacher has worked.
18. Display the count of teachers in every department.

19. Display the name of teachers who have joined in the month of January, April and
July
20. Display the count of male and female teachers in the school.
21. Display the teachername , day (name of day) ,month and year of joining for all
teachers in the descending order of salary.
22. Display the teacher’s name along with their salary truncated to zero decimal places.
23. Display the teacher number along with their names who have not yet been given any
department.
Q2. In database there are two tables “CONSIGNOR” and “CONSIGNEE” tables.
Table : CONSIGNOR
CNORID CNORNAME CNORADDRESS 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
CNEEID CNORID CNEENAME CNEEADDRESS CNEECITY
MU05 ND01 RAHUL KISHORE 5,PARK AVENUE MUMBAI
ND08 ND02 P DHIMANT 16/J,MOORE ENCLAVE NEW DELHI
KO19 MU15 A P ROY 2 A,CENTRAL AVENUE KOLKATA
MU32 ND02 S MITTAL P 245,AB COLONY MUMBAI
ND48 MU50 B P JAIN 13,BLOCK D, A VIHAR NEW 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.
ANS2. (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;

Megha IP File Complete

Uploaded by

Copyright:

Available Formats

Megha IP File Complete

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Megha IP File Complete

Uploaded by

Copyright:

Available Formats

Class XII

17 Number of mistakes done by 20 students in a maths objective test are as follows:

18 As per the data given in Q3. draw a histogram with 3 bins.

22 Questions based on tables “CONSIGNOR” and “CONSIGNEE” .

Q5. Write a program in python to create the following dataframe named

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

Q12. Write a program to write a menu driven program to do the following

n=input("Enter city name")

SQL PRACTICAL FILE WORK

1. Create the above given table.

4. Calculate the total salary given department wise.

6. Display the average salary given for computer dept.

13. Display the total number of records in the table.

17. Display the number of years every teacher has worked.

18. Display the count of teachers in every department.

You might also like