Question

Write SQL qureries for (i) to (iv) and find outputs for SQL queries (v) to (viii), which are based on the tables TRANSPORT and TRIE

Note:

• PERKS is Freight Charages per kilometer
• TTYPE is Transport Vehicle Type

Note:

• NO is Driver Number
• KM is Kilometer travelled
• NOP is number of travellers travelled in vehicle
• 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 COUnT (*) > 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 < 90;

8. SELECT NAME, KM *PERKM FROM TRIP A, TRANSPORT B WHERE A. TCODE = B. TCODE AND A. TCODE = 105′;

Answer 1

1. SELECT NO, NAME, TDATE FROM TRIP ORDER BY NO;

2. SELECT NAME FROM TRIP WHERE TCODE = 101 OR TCODE = 103;

3. SELECT NO AND NAME FROM TRIP WHERE ‘2015-02-10’ < TDATE < ‘2015-04-01’;

4. SELECT NO, NAME, TDATE, KM, TCODE FROM TRIP WHERE KM >100 ORDER BY NOP;

5. TO DISPLAY THE MORE THAN ONE COUNT OF TCODE FROM THE TABLE TRIP

6. TO DISPALY SEPERATE TCODE OF TABLE TRIP

7. TO DISPAY THE NAME AND CODE OF THOSE TRANS PORTERS, WHO HAVE TRAVELLED MORE THAN 90 KMS.

8. TO DISPLAY THE NAME AND EXPENDITARE OF A TRANSPORTER WHO HAVE TCODE AS 105.