Check sibling questions

This question asked in CBSE Outside Delhi Set, 2010

Consider the following tables STOCK and DEALERS and answer (a) and (b) parts of this question:

Question 11 Long Answer 5 - image 1.jpg

Table: STOCK

ItemNo Item Dcode Qty UnitPrice StockDate
5005 Ball Pen 0.5 102 100 16 31-Mar-10
5003 Ball Pen 0.25 102 150 20 01-Jan-10
5002 Gel Pen Premium 101 125 14 14-Feb-10
5006 Gel Pen Classic 101 200 22 01-Jan-09
5001 Eraser Small 102 210 5 19-Mar-09
5004 Eraser Big 102 60 10 12-Dec-09
5009 Sharpener Classic 103 160 8 23-Jan-09

Question 11 Long Answer 5 - image 2.jpg

Table: DEALERS

Dcode Dname
101 Reliable Stationers
103 Classic Plastics
102 Clear Deals

Question 11 (a)

Write SQL commands for the following statements:

(i) To display the details of all Items in the STOCK table in ascending order of StockDate.

Answer:

To display the details of all Items in the STOCK table in ascending order of STOCKDATE, the ORDER BY clause followed by the column name ie., STOCKDATE should be used in the query.

QUERY: SELECT * FROM STOCK ORDER BY STOCKDATE;

Question 11 (i) Long Answer 5 - image 2.jpg

(ii) To display ItemNo and Item name of those items from STOCK table whose UnitPrice is more than Rupees 10.

Answer:

To display ItemNo and Item name of those items from the STOCK table whose UnitPrice is more than Rupees 10, the WHERE clause followed by the condition ie., UNITPRICE>10 should be used in the query.

QUERY: SELECT ITEMNO,ITEM FROM STOCK WHERE UNITPRICE>10;

Question 11 (ii) Long Answer 5 - image 1.jpg

(iii)To display the details of those items whose dealer code (Dcode) is 102 or quantity in STOCK (Qty) is more than 100 from the table Stock.

Answer:

To display the details of those items whose dealer code (Dcode) is 102 or quantity in STOCK (Qty)  is more than 100 from the table Stock, the WHERE clause followed by the condition ie., DCODE=102 OR QTY>100 should be used in the query.

QUERY: SELECT * FROM STOCK WHERE DCODE=102 OR QTY>100;

Question 11 (iii) Long Answer 5 - image 1.jpg

Question 11 (b)

Give the output of the following SQL queries:

(i) SELECT Item, Dname FROM STOCK S, DEALERS D WHERE S.Dcode=D.Dcode AND ItemNo = 5004;

Answer:

The query will display the name of the item and the name of the dealer for the item with ITEMNO as 5004.

Question 11 (iv) Long Answer 5 - image 1.jpg

(ii) SELECT MIN (StockDate) FROM STOCK;

Answer:

The query will display the smallest value in the column STOCKDATE of table STOCK.

Question 11 (v) Long Answer 5 - image 1.jpg


Transcript

This question asked in CBSE Outside Delhi Set, 2010 Consider the following tables STOCK and DEALERS and answer (a) and (b) parts of this question: Table: STOCK ItemNo Item Dcode Qty UnitPrice StockDate 5005 Ball Pen 0.5 102 100 16 31-Mar-10 5003 Ball Pen 0.25 102 150 20 01-Jan-10 5002 Gel Pen Premium 101 125 14 14-Feb-10 5006 Gel Pen Classic 101 200 22 01-Jan-09 5001 Eraser Small 102 210 5 19-Mar-09 5004 Eraser Big 102 60 10 12-Dec-09 5009 Sharpener Classic 103 160 8 23-Jan-09 Table: DEALERS Dcode Dname 101 Reliable Stationers 103 Classic Plastics 102 Clear Deals Question 11 (a) Write SQL commands for the following statements: (i) To display the details of all Items in the STOCK table in ascending order of StockDate. Answer: To display the details of all Items in the STOCK table in ascending order of STOCKDATE, the ORDER BY clause followed by the column name ie., STOCKDATE should be used in the query. QUERY: SELECT * FROM STOCK ORDER BY STOCKDATE; (ii) To display ItemNo and Item name of those items from STOCK table whose UnitPrice is more than Rupees 10. Answer: To display ItemNo and Item name of those items from the STOCK table whose UnitPrice is more than Rupees 10, the WHERE clause followed by the condition ie., UNITPRICE>10 should be used in the query. QUERY: SELECT ITEMNO,ITEM FROM STOCK WHERE UNITPRICE>10; (iii)To display the details of those items whose dealer code (Dcode) is 102 or quantity in STOCK (Qty) is more than 100 from the table Stock. Answer: To display the details of those items whose dealer code (Dcode) is 102 or quantity in STOCK (Qty) is more than 100 from the table Stock, the WHERE clause followed by the condition ie., DCODE=102 OR QTY>100 should be used in the query. QUERY: SELECT * FROM STOCK WHERE DCODE=102 OR QTY>100; Question 11 (b) Give the output of the following SQL queries: (i) SELECT Item, Dname FROM STOCK S, DEALERS D WHERE S.Dcode=D.Dcode AND ItemNo = 5004; Answer: The query will display the name of the item and the name of the dealer for the item with ITEMNO as 5004. (ii) SELECT MIN (StockDate) FROM STOCK; Answer: The query will display the smallest value in the column STOCKDATE of table STOCK.

Davneet Singh's photo - Co-founder, Teachoo

Made by

Davneet Singh

Davneet Singh has done his B.Tech from Indian Institute of Technology, Kanpur. He has been teaching from the past 12 years. He provides courses for Maths, Science, Social Science, Physics, Chemistry, Computer Science at Teachoo.