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:

Specifications for the database are as follows:

1. 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.
2. There is a collection of suppliers from which the store can order described by Suppliers.
3. 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.
4. The store can place an order for one or more movies with one of its suppliers. This information is kept in the Orders relation.
5. 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.
6. The store tracks its customers with the Customers relation.
7. 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.

Please note that these questions may be interpreted in different ways. Just state your interpretations of them if you feel there is any ambiguity.

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

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

2. Which movie was rented for the longest duration (by any customer)?

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)

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

Row count = 12

SELECT Suppliers.SupplierName, COUNT( DISTINCT OrderID)

FROM Suppliers, Orders

WHERE Suppliers.SupplierID = Orders.SupplierID

GROUP BY Suppliers.SupplierName

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

Row count = 3

SELECT Movies.MovieName

FROM Movies, Orders

WHERE Orders.MovieID = Movies.MovieID

GROUP BY Movies.MovieName

HAVING SUM(Copies) > 4

5. 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?

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

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

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)

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

Row count = 63

SELECT LastName, COUNT(*)

FROM Customers, Rentals

WHERE Customers.CustID = Rentals.CustomerID

GROUP BY Customers.CustID, Customers.LastName

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

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

 

9. Which movies aren't in the inventory?

Row count = 700

SELECT Movies.MovieName

FROM Movies

WHERE MovieID NOT IN

(SELECT MovieID

FROM Inventory)

10. 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 DISTINCT Movies.MovieName

FROM Movies, Inventory

WHERE Movies.MovieID = Inventory.MovieID and

Inventory.MovieID NOT IN

(SELECT DISTINCT MovieID

FROM Rentals, Inventory