Page 36 - IPP-12-2024

Section D

31. Consider the following table named “Student”: [4]
RollNo Name Marks Grade Fees Stream
1 Mishra 30 C 6000 Commerce
2 Gupta 48 B 15000 Arts
3 Khan 66 A 4800 Science
4 Chaddha 24 C 12500 Commerce
5 Yadav 23 A 10000 Arts
Write the SQL functions which will perform the following operations:
(i) To show the sum of fees of all students.
(ii) To display maximum and minimum marks.
(iii) To count different types of grades available.
(iv) Write a query to count grade-wise total number of students.
32. (i) Consider the dataframe “EMP”: [3+1=4]

Name Basic Da Hra
E1 Sanya 9500 3000 2000
E2 Krish 7000 5000 1900
E3 Rishav 9650 1500 2100
E4 Deepak 7500 2000 2700
E5 Kriti 9200 1800 500
Give the output

EMP.iloc[1,2]=8000
EMP.Hra=EMP.Hra+200
print(EMP)
(ii) Write a Python statement to change the name ‘Rishav’ to ‘Rishab’ in the above dataframe.
Or
(Option for part ii only)
Write a Python statement to calculate the sum of Basic, Da, and Hra and assign it to the column ‘Salary’.

Section E

33. Consider the following table named “GARMENT”. [5]
Table: GARMENT
GCODE GNAME SIZE COLOUR PRICE
111 T-Shirt XL Red 1400.234
112 Jeans L Blue 1600.123
113 Skirt M Black 1100.65
115 Trousers L Brown 1500.50
116 Ladies Top L Pink 1200.25
Write SQL queries using SQL functions to perform the following operations:
(i) Display the name and price after rounding off to one decimal place.
(ii) Display all Gname in upper case.
(iii) Display the last three characters from Gname.
(iv) Display the highest Gcode from the table GARMENT.
(v) Display the sum of all prices of size ‘L’.
Or

Appendices A.39