Page 223 - Informatics_Practices_Fliipbook_Class12
P. 223

Ans.  +------------------+-----------+
                    | Subject_Name     | Dept_Name |
                    +------------------+-----------+
                    | English          | Arts      |
                    | Chemistry        | Science   |
                    | Physics          | Science   |
                    | Business Studies | Commerce  |
                    | Economics        | Arts      |
                    | Accounts         | Commerce  |
                    +------------------+-----------+
                  c.  SELECT Student_Name, Subject_Name
                    FROM SUBJECT AS J, STUDENT AS S
                    WHERE J.Subject_No = S.Subject_No;
               Ans. +-----------------+------------------+
                    | Student_Name    | Subject_Name     |
                    +-----------------+------------------+
                    | Bhaskar Dhyani  | English          |
                    | Dakshinesh      | Chemistry        |
                    | Saumit Raj      | English          |
                    | Lovepreet Singh | Chemistry        |
                    | Aditya Jayant   | Business Studies |
                    +-----------------+------------------+

                  Case-based Questions


               1.  Kirti studies in class 11 and is a sports enthusiast. To promote co-curricular activities and sports, her school conducts
                  various competitions and events throughout the year. There are five houses, whose names have been inspired from the
                  rivers of India - Ganga, Jamuna, Saraswati, Narmada, and Jhelum. Each student is allocated one house. In the upcoming
                  Annual  Athletic  Meet  in  her  school,  various  inter-house  sports  events  will  be  held  and  she  will  be  representing  her
                  house-Saraswati. Kirti's favourite subject is Computer Science and as she has just studied databases, she wants to maintain
                  data of all athletes of her school in a table named Athletes. In another table, named House, she wants to store the
                  house details. She has created both the tables and has inserted records as shown below:
                  Table: Athletes
                     AthleteId   Name         Gender     Category    Event1            Event2     HouseId
                     A002        Anushka      Girl       Sub Junior  100m                         H01
                     A003        Prakhar      Boy        Senior      High Jump Boys    200m       H02
                     A004        Piyush       Boy        Senior      Relay             100m       H02
                     A005        Nyasa        Girl       Junior      Long Jump         Relay      H03
                     A007        Hamdaan      Boy        Sub Junior  100m              400m       H01
                     A008        Gurpurab     Boy        Junior      Long Jump Boys               H04
                     A009        Manasvi      Girl       Junior      Relay                        H05

                  Table : House
                     HouseID               HouseName             HouseMaster           HouseCaptain
                     H01                   Ganga                 Manju                 Aahana
                     H02                   Saraswati             Onkar                 Kabir
                     H03                   Jamuna                Kusum                 Manasa
                     H04                   Narmada               Sunil                 Prabhjot
                     H05                   Jhelum                Pratul                Soumya

                  Help Kirti to write the queries to perform the following tasks:
                  (i)  To display Athlete name and House name for all athletes.
                  (ii)  To display the house name and house master's name for all athletes of category, Seniors.
                  (iii)  To count the number of athletes for each category.

                                                                                     SQL: Working with Two Tables  209
   218   219   220   221   222   223   224   225   226   227   228