Structured Query Language (4 Marks Questions)

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;