Page 280 - PYTHON-12
P. 280
Note:
• NO is Traveller Number
• KM is Kilometres travelled
• NOP is number of travellers in vehicle
• TDATE is Travel Date
(a) To display NO, NAME, TDATE from the table TRAVEL in descending order of NO.
Ans. SELECT NO, NAME, TDATE FROM TRAVEL ORDER BY NO DESC;
(b) To display the NAME of all the travellers from the table TRAVEL who are travelling by
vehicle with code 101 or 102.
Ans. SELECT NAME FROM TRAVEL
WHERE CODE=‘101’ OR
CODE=’102’; OR
SELECT NAME FROM TRAVEL
WHERE CODE=101 OR
CODE=102;
(c) To display the NO and NAME of those travellers from the table TRAVEL who travelled
between ‘2015–12–31’ and ‘2016–04–01’.
Ans. SELECT NO, NAME from TRAVEL
WHERE TDATE >= ‘20160401’ AND TDATE <= ‘20151231’;
OR
SELECT NO, NAME from TRAVEL
WHERE TDATE BETWEEN ‘20160401’ AND ‘20151231’;
(d) To display the CODE, NAME, VTYPE from both the tables with distance travelled (Km)
less than 90Km.
Ans. SELECT A.CODE, NAME,
VTYPE FROM TRAVEL A,
VEHICLE B
JOIN Operation using
WHERE A.CODE=B.CODE AND KM<90; Table alias names
(e) To display the NAME and amount to be paid for vehicle code as 105. Amount to be paid is
calculated as the product of KM and PERKM.
Ans. SELECT NAME,
Computer Science with Python–XII 12.50 12.19 UNION
KM*PERKM FROM TRAVEL A,
VEHICLE B
WHERE A.CODE=B.CODE AND A.CODE=‘105’;
The UNION operator is used to combine the result-set of two or more SELECT statements. The
UNION operation is used to return all the distinct rows selected by either query. For executing
Union between two tables, the number of columns selected from each table should be the same.
Also, the datatypes of the corresponding columns selected from each table should be the same.