Page 229 - Informatics_Practices_Fliipbook_Class12
P. 229

Case-based Questions


                  Aarushi owns a Performing Arts Studio in Delhi. In its two auditoriums, various theatre troupes stage their play/acts. She
                  wants to maintain a database to store data of all the stage shows in the Theatre table given below.
                  Table: Theatre


                   TCODE    TITLE                     AUDI   LANGUAGE    SHOWDATE     TICKET PRICE  TROUPID
                   T001     Akbar The Great Nahi Rahe   2    Hinglish    2023-05-12        150      R003
                   T002     Mughal-E-Azam               1    Hindi       2023-05-08        200      R001
                   T003     Romeo and Juliet            2    English     2023-05-09        125      R004
                   T004     The Lost Love               2    English     2023-05-10        100      R001
                   T005     Salaam Noni Appa            1    Hindi       2023-05-12        250      R002
                   T006     Gandhi The Untold Story     1    Hinglish    2023-07-13        375      R006
                  The details of each troupe is stored in the Troupe Table given below.
                  Table: Troupe

                  TROUPID                        TNAME                           CITY
                  R001                           Nattak Mela                     Nagpur
                  R002                           Dhara                           Delhi
                  R003                           OnStage                         Delhi
                  R004                           Reflection                      Mumbai
                  R005                           Beyond the Skies                Jaipur
                  R006                           Parivartan                      Mumbai

                  As a DBA, help Aarushi to retrieve the following data from the given tables in a MySQL database.
                  (i)  To view Audi no. and Troupe Name from tables Theatre and Troupe.
                  (ii)  To view troupe name, title of play and ticket price of the shows staged in Audi 1.
                  (iii)  To view count of plays in each language.
                  (iv)  To view the names of troupes in alphabetical order.
                  (v)  To view  the names of the plays and the troupes that are from Delhi, in chronological order.
                  (vi)  Aarushi wants to add another column to the table TROUPE with the following details:
                     Name of column- CONTACTNO
                     Data Type- string of size 10
                     Null Values allowed
                     Write the SQL statement to complete the task.


                                            NCERT Exercise Solutions


               1.  Answer the following questions:
                  a.  Define RDBMS. Name any two RDBMS software.
             Ans.   A DBMS that is used to manage Relational Databases is called RDBMS (Relational Database Management System). Some
                  popular RDBMS software available are: Oracle, MySQL.
                  b.  What is the purpose of the following clauses in a select statement?
                     i.  ORDER BY
                     ii.  HAVING




                                                                                     SQL: Working with Two Tables  215
   224   225   226   227   228   229   230   231   232   233   234