CSE444 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:
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.
There is a collection of suppliers from which the store can order described by Suppliers.
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.
The store can place an order for one or more movies with one of its suppliers. This information is kept in the Orders relation.
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.
The store tracks its customers with the Customers relation.
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.
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.
Which movie was rented for the longest duration (by any customer)?
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.
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.
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?
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)
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.
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.
Which movies aren't in the inventory?
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.
