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 Classic |
21 |
150 |
12 |
24-Feb-10 |
2006 |
Gel Pen Premium |
21 |
250 |
20 |
11-Mar-09 |
2001 |
Eraser small |
22 |
220 |
6 |
19-Jan-09 |
2004 |
Eraser Big |
22 |
110 |
8 |
02-Dec-09 |
2009 |
Ball pen 0.5 |
21 |
180 |
18 |
03-Dec-09 |
Table: SUPPLIERS
Scode |
Sname |
21 |
Premium Stationers |
23 |
Soft Plastics |
22 |
Tetra Supply |
(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.
(ii) To display ItemNo and Item name of those items from Store table, whose Rate is more than 15 Rupees.
(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.
(iv) To display Minimum Rate of items for each Supplier individually as per Scode from the table Store.