CPSC 343 Database Theory and Practice Fall 2017

Homework 3: SQL

due Wed Oct 18 in class

Express each of the following queries using SQL. Make sure that your queries work no matter what rows are in the tables. Avoid duplicates in the results (unless otherwise specified), but you should only explicitly eliminate duplicates if duplicates are possible. Any correct query is acceptable, but you should try to write as simple a solution as you can. (Simpler solutions are easier to reason about in order to check their correctness.)

Use the following database schema:

BOOK(Book_id,Title,Publisher_name)
BOOK_AUTHORS(Book_id,Author_name)
PUBLISHER(Name,Address,Phone)
BOOK_COPIES(Book_id,Branch_id,No_of_copies)
BOOK_LOANS(Book_id,Branch_id,Card_no,Date_out,Due_date)
LIBRARY_BRANCH(Branch_id,Branch_name,Address)
BORROWER(Card_no,Name,Address,Phone)

Note that BOOK_COPIES only contains entries where No_of_copies is at least 1 - if a branch doesn't contain any copies of some book, there is no entry in BOOK_COPIES (not an entry with 0 copies).

Key attributes are underlined. The foreign key constraints are the following:

You can test your queries for 1-17 (not the data modification statements) using the ex_library database, but remember that a correct answer must work regardless of the specific data in the database.

  1. Display all of the information in the BORROWER table.

        SELECT *
        FROM BORROWER
      
  2. Find all of the book titles, in alphabetical order.

        SELECT Title
        FROM BOOK
        ORDER BY Title ASC
      
  3. Display the title, due date, and borrower name for all books that have been checked out from Wood Library, with the latest due date first. List books with the same due date in order by borrower name, and those with the same due date and borrower name in order by title.

        SELECT Title, Due_date, Name AS Borrower
        FROM BOOK NATURAL JOIN BOOK_LOANS BL NATURAL JOIN BORROWER JOIN LIBRARY_BRANCH LB ON LB.Branch_id=BL.Branch_id
        WHERE Branch_name='Wood Library'
        ORDER BY Due_date DESC, Name ASC, Title ASC
      
  4. Display all of the author names.

        SELECT DISTINCT Author_name
        FROM BOOK_AUTHORS
      
  5. Find the name, address, and phone number for all borrowers with a 789 area code.

        SELECT Name, Address, Phone
        FROM BORROWER
        WHERE Phone LIKE '789-%'
      
        SELECT Name, Address, Phone
        FROM BORROWER
        WHERE LEFT(Phone,3) = '789'
      
  6. Find the names and card numbers of borrowers who have checked out books from more than one branch, in order by card number.

        SELECT DISTINCT Name, Card_no
        FROM BORROWER B NATURAL JOIN BOOK_LOANS BL
        WHERE EXISTS ( SELECT * 
                       FROM BOOK_LOANS BL2
                       WHERE BL2.Card_no=B.Card_no AND BL2.Branch_id<>BL.Branch_id )
        ORDER BY Card_no
      
  7. Find the books (book titles and authors) not written by C.J. Cherryh, in order by author and title.

        SELECT Title, Author_name
        FROM BOOK NATURAL JOIN BOOK_AUTHORS
        WHERE Author_name <> 'C. J. Cherryh'
        ORDER BY Author_name,Title
      
  8. Find the names of library branches which do not have at least one copy of the book Mission Child, in order by branch name.

        SELECT Branch_name
        FROM LIBRARY_BRANCH LB
        WHERE NOT EXISTS ( SELECT *
                           FROM BOOK_COPIES BC NATURAL JOIN BOOK B
                           WHERE BC.Branch_id=LB.Branch_id AND B.Title='Mission Child' )
        ORDER BY Branch_name
      
        SELECT Branch_name
        FROM LIBRARY_BRANCH LB
        WHERE Branch_id NOT IN ( SELECT Branch_id
                                 FROM BOOK_COPIES BC NATURAL JOIN BOOK B
                                 WHERE B.Title='Mission Child' )
        ORDER BY Branch_name
      
    The following does NOT work:
        SELECT DISTINCT Branch_name
        FROM LIBRARY_BRANCH LB NATURAL JOIN BOOK_COPIES BC NATURAL JOIN BOOK B
        WHERE B.Title<>'Mission Child' 
        ORDER BY Branch_name
      

    It finds the library branches which have a book other than Mission Child.

  9. Find the names of library branches which have copies of all of the books, in order by branch name.

        SELECT Branch_name
        FROM LIBRARY_BRANCH
        WHERE Branch_id NOT IN 
           ( SELECT DISTINCT Branch_id, Branch_name
             FROM LIBRARY_BRANCH LB, BOOK B
             WHERE NOT EXISTS ( SELECT *
                                FROM BOOK_COPIES BC
                                WHERE BC.Book_id=B.Book_id AND LB.Branch_id=BC.Branch_id ) )
        ORDER BY Branch_name
      
        SELECT Branch_name
        FROM LIBRARY_BRANCH LB
        WHERE ( SELECT COUNT(BC.Book_id)
                FROM BOOK_COPIES BC
                WHERE LB.Branch_id=BC.Branch_id ) =
              ( SELECT COUNT(B.Book_id) FROM BOOK B )
        ORDER BY Branch_name
      
  10. Find the title of the book which is alphabetically first of all of the books.

        SELECT MIN(Title)
        FROM BOOK
      
        SELECT Title
        FROM BOOK
        ORDER BY Title
        LIMIT 1
      
  11. Find the book, its author(s), and library branch name for the book with the highest number of copies at a branch. If there's a tie, include all of the books/branches with the highest number of copies.

        SELECT Title, Author_name, Branch_name
        FROM LIBRARY_BRANCH NATURAL JOIN BOOK_COPIES NATURAL JOIN BOOK NATURAL JOIN BOOK_AUTHORS
        WHERE No_of_copies >= ALL ( SELECT MAX(No_of_copies)
                                    FROM BOOK_COPIES );
      
  12. For each branch (branch ID and name), find the number of books checked out on 2006-10-14.

        SELECT Branch_id, Branch_name, COUNT(*) AS NumCheckedOut
        FROM LIBRARY_BRANCH LB NATURAL JOIN BOOK_LOANS BL
        WHERE Date_out='2006-10-14'
        GROUP BY Branch_id
      
  13. Find the branches (branch ID and name) with copies of at least 10 different books (and how many different books those branches have copies of).

        SELECT Branch_id, Branch_name, COUNT(DISTINCT Book_id) AS NumBooks
        FROM LIBRARY_BRANCH NATURAL JOIN BOOK_COPIES
        GROUP BY Branch_id
        HAVING COUNT(DISTINCT Book_id) >= 10
      
        SELECT Branch_id, Branch_name, NumBooks
        FROM LIBRARY_BRANCH LB NATURAL JOIN
          ( SELECT Branch_id, COUNT(DISTINCT Book_id) AS NumBooks
            FROM BOOK_COPIES
            GROUP BY Branch_id ) C
        WHERE NumBooks >= 10
      
  14. For each branch (branch ID and name), find the total number of books (counting all of the copies of each book) that branch has.

        SELECT Branch_id, Branch_name, SUM(No_of_copies) AS TotalCopies
        FROM LIBRARY_BRANCH NATURAL JOIN BOOK_COPIES
        GROUP BY Branch_id
      
  15. Find the publisher who has published the largest number of books.

        SELECT Publisher_name
        FROM BOOK
        GROUP BY Publisher_name
        HAVING COUNT(*) >= ALL ( SELECT COUNT(*)
                                 FROM BOOK B 
                                 GROUP BY Publisher_name )
      
        SELECT Name
        FROM PUBLISHER P
        WHERE ( SELECT COUNT(*)
                FROM BOOK B
                WHERE B.Publisher_name=P.Name ) >= ALL ( SELECT COUNT(*)
                                                         FROM BOOK
                                                         GROUP BY Publisher_name )
      
  16. Find the titles of all of the books written by the author(s) who have written the largest number of books.

        SELECT DISTINCT Title
        FROM BOOK NATURAL JOIN BOOK_AUTHORS
        WHERE Author_name IN ( SELECT Author_name
                               FROM BOOK NATURAL JOIN BOOK_AUTHORS
                               GROUP BY Author_name
                               HAVING COUNT(*) >= ALL ( SELECT COUNT(*)
                                                        FROM BOOK NATURAL JOIN BOOK_AUTHORS
                                                        GROUP BY Author_name ) )
      
        SELECT DISTINCT Title
        FROM BOOK NATURAL JOIN BOOK_AUTHORS
        WHERE Author_name = ANY ( SELECT Author_name
                                  FROM BOOK NATURAL JOIN BOOK_AUTHORS
                                  GROUP BY Author_name
                                  HAVING COUNT(*) >= ALL ( SELECT COUNT(*)
                                                           FROM BOOK NATURAL JOIN BOOK_AUTHORS
                                                           GROUP BY Author_name ) )
      
        SELECT DISTINCT Title
        FROM BOOK NATURAL JOIN BOOK_AUTHORS A
        WHERE ( SELECT COUNT(*)
                FROM BOOK_AUTHORS A2
                WHERE A.Author_name=A2.Author_name ) >= ALL ( SELECT COUNT(*)
                                                              FROM BOOK NATURAL JOIN BOOK_AUTHORS
                                                              GROUP BY Author_name )
      

    Note that the following only works if there is not a tie i.e. there is a single author with the largest number of books.

        SELECT DISTINCT Title
        FROM BOOK NATURAL JOIN BOOK_AUTHORS
        WHERE Author_name = ( SELECT Author_name
                               FROM BOOK NATURAL JOIN BOOK_AUTHORS
                               GROUP BY Author_name
                               HAVING COUNT(*) >= ALL ( SELECT COUNT(*)
                                                        FROM BOOK NATURAL JOIN BOOK_AUTHORS
                                                        GROUP BY Author_name ) )
      
  17. For each branch and book, find the number of copies of the book still on the shelf at the branch on 2006-10-14. (The number still on the shelf is the number of copies the branch has that haven't been checked out.)

    Date_out < '2006-10-14' was accepted as "checked out on 2006-10-14" though the intent was that 2006-10-14 should be between the date out and the due date.

    A first step is to count how many copies of each book were checked out on the specified date:

        SELECT Branch_id, Book_id, COUNT(*) AS NumCheckedOut
        FROM BOOK_LOANS
        WHERE Date_out < '2006-10-14' AND Due_date > '2006-10-14'
        GROUP BY Branch_id, Book_id		    
      

    The number on the shelf is No_of_copies minus the number checked out, so we build a relation combining No_of_copies and NumCheckedOut for each branch and book combination:

        SELECT Branch_id, Branch_name, Book_id, Title, No_of_copies-NumCheckedOut AS OnShelf
        FROM LIBRARY_BRANCH NATURAL JOIN BOOK_COPIES NATURAL JOIN
          ( SELECT Branch_id, Book_id, COUNT(*) AS NumCheckedOut
            FROM BOOK_LOANS
            WHERE Date_out < '2006-10-14' AND Due_date > '2006-10-14'
            GROUP BY Branch_id, Book_id ) A NATURAL JOIN BOOK
      

    ...except that the relation with the number of copies checked out only contains entries for books and branches where copies have been checked out - so when that relation is joined with BOOK_COPIES, the only rows that result are for books and branches where copies have been checked out.

    So, add a count of 0 copies checked out for all the books that don't have any copies checked out to the relation containing how many copies of each book have been checked out on the specified date:

        ( SELECT Branch_id, Book_id, COUNT(*) AS NumCheckedOut
          FROM BOOK_LOANS
          WHERE Date_out < '2006-10-14' AND Due_date > '2006-10-14'
          GROUP BY Branch_id, Book_id )
        UNION 
        ( SELECT Branch_id, Book_id, 0 AS NumCheckedOut
          FROM BOOK_COPIES BC
          WHERE NOT EXISTS ( SELECT *
                             FROM BOOK_LOANS BL
                             WHERE BC.Branch_id=BL.Branch_id AND BC.Book_id=BL.Book_id AND
                             Date_out < '2006-10-14' AND Due_date > '2006-10-14') )
      

    And then use this as before to get the final result:

        SELECT Branch_id, Branch_name, Book_id, Title, No_of_copies-NumCheckedOut AS OnShelf
        FROM LIBRARY_BRANCH NATURAL JOIN BOOK_COPIES NATURAL JOIN
        ( ( SELECT Branch_id, Book_id, COUNT(*) AS NumCheckedOut
            FROM BOOK_LOANS
            WHERE Date_out < '2006-10-14' AND Due_date > '2006-10-14'
            GROUP BY Branch_id, Book_id )
          UNION 
          ( SELECT Branch_id, Book_id, 0 AS NumCheckedOut
            FROM BOOK_COPIES BC
            WHERE NOT EXISTS ( SELECT *
                               FROM BOOK_LOANS BL
                               WHERE BC.Branch_id=BL.Branch_id AND BC.Book_id=BL.Book_id AND
                               Date_out < '2006-10-14' AND Due_date > '2006-10-14') ) ) A NATURAL JOIN BOOK
      
  18. Delete any entries in BOOK_COPIES where the number of copies is 0.

        DELETE
        FROM BOOK_COPIES
        WHERE No_of_copies = 0
      
  19. Add 2 copies to each book owned by Wood Library.

    The following works as long as there is only one library branch named 'Wood Library'. Use IN or = ANY to account for the possibility of multiple branches with the same name.

        UPDATE BOOK_COPIES
        SET No_of_copies = No_of_copies+2
        WHERE Branch_id = ( SELECT Branch_id
                            FROM LIBRARY_BRANCH
                            WHERE Branch_name='Wood Library' )
      
  20. Change the due date of any book checked out from Wood Library that is due before 2006-11-05 to 2006-12-31.

    The following works as long as there is only one library branch named 'Wood Library'. Use IN or = ANY to account for the possibility of multiple branches with the same name.

        UPDATE BOOK_COPIES
        SET Due_date='2006-12-31'
        WHERE Due_date<'2006-11-05' AND Branch_id = ( SELECT Branch_id
                                                      FROM LIBRARY_BRANCH
                                                      WHERE Branch_name='Wood Library' )
      

Valid HTML 4.01!