Oracle Project: SQL Movie Database

In order to rent a movie, a person must be enrolled at the online store. There are two different membership programs. There are no late fees and no due dates, and shipping is free both ways. Programs start at $11.99. With our most popular program, 3-at-a-time, you can rent as many DVDs as you want for just $17.99 a month. You keep a revolving library of up to 3 DVDs at a time and can exchange them for new available DVDs as often as you like. … Our 2-at-a-time program (limit 4 DVDs per month) is $11.99.” Use Cases 1) A customer requests the titles of all movies that are directed by "George Lucas" or by “Rich Christiano”. Write a single query that retrieves this information. 2) Management requests the names of all currently active customers, as well as the name of the current plan in which each of these customers is enrolled. Write a single query that retrieves this information. 3) A customer wants to add a movie to their queue so that the newly added movie will be the next movie they receive. Develop a parameterized stored procedure that accomplishes this, then invoke the stored procedure for a customer of your choosing. 4) A customer requests the titles of all the DVD’s that he or she has not rented. Write a single query that retrieves this information for a customer of your choosing. 5) A customer cancels their membership and does not return a rented DVD, necessitating that a $25 DVD replacement fee be charged to their account. Develop a parameterized stored procedure that accomplishes this, then invoke the stored procedure for a customer of your choosing. 6) A customer enrolled in the two-at-a-time plan cancels their membership. When a customer cancels their membership, they become inactive, but their DVD queue and rental history remains in the database, in the event they return as a customer. Develop a parameterized stored procedure that accomplishes this, then invoke the stored procedure for a customer of your choosing. 7) Management requests the names of all movies that are currently sold out. A movie is sold out if all copies of the movie are currently rented and not yet returned. Write a single query that retrieves this information for management. 8) Management requests identification of the plan with the most customer enrollees, and for that plan, the name, number of DVDs allowed at one time, and the number of customer enrollees. Write a single query that retrieves this information for management. 9) Management requests the names of all customers, and for each customer, the titles of the movies that they rented multiple times. For each title, management would like to see the number of times it was rented by the customer, only including titles that the customer rented more than once. If a customer has no rentals, or did not rent any movies multiple times, management does not want to see them in the list. Write a single query that retrieves this information for management. 10) Management requests the titles of all movies, and for each movie, the number of different customers that rented the movie. They would like the list to be ordered from the highest number of different rentals to the lowest number. Multiple rentals of the same movie by the same customer only count as one unique rental. Management is interested in the number of different customers that rented the movie, but not whether the same customer rented the same movie more than once. Write a single query that retrieves this information for management.