dba1b

Query for given questions:

1) SELECT LP.PGM_NAME, B.BOOK_ID,TITLE, PUB_NAME, AUTHOR_NAME,
NO_OF_COPIES
FROM BOOK B, BOOK_AUTHORS BA, BOOK_COPIES BC, LIBRARY_PROGRAM LP,
PUBLISHER P
WHERE B.BOOK_ID = BA.BOOK_ID AND
BA.BOOK_ID = BC.BOOK_ID AND
BC.PGM_ID = LP.PGM_ID
GROUP BY LP.PGM_NAME, B.BOOK_ID, TITLE,
PUB_NAME, AUTHOR_NAME, NO_OF_COPIES;

2) SELECT CARD_NO FROM BOOK_LENDING
WHERE DATE_OUT > '01-JAN-2017' AND
DATE_OUT < '01-JUN-2017'
GROUP BY CARD_NO
HAVING COUNT (*) > 3;

3) DELETE FROM BOOK WHERE BOOK_ID = 001;

4) SELECT PUB_YEAR FROM BOOK;

5) CREATE VIEW BOOKS_AVAILABLE AS
SELECT B.BOOK_ID, B.TITLE, C.NO_OF_COPIES
FROM LIBRARY_PROGRAM L, BOOK B, BOOK_COPIES C
WHERE B.BOOK_ID = C.BOOK_ID AND
L.PGM_ID=C.PGM_ID;
SELECT * FROM BOOKS_AVAILABLE;