Question:
Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii), which are based on the tables
Table: VEHICLE
CODE | VTYPE | PERKM |
---|---|---|
101 | VOLVO BUS | 160 |
102 | AC DELUXE BUS | 150 |
103 | ORDINARY BUS | 90 |
105 | SUV | 40 |
104 | CAR | 20 |
Note:
– PERKM is Freight Charges per kilometer
– VTYPE is Vehicle Type
Table: TRAVEL
NO | NAME | TDATE | KM | CODE | NOP |
---|---|---|---|---|---|
101 | Janish Kin | 2015-11-13 | 200 | 101 | 32 |
103 | Vedika Sahai | 2016-04-21 | 100 | 103 | 45 |
105 | Tarun Ram | 2016-03-23 | 250 | 102 | 42 |
102 | John Fen | 2016-02-13 | 90 | 102 | 40 |
107 | Ahmed Khan | 2015-01-10 | 75 | 104 | 2 |
104 | Raveen | 2016-05-28 | 80 | 105 | 4 |
106 | Kripal Anya | 2016-02-06 | 200 | 101 | 25 |
Note:
– NO is Traveller Number
- KM is Kilometer travelled
– NOP is number of travellers travelled in vehicle
- TDATE is Travel Date
(i) To display NO, NAME, TDATE from the table TRAVEL in descending order of NO.
(ii) To display the NAME of all the travellers from the table TRAVEL who are traveling by vehicle with code 101 or 102.
(iii) To display the NO and NAME of those travellers from the table TRAVEL who
travelled between ’2015-12-31′ and ’2015-04-01’.
(iv) To display all the details from table TRAVEL for the travellers, who have
travelled distance more than 100 KM in ascending order of NOP.
(v) SELECT COUNT(*),CODE FROM TRAVEL
GROUP BY CODE HAVING COUNT(*)>1;
(vi) SELECT DISTINCT CODE FROM TRAVEL;
(vii) SELECT A.CODE,NAME,VTYPE
FROM TRAVEL A,VEHICLE B
WHERE A.CODE=B.CODE AND KM<90;
(viii) SELECT NAME,KM*PERKM
FROM TRAVEL A,VEHICLE B
WHERE A.CODE=B.CODE AND A.CODE=’105’;
CSKC| Created: 10-Jan-2019 | Updated: 10-Jan-2019|CBSE12D-2016