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.

  1. 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
        
  2. 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.

  3. 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
        
  4. 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
        
  5. 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.

  1. 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.

  2. 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
        
  3. 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
        
  4. 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).