Delhi 2008:
5.b) Consider the following tables Product and Client. Write SQL commands for the
statement (i) to (iv) and give outputs for
SQL queries (v) to (viii)
Table: PRODUCT
| P_ID | Product Name | Manufacturer | Price |
|---|---|---|---|
| TP01 | TalcomPowder | LAK | 40 |
| FW05 | Face Wash | ABC | 45 |
| BS01 | Bath Soap | ABC | 55 |
| SH06 | Shampoo | XYZ | 120 |
| FW12 | Face Wash | XYZ | 95 |
Table: CLIENT
| C_ID | Client Name | City | P_ID |
|---|---|---|---|
| 01 | TalcomPowder | Delhi | FW05 |
| 06 | Face Wash | Mumbai | BS01 |
| 12 | Bath Soap | Delhi | SH06 |
| 15 | Shampoo | Delhi | FW12 |
| 16 | Face Wash | Banglore | TP01 |
(i) To display the details of those Clients whose city is Delhi.
Ans: Select all from Client where City=”Delhi”
(ii) To display the details of Products whose Price is in the range of 50 to 100(Both values included).
Ans: Select all from product where Price between 50 and 100
(iii) To display the ClientName, City from table Client, and ProductName and Price from table Product, with their corresponding matching P_ID.
Ans: Select ClientName,City,ProductName, Price from Product,Client where Product.P_ID=Client.P_ID.
(iv) To increase the Price of all Products by 10
Ans: Update Product Set Price=Price +10
(v) SELECT DISTINCT Address FROM Client.
Ans: ( The above question may consist DISTINCT City. If it is DISTINCT City, the following is the answer)
City
-----
Delhi
Mumbai
Bangalore
(vi) SELECT Manufacturer, MAX(Price), Min(Price), Count(*) FROM Product GROUP BY
Manufacturer;
Ans:
Manufacturer Max(Price) Min(Price) Count(*)
LAK 40 40 1
ABC 55 45 2
XYZ 120 95 2
(vii) SELECT ClientName, ManufacturerName FROM Product, Client WHERE Client.Prod_Id=Product.P_Id;
Ans:
ClientName ManufacturerName
Cosmetic Shop ABC
Total Health ABC
Live Life XYZ
Pretty Woman XYZ
Dreams LAK
(viii) SELECT ProductName, Price * 4 FROM Product.
ProductName Price*4
Talcom Poweder 160
Face Wash 180
Bath Soap 220
Shampoo 480
Face Wash 380
CBSE Computer Science Solved Revision Tour By Mr. Ravi Kiran ( [email protected] )
