Consider the following tables STORE and SUPPLIERS and answer (a) and (

1.	Consider the following tables STORE and SUPPLIERS and answer (a) and (b) parts of this question:(a) Write SQL commands for the following statements:1. To display details of all the items in the STORE table in ascending order of BestBuy.2. To display ItemNo and Item name of those items from STORE table whose Rate is more than 15 Rupees.3. 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.4. To display the minimum Rate of items for each supplier individually as per Scode from the table STORE.(b) Give the output of the following SQL queries:1. SELECT COUNT(DISTINCT Scode) FROM STORE;2. SELECT Rate* Qty FROM STORE WHERE ItemNo=2004;3. SELECT Item, Sname FROM STORE S, Suppliers P4. WHERE S.Scode=PScode AND ItemNo=2006;5. SELECT MAX(LastBuy) FROM STORE;
Answer» (a) 1. SELECT * FROM STORE ORDER BY LastBuy ASC; 2. SELECT ItemNo, Item FROM STORE WHERE Rate > 15; 3. SELECT * FROM STORE WHERE (Scode = ’22’ OR Qty >’110′); 4. SELECT Sname, MIN(Rate) FROM STORE, SUPPLIERS WHERE STORE. Scode = SUPPLIERS.Scode GROUP BY Sname; (b) 1. 3 2. 880 3. Item Sname Gel Pen Classic Premium Stationers 4. 24-Feb-10

Consider the following tables STORE and SUPPLIERS and answer (a) and (b) parts of this question:(a) Write SQL commands for the following statements:1. To display details of all the items in the STORE table in ascending order of BestBuy.2. To display ItemNo and Item name of those items from STORE table whose Rate is more than 15 Rupees.3. 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.4. To display the minimum Rate of items for each supplier individually as per Scode from the table STORE.(b) Give the output of the following SQL queries:1. SELECT COUNT(DISTINCT Scode) FROM STORE;2. SELECT Rate* Qty FROM STORE WHERE ItemNo=2004;3. SELECT Item, Sname FROM STORE S, Suppliers P4. WHERE S.Scode=PScode AND ItemNo=2006;5. SELECT MAX(LastBuy) FROM STORE;

Answer»

(a)

1. SELECT * FROM STORE ORDER BY LastBuy ASC;

2. SELECT ItemNo, Item FROM STORE WHERE Rate > 15;

3. SELECT * FROM STORE WHERE (Scode = ’22’ OR Qty >’110′);

4. SELECT Sname, MIN(Rate) FROM STORE, SUPPLIERS WHERE STORE. Scode = SUPPLIERS.Scode GROUP BY Sname;

(b)

1. 3

2. 880

3. Item Sname

Gel Pen Classic Premium Stationers

4. 24-Feb-10