Write SQL commands for the following queries (i) to (v) based on the relations STORE and SUPPLIERS given below:
TABLE: STORE
ITEMNO | ITEM | SCODE | QTY | RATE | LASTBUY |
2005 | Sharpner 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 |
2009 | Ball Pen 0.5 | 21 | 180 | 18 | 03-Nov-09 |
TABLE: SUPPLIERS
SCODE | SNAME |
21 | Premium Stationers |
23 | Soft Plastics |
22 | Tetra Supply |
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 whose Rate is more than 15 Rupees.
iii) To display the details of those items whose Suppliers 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.
v) To display total number of records having distinct SCODE from the table STORE.
Write the outputs of the SQL queries based on the relations STORE and SUPPLIERS.
vi) SELECT RATE*QTY FROM STORE WHERE ITEMNO=2004;
vii) SELECT ITEM, SNAME FROM STORE S, SUPPLIERS P WHERE S.SCODE = P.SCODE AND ITEM NO = 2006;
viii) SELECT MAX (LASTBUY) FROM STORE;