Page 288 - PYTHON-12

(iv) To increase the price of all the stationery items in Stationery table by ` 2.
(v) Select COUNT(DISTINCT Address) from Consumer;
(vi) Select StationeryName, price * 3 from Stationery
where Company = ‘CAM’;
Ans. (i) Select * from Stationery
order by StockDate desc;
(ii) Select * from Stationery
where Company = ‘XYZ’ and Price < 10;
(iii) Select ConsumerName, Address, Company, Price from Stationery, Consumer
where Stationery. S_ID = Consumer.P_ID;
(iv) Update Stationery
Set Price = Price + 2;
(v) 3
(vi) Pencil 15
15. Consider the following tables: STOCK and DEALER.
Table: Stock

ItemNo Item Dcode Qty UnitPrice StockDate
5005 Ball Pen 0.5 102 100 16 2011-03-31
5003 Ball Pen 0.25 102 150 20 2010-01-01
5002 Gel Pen Premium 101 125 14 2010-02-14
5006 Gel Pen Classic 101 200 22 2009-01-09
5001 Eraser Small 102 210 5 2009-03-19
5004 Eraser Big 102 60 10 2010-12-12
5009 Sharpener Classic 103 160 8 2010-01-23
Note:
(a) ItemNo is the Primary Key.
(b) Dcode is the Foreign Key referencing Dcode of Dealer table.
Table: Dealer
Dcode DName
101 Reliable Stationers
103 Class Plastics
104 Fair Deals
102 Clear Deals

Note:
Write SQL statements for the queries (i) to (iv) and output for (v) and (vi):
(c) Dcode is the Primary Key.
Computer Science with Python–XII 12.58 (iii) To insert a record in the Stock table with the values:
(i) To display details of all the Items in the Stock table in ascending order of StockDate.
(ii) To display details of those Items in Stock table whose Dealer Code(Dcode) is 102 or quantity in
Stock(Qty) is more than 100.

(5010, ‘Pencil HB’, 102, 500, 10, ‘2010-01-26’)
(iv) To display Dcode, Dname from Dealer table and Item, UnitPrice from Stock table of all the Dealers
(including the dealer details that have not sold any item)
(v) Select COUNT(DISTINCT Dcode) from Stock;
(vi) Select Qty * UnitPrice from Stock where ItemNo=5006;