Note that in the queries below we are assuming that BOOK_LOANS only contains information on currently checked out books. Also, as with any problem involving writing queries, there may be multiple ways to write a correct query.
Retrieve information about all of the books: book id, title, author, publisher name, publisher address, publisher phone.
SELECT Book_id,Title,Author_name,Publisher_name,Address as Publisher_addr, Phone as Publisher_phone FROM BOOK NATURAL JOIN BOOK_AUTHORS JOIN PUBLISHER ON Publisher_name=Name
Retrieve information about checked-out books: book id, title, library branch name, branch id, branch address, borrower name, borrower address, borrower card number, date out, date due.
SELECT Book_id,Title,Branch_name,LB.Branch_id,LB.Address AS Branch_addr, B.Name as Borrower_name,B.Address AS Borrower_addr, Card_no AS Borrower_card,Date_out,Due_date FROM BOOK NATURAL JOIN BOOK_LOANS BL NATURAL JOIN BORROWER B JOIN LIBRARY_BRANCH LB ON BL.Branch_id=LB.Branch_id
Note that you can't use NATURAL JOIN for all four tables because BORROWER and LIBRARY_BRANCH both have a column called Address which should not be involved in the join.
Retrieve information about each borrower's borrowings: borrower's name, card number, address, phone number, and number of books checked out.
SELECT Name,Card_no,Address,Phone,COUNT(*) AS NumBooks FROM BORROWER NATURAL JOIN BOOK_LOANS GROUP BY Card_no
Find the high-volume borrowers: get the borrower's name, card number, address, phone number, and number of books checked out for borrowers who have checked out more than 10 books.
SELECT Name,Card_no,Address,Phone,COUNT(*) AS NumBooks FROM BORROWER NATURAL JOIN BOOK_LOANS GROUP BY Card_no HAVING COUNT(*) > 10
Retrieve information about what books are currently available: library branch name, branch id, book id, book title, and the number of available copies of that book at that branch (= number of copies - number of copies checked out). This should include information about all books that a library owns at least one copy of (even if all those copies are checked out), and no information about books that branch does not own a copy of.
A first attempt is the following, which combines branch information and book information with loaned books from that branch and the number of copies of that book at that branch and then counts the number of loans of each book from each branch:
SELECT Branch_name,Branch_id,Book_id,Title, No_of_copies-COUNT(*) AS NumAvailable FROM LIBRARY_BRANCH NATURAL JOIN BOOK_LOANS NATURAL JOIN BOOK_COPIES NATURAL JOIN BOOK GROUP BY Branch_id,Book_id
This is almost correct, except what about books which haven't been checked out at all? There is no entry for them in BOOK_LOANS, so they are also not represented when BOOK_LOANS is joined with the other tables.
One solution is to simply combine this with the books that haven't been checked out:
( SELECT Branch_name,Branch_id,Book_id,Title, No_of_copies-COUNT(*) AS NumAvailable FROM LIBRARY_BRANCH NATURAL JOIN BOOK_LOANS NATURAL JOIN BOOK_COPIES NATURAL JOIN BOOK GROUP BY Branch_id,Book_id ) UNION ( SELECT Branch_name,Branch_id,Book_id,Title,No_of_copies AS NumAvailable FROM LIBRARY_BRANCH LB NATURAL JOIN BOOK_COPIES NATURAL JOIN BOOK B WHERE NOT EXISTS ( SELECT * FROM BOOK_LOANS BL WHERE BL.Branch_id=LB.Branch_id AND BL.Book_id=B.Book_id ) )
Assume that the views created have the names BOOK_INFO, CHECKED_OUT, BORROWINGS, HIGH_VOLUME, and AVAILABLE, respectively.
Find how many books each borrower has checked out.
SELECT Card_no,Name,NumBooks FROM BORROWINGS
Card_no is included to avoid combining borrowers with the same name, while Name is included because that is more useful for people looking at the information.
Find how many books each borrower has checked out for each author.
SELECT Borrower_card,Borrower_name,Author_name,COUNT(*) AS NumBooks FROM CHECKED_OUT NATURAL JOIN BOOK_AUTHORS GROUP BY Borrower_card,Author_name
Find the library branches and books for which all of the copies of that book at that branch are currently checked out.
SELECT Branch_id,Branch_name,Book_id,Title FROM AVAILABLE WHERE NumAvailable=0
Find the author with the highest number of available copies of their books across all of the library branches. Include all the relevant authors if there is a tie.
A first step is to figure out how to get the total number of available copies of books for each author:
SELECT Author_name,SUM(NumAvailable) AS NumAvailable FROM AVAILABLE NATURAL JOIN BOOK_AUTHORS GROUP BY Author_name
Then choose the highest:
SELECT DISTINCT Author_name FROM BOOK_AUTHORS BA WHERE ( SELECT SUM(NumAvailable) FROM AVAILABLE NATURAL JOIN BOOK_AUTHORS BA2 WHERE BA.Author_name=BA2.Author_name ) >= ALL ( SELECT SUM(NumAvailable) FROM AVAILABLE NATURAL JOIN BOOK_AUTHORS GROUP BY Author_name )
(Since the author name is needed, this isn't just a matter of using MAX.) DISTINCT is needed because an author may be listed multiple times in BOOK_AUTHORS (if they wrote multiple books).