CSE444
HW3 Possible solutions
The queries that you'll implement in SQL Server are based on a movies database. The database keeps track of the movies, customers, orders, and movie suppliers for a single movie rental store. The schema for the movie database is as follows:
Customers
CustID |
LastName |
FirstName |
Inventory
TapeID |
MovieID |
Movies
MovieID |
MovieName |
MovieSupplier
SupplierID |
MovieID |
Price |
Orders
OrderID |
SupplierID |
MovieID |
Copies |
Rentals
CustomerID |
TapeID |
CkoutDate |
Duration |
Suppliers
SupplierID |
SupplierName |
Specifications for the database are as follows:
Please note that these questions may be interpreted in different ways. Just state your interpretations of them if you feel there is any ambiguity.
Row count = 193
SELECT MovieName
FROM Movies, MovieSupplier, Suppliers
WHERE Suppliers.SupplierName = "Joe's House of Video" and Movies.MovieID = MovieSupplier.MovieID and Suppliers.SupplierID = MovieSupplier.SupplierID
UNION
SELECT MovieName
FROM Movies, MovieSupplier, Suppliers
WHERE Suppliers.SupplierName = "Video Warehouse" and Movies.MovieID = MovieSupplier.MovieID and Suppliers.SupplierID = MovieSupplier.SupplierID
Row count = 14
SELECT Movies.MovieName
FROM Rentals, Movies, Inventory
WHERE Movies.MovieID = Inventory.MovieID and Inventory.TapeID = Rentals.TapeID and Rentals.Duration >= ALL (SELECT Duration FROM Rentals)
Row count = 12
SELECT Suppliers.SupplierName, COUNT( DISTINCT OrderID)
FROM Suppliers, Orders
WHERE Suppliers.SupplierID = Orders.SupplierID
GROUP BY Suppliers.SupplierName
Row count = 3
SELECT Movies.MovieName
FROM Movies, Orders
WHERE Orders.MovieID = Movies.MovieID
GROUP BY Movies.MovieName
HAVING SUM(Copies) > 4
Row count = 0
SELECT LastName
FROM Customers, Rentals, Inventory, Movies
WHERE Customers.CustID = Rentals.CustomerID and Rentals.TapeID = Inventory.TapeID and Inventory.MovieID = Movies.MovieID and (Movies.MovieName = "Ace Ventura: Pet Detective 1994" or
Movies.MovieName = "Ace Ventura: When Nature Calls 1995" )
Row count = 29
SELECT DISTINCT Customers.LastName
FROM Customers, Rentals, Inventory
WHERE Customers.CustID = Rentals.CustomerID and Rentals.TapeID = Inventory.TapeID and Inventory.MovieID IN
(SELECT Inventory.MovieID
FROM Inventory
GROUP BY Inventory.MovieID
HAVING COUNT(TapeID) = 1)
Row count = 63
SELECT LastName, COUNT(*)
FROM Customers, Rentals
WHERE Customers.CustID = Rentals.CustomerID
GROUP BY Customers.CustID, Customers.LastName
Row count = 1
SELECT Suppliers.SupplierName
FROM Suppliers, MovieSupplier, Movies
WHERE Suppliers.SupplierID = MovieSupplier.SupplierID and MovieSupplier.MovieID = Movies.MovieID and Movies.MovieName = "Almost Angels 1962"
and price <= ALL
(SELECT price
FROM MovieSupplier, Movies
WHERE Movies.MovieID = MovieSupplier.MovieID and
Movies.MovieName = "Almost Angels 1962")
Row count = 700
SELECT Movies.MovieName
FROM Movies
WHERE MovieID NOT IN
(SELECT MovieID
FROM Inventory)
Row count = 1460
SELECT DISTINCT Movies.MovieName
FROM Movies, Inventory
WHERE Movies.MovieID = Inventory.MovieID and
Inventory.MovieID NOT IN
(SELECT DISTINCT MovieID
FROM Rentals, Inventory
WHERE Rentals.TapeID = Inventory.TapeID)