Online Movie Rentals

DML Queries

#How many items does the company have as a whole?

Select Count(*) From Item_T;

+----------+

| Count(*) |

+----------+

|        4 |

+----------+

#Which movies and how many movies does a given user currently have out on loan?

Select U.UserID,U.Name,OL.ItemID,I.Name

From User_T U LEFT JOIN Order_T O on(U.UserID = O.UserID)

LEFT JOIN Orderline_T OL on(O.OrderID=OL.OrderID)

LEFT JOIN Item_T I on(OL.ItemID=I.ItemID)

Where O.ReturnDate<Curdate()

;

+--------+------------------+--------+-------------------+

| UserID | Name             | ItemID | Name              |

+--------+------------------+--------+-------------------+

|  12346 | Schlomo Riddling |      4 | The Hangover      |

|  12346 | Schlomo Riddling |      2 | World Of Warcraft |

|  12349 | Albert Pike      |      2 | World Of Warcraft |

+--------+------------------+--------+-------------------+

#When will an Out of Stock Movie be next availible?

Select O.ReturnDate

From Item_T I Join Orderline_T OL on(I.ItemID = OL.ItemID)

JOIN Order_T O on(OL.OrderID=O.OrderID)

Where O.Returndate > Curdate();

Empty Set

#What are the most popular Items?

Select I.Name,Count(OL.OrderID)

From Item_T I LEFT JOIN Orderline_T OL on (I.ItemID=OL.ItemID)

Group BY I.Name;

+-------------------+-------------------+

| Name              | Count(OL.OrderID) |

+-------------------+-------------------+

| Pulp Fiction      |                 0 |

| The Hangover      |                 1 |

| Wayne's World     |                 0 |

| World Of Warcraft |                 2 |

+-------------------+-------------------+

#What are the highest rated Items

Select I.Name,R.Rating

From Rating_T R RIGHT JOIN Item_T I on(R.ItemID=I.ItemID)

Order BY R.Rating DESC;

+-------------------+--------+

| Name              | Rating |

+-------------------+--------+

| The Hangover      |     10 |

| Wayne's World     |      9 |

| World Of Warcraft |   NULL |

| Pulp Fiction      |   NULL |

+-------------------+--------+

#Which Hub ships the most movies?

Select temp.HubID,c

FROM (

Select H.HubID,Count(OL.OrderID) AS c

From Hub_T H LEFT JOIN Orderline_T OL on(H.HubID=OL.HubID)

Group BY H.HubID)temp

Order BY temp.c DESC;

+-------+---+

| HubID | c |

+-------+---+

|     2 | 2 |

|     1 | 1 |

|     3 | 0 |

+-------+---+


© Andrew Keenan & Kamel Branch-Powers