CSE444 This homework will give you practice writing SQL statements and using the query facility of SQL Server. The queries that you'll implement in SQL Server are based on a movies database. The schema for the movie database is as follows:
Write out SQL statements for the following 10 queries about the movie database. Enter the SQL code in the ISQL_w facility of SQL Server (as talked about in class last Friday). Select the 'cse444sql' database in the DB combo box of the ISQL_w window, (that's the movie database described above despite the undescriptive name). Enter your query in the query tab and check your results in the result tab.
To turn in: Please turn in a print out of your SQL queries and the number of tuples returned in the resulting tables for each one. (The tables themselves may be long, so don't bother printing those out.)
Please note that these questions may be interpreted in different ways. Just state your interpretations of them if you feel there is any ambiguity.
Which movies are supplied by "Joe's House of Video" or "Video Warehouse"?
WHERE Suppliers.SupplierName = "Joe's House of Video" and Movies.MovieID = MovieSupplier.MovieID and Suppliers.SupplierID = MovieSupplier.SupplierID
FROM Movies, MovieSupplier, Suppliers
WHERE Suppliers.SupplierName = "Video Warehouse" and Movies.MovieID = MovieSupplier.MovieID and Suppliers.SupplierID = MovieSupplier.SupplierID
Which movie was rented for the longest duration (by any customer)?
FROM Rentals, Movies, Inventory
WHERE Movies.MovieID = Inventory.MovieID and Inventory.TapeID = Rentals.TapeID and Rentals.Duration >= ALL (SELECT Duration FROM Rentals)
Which suppliers supply all the movies in the inventory? (Hint: first get a list of the movie suppliers and all the movies in the inventory using the cross product. Then find out which of these tuples are invalid.)