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.
   275   276   277   278   279   280   281   282   283   284   285