CBSE XII Informatics Practices 065 Practical Question Paper Set 4 – Download Word Document

Answer Key Set – 4

Q1. Problem Solving using PANDAS & MATPLOTLIB                 [5 + 3 = 8]

1. Consider the following DataFrame df and answer the given questions (i)- (v)

Rollno             Name                           UT1     UT2     UT3     UT4

1                      Prerna Singh                 24        24        20        22

2                      Manish Arora               18        17        19        22

3                      Tanish Goel                  20        22        18        24

4                      Falguni Jain                  22        20        24        20

5                     Kanika Bhatnagar         15        20        18        22

(i) Write the command to print top two rows.

(ii) Write the command to print last two rows.

(iii) Write the command to add one more column in dataframe df name total. Total = UT1+UT2+UT3+UT4

(iv) Write the command to display the rows having roll no is 4

(v)  Write the command to display the column labels of the DataFrame.

Answer: 

#Question-1.a)

#Command to create given DataFrame

import pandas as pd

import numpy as np

RollNo = [1,2,3,4,5]

Name = [‘Prema Singh’, ‘Manish Arora’, ‘Tanish Goel’, ‘Falguni Jain’, ‘Kanika Bhatnagar’]

UT1 = [24,18,20,22,15]

UT2 = [24,17,22,20,20]

UT3 = [20,19,18,24,18]

UT4 = [22,22,24,20,22]

data = {‘Rollno’:RollNo, ‘Name’:Name,

         ‘UT1’:UT1, ‘UT2’:UT2,

         ‘UT3’: UT3, ‘UT4’: UT4 }

df = pd.DataFrame(data)

print(df)

Output:

   Rollno              Name            UT1  UT2  UT3  UT4

0       1       Prema Singh             24   24   20   22

1       2      Manish Arora            18   17   19   22

2       3       Tanish Goel             20   22   18   24

3       4      Falguni Jain               22   20   24   20

4       5  Kanika Bhatnagar         15   20   18   22

(i) Write the command to print top two rows.

print(“\n\n#i. Command to print top two rows\n\n”)

print(df.head(2))

Output:

#i. Command to print top two rows

   Rollno          Name  UT1  UT2  UT3  UT4

0       1   Prema Singh   24   24   20   22

1       2  Manish Arora   18   17   19   22

(ii) Write the command to print last two rows.

print(“\n\n#ii. Command to print last two rows\n\n”)

print(df.tail(2))

Output:

#ii. Command to print last two rows

   Rollno              Name  UT1  UT2  UT3  UT4

3       4      Falguni Jain   22   20   24   20

4       5  Kanika Bhatnagar   15   20   18   22

(iii) Write the command to add one more column in dataframe df name total. Total = UT1+UT2+UT3+UT4

print(“\n\n#iii. command to add one more column in dataframe df name total. Total = UT1+UT2+UT3+UT4 \n\n”)

df[‘Total’] = df[‘UT1’] + df[‘UT2’] + df[‘UT3’] + df[‘UT4’]

print(df)

Output:

#iii. command to add one more column in dataframe df name total. Total = UT1+UT2+UT3+UT4

    Rollno              Name  UT1  UT2  UT3  UT4  Total

0       1       Prema Singh   24   24   20   22     90

1       2      Manish Arora   18   17   19   22     76

2       3       Tanish Goel   20   22   18   24     84

3       4       Falguni Jain   22   20   24   20     86

4       5  Kanika Bhatnagar   15   20   18   22     75

(iv) Write the command to display the rows having roll no is 4

print(“\n\n#iv. command to display the rows having roll no is 4\n\n”)

print(df[df[‘Rollno’] == 4])

Output:

#iv. command to display the rows having roll no is 4

      Rollno          Name  UT1  UT2  UT3  UT4  Total

3       4      Falguni Jain   22   20   24   20     86

(v)  Write the command to display the column labels of the DataFrame.

print(“\n\n#v. command to display the column labels of the DataFrame.\n\n”)

print(df.columns)

Output:

#v. command to display the column labels of the DataFrame.

Index([‘Rollno’, ‘Name’, ‘UT1’, ‘UT2’, ‘UT3’, ‘UT4’, ‘Total’], dtype=’object’)

B. Draw the following bar graph representing the number of students in each class.

• Set the appropriate label to x-axis and y-axis.
• Set the title of Graph “Number of Students”

Answer:

#Que No: 1 (b)

import matplotlib.pyplot as plt

Group = [‘VII’,’VIII’, ‘IX’, ‘X’]

No_Students = [40, 45, 35, 45]

plt.bar(Group, No_Students)

plt.xlabel(“Class”)

plt.ylabel(“Students”)

plt.title(“Numbers of Students in Class”)

plt.show()

Output:

Q2. SQL Queries:- Write the SQL Commands to perform the following:-                                                 [7]

(i) Create a student table with the student_id, name, and marks as attributes where the student_id is the primary key.

(ii) Insert the details of a new student in the above table.

(iii) Delete the details of a student in the above table.

(iv) Use the select command to get the details of the students with marks more than 80.

(v) Write a SQL query to display names into capital letters, small letters, display first 3 letters of name, display last 3 letters of the name, display the position the letter ‘A’ in name.

(vi) Remove extra spaces from left, right and both sides from the text – “Informatics Practices Class XII”.

(vii) Display dayname, monthname, day, day of month, day of year for today’s date.

Answer

(i)         CREATE TABLE STUDENT (

STUDENT_ID INTEGER PRIMARY KEY,

NAME VARCHAR(30),

MARKS DECIMAL(10,2) );

(ii)       INSERT INTO STUDENT VALUES (125, ‘ANJEEV SINGH’, 98.5);

(iii)      DELETE FROM STUDENT;

(iv)       SELECT * FROM STUDENT WHERE MARKS > 80 ;

(v)        SELECT UPPER(NAME), LOWER(NAME), LEFT(NAME, 3), RIGHT(NAME, 3) INSTR(NAME, ‘A’) FROM  STUDENT;

(vi)       SELECT LTRIM(“   Informatics Practices Class XII    ”), RTRIM(“   Informatics Practices Class XII    ” ), TRIM(“   Informatics Practices Class XII    ” );

(vii)      SELECT DAYNAME(CURDATE( )), MONTHNAME(CURDATE( ) ),  DAY(CURDATE( )), DAYOFMONTH( CURDATE( )), DAYOFYEAR (CURDATE( ) ) ;