LIBRARY

1. Retrieve details of all books in the library – id, title, name of publisher, authors,
   number of copies in each branch, etc

SELECT B.BOOK_ID, B.TITLE, B.PNAME, A.AUTHOR_NAME, C.NO_OF_COPIES,
L.BRANCH_ID
FROM SBOOK B, SBOOK_AUTHORS A, SBOOK_COPIES C, SLIBRARY_BRANCH L
WHERE B.BOOK_ID=A.BOOK_ID
AND B.BOOK_ID=C.BOOK_ID
AND L.BRANCH_ID=C.BRANCH_ID;

2. Get the particulars of borrowers who have borrowed more than 3 books, but from Jan
   2017 to Jun 2017

SELECT CARD_NO
FROM SBOOK_LENDING
WHERE DATE_OUT BETWEEN ’01-JAN-2017′ AND ’01-JUL-2017′
GROUP BY CARD_NO
HAVING COUNT (*)>3;

3. Delete a book in BOOK table. Update the contents of other tables to reflect this data
   manipulation operation.

DELETE FROM SBOOK
WHERE BOOK_ID=2;
To See The Changes in Other Tables Execute The Following Statements
SELECT * FROM SBOOK;
SELECT * FROM SBOOK_AUTHORS;

4. Partition the BOOK table based on year of publication. Demonstrate its working with a
   simple query

CREATE VIEW V_PUBLICATION AS
(SELECT PUB_YEAR
FROM SBOOK);

SELECT * FROM V_PUBLICATION;

Create a view of all books and its number of copies that are currently available in the
Library.

CREATE VIEW V_BOOKS AS
(SELECT B.BOOK_ID, B.TITLE, C.NO_OF_COPIES
FROM SBOOK B,SBOOK_COPIES C, SLIBRARY_BRANCH L
WHERE B.BOOK_ID=C.BOOK_ID
AND C.BRANCH_ID=L.BRANCH_ID);

SELECT * FROM V_BOOKS;