Page 227 - Informatics_Practices_Fliipbook_Class12
P. 227

2.  Using the  tables  DEPARTMENT and  SUBJECT  from  the  STUDENT database on page  no 184-185, write  a  SELECT
                  statement for each of the following:
                  (i)  For each department, list the department name and subjects offered by that department.
                  (ii)  For each department, list the department name  and the number of subjects offered by that department.
                  (iii)  List names of the subjects and the department number of the associated department  for the subjects for which
                     teachers have been currently hired.
               3.  Write SQL queries for (a) to (d) and write outputs for SQL queries  (i) and (ii) of part (e), which are based on the tables given
                  below:                                                                                       (2019)
                  TRAINS

                      TNO               TNAME                      START                      END
                     11096          Ahimsa Express              Pune Junction          Ahmedabad Junction
                     12015           Ajmer Shatabdi              New Delhi                Ajmer Junction

                      1651          Pune Hbj Special            Pune Junction               Habibganj
                     13005           Amritsar Mail             Howrah Junction           Amritsar Junction
                     12002          Bhopal Shatabdi              New Delhi                  Habibganj

                     12417         Prayag Raj Express         Allahabad Junction            New Delhi
                     14673          Shaheed Express               Jaynagar               Amritsar Junction
                     12314          Sealdah Rajdhani             New Delhi                   Sealdah
                     12498           Shan-e-Punjab            Amritsar Junction             New Delhi

                     12451        Shram Shakti Express         Kanpur Central               New Delhi
                     12030          Swarna Shatabdi           Amritsar Junction             New Delhi

                  PASSENGERS

                      PNR       TNO           PNAME           GENDER           AGE            TRAVELDATE

                      P001      13005       R N AGRAWAL        MALE             45            2018-12-25
                      P002      12015         P TIWARY         MALE             28            2018-11-10
                      P003      12015         S TIWARY        FEMALE            22            2018-11-10

                      P004      12030        S K SAXENA        MALE             42            2018-10-12
                      P005      12030         S SAXENA        FEMALE            35            2018-10-12
                      P006      12030        P SAXENA         FEMALE            12            2018-10-12
                      P007      13005        N S SINGH         MALE             52            2018-05-09

                      P008      12030        J K SHARMA        MALE             65            2018-05-09
                      P009      12030        R SHARMA         FEMALE            58            2018-05-09

                  NOTE : All Dates are given in 'YYYY-MM-DD' format.
                  (i)  To display details of all passengers travelling in train whose TNO is 12030
                  (ii)  To count the number of FEMALE passengers travelling in train whose TNO is 12030
                  (iii)  To display the names of the passengers along with the train names in which they are travelling.
                  (iv)  To display the date of travel for the train, 'Ajmer Shatabdi'.
                  (v)  Write the output of the following queries:
                     a.  SELECT TNAME, PNAME

                        FROM TRAINS T, PASSENGERS P
                        WHERE (T.TNO = P.TNO) AND (AGE BETWEEN 40 AND 50);
                                                                                     SQL: Working with Two Tables  213
   222   223   224   225   226   227   228   229   230   231   232