sqlintermediate15 minutes

Design and Query a Movie Rental Database Schema

Build a normalized relational schema for a movie rental system including customers, movies, rentals, and payments, then write SQL queries to retrieve key business insights.

Challenge prompt

You are tasked with designing a simple movie rental database schema that must include tables for Customers, Movies, Rentals, and Payments. Each customer can rent multiple movies, and each rental can have multiple payments (for instance, partial payments or late fees). After creating tables with appropriate keys and relationships, write SQL queries to: 1. List all customers who have rented more than 3 movies. 2. Find the total amount paid by each customer. 3. Identify movies that have never been rented. Your solution should define the schema using CREATE TABLE statements with relevant constraints, then provide the SQL queries fulfilling the requirements.

Guidance

• Use primary and foreign keys to enforce relationships between customers, movies, rentals, and payments tables.
• Consider including columns for rental date, return date, and payment amount to enrich your data.
• Use JOINs and GROUP BY clauses to aggregate data according to the queries.

Hints

• Ensure the Rentals table includes foreign keys to the Customers and Movies tables to link data correctly.
• Use LEFT JOIN when querying movies that have never been rented to include movies with no corresponding rental records.
• Aggregate payments by customer using SUM and GROUP BY to find total payments.

Starter code

CREATE TABLE Customers (
  customer_id INT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100)
);

CREATE TABLE Movies (
  movie_id INT PRIMARY KEY,
  title VARCHAR(200),
  genre VARCHAR(50)
);

-- Define Rentals and Payments tables with appropriate keys and columns

Expected output

Query1 Example Output: | customer_id | name | rental_count | |-------------|------------|--------------| | 101 | Alice Lee | 5 | | 103 | Bob Smith | 4 | Query2 Example Output: | customer_id | total_paid | |-------------|------------| | 101 | 150.00 | | 102 | 80.00 | Query3 Example Output: | movie_id | title | |----------|---------------------| | 12 | The Forgotten Movie | | 15 | Hidden Gems |

Core concepts

database schema designprimary and foreign keysSQL JOINs and aggregation

Challenge a Friend

Send this duel to someone else and see if they can solve it.