Question

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

Table VEHICLE

VCODE	VEHICLETYPE	PERKM
V01	VOLVO BUS	150
V02	AC DELUXE	125
V03	ORDINARY BUS	80
V05	SUN	30
V04	CAR	18

Table: TRAVEL

CNO	CNAME	TRAVELDATE	KM	VCODE	NOP
101	K. Niwal	2015-12-13	200	V01	32
103	Fredrick Sym	2016-03-21	120	V03	45
105	Hitesh Jain	2016-04-23	450	V02	42
102	Ravi Anish	2016-01-13	80	V02	40
107	John Malina	2015-02-10	65	V04	2
104	Sahanubhuti	2016-01-28	90	V05	4
106	Ramesh Jaya	2016-04-06	100	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 vechicle with code Vo1 or Vo2

3. To display the CNO and CNAME of those customers from the table TRAVEL who travelled between ‘2015-1231’ 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

5. SELECT COUNT (*), VCODE FROM TRAVEL GROUP BY VCODE HAVING COUNT (*) > 1;

6. SELECT DISTINCT VCODE FROM TRAVEL :

7. SELECT A.VCODE, CNAME, VEHICLETYPE FROM TRAVEL A, VEHICLE B WHERE A. VCODE = B. VCODE and KM < 90;

8. SELECT CNAME, KM*PERKM FROM TRAVEL A, VEHICLE B WHERE A.VCODE = B.VCODE AND A. VCODE ‘V05’;

Answer 1

Output for SQL queries: