Database Systems
CIT 381 Fall 2007

Assignment 4
Due: November 20, 4:30pm

For queries, please cut and paste the queries (and results of the queries if possible) into a document. Email to Paea a pdf version of your document. As before, use the BigHitVideo database.

Do four of the following five questions. For an extra 10 points, do all five.

1. (15 pts) List all customers who live in a state in which less than 5 customers live.
   
   
2. (15 pts) For each state, show the number of different movies that have been (previously) rented. This will involve some duplicate removal, as "Annie Hall" has been rented three times and "The Thirty-Nine Steps" two times by Florida residents - each should count as one Florida movie rental.
   
   
3. (15 pts) Determine which customer has (previously) rented the movie 'Annie Hall' the fewest times (but at least once). For full credit, write your query so that if there is a k-way tie for the fewest times, all k customers are displayed.
   
   
4. (15 pts) List all movie titles, genres, and count of videos, for movies that have never been previously rented (don't worry if they are currently rented). Label your count column appropriately. For example, the movie Elizabeth has not been previously rented (no videos of the movie are in the previousRental table), the genre is costume drama, and there is only one video of that movie.
   
   
5. (15 pts) List the total number of hours each employee has worked. You will need to join employee with timecard and use the TIMEDIFF function as in class.