School of Computer Science, McGill University
COMP-421B Database Systems, Winter 2008
Written Assignment 3: SQL
Sample Solution
Consider the following relational schema for a single movie rental store:
Customers(CustID, LastName, FirstName)
Inventory(TapeID, MovieID)
Movies(MovieID, MovieName)
MovieSupplier(SupplierID, MovieID, Price)
Orders(OrderID, SupplierID, MovieID, Copies)
Rentals(CustomerID, TapeID, CkoutDat, Duration)
Suppliers(SupplierID, SupplierName)
Following are the specifications for the database:
- There is a large collection of movies described by the Movies relation - conceptually, this refers to all the movies ever put on videocassette. Each is uniquely identified by the MovieID.
- There is a collection of suppliers from which the store can order described by Suppliers.
- The MovieSupplier relation describes all the movies a supplier has to sell. It also describes how much the supplier will charge for a copy of that movie.
- The store can place an order for one or more movies with one of its suppliers. This information is kept in the Orders relation.
- When an order from a supplier arrives, the movies are marked as being in inventory. The Inventory relation describes the current set of movies the movie rental store has to offer customers. The store uses a TapeID to uniquely distinguish the movies it has in inventory since it may have more than one copy of a movie.
- The store tracks its customers with the Customers relation.
- The Rentals relation tracks which movies from the stores inventory that a customer has ever rented. A movie has a checkout date and a rental period associated with it.
Note: For all the exercises one to five, you need to submit a db2 runnable script.
Exercise 1: DDL (10 Points)
Provide a set of SQL DDL commands that can be used to create this relational schema. These commands must be runnable in DB2. These commands should define the tables and primary and foreign key constraints that appear in your relational schema. Also, you need to declare the attributes that may and may not be null.
“create table customers (custID int not null, LastName varchar (20), Firstname varchar (20), primary key (custID))”
"create table inventory ( \
tapeID int not null,\
movieID varchar(10),\
primary key (tapeID),\
foreign key (movieID) references movies)"
"create table movies( \
movieID varchar(10) not null,\
movieName varchar(40),\
primary key (movieID))"
"create table movieSupplier ( \
movieID varchar(10) not null,\
supplierID varchar(10) not null,\
price float,\
primary key (movieID, supplierID),\
foreign key (movieID) references movies,\
foreign key (supplierID) references suppliers)"
"create table orders ( \
orderID int not null,\
supplierID varchar(10),\
movieID varchar(10),\
copies int, \
primary key (orderID),\
foreign key (movieID) references movies,\
foreign key (supplierID) references suppliers)"
"create table rentals ( \
custID int not null,\
tapeID int not null,\
ckoutdat date not null,\
duration int,\
primary key (custID, tapeID, ckoutdat),\
foreign key (custID) references customers,\
foreign key (tapeID) references inventory)"
"create table suppliers ( \
supplierID varchar(10) not null,\
supplierName varchar(40),\
primary key (supplierID))"
Exercise 2: Populating Data (10 Points)
Provide several INSERT statements with data that will populate the tables, i.e. some Customers (at least 5), some Inventory (at least 20), some Movies (at least 10), some MovieSupplier (at least 10), some Orders (at least 2 per movie), some rental records (at least 10), some suppliers (at least 2).
"insert into customers values (90000, 'Xu', 'David')"
"insert into customers values (90001,'Sun', 'Bill')"
"insert into customers values (90002, 'Tremblay', 'Gerald')"
"insert into customers values (90003, 'Hanks', 'Tom')"
"insert into customers values (90004, 'Simth', 'Jen')"
insert into movies values ('a0000','Chicago');
insert into movies values ('a0001', 'Shanghai knight');
insert into movies values ('a0002','Hours');
insert into movies values ('a0003','Gangs of New York');
insert into movies values ('d0001','Pianist');
insert into movies values ('c0000','Dragon Land');
insert into movies values ('c0001','Olie');
insert into movies values ('c0002','Almost Angels 1962');
insert into movies values ('c0003','Nature Calls 1995');
insert into movies values ('d0000','Pet Detective 1994');
"insert into suppliers values ('s001', 'Joe’’s House of Video')"
"insert into suppliers values ('s002', 'Video Warehouse')"
insert into movieSupplier values ('a0000', 's001',9.99);
insert into movieSupplier values ('a0001', 's001', 9.99);
“insert into movieSupplier values ('c0002', 's002', 9.99)”
insert into movieSupplier values ('a0003', 's002', 8.99);
insert into movieSupplier values ('a0000', 's002', 7.99);
insert into movieSupplier values ('c0000', 's001', 5.99);
insert into movieSupplier values ('c0001', 's001', 4.99);
“insert into movieSupplier values ('c0002', 's001', 5.99)”
insert into movieSupplier values ('c0003', 's001', 6.99);
insert into movieSupplier values ('d0000', 's001', 9.99);
insert into inventory values (10000, 'a0000');
insert into inventory values (10001, 'a0000');
insert into inventory values (10002, 'a0000');
insert into inventory values (10003, 'a0000');
insert into inventory values (10004, 'a0000');
insert into inventory values (10005, 'c0000');
insert into inventory values (10006, 'c0000');
insert into inventory values (10007, 'c0001');
insert into inventory values (10008, 'c0002');
insert into inventory values (10009, 'c0002');
insert into inventory values (10010, 'c0003');
insert into inventory values (10011, 'd0000');
insert into inventory values (10012, 'd0000');
insert into inventory values (10013, 'd0001');
insert into inventory values (10014, 'd0001');
insert into inventory values (10015, 'd0002');
insert into inventory values (10016, 'd0002');
insert into inventory values (10017, 'a0003');
insert into inventory values (10018, 'c0000');
insert into inventory values (10019, 'c0001');
insert into orders values (50000, 's001', 'a0000', 150);
insert into orders values (50001, 's002', 'a0000', 100);
insert into orders values (50002, 's001', 'a0001', 100);
insert into orders values (50003, 's001', 'a0001', 50);
insert into orders values (50004, 's001', 'a0002', 50);
insert into orders values (50005, 's001', 'a0002', 50);
insert into orders values (50006, 's002', 'a0003', 40);
insert into orders values (50007, 's002', 'a0003', 40);
insert into orders values (50008, 's001', 'a0004', 50);
insert into orders values (50009, 's001', 'a0003', 50);
insert into orders values (50010, 's001', 'c0000', 20);
insert into orders values (50011, 's001', 'c0000', 50);
insert into orders values (50012, 's001', 'c0001', 30);
insert into orders values (50013, 's002', 'c0001', 50);
insert into orders values (50014, 's001', 'c0002', 50);
insert into orders values (50015, 's001', 'c0002', 50);
insert into orders values (50016, 's001', 'c0003', 50);
insert into orders values (50017, 's001', 'c0003', 50);
insert into orders values (50018, 's001', 'd0000', 15);
insert into orders values (50019, 's001', 'd0000', 20);
insert into rentals values (90000,10000, '2003-02-16',2);
insert into rentals values (90000,10001, '2003-02-12',1);
insert into rentals values (90000,10002, '2003-02-16',2);
insert into rentals values (90000,10003, '2003-02-16',4);
insert into rentals values (90000,10000, '2003-01-16',2);
insert into rentals values (90001,10000, '2003-02-16',1);
insert into rentals values (90001,10001, '2003-02-16',1);
insert into rentals values (90002,10003, '2003-02-15',2);
insert into rentals values (90003,10004, '2003-01-12',3);
insert into rentals values (90003,10008, '2003-01-16',1);
insert into rentals values (90003,10000, '2003-02-16',2);
Exercise 3: Queries (60 Points)
Write the following queries in SQL.
- 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.
"select m.movieID \
from Suppliers s, MovieSupplier m \
where s.SupplierID = m.SupplierID AND \
(s.SupplierName = 'Joe’s House of Video' OR\
s.SupplierName = 'Video Warehouse')\
- Which movie was rented for the longest duration (by any customer)?
"select i.movieID \
from rentals r, inventory i \
where r.tapeID = i.tapeID AND \
r.duration >= All (select duration \
from rentals) "
or: r.duration in (select MAX(duration)
from rentals)
- 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.
"select s.supplierName, count (distinct o.orderID) \
from suppliers s, orders o \
where s.supplierID = o.supplierID \
group by s.supplierName"
- 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.
"select distinct m.movieName \
from movies m, orders o \
where m.movieID = o.movieID AND \
o.copies > 4"
- 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?
"select r.custID \
from rentals r, Inventory i, movies m \
where r.tapeID = i.tapeID AND \
i.movieID = m.movieID AND\
m.movieName = 'Ace Ventura: Nature Calls 1995 '\
union \
select r.custID\
from rentals r, Inventory i, movies m\
where r.tapeID = i.tapeID AND\
i.movieID = m.movieID AND\
m.movieName = 'Ace Ventura: Pet Detective 1994'"
- 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)
"select distinct r.custID \
from rentals r, inventory I \
where r. tapeID = i.tapeID \
Having count(i.movieID) = 1"
- 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.
"select r.custID, count(i.movieID)\
from rentals r, inventory i\
where r.tapeID = i. tapeID\
group by r.custID"
- 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.
"select ms.supplierID \
from movieSupplier ms, movies m\
where m.movieName = 'Almost Angels 1962' And\
ms.movieID = m.movieID AND\
ms.price <= All (select ms1. price\
from movieSupplier ms1\
where ms1.movieID = m.movieID)"
or: ms.price = (select MIN(ms1.price )
from movieSupplier ms1
where ms1. movieID = m.movieID)
- Which movies aren't in the inventory?
"select movieID\
from movies\
except\
select movieID\
from inventory"
- 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?
"select movieName \
from Movies\
where movieID not in (select i.movieID\
from inventory i, rentals r\
where r.tapeID = i.tapeID)"
Exercise 4: Deleting Data (10 Points)
Provide several DELETE statements that delete all data that you populated the database with in exercise2.
delete from customers or delete * from customers
delete from inventory
delete from movies
delete from movieSupplier
delete from orders
delete from rentals
Exercise 5: Deleting tables (10 Points)
Provide several DROP statements that delete all tables that you created in the database in exercise1.
drop table customers;
drop table inventory;
drop table movies;
drop table moviesupplier;
drop table orders;
drop table rentals;
drop table suppliers;