Question -
| 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 :
1. PERKM is FreightCharges per Kilometer.
2. VTYPE is VehicleType.
| 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 | 350 | 102 | 42 |
| 102 | John Fen | 2016-02-13 | 90 | 102 | 40 |
| 107 | Ahmed Khan | 2015-01-10 | 75 | 104 | 2 |
| 104 | Raveena | 2016-05-28 | 80 | 105 | 4 |
| 106 | Kripal Anya | 2016-02-06 | 200 | 101 | 25 |
Note:
· NO is Traveller Number
· KM is KilometerTravelled
· NOP is number oftravellers travelled in vehicle.
· TDATE is Travel Date
1. To display NO, NAME,TDATE from the table TRAVEL in descending order of NO.
2. To display the NAME ofall the travellers from the table TRAVEL who are travelling by vehicle withcode 101 or 102.
3. To display the NO andNAME of those travellers from the table TRAVEL who travelled between ‘2015-1231’and ‘2015-04-01’.
4. To display all thedetails from table TRAVEL for the travellers, who have travelled distance morethan 100 KM in ascending order of NOP .
5. SELECT COUNT (*), CODEFROM TRAVEL GROUP BY CODE HAVING COUNT(*)>1;
6. SELECT DISTINCT CODEFROM TRAVEL;
7. SELECT A. CODE,NAME,VTYPE
FROM TRAVEL A,VEHICLEB
WHERE A.CODE=B.CODE AND ‘KM<90;
8.SELECT NAME, KM*PERKM
FROM TRAVEL A,VEHICLE B
WHERE A.CODE=B. CODE AND A.CODE=’105’;
Answer -
1. Select NO,Name, TDATE from TRAVEL order by NO desc
2. Select NAME fromTRAVEL, where CODE in (101, 102)
3. Select NO, NAME fromTRAVEL where TDATE between ’2015-12-31′ and ‘2015-04-01’.
4. Select * from TRAVELwhere KM > 100 order by NOP.
5.
| COUNT (*) | CODE |
| 2 | 101 |
| 2 | 102 |
6.
| DISTANCE (CODE) |
| 101 |
| 103 |
| 102 |
| 104 |
| 105 |
7.
| CODE | NAME | VTYPE |
| 104 | Ahmed khan | CAR |
| 105 | Raveena | SUV |
8.
NAME KM*PERKM
Tarun Ram 14000