- Write SQL queries for (i) to (iv) and write outputs for SQL queries (v) to (viii), which are based on the table given below :
- 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.
- 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';
- 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)
- 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;
- 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’ - 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%’;
- 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.
- 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;
Write the Queries for: (1 each)
can i get the solution for the 7th one
ReplyDeleteverryyyyyyyy gooddddddddddddd
Delete