CBSE Computer Science - Revision Tour(Solved)

CBSE Guess > eBooks > Class XII > CBSE Computer Science Structure Query Language Solved Revision Tour By Mr. Ravi Kiran

COMPUTER SCIENCE STRUCTURED QUERY LANGUAGE

Previous Index Next

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 TalcomPowder LAK 40
FW05 Face Wash ABC 45
BS01 Bath Soap ABC 55
SH06 Shampoo XYZ 120
FW12 Face Wash XYZ 95

Table: CLIENT

C_ID Client Name City P_ID
01 TalcomPowder Delhi FW05
06 Face Wash Mumbai BS01
12 Bath Soap Delhi SH06
15 Shampoo Delhi FW12
16 Face Wash 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

(vii) SELECT ClientName, ManufacturerName FROM Product, Client WHERE Client.Prod_Id=Product.P_Id;

Ans:

ClientName                ManufacturerName
Cosmetic Shop            ABC
Total Health                  ABC
Live Life                          XYZ
Pretty Woman               XYZ
Dreams                         LAK

(viii) SELECT ProductName, Price * 4 FROM Product.

ProductName          Price*4
 Talcom Poweder       160
 Face Wash                 180
 Bath Soap                   220
 Shampoo                    480
 Face Wash                 380

Previous Index Next

CBSE Computer Science Solved Revision Tour By Mr. Ravi Kiran ( mrkdata@yahoo.com )