homework 2 solutions

1)  Remember what are keys.  In B), the question asked for a library.  You
should put down either (branchid, no_of_copies) or (branchid, branchname,
no_of_copies), not just (branchname, no_of_copies).

2)  GROUP BY should include all the SELECT attributes and the KEY in
queries e) and f).

3)  Don't use JOIN or NATURAL JOIN or any of these operators.  Lots of SQL
implementations don't support it.

4)  Qualify your fields, otherwise, there will be ambiguous references.





A)  How many copies of the book titled The Lost Tribe are owned by the library branch whose name is 'Sharpstown'?
SELECT No_Of_Copies
  FROM BOOK_COPIES, BOOK, LIBRARY_BRANCH
 WHERE BOOK_COPIES.BookId = BOOK.BookId AND
       BOOK_COPIES.BranchId = LIBRARY_BRANCH.BranchId AND
       LIBRARY_BRANCH.BranchName = 'Sharpstown' AND
       BOOK.Title = 'The Lost World';


B)  How many copies of the book titled The Lost Tribe are owned by each library branch?
SELECT No_Of_Copies, BranchId
  FROM BOOK_COPIES, BOOK
 WHERE BOOK_COPIES.BookId = BOOK.BookId AND
       BOOK.Title = 'The Lost World';


C)  Retrieve the names of all borrowers who do not have any books checked out.

select name
from borrower
where not exists (select * 
                  from book_loans
                  where book_loans.cardno = borrower.cardno);

select br.name 
from borrower br 
where br.cardno not in (select bl.cardno 
                        from book_loans bl);

SELECT Name
FROM BORROWER,
 ((SELECT CardNo
    FROM BORROWER)
 MINUS
 (SELECT CardNo
  FROM BOOK_LOANS)) NO_CHECKOUT_B
WHERE BORROWER.CardNo = NO_CHECKOUT_B.CardNo;

Note:  Instead of EXCEPT, NOT IN is also acceptable.  See Sec. 8.3.1 in Navathe99.  In ORACLE, use MINUS instead of EXCEPT.

D)  For each book that is loaned out from the 'Sharpstown' branch and whose DueDate is today, retrieve the book title, the borrower's name, and the borrower's address.

SELECT Title, Name, BORROWER.Address
  FROM LIBRARY_BRANCH, BOOK_LOANS, BORROWER, BOOK
 WHERE BranchName='Sharpstown' AND
       DueDate='10/10/99' AND
       BOOK.BookId=BOOK_LOANS.BookId AND
       BORROWER.CardNo = BOOK_LOANS.CardNo AND
       LIBRARY_BRANCH.BranchId = BOOK_LOANS.BranchId;

E)  For each library branch, retrieve the branch name and the total number of books loaned out from that branch.

SELECT BranchName, COUNT(*)
  FROM BOOK_LOANS, LIBRARY_BRANCH
 WHERE BOOK_LOANS.BranchId = LIBRARY_BRANCH.BranchId
GROUP BY BranchName, LIBRARY_BRANCH.BranchId;

F)  Retrieve the names, addresses, and number of books checked out for all borrowers who have more than five books checked out.

SELECT Name, Address, COUNT(*)
  FROM BOOK_LOANS, BORROWER
 WHERE BOOK_LOANS.CardNo = BORROWER.CardNo
  GROUP BY Name, Address, BORROWER.CardNo
  HAVING COUNT(*) > 5;

G)  For each book authored (or coauthored) by 'Stephen King,' retrieve the title and the number of copies owned by the library branch whose name is 'Central.'

SELECT Title, No_Of_Copies
  FROM BOOK, LIBRARY_BRANCH, BOOK_AUTHORS, BOOK_COPIES
 WHERE BOOK_AUTHORS.AuthorName='Stephen King' AND
       BOOK_AUTHORS.BookId=BOOK.BookId AND
       BOOK.BookId=BOOK_COPIES.BookId AND
       BOOK_COPIES.BranchId=LIBRARY_BRANCH.BranchId AND
       LIBRARY_BRANCH.BranchName='Central';