Question 1: Write queries for (i) to (iv) and find guauts for SQL queries (v) to (viii), which are based on the tahlles VCODE VO1 PERKM 150 VO2 VEHICLETYPE VOLVO BUS AC DELUXE BUS ORDINARY BUS SUV 125 80 V03 V05 30 V04 CAR 18 NOP 32 45 Table : TRAVEL TRAVELDATE KM 2015-12-13 200 2016-03-21 120 2016-04-23 450 2016-01-13 80 2015-02-10 65 2016-01-28 90 2016-04-06 100 CNO CNAME 101 K. Niwal 103 Fredrick Sym 105 Hitesh Jain 102 Ravi Anish 107 John Malina 104 Sahanubhuti 106 Ramesh Jaya VCODE v01 V03 VO2 VO2 V04 V05 42 40 2 4 V01 25 Note: PERKS is Freight Charges per kilometer Km is kilometers Travelled NOP is number of passangers travelled in vechicle 1. To display CNO, CNAME, TRAVELDATE from the table TRAVEL in descending order of CNO. 2. To display the CNAME of all customers from the table TRAVEL who are travelling by vesbicle with code Vol or Vo2 3. To display the CNO and CNAME of those customers from the table TRAVEL who travelled between '2015-12-31' and '2015-05-01'. 4. To display all the details from table TRAVEL for the customers, who have travel distacne more than 120 KM in ascending order of NOE TIME DAYS SECTION STUDENT NAME ROOM DATE ID NO. 6. 5. SELECT COUNT(*), VCODE FROM TRAVEL GROUP BY VCODE HAVING COUNT(*)>1 SELECT DISTINCT VCODE FROM TRAVEL 7. SELECT A VCODE, CNAME, VEHICLETYPE FROM TRAVEL A, VEHICLE B WHERE A. VCODE = B. VCODE and KM 12 ORDER BY TEACHER; 4. SELECT COUNT(*), GENDER FROM AD-MIN GROUP BY GENDER; TABLE: SCHOOL CODE TEACHER SUBJECT DOJ PERIODS EXPERIENCE 1001 RAVI SHANKAR ENGLISH 12/3/2000 1009 PRIYA RAI PHYSICS 03/09/1998 12 1203 LIS ANAND ENGLISH 09/04/2000 1045 YASHRA MATHS 24/8/2000 1123 GANAN PHYSICS 16/7/1999 1167 HARISH B CHEMISTRY 19/10/1999 1215 UMESH PHYSICS 11/05/1998 24 10 26 27 5 24 15 28 3 27 5 22 16 CODE 1001 1009 1203 1045 1123 1167 1215 TABLE: ADMIN GENDER DESIGNATION MALE VICE PRINCIPAL FEMALE COORDINATOR FEMALE COORDINATOR MALE HOD MALE SENIOR TEACHER MALE SENIOR TEACHER MALE HOD Uab x, x? ADA v Paragraph Font LUUNL LUUL SECTION DUIVOS VUUNIL MILL TIME DAYS ROOM DATE STUDENT NAME ID NO. Question 3: Write SQL gurecies for (i) to (iv) and find outputs for SQL queries (v) to (viii), which are based on the tables TRANSPORT and TRIP PERKM 90 TCODE 103 105 104 40 TABLE: TRANSPORT TTYPE ORDINARY BUS SUV CAR ORDINARY BUS VOLVO BUS AC DELUXE BUS 20 90 103 160 101 102 140 Note: NO NOP TCODE 101 11 32 KM 200 100 350 13 103 45 15 TABLE: TRIP TDATE 2015-12-13 2016-06-21 2016-02-23 2016-01-13 2015-02-10 2016-06-28 2016-06-06 NAME Tanish Khan Danish Sahai Ram Kumar Fen Shen Aan Kumar Veena Rajpal Kirti 42 12 90 40 17 102 102 104 105 101 75 80 2 4 14 16 200 25 PERKS is Freight Charges per kilometre TTYPE is Transport Vehicle Type Note: NO is Driver Number KM is Kilometer travelled NOP is number of travellers travelled in vehicle. STUDENT NAME ID NO DATE TDATE is Trip Date 1. To display NO, NAME, TDATE from the table TRIP in descending order of NO. 2. To display the NAME of the drivers from the table TRIP who are traveling by transport vehicle with code 101 or 103. 3. To display the NO and NAME of those drivers from the table TRIP who travelled between 2015- 02-10' and 2015-04-01'. 4. To display all the details from table TRIP in which the distance travelled is more than 100 KM in ascending order of NOP 5. SELECT COUNT(*), TCODE From TRIP GROUP BY TCODE HAVNING COUOT (*)>1 6. SELECT DISTINCT TCODE from TRIP; 7. SELECT A TCODE, NAME, TTYPE FROM TRIP A, TRANSPORT B WHERE A. TCODE = B. TCODE AND KM
