Page 300 - IT-802_class_12
P. 300

5.   Consider  a  table  PRODUCT  with  the  fields  –  PROD_ID,  PROD_NAME,  PROD_PRICE,  PROD_QTY.  Write  a  MySQL
             command to set the Quantity to 20 units for all products whose current quantity is 5 or less.

                         +------------+-------------+------+-----+---------+-------+
                         | Field      | Type        | Null | Key | Default | Extra |
                         +------------+-------------+------+-----+---------+-------+
                         | pROD_iD    | int         | yES  |     | NULL    |       |
                         | pROD_NAmE  | varchar(40) | yES  |     | NULL    |       |
                         | pROD_pRiCE | int         | yES  |     | NULL    |       |
                         | pROD_QTy   | int         | yES  |     | NULL    |       |
                         +------------+-------------+------+-----+---------+-------+

         Ans.  +---------+-----------+-------------+------------+
             | pROD_iD | pROD_NAmE | pRODUCT_QTy | pROD_pRiCE |
             +---------+-----------+-------------+------------+
             |    1001 | Shirt     |           5 |        250 |
             |    1002 | pant      |           3 |        500 |
             |    1003 | T-Shirt   |           2 |        200 |
             |    1004 | Lower     |           4 |        300 |
             |    1005 | Sweater   |           2 |        600 |
             |    1006 | Jacket    |           3 |       1250 |
             |    1007 | Cap       |          15 |        100 |
             +---------+-----------+-------------+------------+
             update PRODUCT set PRODUCT_QTY = 20 where PRODUCT_QTY <= 5;

             +---------+-----------+-------------+------------+
             | pROD_iD | pROD_NAmE | pRODUCT_QTy | pROD_pRiCE |
             +---------+-----------+-------------+------------+
             |    1001 | Shirt     |          20 |        250 |
             |    1002 | pant      |          20 |        500 |
             |    1003 | T-Shirt   |          20 |        200 |
             |    1004 | Lower     |          20 |        300 |
             |    1005 | Sweater   |          20 |        600 |
             |    1006 | Jacket    |          20 |       1250 |
             |    1007 | Cap       |          15 |        100 |
             +---------+-----------+-------------+------------+
          6.   Naveen wants to remove all the rows from STOCK table, but he does not want to remove the structure of the table.
             What MySQL statement can he use to do so?

         Ans. DELETE FROm STOCK;
          7.   Consider  the  table  DEPARTMENT  with  the  fields  –  DEPT_ID,  DEPT_NAME,  DEPT_LOCATION.  Write  a  query  which
             displays all the department locations.

                       +---------------+-------------+------+-----+---------+-------+
                       | Field         | Type        | Null | Key | Default | Extra |
                       +---------------+-------------+------+-----+---------+-------+
                       | DEpT_iD       | int         | yES  |     | NULL    |       |
                       | DEpT_NAmE     | varchar(20) | yES  |     | NULL    |       |
                       | DEpT_LOCATiON | varchar(30) | yES  |     | NULL    |       |
                       +---------------+-------------+------+-----+---------+-------+

         Ans. SELECT DEpT_LOCATiON FROm DEpARTmENT;
          8.  Modify the query of Ques 7 so that name of each location is displayed only once.

         Ans. SELECT DiSTiNCT DEpT_LOCATiON FROm DEpARTmENT;
          9.  Write MySQL command used to display the structure of a table named HOSPITAL.
         Ans. DESC HOSpiTAL;

          298   T ouchpad In f orma tion T echnology
                Touchpad Information Technology-XI-XII
   295   296   297   298   299   300   301   302   303   304   305