COMPUTER SCIENCE STRUCTURED QUERY LANGUAGE

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 ( [email protected] )