Page 190 - C++

(b) Consider the following tables STOCK and DEALERS and answer (a) and (b) parts of this question: (4+2)

(a) Write SQL commands for the following statements:

(i) To display details of all the items in the STOCK table in ascending order of StockDate.

(ii) To display ItemNo and ItemName of those items from STOCK table whose UnitPrice is more than Rs. 10?

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

(iv) 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:

(i) SELECT COUNT(DISTINCT Dcode) FROM STOCK;

(ii) SELECT Qty * UnitPrice FROM STOCK WHERE ItemNo = 5006;

(iii) SELECT ItemName. Dname FROM STOCK S, DEALERS D

WHERE S.Dcode = D.Dcode AND ItemNo = 5004;

(iv) SELECT MIN(StockDate) FROM STOCK;

Ans.
(a) (i) SELECT * FROM STOCK ORDER BY StockDate;

(ii) SELECT ItemNo, ItemName FROM STOCK WHERE UnitPrice>10;
(iii) SELECT * FROM STOCK Where Dcode = 102 OR Qty>100;

(iv) SELECT MAX (UNITEPRICE) FROM ST0CK GROUP BY Dcode;