Comp 521: Files and Databases -- Fall 2010 Problem Set #2 Issued: 9/15/2010 Due: In class 9/29/2010
Homework Information: Some of the problems are probably too long to attempt the night before the due date, so plan accordingly. Late homework will penalized by a factor of 70.71% for each late class period. Feel free to work with others, but the work you hand in should be your own.
Question 1.
Consider the follow database schema:
CREATE TABLE Person ( id INTEGER, FirstName TEXT, FamilyName TEXT, DateOfBirth DATE, BirthPlace TEXT, PRIMARY KEY (id) );
Write a single python program that prints answers to the following queries. You should attempt to answer each using a single embedded SQL query.
A. The list of customers born in 1990 or after with a first name that is the same as their last name.
B. The most rented movie(s).
C. The customer(s) with the most rentals.
D. How many customers have not rented a movie.
E. Customers who have rented more than 100 movies and rated every one with one star (rating = 1).
F. Customers who have rented more than 200 movies in a same day.
G. A list of customers who have rented at least 4 of the 6 movies in the Star Wars saga (ignore if they rented any supplemental materials or other Star Wars related materials).
H. All movie titles and their rating which have a unanimous rating by all customers who rented it.
I. All movies rented by two or more customers that also rented the movie "Woman in the Moon"
J. The two movies with the highest and lowest average ratings respectively.
K. In this database, in what year were the most movies released?
L. Find all pairs of customers who have rented more than 50 rentals in common and given them the same ratings
M. Find the most times that any single movie was rented by the same customer.
Turn in a listing of your program and a print out of its output (label the output with its corresponding part).