# Possible Solutions for HW#2 SQL

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).

1. SELECT LastName, FirstName, MovieName
2. FROM Customers, Inventory, Movies, Rentals

WHERE CustID = CustomerID AND Inventory.TapeID = Rentals.TapeID AND Inventory.MovieID = Movies.MovieID;

3. SELECT LastName, FirstName, MovieName
4. FROM Customers, Inventory, Movies, Rentals

WHERE CustID = CustomerID AND Inventory.TapeID = Rentals.TapeID AND Inventory.MovieID = Movies.MovieID AND CkoutDate < 10/4/96;

5. SELECT DISTINCT MovieName
6. FROM MovieSupplier, Movies

WHERE MovieSupplier.MovieID = Movies.MovieID

7. SELECT DISTINCT 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));

1. SELECT SupplierName, COUNT(MovieID) AS MovieCount, MIN(Price) AS MinPrice, MAX(Price) AS MaxPrice, AVG(Price) AS AvgPrice

FROM MovieSupplier, Suppliers

WHERE MovieSupplier.SupplierID = Suppliers.SupplierID

GROUP BY SupplierName;

7.) NumTapes

1. SELECT COUNT(TapeID) AS TapeCount, LastName, FirstName

FROM Customers, Rentals

WHERE CustID = CustomerID

GROUP BY CustID;

SELECT LastName, FirstName, MAX(TapeCount) AS MaxTapes

FROM NumTapes;

1. SELECT DISTINCT LastName, FirstName

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);