Homework





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:

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


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

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

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

SELECT Suppliers.SupplierName
FROM Suppliers
WHERE Supplier.SupplierID NOT IN
(SELECT MovieSupplier.SupplierID,
FROM MovieSupplier AS MS, Inventory AS I
WHERE NOT EXISTS
                  (SELECT *
                  FROM Inventory, MovieSupplier
WHERE MovieSupplier.MovieID = Inventory.MovieID and
   MovieSupplier.SupplierID = MS.SupplierID and
   Inventory.MovieID = I.MovieID) )


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.

SELECT Suppliers.SupplierName, COUNT( DISTINCT MovieID)
FROM Suppliers, MovieSupplier, Movies
WHERE Suppliers.SupplierID = MovieSupplier.SupplierID and MovieSupplier.MovieID = Movies.MovieID
GROUP BY Suppliers.SupplierName


5.     For which movies have more than 4 copies been ordered?

SELECT Movies.MovieName
FROM Movies, Orders
WHERE Orders.MovieID = Movies.MovieID
GROUP BY Movies.MovieName
HAVING SUM(Copies) > 4


6.     Which customers rented "Fatal Attraction 1987" or rented a movie supplied by "VWS Video"?

SELECT LastName
FROM Customers, Rentals, Inventory, Movies
WHERE Customers.CustID = Rentals.CustID and Rentals.TapeID = Inventory.TapeID and Inventory.MovieID = Movies.MovieID and Movies.MovieName LIKE "%Fatal Attraction 1987%"
UNION
SELECT LastName
FROM Customers, Rentals, Inventory, Movies, MovieSupplier, Suppliers
WHERE Customers.CustID = Rentals.CustID and Rentals.TapeID = Inventory.TapeID and Inventory.MovieID = Movies.MovieID and
Movies.MovieID = MovieSupplier.MovieID and MovieSupplier.SupplierID = Suppliers.SupplierID and Suppliers.SupplierName = "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)

SELECT Movies.MovieName
FROM Inventory AS I1, Inventory AS I2, Movies
WHERE I1.MovieID = I2.MovieID and I1.TapeID <> I2.TapeID
      and I1.MovieID = Movies.MovieID

8.     Which customers rented movies for 5 days or more?

SELECT DISTINCT LastName
FROM Customers, Rentals
WHERE Customers.CustID = Rentals.CustID and Duration >= 5

9.     Which supplier has the cheapest price for the movie "Almost Angels 1962"?

SELECT Suppliers.SupplierName
FROM Suppliers, MovieSuppliers, Movies
WHERE Suppliers.SupplierName = MovieSuppliers.SupplierName and MovieSuppliers.MovieID = Movies.MovieID and Movies.MovieName LIKE "% Almost Angels 1962%"
and price <= ALL
(SELECT price
FROM MovieSuppliers, Movies
WHERE Movies.MovieID = MovieSupplier.MovieID and
Movies.MovieName LIKE "% Almost Angels 1962%")


10.  Which movies aren't in the inventory?

            SELECT Movies.MovieName
            FROM Movies
            WHERE MovieID NOT IN
                        (SELECT MovieID
                        FROM Inventory)
                       

Comments

Post a Comment

Popular posts from this blog

SQL Query Questions and Answers for Practice

Image
https://www.techbeamers.com/sql-query-questions-answers-for-practice/ If you want to improve SQL skills, then install a SQL package like MySQL and start practicing with it. To get you started, we’ve outlined a few  SQL query questions  in this post. Solving practice questions is the fastest way to learn any subject. That’s why we’ve selected a set of  50 SQL queries that you can use to step up your learning. We’ve also given  SQL scripts to create the test data . So, you can use them to create a test database and tables. Most of the SQL query questions we’ve filtered out of interviews held by top IT MNC like Flipkart and Amazon. So you’ll gain real-time experience by going through them. Also, we recommend that you first try to form queries by yourself rather than just reading them from the post. Try to find answers on your own. But you can’t start until the required sample data is not in place. You can check out the tables below that we’ve provided for...
Read more

Interview Questions and Answers

Image
https://www.softwaretestinghelp.com/50-popular-sql-interview-questions-for-testers/ These are the most common and useful SQL interview questions useful for freshers as well as experienced candidates. From basics up to questions asked on advanced concepts of SQL have been also covered in this article. You can go through these questions for a quick revision of major SQL concepts before appearing for an interview.  Best SQL Interview Questions & Answers Question #1) What is SQL? Structured Query Language is a database tool which is used to create and access database to support software application. Question #2) What are tables in SQL? The table is a collection of record and its information at a single view. Question #3) What are different types of statements supported by SQL? There are 3 types of SQL statements 1) DDL (Data Definition Language):  It is used to define the database structure such as tables. It includes three statements such as...
Read more