SQL Projects: Complete Movie Rental System with Efficient Database Design and Management

Movie Rental System:

The Movie Rental System allows customers to rent movies, manage rentals, track returns, and handle payments. The system includes a database to store information about movies, customers, rentals, and payments. The project will focus on the complete design of the database and the SQL queries required for efficient management.

Creating the Database in MySQL or PostgreSQL:

Create the Database:

-- Create the database for the Movie Rental System
CREATE DATABASE MovieRentalDB;
USE OnlineStoreDB;

Create the Tables:

Movies Table:

The Movies table stores information about the available movies in the rental system. It includes details such as the movie's unique identifier, title, genre, release year, and availability status.

Structure:

Code:

-- Create the Movies table
CREATE TABLE Movies (
    movie_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,  -- Unique identifier for each movie
    title VARCHAR(55) NOT NULL,              -- Title of the movie
    genre VARCHAR(50),                       -- Genre of the movie
    release_year YEAR,                        -- Year the movie was released
    is_available BOOLEAN DEFAULT TRUE         -- Movie availability status (default is available)
);

Customers Table:

The Customers table stores essential information about customers in the online store. It includes details such as the customer's unique identifier, full name, email address, phone number, and the date they registered with the store.

Structure:

Code:

-- Create the Customers table
CREATE TABLE Customers (
    customer_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,  -- Unique identifier for each customer
    name VARCHAR(25) NOT NULL,                  -- Full name of the customer
    email VARCHAR(25),                          -- Email address of the customer
    phone_number VARCHAR(15),                    -- Contact number of the customer
    join_date DATE DEFAULT (CURRENT_DATE)        -- Date the customer joined (default is current date)
);

Rentals Table:

The Rentals table tracks movie rentals in the movie rental system. It contains information such as a unique rental identifier, the rented movie's reference (foreign key to the Movies table), the customer who rented it (foreign key to the Customers table), the rental date, and the return date.

Structure:

Code:

-- Create the Rentals table
CREATE TABLE Rentals (
    rental_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,  -- Unique identifier for each rental
    movie_id INT,                              -- Foreign key referencing the Movies table
    customer_id INT,                           -- Foreign key referencing the Customers table
    rental_date DATE DEFAULT (CURRENT_DATE),   -- Date when the movie was rented (default is current date)
    return_date DATE,                          -- Date when the movie was returned (can be NULL if not returned yet)
    FOREIGN KEY (movie_id) REFERENCES Movies(movie_id),  -- Link to Movies table
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id) -- Link to Customers table
); 

Payments Table:

The Payments table records payment transactions for movie rentals in the system. It includes a unique payment ID, the rental associated with the payment (foreign key to the Rentals table), the amount paid, and the date of payment.

Structure:

Code:

-- Create the Payments table
CREATE TABLE Payments (
    payment_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,  -- Unique identifier for each payment
    rental_id INT,                              -- Foreign key referencing the Rentals table
    amount DECIMAL(10, 2) NOT NULL,             -- Payment amount
    payment_date DATE DEFAULT (CURRENT_DATE),   -- Date when payment was made (default is current date)
    FOREIGN KEY (rental_id) REFERENCES Rentals(rental_id) -- Link to Rentals table
);

Staff Table:

The Staff table stores information about the staff members who manage the movie rental system. Each staff member is assigned a unique identifier (staff_id), along with their full name and role within the organization (such as Manager or Clerk).

Structure:

Code:

-- Create the Staff table
CREATE TABLE Staff (
    staff_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,  -- Unique identifier for each staff member
    name VARCHAR(25) NOT NULL,               -- Full name of the staff member
    role VARCHAR(50)                         -- Role of the staff member (e.g., Manager, Clerk)
);

Inserting Data:

Add some sample data to the tables.

Inserting Data into Movies Table:

-- Insert movies into Movies table
INSERT INTO Movies (title, genre, release_year) VALUES
('The Godfather', 'Crime', 1972),
('Inception', 'Sci-Fi', 2010),
('The Dark Knight', 'Action', 2008); 

Inserting Data into Customers Table:

-- Insert customers into Customers table
INSERT INTO Customers (name, email, phone_number) VALUES
('Jimmy Milton', '[email protected]', '1234567890'),
('Jemes Bruck', '[email protected]', '0987654321');

Inserting Data into Rentals Table:

-- Insert rental transactions into Rentals table
INSERT INTO Rentals (movie_id, customer_id, rental_date) VALUES
(1, 1, '2024-10-01'),  -- The Godfather rented by John Doe
(2, 2, '2024-10-05');  -- Inception rented by Jane Smith

Inserting Data into Payments Table:

-- Insert payments into Payments table
INSERT INTO Payments (rental_id, amount) VALUES
(1, 5.99),  -- Payment for The Godfather rental
(2, 3.99);  -- Payment for Inception rental

Inserting Data into Staff Table:

-- Insert staff members into Staff table
INSERT INTO Staff (name, role) VALUES
('Boris Hudson', 'Manager'),
('Charls Kelton', 'Clerk');

Basic Functionalities:

• Add New Movies
• Register New Customers
• Process Movie Rentals
• Manage Payments
• Track Movie Availability
• Track Overdue Rentals
• Generate Reports on Rental History

Writing Queries for Functionality:

Query-1: List All Available Movies

SELECT * FROM Movies                 -- Select all columns from the 'Movies' table
WHERE is_available = TRUE;           -- Filter to show only movies that are currently available for rent

Explanation:

This query retrieves all movies that are currently available for rent by checking the is_available column for a value of TRUE. It provides a straightforward way to list all movies that customers can rent at the moment.

Output:

movie_id	title	genre	release_year	is_available
1	The Godfather	Crime	1972	1
2	Inception	Sci-Fi	2010	1
3	The Dark Knight	Action	2008	1

Query-2: Show All Customers

SELECT * FROM Customers;              -- Select all columns from the 'Customers' table

Explanation:

This query retrieves all records from the Customers table, showing details for each registered customer.

Output:

customer_id	name	email	phone_number	join_date
1	Jimmy Milton	[email protected]	1234567890	2024-10-26
2	Jemes Bruck	[email protected]	0987654321	2024-10-26

Query-3: Show All Rentals

SELECT * FROM Rentals;              -- Selects all columns from the 'Rentals' table

Explanation:

This query retrieves every record from the Rentals table, displaying a list of all rental transactions.

Output:

rental_id	movie_id	customer_id	rental_date	return_date
1	1	1	2024-10-01	NULL
2	2	2	2024-10-05	NULL

Query-4: Total Payments Collected

SELECT SUM(amount) AS total_payments FROM Payments;   -- Calculates the sum of all payment amounts and renames it as 'total_payments'  

Explanation:

This query calculates the total amount of payments received across all rentals by summing the values in the amount column of the Payments table. The result is labeled as total_payments, providing a quick view of the total revenue generated from all rental transactions.

Output:

total_payments
9.98

Query-5: Movies Not Yet Returned

	SELECT r.rental_id, m.title, c.name                 -- Selects rental ID, movie title, and customer name
FROM Rentals r                                       -- Specifies the Rentals table, using an alias 'r'
JOIN Movies m ON r.movie_id = m.movie_id             -- Joins the Movies table to get movie titles using movie_id
JOIN Customers c ON r.customer_id = c.customer_id    -- Joins the Customers table to get customer names using customer_id
WHERE r.return_date IS NULL;                         -- Filters results to show only records where the return_date is NULL

Explanation:

This query identifies movies that have not yet been returned by joining the Rentals, Movies, and Customers tables. It retrieves the rental ID, movie title, and customer name for each transaction where return_date is still NULL, indicating the movie is currently on rent and hasn't been returned.

Output:

rental_id	title	name
1	The Godfather	Jimmy Milton
2	Inception	Jemes Bruck

Query-6: Overdue Rentals

SELECT r.rental_id, m.title, c.name, r.rental_date           -- Selects rental ID, movie title, customer name, and rental date
FROM Rentals r                                               -- Specifies the Rentals table, using alias 'r'
JOIN Movies m ON r.movie_id = m.movie_id                     -- Joins Movies table to retrieve movie titles using movie_id
JOIN Customers c ON r.customer_id = c.customer_id            -- Joins Customers table to retrieve customer names using customer_id
WHERE r.return_date IS NULL                                  -- Filters for records where the return_date is still NULL (movie not returned)
AND r.rental_date < CURDATE() - INTERVAL 7 DAY;              -- Checks if the rental is overdue by more than 7 days

Explanation:

This query identifies overdue rentals by displaying the rental ID, movie title, customer name, and rental date. By joining the Rentals, Movies, and Customers tables, it retrieves transactions where the return_date is NULL (indicating the movie hasn't been returned) and the rental date was more than 7 days ago. This allows the system to track and manage overdue rentals.

Output:

rental_id	title	name	rental_date
1	The Godfather	Jimmy Milton	2024-10-01
2	Inception	Jemes Bruck	2024-10-05

Query-7: Rentals by Customer

SELECT c.name, COUNT(r.rental_id) AS rentals_count       -- Selects the customer’s name and counts the number of rentals
FROM Customers c                                         -- Specifies the Customers table, using alias 'c'
JOIN Rentals r ON c.customer_id = r.customer_id          -- Joins Rentals table to link each customer with their rentals
GROUP BY c.customer_id;                                  -- Groups results by customer ID to count rentals per customer

Explanation:

This query lists each customer along with the number of movies they have rented. By joining the Customers and Rentals tables on customer_id and grouping by each customer's ID, the query can count the number of rental transactions for each customer.

Output:

name	rentals_count
Jimmy Milton	1
Jemes Bruck	1

Query-8: Most Popular Genre

SELECT genre, COUNT(movie_id) AS genre_count          -- Selects genre and counts the number of movies per genre
FROM Movies                                           -- Specifies the Movies table as the source
GROUP BY genre                                        -- Groups results by each genre to calculate movie count per genre
ORDER BY genre_count DESC                             -- Orders genres by movie count in descending order
LIMIT 1;                                              -- Limits results to show only the top genre with the highest count

Explanation:

This query identifies the most popular genre in the movie collection by counting how many movies are in each genre and ordering them in descending order of count. The LIMIT 1 clause returns only the genre with the highest movie count, providing insight into the genre with the greatest availability or potential customer interest.

Output:

genre	genre_count
Crime	1

Query-9: Update Movie Availability

UPDATE Movies                      -- Specifies the Movies table to modify
SET is_available = FALSE           -- Sets the is_available status to FALSE, indicating the movie is now unavailable
WHERE movie_id = 1;                -- Targets the specific movie with movie_id equal to 1

Explanation:

This query updates the availability status of a specific movie in the Movies table. By setting is_available to FALSE for the movie with movie_id 1, the query marks that movie as unavailable for rent.

Output:

Select * from Movies;

movie_id	title	genre	release_year	is_available
1	The Godfather	Crime	1972	0
2	Inception	Sci-Fi	2010	1
3	The Dark Knight	Action	2008	1

Query-10: Return a Movie

UPDATE Rentals                        -- Specifies the Rentals table to update
SET return_date = CURDATE()           -- Sets the return_date to the current date (indicating the movie has been returned)
WHERE rental_id = 1;                  -- Targets the rental record with rental_id equal to 1

Explanation:

This query marks a movie as returned by updating the return_date in the Rentals table. By setting return_date to the current date for the record with rental_id 1, it records the date on which the movie was returned.

Output:

rental_id	movie_id	customer_id	rental_date	return_date
1	1	1	2024-10-01	2024-10-26
2	2	2	2024-10-05	NULL

Query-11: List All Payments for a Rental

SELECT *                           -- Selects all columns
FROM Payments                       -- From the Payments table
WHERE rental_id = 1;                -- Filters for payments related to the rental with rental_id equal to 1

Explanation:

This query retrieves all payment details associated with a specific rental. By filtering on rental_id = 1, it returns every record in the Payments table that corresponds to the specified rental, making it easy to view the total and individual payment transactions for that rental.

Output:

payment_id	rental_id	amount	payment_date
1	1	5.99	2024-10-26

Query-12: List All Rentals by a Specific Customer

SELECT r.rental_id,              -- Selects the rental ID from Rentals table
       m.title,                  -- Selects the title of the movie from Movies table
       r.rental_date             -- Selects the rental date from Rentals table
FROM Rentals r                   -- From the Rentals table
JOIN Movies m ON r.movie_id = m.movie_id  -- Joins Movies table on the movie_id field
WHERE r.customer_id = 1;         -- Filters for rentals made by the customer with customer_id equal to 1

Explanation:

This query lists all rental transactions associated with a specific customer identified by customer_id = 1. By joining the Rentals table with the Movies table, it retrieves the rental ID, movie title, and rental date for each rental the customer has made, providing a clear overview of their rental history.

Output:

rental_id	title	rental_date
1	The Godfather	2024-10-01

Query-13: Find Customers with Unpaid Rentals

SELECT c.name,              -- Selects the customer's name from the Customers table
       r.rental_id         -- Selects the rental ID from the Rentals table
FROM Customers c            -- From the Customers table
JOIN Rentals r ON c.customer_id = r.customer_id  -- Joins Rentals table on customer_id
LEFT JOIN Payments p ON r.rental_id = p.rental_id  -- Left join Payments to find matching rental payments
WHERE p.payment_id IS NULL;  -- Filters for rentals without an associated payment

Explanation:

This query identifies customers who have rentals that have not been paid for. By joining the Customers and Rentals tables, and then using a left join with the Payments table, it retrieves the names of customers alongside the rental IDs where no payment has been recorded (payment_id IS NULL).

Output:

Output not generated for insufficient data

Query-14: List of Staff and Their Roles

SELECT * FROM Staff;  -- Retrieves all columns for all records in the Staff table

Explanation:

This query fetches all the details from the Staff table, providing a comprehensive view of all staff members and their associated roles within the movie rental system.

Output:

staff_id	name	role
1	Boris Hudson	Manager
2	Charls Kelton	Clerk

Query-15: Movie Availability Check by Title

SELECT title, is_available 
FROM Movies 
WHERE title = 'The Godfather';  -- Retrieves the title and availability status of a specific movie

Explanation:

This query checks the availability of a specific movie, in this case, "The Godfather," by retrieving its title and availability status from the Movies table.

Output:

title	is_available
The Godfather	1

Query-16: Total Rentals and Revenue by Genre

SELECT m.genre, COUNT(r.rental_id) AS total_rentals, SUM(p.amount) AS total_revenue
FROM Movies m
JOIN Rentals r ON m.movie_id = r.movie_id        -- Joins Rentals table with Movies based on movie_id
JOIN Payments p ON r.rental_id = p.rental_id     -- Joins Payments table with Rentals based on rental_id
GROUP BY m.genre                                 -- Groups results by movie genre
ORDER BY total_revenue DESC;                     -- Orders results by total revenue in descending order

Explanation:

This query calculates the total number of rentals and the total revenue generated for each movie genre. By joining the Movies, Rentals, and Payments tables, it counts the rentals and sums the payment amounts for each genre. The results are grouped by genre and sorted by total revenue, allowing for a clear view of which genres are the most popular and profitable for the rental business.

Output:

genre	total_rentals	total_revenue
Crime	1	5.99
Sci-Fi	1	3.99

Query-17: Average Rental Duration by Movie

SELECT m.title, AVG(DATEDIFF(r.return_date, r.rental_date)) AS avg_rental_duration
FROM Movies m
JOIN Rentals r ON m.movie_id = r.movie_id               -- Joins Rentals table with Movies based on movie_id
WHERE r.return_date IS NOT NULL                          -- Filters out rentals that have not been returned
GROUP BY m.title                                         -- Groups results by each movie title
ORDER BY avg_rental_duration DESC;                       -- Orders results by average rental duration in descending order

Explanation:

This query calculates the average rental duration for each movie in the system. It joins the Movies and Rentals tables, filters out records with no return_date, and then uses DATEDIFF to find the difference in days between the rental_date and return_date. The results are grouped by movie title and ordered by the average rental duration in descending order, revealing which movies tend to be rented for longer periods on average.

Output:

Output not generated for insufficient data

Query-18: Most Active Customer (Highest Number of Rentals)

SELECT c.name, COUNT(r.rental_id) AS rental_count
FROM Customers c
JOIN Rentals r ON c.customer_id = r.customer_id       -- Joins Rentals table with Customers based on customer_id
GROUP BY c.customer_id                                -- Groups results by each customer
ORDER BY rental_count DESC                            -- Orders results by rental count in descending order
LIMIT 1;                                              -- Limits the result to the customer with the highest rental count

Explanation:

This query identifies the most active customer based on the number of rentals they have made. By joining the Customers and Rentals tables and grouping by each customer's ID, it counts the rentals per customer. The results are then ordered by rental_count in descending order, and the LIMIT 1 clause ensures only the customer with the highest rental count is returned. This helps in identifying top customers.

Output:

name	rental_count
Jimmy Milton	1

Query-19: Movies Rented More Than a Certain Number of Times

SELECT m.title, COUNT(r.rental_id) AS rental_count
FROM Movies m
JOIN Rentals r ON m.movie_id = r.movie_id           -- Joins Rentals table with Movies based on movie_id
GROUP BY m.title                                    -- Groups results by each movie title
HAVING rental_count > 5                             -- Filters to include only movies rented more than 5 times
ORDER BY rental_count DESC;                         -- Orders results by rental count in descending order

Explanation:

This query lists movies that have been rented more than five times. It joins the Movies and Rentals tables, groups the results by movie title, and uses the HAVING clause to filter for movies with a rental count exceeding five. The results are ordered in descending order of rentals, showing the most popular movies based on rental frequency.

Output:

Output not generated for insufficient data

Query-20: Top 5 Movies by Revenue

SELECT m.title, SUM(p.amount) AS total_revenue
FROM Movies m
JOIN Rentals r ON m.movie_id = r.movie_id         -- Joins Movies with Rentals on movie_id to link rentals to each movie
JOIN Payments p ON r.rental_id = p.rental_id      -- Joins Payments with Rentals on rental_id to calculate revenue per rental
GROUP BY m.title                                  -- Groups results by each movie title to aggregate revenue
ORDER BY total_revenue DESC                       -- Orders movies by total revenue in descending order
LIMIT 5;                                          -- Limits results to the top 5 movies with the highest revenue

Explanation:

This query identifies the top five movies generating the most revenue. By joining Movies, Rentals, and Payments tables, it calculates the total revenue each movie has earned. The GROUP BY clause groups results by each movie title, and the SUM() function aggregates revenue for each movie. Finally, it orders by total_revenue in descending order and limits the result to the top five.

Output:

title	total_revenue
The Godfather	5.99
Inception	3.99

Query-21: Customer Payment History

SELECT c.name, p.payment_date, p.amount
FROM Customers c
JOIN Rentals r ON c.customer_id = r.customer_id       -- Joins Customers and Rentals on customer_id to link rentals to customers
JOIN Payments p ON r.rental_id = p.rental_id          -- Joins Rentals and Payments on rental_id to retrieve payment details
WHERE c.customer_id = 1                               -- Filters results for a specific customer with customer_id = 1
ORDER BY p.payment_date;                              -- Orders the payment history by payment date for clarity

Explanation:

This query displays the payment history for a specific customer (with customer_id = 1). By joining the Customers, Rentals, and Payments tables, it retrieves the customer's name, payment dates, and payment amounts. The ORDER BY clause sorts the results by payment_date in ascending order to show the history chronologically.

Output:

name	payment_date	amount
Jimmy Milton	2024-10-26	5.99

Query-22: Customers with Most Overdue Rentals

SELECT c.name, COUNT(r.rental_id) AS overdue_count
FROM Customers c
JOIN Rentals r ON c.customer_id = r.customer_id             -- Joins Customers and Rentals to link customer data to their rentals
WHERE r.return_date IS NULL AND r.rental_date < CURDATE() - INTERVAL 7 DAY  -- Filters for rentals not returned and overdue by more than 7 days
GROUP BY c.customer_id                                      -- Groups results by customer to count overdue rentals per customer
ORDER BY overdue_count DESC;                                -- Orders the results by overdue_count in descending order to show the most overdue first

Explanation:

This query identifies customers with the highest number of overdue rentals. By joining the Customers and Rentals tables, it counts rentals that are overdue (not returned within 7 days). The results are grouped by customer and ordered by overdue_count in descending order, highlighting the customers with the most overdue rentals at the top.

Output:

name	overdue_count
Jimmy Milton	1
Jemes Bruck	1

Query-23: Find Customers Who Have Rented All Available Movies

SELECT c.name
FROM Customers c
WHERE NOT EXISTS (                        -- Ensures no rows exist in the following subquery for the condition to be met
    SELECT m.movie_id
    FROM Movies m
    WHERE m.is_available = TRUE           -- Filters only currently available movies
    AND m.movie_id NOT IN (               -- Checks for any movie IDs not rented by the customer
        SELECT r.movie_id
        FROM Rentals r
        WHERE r.customer_id = c.customer_id  -- Matches rentals to the current customer in the main query
    )
);

Explanation:

This query identifies customers who have rented every available movie. It uses a nested NOT EXISTS condition to verify that no available movies remain unrented by the customer. By filtering for is_available = TRUE, it only considers movies currently available for rent and returns customers who have completed rentals for all such movies.

Output:

Output not generated for insufficient data

Query-24: Movies Never Rented

SELECT m.title
FROM Movies m
LEFT JOIN Rentals r ON m.movie_id = r.movie_id  -- Uses a LEFT JOIN to include all movies, regardless of rental status
WHERE r.rental_id IS NULL;                      -- Filters for movies with no matching rental records

Explanation:

This query lists all movies that have never been rented. It uses a LEFT JOIN between the Movies and Rentals tables, which returns all movies while pairing them with any matching rentals. The WHERE clause then filters for rows where r.rental_id is NULL, identifying movies with no rental records in the system.

Output:

title
The Dark Knight

Query-25: Average Rental Revenue Per Customer

SELECT c.name, AVG(p.amount) AS avg_revenue_per_rental
FROM Customers c
JOIN Rentals r ON c.customer_id = r.customer_id       -- Joins Rentals table to link each customer to their rentals
JOIN Payments p ON r.rental_id = p.rental_id          -- Joins Payments table to link each rental to its payment
GROUP BY c.customer_id                                -- Groups results by customer to calculate revenue per customer
ORDER BY avg_revenue_per_rental DESC;                 -- Orders customers by average revenue in descending order

Explanation:

This query calculates the average revenue generated per rental for each customer. By joining the Customers, Rentals, and Payments tables, it captures all payments related to each customer’s rentals. It then groups results by customer_id and uses AVG(p.amount) to find the average payment for each rental per customer, ordering the results from highest to lowest average rental revenue.

Output:

name	avg_revenue_per_rental
Jimmy Milton	5.990000
Jemes Bruck	3.990000

Query-26: Customers Who Have Rented Movies in Multiple Genres

SELECT c.name
FROM Customers c
JOIN Rentals r ON c.customer_id = r.customer_id          -- Joins Rentals table to link each customer to their rentals
JOIN Movies m ON r.movie_id = m.movie_id                 -- Joins Movies table to link each rental to its associated movie genre
GROUP BY c.customer_id                                   -- Groups results by customer to analyze genre diversity in rentals
HAVING COUNT(DISTINCT m.genre) > 1;                      -- Filters customers who have rented movies from more than one genre

Explanation:

This query identifies customers who have rented movies across multiple genres. By joining the Customers, Rentals, and Movies tables, it accesses each customer’s rental history and the genre of each rented movie. The GROUP BY groups records by customer_id, and COUNT(DISTINCT m.genre) > 1 filters for customers who rented from more than one genre, showing only those who explored diverse movie types.

Output:

Output not generated for insufficient data

Query-27: Total Revenue by Rental Date

SELECT r.rental_date, SUM(p.amount) AS total_revenue                 -- Selects rental date and calculates total revenue for that date
FROM Rentals r                                                         -- From the Rentals table to access rental dates
JOIN Payments p ON r.rental_id = p.rental_id                           -- Joins Payments table to link revenue with corresponding rentals
GROUP BY r.rental_date                                                 -- Groups results by rental date for aggregated revenue calculation
ORDER BY r.rental_date;                                               -- Orders results by rental date for chronological clarity

Explanation:

This query calculates the total revenue generated for each rental date. It combines the Rentals and Payments tables to correlate rental transactions with their respective payments. By grouping the results by rental_date, it aggregates the total revenue collected for each date, allowing for analysis of daily revenue trends. The results are then ordered chronologically to facilitate easier review of revenue patterns over time.

Output:

rental_date	total_revenue
2024-10-01	5.99
2024-10-05	3.99

Query-28: Top 3 Most Frequent Genres Rented by Each Customer

SELECT c.name, m.genre, COUNT(m.genre) AS genre_count             -- Selects customer name, genre, and counts how many times each genre is rented
FROM Customers c                                                   -- From the Customers table to access customer information
JOIN Rentals r ON c.customer_id = r.customer_id                     -- Joins Rentals table to link rentals with customers
JOIN Movies m ON r.movie_id = m.movie_id                           -- Joins Movies table to access genre information of rented movies
GROUP BY c.customer_id, m.genre                                    -- Groups results by customer and genre to aggregate counts
ORDER BY c.customer_id, genre_count DESC                           -- Orders results first by customer ID, then by genre count in descending order
LIMIT 3;                                                           -- Limits the results to the top 3 genres per customer

Explanation:

This query identifies the top three most frequently rented movie genres for each customer. It joins the Customers, Rentals, and Movies tables to gather the necessary data. By grouping the results by both customer and genre, it counts how many times each genre has been rented by each customer. The results are sorted so that the most frequently rented genres appear first for each customer. The LIMIT 3 clause ensures that only the top three genres per customer are returned, providing a focused view of customer preferences.

Output:

name	genre	genre_count
Jimmy Milton	Crime	1
Jemes Bruck	Sci-Fi	1

Query-29: Customers Who Have Never Made a Payment

SELECT DISTINCT c.name                                           -- Selects unique customer names
FROM Customers c                                                -- From the Customers table to access customer details
LEFT JOIN Rentals r ON c.customer_id = r.customer_id           -- Left joins Rentals table to link rentals with customers
LEFT JOIN Payments p ON r.rental_id = p.rental_id              -- Left joins Payments table to check payment records
WHERE p.payment_id IS NULL;                                    -- Filters results to include only customers without payment records

Explanation:

This query identifies customers who have never made a payment for their rentals. It begins by selecting distinct customer names from the Customers table. By using left joins with the Rentals and Payments tables, it links rental records to the customers while allowing for customers without any rental records to be included in the results. The WHERE clause filters the results to only include customers whose payment ID is NULL, indicating that they have not made any payments. This helps in identifying potential customer engagement opportunities or issues with the rental system.

Output:

Output not generated for insufficient data

Query-30: Average Payment Amount Per Genre

SELECT m.genre, AVG(p.amount) AS avg_payment_amount           -- Selects the movie genre and calculates the average payment amount
FROM Movies m                                                  -- From the Movies table to access genre information
JOIN Rentals r ON m.movie_id = r.movie_id                     -- Joins Rentals table to link rentals with corresponding movies
JOIN Payments p ON r.rental_id = p.rental_id                  -- Joins Payments table to link payments with rentals
GROUP BY m.genre                                              -- Groups results by movie genre to calculate averages per genre
ORDER BY avg_payment_amount DESC;                             -- Orders the results by average payment amount in descending order

Explanation:

This query calculates the average payment amount for movie rentals, grouped by genre. It starts by selecting the genre from the Movies table and computing the average payment amount from the Payments table. By joining the Rentals table, the query links the rental records to their corresponding movies. The results are grouped by genre, allowing the calculation of the average payment for each genre. Finally, the output is ordered in descending order based on the average payment amount, highlighting which genres generate higher revenue from rentals.

Output:

genre	avg_payment_amount
Crime	5.990000
Sci-Fi	3.990000

SQL Code Editor: