Consider the following tables STORE and SUPPLIERS. Write SQL commands for the statements

Q. Consider the following tables STORE and SUPPLIERS. Write SQL commands for the statements (i) to (iv) and give outputs for SQL queries (v) to (viii).

Table: Store

 
 
  
 
	 Item No 
	 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 
 
 
	 2009 
	 Ball Pen 0.5 
	 21 
	 180 
	 18 
	 03-Nov-09

Item No	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
2009	Ball Pen 0.5	21	180	18	03-Nov-09

Table: SUPPLIERS

 
 
	 Scode 
	 Sname 
 
 
	 21 
	 Premium Stationery 
 
 
	 23 
	 Soft Plastics 
 
 
	 23 
	 Tetra Supply

Scode	Sname
21	Premium Stationery
23	Soft Plastics
23	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 from Store table whose rate is more than 15 rupees.

(iii) To display the details of those items whose supplier code is 22 or quantity in store 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) SELECT COUNT(DISTINCT Scode) FROM STORE;

(vi) SELECT Rate*Qty FROM STORE WHERE Itemno = 2004;

(vii) SELECT Item, Sname FROM STORE S, SUPPLIER P WHERE S.Scode = P.Scode AND ItemNo = 2006;

(viii) SELECT MAX(LastBuy)FROM STORE;

Answer =

(i)

Select * from Store order by LastBuy;

(ii)

Select itemno,item from Store rate > 15 ;

(iii)

Select * from Store where Scode = 22 and Qty > 110 ;

(iv)

Select min(rate) from Store group by Scode ;

(v)

Count(Distinct Scode)

(vi)

Rate*Qty

880

(vii)

Item	Sname
Gel Pen Classic	Premium Stationery

(viii)

MAX(LastBuy)

24-Feb-10

Path Walla