Consider the following tables Item and Customer. Write SQL commands for the statement (i) to (iv) and give outputs for SQL queries (v) to (viii)

DATABASE MANAGEMENT SYSTEM

Consider the following tables Item and Customer. Write SQL commands for the statement (i) to (iv) and give outputs for SQL queries (v) to (viii)

Table: ITEM

S.no	I_ID	Item Name	Manufacturer Price
01	PC01	Personal Computer	ABC 35000
02	LC05	Laptop	ABC 55000
03	PC03	Personal Computer	XYZ 32000

04	PC06	Personal Computer	COMP 37000
05	LC03	Laptop	PQR 57000

Table: CUSTOMER C_ID Customer Name City I_ID 

S.no	CUSTOMER C_ID	Customer Name	City	I_ID
01	01	N.Roy	Delhi	LC03
02	06	H.Singh	Mumbai	PC03
03	12	R.Pandey	Delhi	PC06
04	15	C.Sharma	Delhi	LC03
05	16	K.Agrawal	Bangalore	PC01

(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 Customer Name, City from table Customer, and Item Name and Price from table Item, with their correspondingmatching I_ID. 

Ans: Select Customer Name, 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 Item Name, MAX(Price), Count(*) FROM Item GROUP BY Item Name; Ans: Item Name Max(Price) Count(*) Personal Computer 37000 3 Laptop 57000 2

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

Ans: Customer Name Manufacturer Name 

N.Roy PQR 

H.Singh XYZ 

R.Pandey COMP 

C.Sharma PQR 

K.Agarwal ABC 

(viii) SELECT Item Name, Price * 100 FROM Item WHERE 

Manufacturer = ‘ABC’; 

Ans: Item Name Price*100 

Personal Computer 3500000 

Laptop 5500000