CSE444

Homework 4: SQL Server

Due: Wednesday, Dec. 3

This is an HTML version of the handout Jake prepared for 11/24/97. It should be identical except for formatting. Check the mailing list for corrections or further instructions on the use of the software. In calculating the final course grade, this will be part of the "Hands-On" category (so is the Microsoft Access part of a previous homework).

MSAccess would not allow us to use several important SQL keywords. To give you practice with such keywords and with a new query application, this assignment requires you to implement 5 queries in Microsoft SQL Server. The queries are based on the Movies Database. This database was imported to SQL Server. All tables and attributes are identical to the original schema:

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

To turn in:

Run each of the following queries in SQL Server. Print out the query and the resulting table. (Note: The first 2 are directly from HW2. You can use the solution sheet for HW2 to do these.)

Generate the names of all customers and the movies they have checked out.
Generate a table that shows for each supplier the number of movies supplied and the minimum, maximum, and average price for a movie.
Generate a table that shows the number of orders we’ve placed with each supplier. Show only those suppliers with whom we have placed more than 2 orders. (Hint: SQL Server lets us use the HAVING clause)
Generate a list of all customers who have rented an adventure movie. That is, the title of the movie has the word "Adventure" in it. (Hint: page 210 in the book… LIKE)
Generate a list of movies which are more expensive than ALL the Movies that "MegaVideo" supplies. (Hint: page 200… ALL)

Using Microsoft SQL Server…

To run the application (from your csepclab account), click on the start menu and go to Programs, Visual Studio 97, Microsoft SQL Server 6.5, ISQL_w.
Select the CSE444sql database from the "DB:" combo box in the open window. This is the Movies database.
Enter queries directly in the query tab.
Hit the green arrow button in the upper right hand corner of the window to execute the query.
Click on the results tab to find the results of the query.
Print the results using the print option in the File menu on the main menu bar.