2003:
5.b Write SQL commands for (b) to (g) and write the outputs for (h) on the basis of tables TNTERIORS and NEWONES.
TABLE: INTERIORS
| NO |
ITEM NAME | TYPE | DATEOFSTOCK | PRICE | DISCOUNT |
|---|---|---|---|---|---|
| 1 | Red rose | DoubleBed | 23/02/02 | 32000 | 15 |
| 2 |
Soft touch | Baby cot | 20/01/02 | 9000 | 10 |
| 3 |
Jerry’shome | Baby cot | 19/02/02 | 8500 | 10 |
| 4 |
Rough wood | Office Table | 01/01/02 | 20000 | 20 |
| 5 |
Comfort zone | Double Bed | 12/01/02 | 15000 | 20 |
| 6 |
Jerry look | Baby cot | 24/02/02 | 7000 | 19 |
| 7 |
Lion king | Office Table | 20/02/02 | 16000 | 20 |
| 8 |
Royal tiger | Sofa | 22/02/02 | 30000 | 25 |
| 9 |
Park sitting | Sofa | 13/12/01 | 9000 | 15 |
| 10 |
Dine paradise | DinningTable | 19/02/02 | 11000 | 15 |
TABLE:NEWONES
| NO |
ITEMNAME | TYPE | DATEOFSTOCK | PRICE | DISCOUNT |
|---|---|---|---|---|---|
| 11 |
White wood |
Doublebed | 23/03/03 | 20000 | 20 |
| 12 |
James007 |
Sofa | 20/02/03 | 15000 | 15 |
| 13 |
Tom
look |
Baby cot | 21/02/03 | 7000 | 10 |
(b) To show all information about the sofas from the INTERIORS table.
Ans: Select * from INTERIORS where type= “sofa”.
(d) To list ITEMNAME and TYPE of those items, in which DATEOFSTOCK is before 22/01/02 from the INTERIORS table in descending order of ITEMNAME.
Ans: Select Itemname,Type From Interiors Where Dateofstock<{22/01/02} order by
Itemname
(e) To display ITEMNAME and DATEOFSTOCK of those items in which the Discount percentage is more than 15 from INTERIORS.
Ans: Select Itemname,Dateofstock from Interiors Where Discount>15
(f) To count the number of items whose type is “Double bed”;
Ans: Select Count(*) from Interiors Where Type=”Double Bed”
(g) To insert new row in the NEWONES table with the following data:14, “True Indian “, “Office Table “, {28/03/03},15000,20.
Ans: Insert into Newones values(14,”True Indian”,”Office Table”,”{28/03/03},15000,20).
(h) Give the outputs for the following SQL statements.
(i) Select COUNT (distinct TYPE) from INTERIORS;
Ans: 5
(ii) Select AVG(DISCOUNT)from INTERIORS where TYPE =”Baby cot”;
Ans: 13
(iii) Select SUM(price)from INTERIORS where DATEOFSTOCK<{12/02/02};
Ans: 53000
CBSE Computer Science Solved Revision Tour By Mr. Ravi Kiran ( mrkdata@yahoo.com )
