Scribd
Upload
170 views3 pages

Movie Supply and Rental Analysis

This document contains 10 SQL queries related to a movie rental database. The queries find movies supplied by specific suppliers, the longest rented movie, suppliers that supply all inventory movies, the number of movies each supplier provides that are in inventory, movies with over 4 copies ordered, customers who rented a specific movie or movies from a certain supplier, movies with multiple copies in inventory, customers who rented for 5 or more days, the cheapest supplier for a given movie, and movies not in inventory. It lists the project members and assigns the SQL queries homework.

Uploaded by

asasbf
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
170 views3 pages

Movie Supply and Rental Analysis

This document contains 10 SQL queries related to a movie rental database. The queries find movies supplied by specific suppliers, the longest rented movie, suppliers that supply all inventory movies, the number of movies each supplier provides that are in inventory, movies with over 4 copies ordered, customers who rented a specific movie or movies from a certain supplier, movies with multiple copies in inventory, customers who rented for 5 or more days, the cheapest supplier for a given movie, and movies not in inventory. It lists the project members and assigns the SQL queries homework.

Uploaded by

asasbf
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

PROJECT MEMBERS:

Muhammad Asif Jawed


BSCS 4(B) 16
Muhammad Sheraz
BSCS 4(B) - 18
SQL Queries Assignment

1. Which movies are supplied by "Joe's House of Video" or "Video


Warehouse"?

Select [Link] from Movies M, MovieSupplier MS, Suppliers S where


[Link] = Joe's House of Video and [Link] = [Link]
and [Link] = [Link]
Union
Select [Link] from Movies M, MovieSupplier MS, Suppliers S where
[Link] = "Video Warehouse" and [Link] = [Link] and
[Link] = [Link]

2. Which movie was rented for the longest duration (by any customer)?

Select [Link] from Rentals R, Movies M, Inventory I where


[Link] = [Link] and [Link] = [Link] and [Link] >= 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 [Link] from Suppliers where [Link] not in (Select


[Link], from MovieSupplier MS, Inventory I where not exists
(Select * from Inventory I, MovieSupplier MS where [Link] =
[Link] and [Link] = [Link] and [Link] = [Link]))

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 [Link], Count (Distinct [Link]) FROM Suppliers S,
MovieSupplier MS, Movies M where [Link] = [Link] and
[Link] = [Link] GROUP BY [Link]

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

Select [Link] from Movies M, Orders O where [Link] =


[Link] GROUP BY [Link] HAVING SUM (Copies) > 4

6. Which customers rented "Fatal Attraction 1987" or rented a movie


supplied by "VWS Video"?

Select LastName from Customers C, Rentals R, Inventory I, Movies M


Where [Link] = [Link] and [Link] = [Link] and [Link] =
[Link] and [Link] LIKE "%Fatal Attraction 1987%"
UNION
Select LastName from Customers C, Rentals R, Inventory I, Movies M,
MovieSupplier MS, Suppliers S where [Link] = [Link] and [Link] =
[Link] and [Link] = [Link] and [Link] = [Link] and
[Link] = [Link] and [Link] = "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 [Link] from Inventory I1, Inventory I2, Movies M


Where [Link] = [Link] and [Link] <> [Link] and [Link]
= [Link]

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


Select DISTINCT LastName from Customers C, Rentals R where [Link] =
[Link] and Duration >= 5

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

Select [Link] from Suppliers S, MovieSuppliers MS, Movies M


Where [Link] = [Link] and [Link] = [Link]
and [Link] LIKE "% Almost Angels 1962%" and price <= ALL
(SELECT price from MovieSuppliers MS, Movies M where [Link] =
[Link] and [Link] LIKE "% Almost Angels 1962%")

10. Which movies aren't in the inventory?

Select [Link] from Movies M where [Link] NOT IN (SELECT


MovieID FROM Inventory)

You might also like