Program 1 (QUERIES)

Queries (Program 1)

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, Title, Publisher_name, Author_name,Branch_id, No_of_copies

FROM BOOKB, BOOK_AUTHORS BA, BOOK_COPIES BC

WHERE B.Book_id=BA.Book_id AND B.Book_id=BC.Book_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 BOOK_LENDING

WHERE Date_out BETWEEN'01-Jan-2017'AND'30-Jun-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

FROMBOOK

WHERE book_id=501;

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

SELECT count(Book_id), Pub_year

FROM BOOK

GROUP BY(Pub_year);

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

CREATE VIEW BOOK_VIEW

ASSELECT B.Book_id,B.Title,LB.Branch_name,BC.No_of_copies

FROM BOOK B, BOOK_COPIES BC, LIBRARY_BRANCH LB

WHERE B.Book_id=BC.Book_id and BC.Branch_id=LB.Branch_id;