Chapter – 8 . STRUCTURED QUERY LANGUAGE

(i) To display the details of those Customers whose city is Delhi.

Ans. Select all from Customer Where City=”Delhi” .

(ii) To display the details of Item whose Price is in the range of 35000 to 55000 (Both values included).

Ans. Select all from Item Where Price>=35000 and Price <=55000.

(iii) To display the CustomerName, City from table Customer, and ItemName and Price from table Item, with their corresponding matching I_ID.

Ans. Select CustomerName,City,ItemName,Price from Item,Customer where Item.I_ID=Customer.I_ID.

(iv) To increase the Price of all Items by 1000 in the table Item.

Ans. Update Item set Price=Price+1000

(v) SELECT DISTINCT City FROM Customer.

Ans. City Delhi Mumbai Bangalore

(vi) SELECT ItemName, MAX(Price), Count(*) FROM Item GROUP BY ItemName;

Ans.

ItemName	Max(Price)	Count(*)
Personal Computer	37000	3
Laptop	57000	2

(vii) SELECT CustomerName, Manufacturer FROM Item, Customer WHERE Item.Item_Id=Customer.Item_Id;

Ans:

CustomerName	ManufacturerName
N.Roy	PQR
H.Singh	XYZ
R.Pandey	COMP
C.Sharma	PQR
K.Agarwal	ABC

(viii) SELECT ItemName, Price * 100 FROM Item WHERE Manufacturer = ‘ABC’;

Ans:

ItemName	Price*100
Personal Computer	3500000
Laptop	5500000

Outside Delhi 2007

5.b. Consider the following tables Consignor and Consignee. Write SQL command for the statements(i)to(iv) And give outputs for the SQL quries (v) to ( viii).