Chapter – 8 . STRUCTURED QUERY LANGUAGE

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 Talcom LAK 40 Powder
FW05

FaceWash

ABC 45
BS01 BathSoap ABC 55
SH06 Shampoo XYZ 120
FW12

Face Wash

XYZ 95

Table: CLIENT
C_ID Client Name City P_ID
01 Cosmetic Shop Delhi FW05
06 Total Health Mumbai BS01
12 Live Life Delhi SH06
15 Pretty Woman Delhi FW12
16 Dreams 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