Consider the following tables STOCK and DEALERS and answer (a) and (b)

1.	Consider the following tables STOCK and DEALERS and answer (a) and (b) parts of this question:(a) Write SQL commands for the following statements:1. To display the details of all Items in the STOCK table in ascending order of StockDate.2. To display ItemNo and Item name of those items from STOCK table whose UnitPrice is more than Rupees 10.3. To display the details of those items whose dealer code (Dcode) is 102 or quantity in STOCK (Qty) is more than 100 from the table Stock.4. To display maximum UnitPrice of items for each dealer individually as per Dcode from the table STOCK.(b) Give the output of the following SQL queries:1. SELECT COUNT(DISTINCT Dcode) FROM STOCK;2. SELECT Qty* UnitPrice FROM STOCK WHERE ItemNo=5006;3. SELECT Item, Dname FROM STOCK S, Dealers D WHERE S.Dcode=D.Dcode AND ItemNo = 5004;4. SELECT MIN (StockDate) FROM STOCK;
Answer» (a) 1. SELECTFROM STOCK ORDER BY StockDate; 2. SELECT Item No, Item FROM STOCK WHERE UnitPrice >10; 3. SELECT FROM DEALERS, STOCK WHERE (DEALERS.Dcode=”102″OR STOCK.Qty >100 and DEALERS. DCODE = STOCK.DCODE); 4. SELECT MAX (Unitprice) FROM DEALERS, STOCK ORDER BY STOCK. Dcode WHERE DEALERS.Dcode = STOCK.Dcode; (b) 1. 3 2. 4400 3. Item Dname Eraser Big Clear Deals 4. 01-Jan-09

Consider the following tables STOCK and DEALERS and answer (a) and (b) parts of this question:(a) Write SQL commands for the following statements:1. To display the details of all Items in the STOCK table in ascending order of StockDate.2. To display ItemNo and Item name of those items from STOCK table whose UnitPrice is more than Rupees 10.3. To display the details of those items whose dealer code (Dcode) is 102 or quantity in STOCK (Qty) is more than 100 from the table Stock.4. To display maximum UnitPrice of items for each dealer individually as per Dcode from the table STOCK.(b) Give the output of the following SQL queries:1. SELECT COUNT(DISTINCT Dcode) FROM STOCK;2. SELECT Qty* UnitPrice FROM STOCK WHERE ItemNo=5006;3. SELECT Item, Dname FROM STOCK S, Dealers D WHERE S.Dcode=D.Dcode AND ItemNo = 5004;4. SELECT MIN (StockDate) FROM STOCK;

Answer»

(a)

1. SELECT*FROM STOCK ORDER BY StockDate;

2. SELECT Item No, Item FROM STOCK WHERE UnitPrice >10;

3. SELECT *FROM DEALERS, STOCK WHERE (DEALERS.Dcode=”102″OR STOCK.Qty >100 and DEALERS. DCODE = STOCK.DCODE);

4. SELECT MAX (Unitprice) FROM DEALERS, STOCK ORDER BY STOCK. Dcode WHERE DEALERS.Dcode = STOCK.Dcode;

(b)

1. 3

2. 4400

3. Item Dname

Eraser Big Clear Deals

4. 01-Jan-09