CSE444 Homework 3

Posted by:Prof. Lexx Posted on:Jul 6,2016

Homework 3 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:



 CustID LastName FirstName



TapeID MovieID



MovieID MovieName



SupplierID MovieID Price



OrderID SupplierID MovieID Copies



CustomerID TapeID CkoutDate Duration



SupplierID SupplierName



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.


  1. Which movies are supplied by “Joe’s House of Video” or “Video Warehouse”?
  2. Which movie was rented for the longest duration (by any customer)?
  3. 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.)
  4. How many movies in the inventory does each movie supplier supply? That is, for each movie supplier, calculate the number of movies it supplies that also happen to be movies in the inventory.
  5. For which movies have more than 4 copies been ordered?
  6. Which customers rented “Fatal Attraction 1987” or rented a movie supplied by “VWS Video”?
  7. For which movies are there more than 1 copy in our inventory? (Note that the TapeID in inventory is different for different copies of the same MovieID)
  8. Which customers rented movies for 5 days or more?
  9. Which supplier has the cheapest price for the movie “Almost Angels 1962”?
  10. Which movies aren’t in the inventory?



Prof. Lexx

Overview I have a Bachelor of Arts, majoring in English and minoring in History. I have worked in various fields ranging from academic research to freelance translating to editing to customer support and data entry. These include editing my old university's newspaper as well as co-leading their creative writing team; serving as a junior member in the history department's research network; publishing music and film reviews for several different magazines and webzines; and translating papers and books for numerous researchers in various languages, including Russian, Spanish, and Romanian. I am also a skilled typist, with a rate of at least 70 words per minute, and have myself digitized dozens of books and essays for both private and commercial use. I have a very strong work ethic, and make sure to prioritize the task I am given so that it is completed as quickly as possible. I am organized and disciplined, ensuring a job done professionally and efficiently.

    I have a background of 8 years in writing profession and currently pursuing also as an editor and proofreader. I have a knack for writing and thus, it was obvious to enhance my skills and serve others. Currently joined ‘Member of Association of Professional Writers and Editors’ a

    Pro Alex