Question 11 - Past Year - 5 Mark Questions - Chapter 9 - Structured Query Language

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.