CBSE Class 11 & 12 Computer Science and Informatics Practices Python Materials, Video Lecture

1. Write SQL queries for (i) to (iv) and write outputs for SQL queries (v) to (viii), which are based on the table given below :

Write the Queries for: (1 each)
• a) Display the passengers Names (PNAME) who are in the train which has started from New Delhi.
• b) Find the average age of all the FEMALE Passengers.
Write the output of the following queries: (1/2 each)
• a) SELECT END, COUNT(*) FROM TRAINS GROUP BY END HAVING COUNT(*)>1;
• b) SELECT TNAME, PNAME FROM TRAINS T, PASSENGERS P WHERE T.TNO = P.TNO AND AGE BETWEEN 50 AND 60;
• c) SELECT DISTINCT TRAVELDATE FROM PASSENGERS;
• d) SELECT MAX (TRAVELDATE), MIN(TRAVELDATE) FROM PASSENGERS WHERE GENDER = 'FEMALE';
2. Consider the table TEACHER given below. Write commands in SQL for (i) to (iii) and output for (iv) to (v) .  Note: Hiredate is in mm/dd/yyyy format.
• To display all information about teachers of PGT category. (1)
• To list names, departments and date of hiring of all the teachers in descending order of date of joining. (1)
• To count the number of teachers and sum of their salary department wise. (1)
• SELECT MAX(Hiredate) FROM Teacher; (1/2)
• SELECT COUNT(DISTINCT(Department)) FROM Teacher; (1/2)
3. Write commands in SQL for (i) to (iv) and output for (v) and (vi).
```Table : Store
+---------+----------------+----------------+--------+---------+------------+---------+
| StoreId | Name           | Location       | City   | NoOfEmp | DateOpen   |SalesAmt |
+---------+----------------+----------------+--------+---------+------------+---------+
| S101    | Planet Fashion | Bandra         | Mumbai | 7       | 2015-10-16 | 40000   |
| S102    | Vogue          | Karol Bagh     | Delhi  | 8       | 2015-07-14 | 120000  |
| S103    | Trends         | Powai          | Mumbai | 10      | 2015-06-24 | 30000   |
| S104    | SuperFashion   | Thane          | Mumbai | 11      | 2015-02-06 | 45000   |
| S105    | Annabelle      | South Extn.    | Delhi  | 8       | 2015-04-09 | 60000   |
| S106    | Rage           | Defence Colony | Delhi  | 5       | 2015-03-01 | 20000   |
+---------+----------------+----------------+--------+---------+------------+---------+```
• To display names of stores along with SalesAmount of those stores that have ‘fashion’ anywhere in their store names.
• To display Stores names, Location and DateOfOpen of stores that were opened before 1st March, 2015.
• To display name and location of those store which have either ‘u’ as second character in their name.
• To display the City and the number of stores located in that City, only if number of stores is more than 2.
• Select Min(DateOpen) from Store;
• Select Count(Storeid), NoOfEmpfemp From Store Group By NoOfEmp Having Max(Salesamt)<60000;
4. Consider a table “Billboard_top_100” which lists top 100 songs of each year from 1990 to 2019. Columns in the table are: Title, Rank, Year, Artist. Write SQL queries for the following scenarios:
• Return all songs of 2012 with their ranks where song title is sorted from Z to A.
• Display songs that ranked between 10 and 20 (inclusive) in 1993, 2003, and 2013. Order the results by year and rank.
• Return the names of artists whose number of songs in Bulletin top 100 is more than 20.
5. Consider the following tables Stationary and Consumer. Write SQL commands for the statement (i) to and output for SQL queries (v) to (vii):  [6 Marks]
Customer Stationary
C_ID CustomerName Address S_ID SID StationaryName Company Price
01 Good Learner Delhi PL01 DP01 Dot ABC 10
06 Write Well Mumbai GP02 PL02 Pencil XYZ 6
12 Topper Delhi DP01 ER05 Eraser XYZ 7
15 Write & Draw Delhi PL02 PL01 Pencil CAM 5
16 Motivation Bangalore PL01 GP02 Gel ABC 15
• (i) To display the details of those consumers whose Address is Delhi.
• (ii) To display the details of Stationary whose Price is in the range of 8 to 15. (Both Value included)
• (iii) To increase the Price of all stationary by 2.
• (v) SELECT DISTINCT Address FROM Consumer;
• (vi) SELECT Company, MAX(Price), MIN(Price), COUNT(*) from Stationary GROUP BY Company;
• (vii) Select StationaryName, Price*3 From Stationary;