Consider the following tables STORE and SUPPLIERS.

Q. Consider the following tables STORE and SUPPLIERS. Write SQL commands for the statements (a) to (d) and give outputs for SQL queries (e) to (g).

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

Table: SUPPLIERS

 
	 
Scode
 
	 
Sname
 
 
 
	 
21
 
	 
Premium Stationery
 
 
 
	 
23
 
	 
Soft Plastics
 
 
 
	 
22
 
	 
Tetra Supply
 
 

(a) To display details of all the items in the Store table in ascending order of LastBuy.

(b) To display Itemno and item name of those items from store table whose rate is more than 15 rupees.

(c) To display the details of those items whose supplier code is 22 or Quantity in store is more than 110 from the table Store.

(d) To display minimum rate of items for each Supplier individually as per Scode from the table Store.

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

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

(g) SELECT MAX(LastBuy) FROM STORE;

Answer :-

(a) Select * from store order by Lastbuy ;
(b) Select Itemno, item name where rate > 15 ;
(c) Select * from scode = 22 or Qty > 110 ;
(d) Select  min( rate ) , Scode from store group by Scode ;
(e)
COUNT (DISTINCT Scode)
3

(f)
Rate * Qty
880

(g)
MAX(LastBuy)
20100224

because date can not enter in this ( 24-Feb-10 )  formate.