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

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

Table: CUSTOMER

(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] )