The Emerald Heights International School

PACTICAL RECORD FILE

Subject – Informatics Practices
Class - XII
Subject Code – 065
Session – [2023 – 24]

Name:
Class and Section:

1|Page
INDEX:

SNO PRACTICAL PROBLEM DATE

1 Series operations
2 Creating a dataframe using dictionary and finding sum of salary.
3 Creating a dataframe using list and filtering records.
4 Creating dataframe from nested list with appropriate headings
5 Dataframe operations on the student result data
6 Applying sorting on a dataframe.
7 Display data from a series by applying condition on index values.
8 Replacing all negative values with 0 in a dataframe column
9 Applying multiple conditions on a column of a dataframe
10 Removing records based on a condition from a dataframe.
11 Dataframe operations on dataframe with repeated index values
12 Dataframe operations
13 Deleting records from a series based on given condition.
14 Calculating result (Total, Percentage, and Grade) on a dataframe.
15 Dataframe operations – Inserting / Deleting rows / columns
16 Handling NaN values in a dataframe
17 Creating a simple bar chart from a dataframe
18 Creating multiple bars chart from a dataframe
19 Creating histogram.
20 Creating multiple lines chart with different styles and properties
21 MySQL Queries – Question 1
22 MySQL Queries – Question 2

2|Page
1. Write python code to create the following series ‘Result’ using list and using dictionary.
Raj 99
Ajay 50
Vijay 75
Gourav 85
Tina 55
Amay 35
Aakash 40

Now write code for following:

a. Change marks of Gourav to 87
b. Display details of students who have got marks more than 70.
c. Display names of students who have got marks less than 70.
d. Display maximum, minimum and average marks.
e. Display first four records.
f. Display last five records.
g. Delete record of Aakash
h. Insert a new student as ‘Neeta’ with marks as 95.
i. Display the series in descending order of marks.
j. Decrease the marks of each student by 2.

2. Create the following dataframe ‘EMP’ using a dictionary.

Eno Month Salary
A 1 Jan 90000
B 2 Jan 80000
C 1 Feb 95000
D 3 Jan 50000
E 2 Feb 82000
F 1 Feb 95000
G 3 Feb 60000

Now display the sum of salary of employee no 1.

3|Page
3. Create the following dataframe using list:
Rno Name Marks
A 10 Raj 50
B 20 Ajay 60
C 30 Minu 80
D 40 Raj 35
E 50 Aakash 90
Now display records of students who have got marks more than 70.

4. Write a python code to create a dataframe with appropriate headings from the list given below
:
[10, 'Raj’, 70], [20, 'Ajay', 69], [30, 'Vijay', 75], ['40', 'Tina', 99]

5. Consider a dataframe DF given as:

Rno Name Marks Class
A 10 Raj 88 XI
B 20 Ajay 89 XII
C 30 Minu 55 XI
D 40 Raj 66 XII
E 50 Aakash 77 XI

Write Python code to:

a. Display Marks of minu using at[] and iat[].

b. Display records of last three rows
c. Display Rollno, marks and class of rows A to D using loc[].
d. Display names for rows A, C and E
e. Display Rollno and class of rows A and E.
f. Change the marks of Ajay from 89 to 90
g. Insert a new column Grade with all values NaN in it.
h. Insert a new record at rowindex F as:
[60, Tina, 98, XII]
i. Create a new DataFrame DF2 and store Roll numbers and names from DF in it.
j. Add +1 to the marks of each student.
k. Display name and class of second, third and fourth rows using iloc[].

6. Consider a dataframe “DF” given as:

Rno Name
A 10 Raj
B 20 Ajay
C 30 Vijay
D 40 Gourav

4|Page
Write python code to arrange this dataframe in ascending order of names. The changes should
be saved in the original dataframe.

7. Assume a series s is given as:

Indore 10

Bhopal 50

Indore 5

Mumbai 60

Bhopal 90

Mumbai 75

Write python code to display sum of Indore City.

8. Write a program that creates a dataframe that looks like following:

Rno Name Points

0 10 Raj 2
1 20 Ajay 1
2 30 Minu -1
3 40 Tina 1
4 50 Aakash -2

Now as we can see there are negative values in Points column. The program should replace all
the negative values with 0. The resulting output should be:

Rno Name Points

0 10 Raj 2
1 20 Ajay 1
2 30 Minu 0
3 40 Tina 1
4 50 Aakash 0

5|Page
9. Write a program that creates a dataframe that looks like following:

Rno Name Marks

0 10 Raj 88
1 20 Ajay 99
2 30 Minu 90
3 40 Tina 66
4 50 Aakash 70

Now print only the records of students who have got marks more than or equal to 70 but less
than 95.

10. Given a dataframe df as:

Rno Name Stream Marks

0 10 Raj Science 88
1 20 Ajay Commerce 99
2 30 Minu Arts 55
3 40 Tina Science 66
4 50 Aakash Arts 77

Write python code for removing the records of students of Arts stream.

11. Consider a dataframe ‘colors’ given as:

Color Count Price
Apple Red 3 120
Apple Green 9 110
Pear Red 25 125
Pear Green 26 150
Lime Green 99 70

Write Python code for following:

i. Insert a new record with index ‘Coco’ with following details:

Color = ‘Red’, Count = 5, Price = 90
ii. Increase the price by 10% (The Price column should be updated.)
iii. Add a new column ‘Amount’ to the dataframe. This column should store Amount as
Count * Price.
iv. Display first three records.

6|Page
12. Write program to create and display the following dataframe (df) and to perform the below
given operations.

Item Company Rupees USD

0 TV LG 12000 700
1 TV VIDEOCON 10000 650
2 PC LG 15000 800
3 AC SONY 14000 750

1. To change the index values as ‘A’, ‘B’, ‘C’ and ‘D’.

2. To display sum of USD and Rupees columns.
3. To delete the USD column

13. Assume a series s is given as:

Indore 10
Bhopal 50
Indore 5
Mumbai 60
Bhopal 90
Mumbai 75

Write python code to delete Entries of Mumbai.

14. Consider a DataFrame DF is given as:

Rno Name SemI SemII

A 10 Raj 50 40
B 20 Ajay 60 50
C 30 Minu 80 70
D 40 Raj 70 50
E 50 Aakash 90 80

Write python code to add three new columns – ‘Total’, ‘Percent’ and ‘Grade’. These columns
should contain following data:

The Total column should store total marks as SemI + SemII

- The Percent column should store the percentage based on total marks obtained.(Total
marks out of 200)
- The Grade column should store Grades as ‘A’ or ‘B’ as per following conditions:
 If percentage is > 70 then ‘A’ Grade otherwise ‘B’ Grade
7|Page
15. Create a dataframe ‘result’ given as:
Rno Names Marks
0 10 Raj 50
1 20 Ajay 60
2 30 Vijay 85
3 40 Gourav 80
4 50 Tina 90

Write python code for following:

1. Add 5 to all marks
2. Delete the third row.
3. Delete the column Marks
4. Insert a new column City with all values NaN in it.

16. Create a dataframe df as:

Rno Name Stream Marks
0 10 Raj Science 88
1 20 Ajay Commerce 99
2 30 Minu Arts NaN
3 40 Tina Science NaN
4 50 Aakash NaN 77
Write python code for following:
a. Display total number of NaN values in each row.
b. Display total number of NaN values in each column.
c. Display total number of NaN values in the dataframe.
d. Fill all NaN values in Marks column with value 0.
e. Delete the record that contains NaN values.

8|Page
17. Create a dataframe containing all records of the table. The dataframe may look like
following:

Now create a bar chart showing the performance of each student. Take names on x axis and
Marks on Y axis. Also use proper headings for chart and axis. The output should look like
following:

9|Page
18. Consider the data given below:
App Name App Price in Rs Total Downloads (In thousands)
Angry Birds 100 197
Teen Titans 50 210
PUBG Mobile 200 414
Marvel Comics 150 196
Fun Run 25 272
WhatsApp 10 668
Temple Run 170 420
Google Duo 70 110

Using the above data, plot a bar chart that plots multiple bars for both App price and Total
downloads.

19. Write python code to draw following histogram:

Data given as:

Score = [8, 10, 15, 25, 28, 35, 47, 49, 50, 63, 67, 53, 57, 58, 69, 85, 83, 75, 95, 97, 87, 93, 98, 100]

10 | P a g e
 Also modify x axis values so that it shows difference of 10 between two bins.

20. The names of students and marks obtained in two semesters are given as:

name = ['Raj', 'Ajay', 'Vijay', 'Gourav', 'Tina']

sem1 = [95, 65, 65, 75, 87]
sem2 = [99, 75, 60, 79, 95]

Using this data, write python code to draw a chart which looks like following:

11 | P a g e
21. Consider the following table PREPAID. Write SQL Commands for (a) to (j)

CID Cname Model Connection ActivationDate Validity Amount

1 Sita Nokia Airtel 2004-06-04 365 3300
2 Ritesh Nokia Hutch 2005-02-12 60 3030
3 Reena Samsung Hutch 2004-05-14 365 3030
4 Meetali Nokia Indicom 2004-09-24 180 890
5 Ramanuj Samsung Idea 2004-10-16 180 900
6 Anupam LG Hutch 2005-01-18 60 600
7 Sai Ram Nokia Indicom 2004-12-10 180 890
8 Deepa LG Reliance 2005-03-08 30 300
9 Karan Sony Idea 2005-01-30 90 800
10 Jayant Siemen Airtel 2005-02-02 60 560
a. To display Cname and Amount of customers having “Samsung” model and “Hutch”
Connection, arranged in descending order of amount.
b. To display different types of connections available (names of companies providing
connections).
c. To display sum of validity for each type of connection grouping.
d. To display total amount in each connection except idea.
e. To count the available models from the table.
f. To display records where the name of customer contains exactly five letters.
g. To delete the record of Karan.
h. To count number of models used in each connection.
i. To display customer name and activation date for whom the activation done in the
month of January.
j. Display total amount of each connection where total amount is not greater than 3000.

22. Write SQL Commands for (a) to (j) based on the table CLUB.

CoachID CoachName Age Sports DateOfApp Pay Gender

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 JATIN 33 SWIMMING 1999-03-22 5000 M

5 VINAY 32 CRICKET 1995-11-01 3500 M

6 KIRAN 37 SWIMMING 1993-12-31 5000 F

7 AMIT 35 CHESS 1996-07-05 4000 M

12 | P a g e
 a. To show all information about the SWIMMING coaches in the club.
b. To list names of all coaches with their date of appointment in descending order.
c. To display coach name, pay, age and bonus (as 15% of pay) for all coaches.
d. To select the minimum age of female coaches.
e. To count how many employees working in each age group.
f. To display sports wise total pay only for those records where there are more than one
coaches.
g. Display records of coaches who have joined in the month of march.
h. Display coach name and date of appointment for the coaches who have joined after
the year 1996.
i. Display minimum and maximum pay for male and female coaches (use one select
statement).
j. Increase the pay of all female coaches by 5%.

13 | P a g e