SQL PRACTICE-4 (SELF PRACTICE)

Consider the following tables STORE and SUPPLIERS and answer (a) and (b) parts of this questions:

TABLE: STORE

ItemNo	Item	Scode	Qty	Rate	LastBuy
2005	Sharpener Classic	23	60	8	31-JUN-09
2003	Balls	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	Tera 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 Rs 15.

          (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.

(b)    Give the output of the following SQL queries:

            (i)     SELECT COUNT(DISTINCT Scode) FROM STORE;

          (ii)     SELECT Rate * Qty FROM STORE WHRE ItemNo = 2004;

        (iii)     SELECT Item, Sname FROM STORE S, SUPPLIERS P

WHERE S.Scode=P.Scode AND ItemNo=2006;

          (iv)     SELECT MAX(LastBuy) FROM STORE;