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. Write the SQL commands for the i) to iv) on the basis of table TEACHER.
Table:TEACHER
No. Name Age Department Dateofadm Salary Sex
1 Jugal 34 Computer 10-01-1997 12000 M
2 Sharmila 31 History 24-03-1998 20000 F
3 Sandeep 32 Maths 12-12-1996 30000 M
4 Sangeeta 35 History 01-07-1999 40000 F
5 Rakesh 42 Maths 05-09-1997 25000 M
6 Shyam 50 History 37/06/98 30000 M
7 Shivam 44 Computer 25-02-1997 21000 M
8 Shalakha 33 Maths 31-07-1997 20000 F
• i) To show all information about the teacher of History department.
• ii) To display the list of all male teachers who are in Maths department.
• iii) To display Names, Age and Sex of all teachers with their date of admission in descending order.
• iv) To insert a new row in the TEACHER table with the following data:
9,’Raja’, 26,’Computer’, {13/05/95}, 23000,’M’
5. Write the output of the i) and iv) on the basis of table TEACHER given above
• i) Selectelect COUNT(DISTINCT department) from TEACHER;
• ii) Select MIN(Age) from TEACHER where SEX=’F’;
• iii) SELECT COUNT(*) FROM teacher GROUP BY sex ;
• iv) select * from teacher where Name like ‘S%’;
6. 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.
7. Consider the following tables Stationary and Consumer. Write SQL commands for the statement (i) to and output for SQL queries (v) to (viii):  [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 display the ConsumerName, Address from Table Consumer, and Company and Price from table Stationary, with their corresponding matching S_ID.
• (iv) 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 Consumer.ConsumerName, Stationary.StationaryName, Stationary.Price FROM Strionary, Consumer WHERE Consumer.S_ID=Stationary.S_ID;
• (viii) Select StationaryName, Price*3 From Stationary;