Database Management – SQL — Computer Science STD 12 Science — 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

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	AhmedKhan	2015-01-10	75	104	2
104	Raveena	2015-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 travelling 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 ’;