Outside Delhi 2008:
5.b) 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
| C_ID | ItemName | Manufacturer | Price |
|---|---|---|---|
| PC01 | Personal Computer | ABC | 35000 |
| LC05 | Laptop | ABC | 55000 |
| PC03 | Personal Computer | XYZ | 32000 |
| PC06 | Personal Computer | COMP | 37000 |
| LC03 | Laptop | PQR | 57000 |
Table: CUSTOMER
| C_ID | CustomerName | City | P_ID |
|---|---|---|---|
| 01 | N.Roy | Delhi | LC03 |
| 06 | H.Singh | Mumbai | PC03 |
| 12 | R.Pandey | Delhi | PC06 |
| 15 | C.Sharma | Delhi | LC03 |
| 16 | K.Agarwalh | Banglore | 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 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 Manufacturer Name
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
CBSE Computer Science Solved Revision Tour By Mr. Ravi Kiran ( [email protected] )
