This question asked in CBSE Delhi, 2010
Consider the following tables STORE and SUPPLIERS and answer (a) and (b) parts of this question:
Table: STORE
| ItemNo | Item | Scode | Qty | Rate | LastBuy |
| 2005 | Sharpener Classic | 23 | 60 | 8 | 31-Jun-09 |
| 2003 | Ball Pen 0.25 | 22 | 50 | 25 | 01-Feb-10 |
| 2002 | Gel Pen Premium | 21 | 150 | 12 | 24-Feb-10 |
| 2006 | Gel Pen Classic | 21 | 250 | 20 | 11-Mar-09 |
| 2001 | Eraser Small | 22 | 220 | 6 | 19-Jan-09 |
| 2004 | Eraser Big | 22 | 110 | 8 | 02-Dec-09 |
| 2003 | Ball Pen 0.5 | 21 | 180 | 18 | 03-Nov-09 |
Table: SUPPLIERS
| Scode | Sname |
| 21 | Premium Stationers |
| 23 | Soft Plastics |
| 22 | Tetra Supply |
Question 10 (a)
Write SQL commands for the following statements:
(i) To display details of all the items in the STORE table in ascending order of LastBuy.
Answer:
To display details of all the items in the STORE table in ascending order of LastBuy, the ORDER BY clause followed by the column name ie., LASTBUY should be used in the query.
QUERY: SELECT * FROM STORE ORDER BY LASTBUY;
-long-answer-5---image-2.jpg)
(ii) To display ItemNo and Item name of those items from STORE table whose Rate is more than 15 Rupees.
Answer:
To display ItemNo and Item name of those items from STORE table whose Rate is more than 15 Rupees, the WHERE clause followed by the condition ie., RATE>15 should be used in the query.
QUERY: SELECT ITEMNO,ITEM FROM STORE WHERE RATE>15;
-long-answer-5---image-1.jpg)
(iii)To display the details of those items whose supplier code (Scode) is 22 or Quantity in Store (Qty) is more than 110 from the table Store.
Answer:
To display the details of those items whose supplier code (Scode) is 22 or Quantity in Store (Qty) is more than 110 from the table Store, the WHERE clause followed by condition ie., SCODE=22 OR QTY>110 should be used in the query.
QUERY: SELECT * FROM STORE WHERE SCODE=22 OR QTY>110;
-long-answer-5---image-1.jpg)
Question 10 (b)
Give the output of the following SQL queries:
(i) SELECT COUNT(DISTINCT Scode) FROM STORE;
Answer:
The query will display the number of distinct values in the column SCODE of table STORE.
-long-answer-5---image-1.jpg)
(ii) SELECT Rate* Qty FROM STORE WHERE ItemNo=2004;
Answer:
The query will display the value that is obtained when the values in columns RATE and QTY are multiplied for the record with value 2004 in the column ITEMNO.
-long-answer-5---image-1.jpg)
