A PRACTICAL FILE REPORT ON
“INFORMATICS PRACTICES”
Submitted By:
Student Name – ___________________
Class - ___________ Section ___________
Roll No - ___________________
Practical Sub Code - 065
Submitted To: Mr. Rahil Shrivastav
PGT – Computer Science
Department of Computer Science
1
DEEP PUBLIC SCHOOL
(Affiliated to CBSE, New Delhi)
ACKNOWLEDGEMENT
In the accomplishment of this practical successfully, many people have best owned
upon me their blessings and the heart pledge support, this time I am utilising to thank
all the people who have been concerned with this practical.
Primarily I would like to thank god for being able to complete this project with success.
Then I would like to thank Our HOS ma’am, Ms. Rashmi Adhikari and Our Informatics
Practices teacher Sir, Mr Rahil Shrivastav whose valuable guidance has been the ones
that helped me patch this practical and make it full proof success, their suggestion and
instructions has served as the major contribution towards the completion of this
practical.
Then I would like to thank my parents who have helped me with their valuable
suggestion and guidance has been very helpful in various phases of the completion of
the practical.
I would like to express my gratitude to Mr. Rahil Shrivastav, PGT
Informatics Practices of Deep Public School, who has served as a host of valuable
corrections, who have given necessary guidelines when needed during the
development of this practical file.
Student Name - __________________
Approved and Verified By:
Signature of the Student Signature of the Internal Examiner
2
DEEP PUBLIC SCHOOL
(Affiliated to CBSE, New Delhi)
CERTIFICATE OF ORIGINALITY
This is to certify that ___________________________ student of class __________
has completed the research on the below mentioned practical under the guidance of
Mr Rahil Shrivastav, during the year of 2024-25 in partial fulfilment of Informatics
Practices (065) Practical Examination-2025 conducted by Deep Public School, Delhi.
Name:
Roll No:
Class and Section:
Signature of the Internal Examiner Signature of the External Examiner
3
DEEP PUBLIC SCHOOL
(Affiliated to CBSE, New Delhi)
UNDERTAKING
This is to certify that the “Informatics Practices (Subject Code: 065)” Practical File
submitted to “Deep Public School”, affiliated to “Central Board of Secondary
Education” in partial fulfilment of the requirement for the award of the CBSE Board
Class 12th, is an original work carried out by _______________________________
Class and Section: __________.
Full Name:
Roll No:
Verified By:
Signature of the Student Signature of the Internal/ External Examiner
4
INDEX
[Link] Practical File Questions Page No.
1. Write the Program to create a Pandas series from a 10
dictionary of values and a ndarray.
2. Write the Program to combine number of series to 10
form a dataframe.
3. Write the Program to create a dataframe quarterly 11
sales where each row contains the items category,
item name, and expenditure. Group the rows by the
category, and print the total expenditure. Group the
rows by the category, and print the total expenditure
per category.
4. Write the Program to print all the elements that are 11
above the 75th percentile from the given series.
5. Write the Program to create a dataframe for 12
examination result and display row labels, column
labels datatypes of each column and the
dimensions.
6. Write the Program to filter out rows based on 12
different criteria such as duplicate rows.
7. Write the Program to locate the 3 largest values in a 13
dataframe.
8. Write the Program to replace all negative values in a 13
dataframe with at 0.
9. Write the Program to replace all missing values in a 14
dataframe with a 999.
10. Write the Program to enter data and show data in 14
python using DataFrames and Pandas.
5
11. Write the Python Program to create a series using 15
list in Pandas.
12. Write the Python Program to create a series using 15
dictionary in Pandas.
13. Write the Python code to create a dataframe with the 16
appropriate column heading from the list.
14. Write the Python code to create a dataframe from 16
two series of student data.
15. Write the Python code to create a dataframe from 17
dictionary.
16. Write the Python code to concatenate of two 17
dataframes.
17. Write the Program to create dataframe for 3 students 18
including name and roll numbers and add new
columns for 5 subjects and 1 column to calculate
percentage. It should include random numbers in
marks of all the subjects.
18. Write a Program to plot bar chart for the given school 19
result data, analyze the performance of the students
on different parameters, e.g. subject wise or class
wise.
19. Plot the following data on line chart and follow the 20
given instructions.
A. Write the title for the chart “The Weekly
Income 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 Command style markers on data
points.
20. Consider the following data of a medical store and 21
plot the data on the line chart and customize the
chart as you wish.
6
21. Write the SQL Query for the following- 22-26
a. Create a student table with the student id,
name, and marks as attributes where the
student id is the primary key.
b. Insert the details of a new student in the
above table.
c. Delete the details of a particular student in
the above table.
d. Use the select command to get the details of
the students with marks more than 80.
e. Write a query to display the minimum,
maximum, total and average marks of the
students.
f. Create a new table (orderid, customer name,
and order date) by joining two tables (order
Id, customer Id, and order Date) and
(Customer ID, Customer Name).
g. Create a Foreign Key in one of the two table
mentioned above.
h. Find the total number of customers from each
country in the table (Customer Id, Customer
Name, Country) using group by.
i. Create a new table (name, date of birth) by
joining two tables (student id, name) and
(student id, date of birth).
j. Write a SQL query to order the (Student Id,
marks) table in descending order of the
marks.
22. The table School bus is follows. 26-29
a. To show all information of students where
capacity is more than the no. of students in
order of rtno.
b. To show area_covered for buses covering
more than 20Km., but chargesless than 80000.
c. To show transporter wise total no. of students
traveling.
7
d. To show rtno, area_covered and average cost
per student for all routes where average cost
per student is – charges/ noofstudents.
e. Add a new record with the following data:
(11, “Moti Bagh”, 35,32,10,”Kisan Tours”, 35000)
23. The table graduate is as follows. 29-33
a. List the name of those students who have
obtained DIV 1sorted by NAME.
b. Display a report, listing NAME, STIPEND is paid
every month.
c. To count the number of students who are
either Physics or Computer Science graduates.
d. To insert a new row in the GRADUATE
table:(11, “Kajol”, 300, “Computer Science”,
75, 1)
e. Give the output of the following.
24. The table is hospital is as follows: 33-40
a. To show all the information of the patients of
cardiology department.
b. To list the name of female patients who are
either in the orthopedic orsurgery
department.
c. To list the name of all the patients with their
date of admission inascending order.
d. To display patient name, charges, age for male
patients only.
e. To count the number of patients with age>20.
f. To display various departments.
g. To display the number of patients.
h. To display the number of male and female
patients.
i. To display the details of the patients admitted
in the first quarter of 1998.
j. To display the details of all the patients whose
name starts with the alphabet ‘Z’.
k. To change the age of the patient Kush to 20.
l. To Increase the Charges of all the patients by
5%.
8
m. To remove the record of the patient whose
Name is Tarun.
n. To add another column WardNumber of the
type number in the above table.
o. To set charges to NULL for all the patients in
the Surgery department.
p. To decrease the charges by 10% of all the
patients admitted in the cardiology
department.
q. To insert a new row in the HOSPITAL table
with the following data:- (11, “Mustafa”, 37,
“Ent”, “1998-02-25”, 250, “IM”)
r. Give the output of the following SQL
statements based on the above table-
Hospital.
25. SQL queries based on the following two tables- 40-44
Activity, Coach.
a. Display the Names of all the activities with
their codes in descending order.
b. Display the sum of Prizemoney for the
activities played in each of the stadium
separately.
c. Display the content of Activity table whose
ScheduleDate is earlier than 01/01/2004
inascending order of participantsNum.
d. Display the names activity and its
corresponding coach name.
e. Give the output of the following queries.
9
Q1. Write the Program to create a Pandas series from a dictionary of values and a
ndarray.
Ans-
Q2. Write the Program to combine number of series to form a DataFrame.
Ans-
Q3. Write the Program to create a DataFrame quarterly sales where each row
contains the items category, item name, and expenditure. Group the rows by the
category, and print the total expenditure. Group the rows by the category, and
print the total expenditure per category.
Ans-
10
Q4. Write the Program to print all the elements that are above the 75th percentile
from the given series.
Ans-
11
Q5. Write the Program to create a dataframe for examination result and display
row labels, column labels datatypes of each column and the dimensions.
Ans-
Q6. Write the Program to filter out rows based on different criteria such as
duplicate rows.
Ans-
12
Q7. Write the Program to locate the 3 largest values in a DataFrame.
Ans-
Q8. Write the Program to replace all negative values in a DataFrame with at 0.
Ans-
13
Q9. Write the Program to replace all missing values in a DataFrame with a 999.
Ans-
Q10. Write the Program to enter data and show data in python using DataFrames
and Pandas.
Ans-
14
Q11. Write the Python Program to create a series using list in Pandas.
Ans-
Q12. Write the Python Program to create a series using dictionary in Pandas.
Ans-
15
Q13. Write the Python code to create a dataframe with the appropriate column
heading from the list.
Rollno Name Age
101 Ram 35
102 Shyam 32
103 Krishna 45
Ans-
Q14. Write the Python code to create a DataFrame from two series of student data.
Ans-
16
Q15. Write the Python code to create a dataframe from dictionary.
Ans-
Q16. Write the Python code to concatenate of two dataframes.
Ans-
17
Q17. Write the Program to create dataframe for 3 students including name and roll
numbers and add new columns for 5 subjects and 1 column to calculate percentage.
It should include random numbers in marks of all the subjects.
Ans-
Q18. Write a Program to plot bar chart for the given school result data, analyze the
performance of the students on different parameters, e.g. subject wise or class
wise.
Ans-
18
Q19. Plot the following data on line chart and follow the given instructions.
Day Monday Tuesday Wednesday Thursday Friday
Income 510 350 475 580 600
A. Write the title for the chart “The Weekly Income 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.
Display Command style markers on data points.
Ans-
19
Q20. Consider the following data of a medical store and plot the data on the line
chart and customize the chart as you wish.
Month Masks Sanitizer Hand Wash
March 1500 4400 6500
April 3500 4500 5000
May 6500 5500 5800
June 6700 6000 6300
July 6000 5600 6200
August 6800 6300 4500
Ans-
20
Q21. Write the SQL Query for the following-
a. Create a student table with the student id, name, and marks as attributes
where the student id is the primary key.
Ans-
21
b. Insert the details of a new student in the above table.
Ans-
c. Delete the details of a particular student in the above table.
Ans-
d. Use the select command to get the details of the students with marks more
than 80.
22
Ans-
e. Write a query to display the minimum, maximum, total and average marks of
the students.
Ans-
f. Create a new table (orderid, customer name, and order date) by joining two
tables (order Id, customer Id, and order Date) and (Customer ID, Customer
Name).
Ans-
23
g. Create a Foreign Key in one of the two table mentioned above.
Ans-
24
h. Find the total number of customers from each country in the table (Customer
Id, Customer Name, Country) using group by.
Ans-
i. Create a new table (name, date of birth) by joining two tables (student id,
name) and (student id, date of birth).
Ans-
25
j. Write a SQL query to order the (Student Id, marks) table in descending order
of the marks.
Ans-
Q22. The table School bus is follows:
Rtno Area_covered Capacity Noofstudents Distance transporter Charges
1 Vasant kunj 100 120 10 Shivamtravels 100000
2 Hauz Khas 80 80 10 Anand Travels 85000
3 Pitampura 60 55 30 Anand Travels 60000
4 Rohini 100 90 35 Anand Travels 100000
5 Yamuna Vihar 50 60 20 Bhalla Co. 55000
6 Krishna Nagar 70 80 30 Yadav Co. 80000
7 Vasundhara 100 110 20 Yadav Co. 100000
8 Paschim Vihar 40 40 20 Speed Travels 55000
9 Saket 120 120 10 Speed Travels 100000
10 Janakpuri 100 100 20 Kisan tours 95000
26
a. To show all information of students where capacity is more than the no. of
students in order of rtno.
27
b. To show area_covered for buses covering more than 20Km., but chargesless
than 80000.
c. To show transporter wise total no. of students traveling.
d. To show rtno, area_covered and average cost per student for all routes where
average cost per student is – charges/ noofstudents.
28
e. Add a new record with the following data:
(11, “Moti Bagh”, 35, 32, 10,”Kisan Tours”, 35000)
Q23. The table graduate is as follows:
SNO Name Stipend Subject Average Division
1 Karan 400 Physics 68 I
2 Diwakar 450 Computer Science 68 I
3 Divya 300 Chemistry 62 I
4 Rekha 350 Physics 63 I
5 Arjun 500 Maths 70 I
6 Sabina 400 Chemistry 55 II
7 John 250 Physics 64 I
8 Robert 450 Maths 68 I
9 Rubina 500 Computer Science 62 I
10 Vikas 400 Maths 57 II
a. List the name of those students who have obtained DIV 1sorted by NAME.
29
30
b. Display a report, listing NAME, STIPEND, SUBJECT and amount of stipend
received in a year assuming that the STIPEND is paid every month.
c. To count the number of students who are either Physics or Computer Science
graduates.
31
d. To insert a new row in the GRADUATE table:(11, “Kajol”, 300, “Computer
Science”, 75, 1)
e. Give the output of the following:-
1. select min(average) from graduate where subject=”physics”;
2. select sum(stipend) from graduate where div=2;
3. select count(distinct subject) from graduate;
32
4. Assume that there is one more table GUIDE in the database as shown
below-
Table: GUIDE
5. What will be the output of the following query:-
Q24. The table is hospital is as follows:
Pno Name Age Department DateofAdm Charges Sex
1 Sandeep 65 Surgery 23-02-1998 300 M
2 Ravina 24 Orthopedic 01-01-1998 200 F
3 Karan 45 Orthopedic 19-02-1998 200 M
4 Tarun 12 Surgery 23-02-1998 300 M
33
5 Zubin 36 ENT 23-02-1998 250 M
6 Ketaki 16 ENT 23-02-1998 300 F
7 Ankita 29 Cardiology 23-02-1998 800 F
8 Zareena 45 Gynecology 23-02-1998 300 F
9 Kush 19 Cardiology 23-02-1998 800 M
10 Shailya 31 Medicine 23-02-1998 400 M
a. To show all the information of the patients of cardiology department.
34
b. To list the name of female patients who are either in the orthopedic or
surgery department.
c. To list the name of all the patients with their date of admission inascending
order.
d. To display patient name, charges, age for male patients only.
35
e. To count the number of patients with age>20.
f. To display various departments.
g. To display the number of patients in each department.
h. To display the number of male and female patients.
36
i. To display the details of the patients admitted in the first quarter of 1998.
j. To display the details of all the patients whose name starts with the alphabet
‘Z’.
k. To change the age of the patient Kush to 20.
37
l. To Increase the Charges of all the patients by 5%.
m. To remove the record of the patient whose Name is Tarun.
n. To add another column WardNumber of the type number in the above table.
38
o. To set charges to NULL for all the patients in the Surgery department.
p. To decrease the charges by 10% of all the patients admitted in the cardiology
department.
q. To insert a new row in the HOSPITAL table with the following data:- (11,
“Mustafa”, 37, “Ent”, “1998-02-25”, 250, “IM”)
r. Give the output of the following SQL statements based on the above table-
Hospital:-
39
Q25. SQL queries based on the following two tables- Activity, Coach:
Acode ActivityName Stadium ParticipantNum PrizeMoney ScheduleDate
1001 Relay 100*4 Star Annex 16 10000 24/01/2001
1002 High Jump Star Annex 10 12000 12/12/2003
1003 Shot Put Super Power 12 8000 14/02/2004
1005 Long Jump Star Annex 12 9000 01/01/2004
1008 Discuss Thow Super Power 10 15000 19/03/2004
40
.
Pcode Name Acode
1 Ahmed Hussain 1001
2 Ravinder 1008
3 Janila 1001
4 Naaz 1003
a. Display the Names of all the activities with their codes in descending order.
41
b. Display the sum of Prizemoney for the activities played in each of the stadium
separately.
42
c. Display the content of Activity table whose ScheduleDate is earlier than
01/01/2004 inascending order of participantsNum.
d. Display the names activity and its corresponding coach name.
e. Give the output of the following queries.
43
44