Page 29 - IPP-12-2024

25. Observe the given tables named as “Client”, “Maintenance_Cost” and “Resultant”. Identify the SQL
operation applied on the table “Resultant”. Also write the SQL query for the same.
Table: Client Table: Maintenance_Cost Table: Resultant
ID NAME ID COST ID NAME COST
121 Kapil 126 5600 126 Manish 2400
126 Manish 134 2400 134 Puneet 7200
134 Puneet 180 7200
181 Prakash 133 3600
(2)
SECTION C
26. Based on the SQL table University_of_Melbourne, write suitable queries for the following:
Table: University_of_Melbourne

COURSE EXAMS_ACCEPTED DURATION NUMBER_OF_ FEES
(years) SCHOLARSHIPS (lakhs)
Data Science IELTS 1.5 2 29.87
Computer Science TOEFL 2 0 32.63
Industrial Leadership TOEFL 1.5 3 21.63
Information Technology IELTS 2 2 27.08
Management IELTS 1.5 1 25.28
(i) Display the courses with duration less than 2 years.
(ii) Display the record of courses in the ascending order of Fees.
(iii) Display the courses and the qualifying exam required for taking admission in the university for which
the fees is between 20 to 25 lakhs and number_of_scholarships is not equal to 0. (3)
OR

Predict the output of the following queries based on the table University_of_Melbourne given above:
(i) SELECT LEFT(COURSE,8) FROM University_of_Melbourne

WHERE EXAM_ACCEPTED = "TOEFL";
(ii) SELECT MIN(FEES) as "MINIMUM FEES" FROM University_of_Melbourne WHERE
EXAMS_ACCEPTED = "IELTS";
(iii) SELECT COUNT(COURSE), EXAMS_ACCEPTED FROM University_of_Melbourne
GROUP BY EXAM_ACCEPTED;
27. Create a DataFrame(df) in Python from the given list:
[["Mayur Vihar", "Semi-furnished", "1.0cr"], ["Saket", "Furnished","1.5cr"],
["Gurugram", "Furnished", "1.3cr"], ["Noida", "Unfurnished", "70lac"]]
Also give appropriate column headings as shown below:
Location Status Price
0 Mayur Vihar Semi-furnished 1.0cr
1 Saket Furnished 1.5cr
2 Gurugram Furnished 1.3cr
3 Noida Unfurnished 70lac (3)
28. Write MySQL statements for the following:
TABLE: CHOCOLATE_BOX

BOX_ID BOX_COLOUR COST
H123 RED 42
(i) To create a database named Chocolatiers.
(ii) To create a table given above named CHOCOLATE_BOX applying suitable constraints on the columns
of the table and inserting records for the same as shown above. (3)
A.4 Informatics Practices with Python–XII

CAMPUS ENGINERING
ADMIN BUSINESS