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