Page 8 - IPP-12-2024

(i) SELECT CID, CNAME FROM COURSE WHERE MONTH(STARTDATE)=7;
(ii) SELECT ROUND(12/100*FEES, 2) FROM COURSE WHERE CID=”C206”;
(iii) SELECT MID(CNAME, 3, 2) FROM COURSE WHERE LENGTH(CNAME)= 4;
Ans. (i) CID CNAME
C201 PGDCA
C206 O LEVEL
(ii) Round(12/100*FEES, 2)
2228.04
(iii) MID(CNAME,3,2)
CA
TP
27. WAP to create a dataframe namely aid that stores the aid by NGOs for different states as follows: [3]
toys books uniform shoes
Andhra 7916 6189 610 8810
Odisha 8508 8208 508 6798
M.P. 7226 6149 457 9611
U.P. 7617 6157 611 6457
Ans. t = {'Andhra':7916,'Odisha':8508,'M.P.':7226,'U.P':7617}
b = {'Andhra':6189,'Odisha':8208,'M.P.':6149,'U.P':6157}
u = {'Andhra':610,'Odisha':508,'M.P.':457,'U.P':611}
s= {'Andhra':8810,'Odisha':6798,'M.P.':9611,'U.P':6457}
state = {'toys': t, 'books':b,'uniform':u,'shoes':s}
df = pd.DataFrame(state)
print('dataframe of NGOs aid')
print(df)
Or
import pandas as pd
data = {
'toys': [7916, 8508, 7226, 7617],
'books': [6189, 8208, 6149, 6157],
'uniform': [610, 508, 457, 611],
'shoes': [8810, 6798, 9611, 6457]
}
aid = pd.DataFrame(data, index=['Andhra', 'Odisha', 'M.P.', 'U.P.'])
print('dataframe of NGOS aid')
print(aid)
28. Consider the “EMPLOYEE” table in MySQL and write suitable SQL queries for the following: [3]

Ecode Ename Address Dojoin Amount Area
100 Amit Delhi 2017/09/29 5000.90 East
101 Sushant Gurgaon 2018/01/01 7000.75 East
102 Priya Noida 2018/04/25 3450.45 West
103 Mohit Delhi 2018/11/03 6000.50 North
104 Priyanshi Delhi 2019/12/15 8000.62 North
(i) Display the highest amount of each area.
(ii) Display address-wise number of employees.
(iii) Display the sum of the entire amount in the year 2018.
Or
Discuss the significance of where and having clause in detail with the help of a suitable example.

Ans. (i) Select area, max(amount) from employee group by area;
(ii) Select address,count(*) from employee group by address;
(iii) Select sum(amount) from employee where year(Dojoin) = 2018;

A.30 Informatics Practices with Python–XII