Comp 521 Fall 2015 Assignment 2

Assigned: 15 September 2015
Due: 29 September 2015

Download and unzip movies.db.zip to produce a movies.db file in your working folder. This database is about 836 megabytes so some of the queries may take a while, start early. This database has the following schema:

Customers (
    cardNo INTEGER PRIMARY KEY,
    first TEXT,
    last TEXT,
    sex CHAR,
    dob DATE
)

Movies (
    movieId INTEGER PRIMARY KEY,
    title TEXT,
    year INTEGER
)

Rentals (
    cardNo INTEGER,
    movieId INTEGER,
    date DATE,
    rating INTEGER,
    PRIMARY KEY(cardNo, movieID, date),
    FOREIGN KEY (cardNo) REFERENCES Customers,
    FOREIGN KEY (movieId) REFERENCES Movies
)

Write code in the cells below to answer each question.

1. Find the following using SQL queries

1a. How many male customers are there?

1b. How many movies were released in either 2004 or 2006?

1c. What are the first and last names of the oldest customers in alphabetical order by last and first name?

1d. How many customers were born before January 1st, 1950 (not inclusive)?

1e. What is the average rating for all movies rented between January 1st 2000, and December 31st 2000 (inclusive)?

2. Fill in the body of the functions below.

2a. Fill in code to make this function return a list with the titles of the films released in the given year, sorted alphabetically.

2b. Write a function that takes a string representing some part of a film title, and returns a list of tuples with the first and last names of customers who rented those films and rated them 3 or higher. Sort this list alphabetically by last name and first names.

2c. Write a function that takes year as input, and returns a list of the titles of the most rented films among the films released that year.

Done!

Now get your report, save, and submit your notebook. I recommend you restart the kernel, and run all, just to be sure things are going to work correctly when I run it.