Page 310 - Information_Practice_Fliipbook_Class11
P. 310

a.  The PRICE relation has an attribute named Price. In order to avoid confusion, write SQL query to change the name of the relation
                PRICE to COST.
                                                             PRICE
                                                          UCode      Size          Price
                                                          1          M             500
                                                          1          L             580
                                                          1          XL            620
                                                          2          M             810
                                                          2          L             890
                                                          2          XL            940
                                                          3          M             770
                                                          3          L             830
                UNIFORM                                   3          XL            910

                                                          4          S             150
              UCode        UName         UColor
              1            Shirt         White            4          L             170
              2            Pant          Grey             5          S             180
              3            Skirt         Grey             5          L             210
              4            Tie           Blue             6          M             110
              5            Socks         Blue             6          L             140
              6            Belt          Blue             6          XL            160

               b. M/S Wonderful Garments also keeps handkerchiefs of red color, medium size of '100 each. Insert this record in COST table.
               c.  When you used the above query to insert data, you were able to enter the values for handkerchief without entering its details in the
                UNIFORM relation. Make a provision so that the data can be entered in COST table only if it is already there in UNIFROM table.
               d.  Further, you should be able to assign a new UCode to an item only if it has a valid UName. Write a query to add appropriate constraint
                to the SCHOOL_UNIFORM database.
               e. ALTER table to add the constraint that price of an item is always greater than zero.
         Ans.  a. ALTER TABLE Price
                RENAME TO Cost;
              b. INSERT INTO Cost
                VALUES(7, 'm', 100);
              c. ALTER TABLE Cost
                ADD FOREIGN KEY(UCode) REFERENCES UNIFORM(UCode);
              d. ALTER TABLE Uniform
                MODIFY UName VARCHAR(30) NOT NULL;
              e. ALTER TABLE Cost
                ADD CONSTRAINT CheckPrice
                CHECK(Price > 0);

                                                       Answers

          Multiple Choice Questions
          1. (c)      2. (c)     3. (b)     4. (a)      5. (d)     6. (b)     7. (c)      8. (a)     9. (d)
          10. (b)     11. (a)    12. (c)    13. (d)     14. (c)    15. (d)    16. (b)     17. (b)    18. (c)
          19. (a)     20. (c)    21. (c)    22. (c)     23. (d)    24. (b)    25. (b)     26. (a)    27. (b)
          28. (a)     29. (b)    30. (d)    31. (d)     32. (a)    33. (b)    34. (a)     35. (d)
          True or False
          1. (F)      2. (T)     3. (F)     4. (T)      5. (T)     6. (T)     7. (T)      8. (T)     9. (F)
          10. (T)     11. (F)    12. (T)    13. (F)     14. (T)
          Fill in the blanks
          1. attribute     2. Schema     3. degree     4. relation      5. composite key    6. domain
          7. structure     8. domain     9. 1          10. ; (semicolon)   11. DROP TABLE   12. ALTER TABLE


          296  Touchpad Informatics Practices-XI
   305   306   307   308   309   310   311   312   313   314   315