MCS-274 Lab 3 (Spring 2005)

Objective

You will use more advanced aspects of the SQL query language to retrieve information from your movie rental database.

Specific goals

Write and test an SQL query for each of the following:

• Who rented all movies directed by Orson Welles?
• Who rented only movies directed by Orson Welles? Be sure to make clear (in English) how you are interpreting this question; there is more than one reasonable interpretation.
• What is the average duration of a (completed) rental?
• For each customer, what is the average duration of a (completed) rental? Exclude customers who have never rented, because the average would be undefined for them. Organize this table by decreasing duration.
• For each customer, how many times has that customer rented a movie? Be sure to include customers who have never rented a movie. Specify whether you have included presently ongoing rentals.
• For each customer, how many movies has that customer rented? Be sure to include customers who have never rented a movie. Specify whether you have included presently ongoing rentals.

Lab report

Write a report that is concise and assumes an audience familiar with your assignment and the technology, but which addresses all the above topics in an organized fashion.