Page 297 - PYTHON-12

15. Consider the following tables STORE and SUPPLIERS. Write SQL commands for the statements (i) to (iv)
and give outputs for SQL queries (v) to (viii).
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
(i) To display details of all the items in the Store table in ascending order of LastBuy.
(ii) To display Itemno and item name of those items from Store table whose rate is more than 15 rupees.
(iii) To display the details of those items whose supplier code is 22 or quantity in store is more than 110
from the table Store.
(iv) To display minimum rate of items for each Supplier individually as per Scode from the table Store.
(v) SELECT COUNT(DISTINCT Scode) FROM STORE;
(vi) SELECT Rate*Qty FROM STORE WHERE Itemno=2004;
(vii) SELECT Item, Sname FROM STORE S, SUPPLIER P WHERE S.Scode=P.Scode AND ItemNo=2006;
(viii) SELECT MAX(LastBuy)FROM STORE;
16. Write SQL commands for (i) to (vi) on the basis of relations given below:
BOOKS
book_id Book_name author_name Publishers Price Type qty
k0001 Let us C Sanjay Mukharjee EPB 450 Comp 15
p0001 Genuine J. Mukhi FIRST PUBL. 755 Fiction 24
m0001 Mastering C++ Kantkar EPB 165 Comp 60
n0002 VC++ advance P. Purohit TDH 250 Comp 45
k0002 Programming with Python Sanjeev FIRST PUBL. 350 Fiction 30
ISSUED
Book_ID Qty_Issued
L02 13
L04 5
L05 21
(i) To show the books of FIRST PUBL. Publishers written by P. Purohit.
(ii) To display cost of all the books published for FIRST PUBL.
(iii) Depreciate the price of all books of EPB publishers by 5%.
(iv) To display the BOOK_NAME and price of the books, more than 3 copies of which have been issued.
(v) To show total cost of books of each type.
(vi) To show the details of the costliest book. Relational Database and SQL

12.67