Movie-Store-Database-Management-System

Database Management System for a Movie Rental Store (Green Cycle)

Software Used:

Postgresql

ERD Diagram:

Tasks Performed:

1. Create a list of all the different (distinct) replacement costs of the films.
2. Query that gives an overview of how many films have replacement costs in the following cost ranges
3. Create a list of the film titles including their title, length, and category name ordered descendingly by length. Filter the results to only the movies in the category 'Drama' or 'Sports'.
4. Create an overview of how many movies (titles) there are in each category (name).
5. Create an overview of the actors' first and last names and in how many movies they appear in.
6. Create an overview of the addresses that are not associated with any customer.
7. Create an overview of the sales to determine which city (we are interested in the city in which the customer lives, not where the store is) has the most sales.
8. Create an overview of the revenue (sum of amount) grouped by a column in the format "country, city".
9. Create a list with the average of the sales amount each staff_id has per customer.
10. Create a query that shows the average daily revenue of all Sundays.
11. Create a list of movies - with their length and their replacement cost - that is longer than the average length in each replacement cost group.
12. Create a list that shows the "average customer lifetime value" grouped by the different districts.
13. Create a list that shows all payments including the payment_id, amount, and the film category (name) plus the total amount that was made in this category. Order the results ascendingly by the category (name) and as second order criterion by the payment_id ascendingly.