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.

  2. Find all of the book titles, in alphabetical order.

  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.

  4. Display all of the author names.

  5. Find the name, address, and phone number for all borrowers with a 789 area code.

  6. Find the names and card numbers of borrowers who have checked out books from more than one branch, in order by card number.

  7. Find the books (book titles and authors) not written by C.J. Cherryh, in order by author and 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.

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

  10. Find the title of the book which is alphabetically first of all of the books.

  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.

  12. For each branch (branch ID and name), find the number of books checked out on 2006-10-14.

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

  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.

  15. Find the publisher who has published the largest number of books.

  16. Find the titles of all of the books written by the author(s) who have written the largest number of books.

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

  18. Delete any entries in BOOK_COPIES where the number of copies is 0.

  19. Add 2 copies to each book owned by 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.


Valid HTML 4.01!