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

