Page 299 - Information_Practice_Fliipbook_Class11
P. 299

3.  Abhishek has been assigned a project by his teacher on Railway Management. He has created the following two tables:
                  Table: Trains
                                TrainID       TrainName            Source            Destination
                                3457          Rajdhani             Delhi             Kolkata
                                1274          Jammu Express        Jammu             Delhi
                                3891          Shatabdi             Lucknow           Mumbai
                                6192          Jhelum Express       Amritsar          Mumbai
                                4072          Vande Bharat         Delhi             Trivandrum
                  Table: Passengers

                                    RefNo        TrainID        PassengerName     DOJ
                                    R112         3457           Surya             2021-03-12
                                    R981         1274           Manoj             2020-12-09
                                    R402         4072           NULL              2021-07-07
                                    R505         3457           Morjina           2020-01-23
               a.  From the tables given above, identify the following:
                  (i)  Primary key from both tables
                  (ii)  Foreign key in Trains table
                  (iii) Degree and Cardinality of both tables
               b.  Can the attribute TrainID in the table Passengers have the value 9999? Why/ why not?
               4.  Tia is in class 12 and is learning SQL. As the school is about to begin with the Annual Theatre Festival, her teacher has
                  asked her to create the following table in MySQL that will store the schedule of all plays that will be enacted during the
                  festival:
                  Table: THEATRE

                  TCODE      TITLE                     AUDI      LANGUAGE       SHOWDATE        TICKET PRICE
                  T001       Akbar The Great Nahi Rahe  2        Hinglish       2023-05-12      150
                  T002       Mughle-E-Azam             1         Hindi          2023-05-08      200
                  T003       Romeo and Juliet          2         English        2023-05-09      125
                  T004       The Lost Love             2         English        2023-05-10      100
                  T005       Salaam Noni Appa          1         Hindi          2023-05-12      250
                  (i)  Write the command to create the given table with TCODE as primary key. None of the fields should be left blank.
                  (ii)  Write the following SQL queries:
                     a.  To display the names of plays that will be staged in Audi 1.
                     b.  To display the records of those plays that have language as Hindi.
                     c.  To display the names of plays that have ticket price of more than 200.
                     d.  To display the count of plays that will be screened Hindi and English languages.
                     e.  To display the names of plays in alphabetical order.
                     f.  To add an attribute TROUPE to the table. The attribute is of type string OF SIZE 40  and cannot be left blank.
                     g.  To delete the records of those plays that are staged in Audi 1.


                                            NCERT Exercise Solutions


               1.  Give the terms for each of the following:
                  a. Collection of logically related records.
                  b. DBMS creates a file that contains description about the data stored in the database.
                  c. Attribute that can uniquely identify the tuples in a relation.
                  d. Special value that is stored when actual data value is unknown for an attribute.


                                                                Database Concepts and the Structured Query Language  285
   294   295   296   297   298   299   300   301   302   303   304