Page 224 - Informatics_Practices_Fliipbook_Class12
P. 224

(iv)  To display the Category, house name and house captain of athletes that are participating in 100m.
              (v)  To display gender wise count of athletes.
              (vi)  To display cartesian product of the two tables.

         Ans.  (i)  SELECT Name, HouseName
                  FROM Athlete A, House H
                  WHERE A.HouseId=H.HouseId;
              (ii)  SELECT HouseName, HouseMaster
                  FROM Athlete A, House H
                  WHERE A.HouseId=H.HouseId
                  AND Category="Senior";
              (iii) SELECT Category, COUNT(*)
                  FROM Athlete
                  GROUP BY Category;
              (iv) SELECT Category, HouseName, HouseCaptain
                  FROM Athlete A, House H
                  WHERE A.HouseId=H.HouseId
                  AND (Event1="100m" OR Event2="100m");
              (v)  SELECT GENDER, COUNT(*)
                  FROM Athlete
                  GROUP BY GENDER;
              (vi) SELECT * FROM Athlete, House;
           2.  Qala is a watch dealer and runs a store named 'Timepiece'. She wants to keep a record of all watches that are for sale in her
              shop. At the same time, she also wants to keep a record of all her customers. As a database expert, you have created two
              tables - Watches and Customers and have also inserted records as per the data provided by Qala.
               Table: Watches

               WatchId        WName           Price          Type            Brand           YOP
               T001           Apple iWatch    56000          Smart watch     Apple           2020-12-12
               T002           Titan Raga      25673          Designer        Titan           2015-04-15
               T003           NeoSplash       34908          Sports Watch    Neo Sports      2018-11-09
               T005           G-Shock         13892          Sports Watch    Casio           2021-03-18
               T006           Max Pro         15673          Designer        Maxima          2014-06-03
               T007           Zoop            8987           Kids            Titan           2019-03-18

               Table: Timepiece_Customers
              CID                     CName                  ConatctNo               WatchId
              101                     Sulekha                9090909090              T002
              103                     Jonathan               8888899999              T006
              104                     Gurvinder                                      T001
              105                     Pradipto               7676767634              T005
              Qala wants to retrieve specific data from the database. As per the given tables, write queries for each of the following:
              (i)  To display the Customer name and the watch name for all customers.
              (ii)  To display names of all customers who have purchased a watch of brand Apple.
              (iii)  To display the names of the brand in reverse alphabetical order.
              (iv)  To display the minimum price and latest purchase date of each brand.
              (v)  To increase the price of each watch by 10%.
              (vi)  To delete the record of customer whose contact no. is not known.


          210  Touchpad Informatics Practices-XII
   219   220   221   222   223   224   225   226   227   228   229