Courtesy of Jake. Some formatting was lost in the transition to HTML. We still have a few copies of the handout. There is also a handout with the relational algebra solutions (not online, sorry).
FROM Customers, Inventory, Movies, Rentals
WHERE CustID = CustomerID AND Inventory.TapeID = Rentals.TapeID AND Inventory.MovieID = Movies.MovieID;
FROM Customers, Inventory, Movies, Rentals
WHERE CustID = CustomerID AND Inventory.TapeID = Rentals.TapeID AND Inventory.MovieID = Movies.MovieID AND CkoutDate < 10/4/96;
FROM MovieSupplier, Movies
WHERE MovieSupplier.MovieID = Movies.MovieID
FROM Movies
WHERE MovieID NOT IN
(SELECT DISTINCT MovieID
FROM MovieSupplier);
5.) MegaVideos
SELECT MovieID
FROM MovieSupplier, Suppliers
WHERE MovieSupplier.SupplierID = Suppliers.SupplierID AND
SupplierName = "MegaVideo"
SELECT SupplierName
FROM Suppliers AS S, MovieSupplier
WHERE MovieSupplier.SupplierID = Suppliers.SupplierID AND
NOT EXISTS
(SELECT *
FROM MegaVideos AS MV
WHERE
NOT EXISTS
(SELECT *
FROM MovieSuppliers AS MS
WHERE MV.MovieID = MS.MovieID AND MS.SupplierID =
S.SupplierID));
FROM MovieSupplier, Suppliers
WHERE MovieSupplier.SupplierID = Suppliers.SupplierID
GROUP BY SupplierName;
7.) NumTapes
FROM Customers, Rentals
WHERE CustID = CustomerID
GROUP BY CustID;
SELECT LastName, FirstName, MAX(TapeCount) AS MaxTapes
FROM NumTapes;
FROM Customers, Rentals, Inventory
WHERE CustID = CustomerID AND Rentals.TapeID = Inventory.TapeID
AND MovieID NOT IN
(SELECT DISTINCT A.MovieID
FROM Inventory AS A, Inventory AS B
WHERE A.MovieID = B.MovieID AND A.TapeID <> B.TapeID)
AND MovieID NOT IN
(SELECT DISTINCT MovieID
FROM Orders);