CSE444

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;

  2. SELECT LastName, FirstName, MovieName

    3. FROM Customers, Inventory, Movies, Rentals

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

  3. SELECT DISTINCT MovieName

    4. FROM MovieSupplier, Movies

    WHERE MovieSupplier.MovieID = Movies.MovieID

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