Movie DB Query Questions for HW3

Please note that these questions may be interpreted in different ways. Just state your interpretations of them if you feel there is any ambiguity.

Remember there are a couple standard SQL operations not supported by SQL Server.  You shouldn't need them in the questions below.   However, if you do find them natural to use in your answers, you can reformulate your queries using other SQL operations.

  1. Say our store only wants to order movies from "Joe's House of Video" or "Video Warehouse". Find out which movies are supplied by these 2 suppliers.
  2. Which movie was rented for the longest duration (by any customer)?
  3. Say the store wants to find out the amount of business it is doing with each supplier. List the supplier names and a count of distinct orders that the store has made with that supplier.
  4. Find the names of movies for which more than 4 copies have been ordered? Remember that multiple copies could be ordered in a single order.
  5. Suppose the store is promoting Jim Carey movies and wants to phone customers who are Jim Carey fans. Find which customers rented "Ace Ventura: Pet Detective 1994" or rented "Ace Ventura: When Nature Calls 1995"? Are there any Jim Carey fans?
  6. When the store rents a movie for which it has only one copy, the owners want to ensure prompt return of that movie so other customers can rent it. List all customers who have checked out a movie for which the store has only one copy to show the owners the database can track such customers. (Note that the TapeID in inventory is different for different copies of the same MovieID)
  7. The store wants a list of its best customers so that it can send out flyers with discounts to thank them for continued patronage. To help the sore do this, list each customer and the number of movies they have rented.
  8. The store has received many complaints that it doesn't offer the movie "Almost Angels 1962". Find out which supplier has the cheapest price for that movie.
  9. Which movies aren't in the inventory?
  10. Say the movie rental store wants to offer unpopular movies for sale to free up shelf space for newer ones. Find the names of movies in the inventory that have never been checked out.

Back to Main Page