Comp 521: Files and Databases -- Fall 2012 Problem Set #2 Issued: 9/12/2012 Due: In class 9/25/2012
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.
Write SQL queries to generate the following result sets:
A list of all customer names (First and Last) with invoices dated in the 2011 calendar year. (Note: In SQL you can do comparsions between Dates specified in ISO format, where January 1, 2011 is specified as '2011-01-01' and December 31, 2011 is specified as '2011-12-31').
A list of all customer names (First and Last) who have ordered one or more screwdrivers. (You can assume that 'screwdriver' appears somewhere in the Description field of an Inventory record).
List all products that are only available from a single source (only one manufacturer)
List those products that can be supplied by every manufacturer.
A list of customer numbers and product codes for those items that a customer has ordered on two or more ocassions.
A list of customer names and the number of orders they have made.
A list of all invoice numbers and their total price.
A list of sales volume attributable to each customer, with the customer's name and the total dollar amount.
A list of the sales volume, in units sold, for each inventory item.
A list of the sales volume of all invoices according the customer's home state, with the state and dollar amount.
List all items where the quantities invoiced surpass the current inventory.
Question 2.
For this progamming problem you will need to first download the following database movies.db (~800 Mbytes) with the schema:
CREATE TABLE Customers ( cardNo INTEGER PRIMARY KEY, first TEXT, last TEXT, sex CHAR, dob DATE );
CREATE TABLE Movies ( movieId INTEGER PRIMARY KEY, title TEXT, year INTEGER );
Write a single python program that prints answers to the following queries. You should attempt to answer each using a single embedded SQL query.
List the titles of all movies that include "Star Wars" in their title.
List the full names of all customers with first names of "PAUL" or "ROBERT" who rented "The Sting"
List of customers who rented "2001: A Space Odyssey" in the first 7 days of 2001.
Which customer or customers rented the most movies?
Want is the most rented movie?
Which customers saw both "King Kong" and "Godzilla" and ranked "King Kong" higher?
Which customer rated the most movies as "5"?
Which movie has the most ratings of "1"?
List all first names, last names, and movie titles of customers with first name "ALICE" who rented a movie with "Alice" in the title.
What is the most common Rental rating?
Turn in a listing of your program and a print out of its output (label the output with its corresponding part).
Question 3.
Reconsider the the schema defined in Question 1. Use table constraints, assertions, or triggers to enforce the following integrity constraints.
Before an invoice is deleted all of its invoice lines should first be removed.
You cannot insert or update an invoice line which would result in the total of all NumSold attributes for the specified product would exceed the current inventory for that item.