Homework 3

  1. Please submit this homework in an ascii text file named hw3.txt, with each exercise clearly marked. For example: 
    6.7 SELECT * FROM Hotel;

6.9 SELECT GuestName, GuestAddress FROM Guest
       WHERE GuestAddress LIKE '%London%';
    We will be creating our own test data, then copying and pasting your answers into the mysql interpreter.

  2. Please submit this homework using blackboard and not the submit scripts.

  1. 6.18
  2. 6.20
  3. 7.11: Hint for parts (e) and (f)--You will have to join the Booking table with itself. You can join a table with itself by listing the relation twice in the FROM clause. For example: 
    SELECT * FROM Booking b, Booking c WHERE
	...
  4. Required for graduate students, extra credit (15 points) for undergraduate students: Write an SQL query that prints each branch (i.e., the branchNo) whose staff count exceeds the average staff count for a branch, and print the amount by which each such branch exceeds the average staff count. Use the branch and staff relations from the DreamHome case study in the book.