Page 226 - Informatics_Practices_Fliipbook_Class12
P. 226

Assessment


        A.  Multiple Choice Questions
           1.  Which of the following is used to enforce integrity of a database?
              a.  Candidate key        b. Alternate key       c. Foreign key         d. Primary key

           2.  Which of the following statements is true with respect to foreign key?
              a.  It should refer to primary key of another table
              b.  It can be NULL
              c.  Both a and b
              d.  Neither a nor b

           3.  While retrieving data from two tables, which of the following types of join removes the duplicate column that is common
              to both tables?
              a.  Equi join            b. Natural join        c. Same join           d. Unique join
           4.  Which of the following statements is NOT true about equi joins?
              a.  The columns joining the tables must have the same name in both tables.
              b.  The common attribute values appear only once in the output.
              c.  Specific columns from joining tables are checked for equality.
              d.  Values from joining tables are retrieved if the condition in the WHERE clause is satisfied.
        B.  State whether the following statements are True or False:
           1.  A DBMS will reject any attempt to insert data in a table that violates the foreign key constraint.   _________
           2.  A SELECT statement can be used to retrieve data from multiple tables.                    _________

           3.  Using a SELECT statement with multiple tables displays the cross-product of specified tables.    _________
           4.  It is mandatory to use aliases with multiple tables.                                     _________
           5.  In case of equi join, the column that is common to both tables is displayed only once.   _________
        C.  Fill in the blanks.
           1.  To retrieve information from two or more tables, the tables are joined on one or more common __________.
           2.  Equi join condition checks for __________ of the value of the attribute common to both tables.
           3.  A __________ key constraint is used to retrieve data from multiple tables.
           4.  A foreign key linking multiple tables is a __________ key in at least one of the linked tables.

        D.  Answer the following questions:
           1.  Describe the notion of referential integrity constraints with the help of  an example, clearing showing two tables and the
              foreign key involved. Write SQL statements to define the foreign key constraints.
           2.  What do you mean by natural join? Illustrate with the help of an example.
           3.  Give an example of an equi-join condition involving two tables.
        E.  Application Based Questions:
           1.  Using the tables STUDENT and SUBJECT from the STUDENT database given on Page no 184-185, write a SELECT
              statement for each of the following:
              (i)  For each student, retrieve his/her roll number and the  name of the subject that the student is studying.
              (ii)  For each student, retrieve his/her roll number, name, and department number of the department to which the student
                  belongs.
              (iii)  For each student, retrieve his/her roll number and student name for all the students who are studying Chemistry.
              (iv)  List the names of students who are studying Physics.
              (v)  For each subject, retrieve the subject name and the number of students who have taken that subject.
              (vi)  List subject name, student roll number, and student name for all the students and subjects.


          212  Touchpad Informatics Practices-XII
   221   222   223   224   225   226   227   228   229   230   231