You are here
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;
-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;
-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;
-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.
-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.
-long-answer-5---image-1.jpg)
