JOIN Practice 1

Goals

• Write INNER JOIN queries across two and three tables
• Use LEFT JOIN to find rows with no counterpart in another table
• Chain multiple joins to trace data through the DVD Rental schema
• Aggregate data from joined tables using GROUP BY and SUM

Warm-up

Open psql or pgAdmin and run the following query. It should return 1000 rows.

SELECT COUNT(*) FROM customer;

Now run:

SELECT COUNT(*) FROM rental;

How many rentals exist on average per customer? Use this number as a mental baseline as you work through the steps.

Steps

Step 1 — Films and Their Categories (Easy)

Objective: List all films with their category name using INNER JOIN.

Tables involved: film, film_category, category

The film_category table is a bridge — each row links one film to one category. You need two JOINs to cross it.

Task: Write a query that returns film.title and category.name (aliased as category_name). Order alphabetically by category name, then by title. Limit to 20 rows.

Expected shape:

SELECT f.title, c.name AS category_name
FROM film f
INNER JOIN film_category fc ON f.film_id      = fc.film_id
INNER JOIN category c       ON fc.category_id = c.category_id
ORDER BY c.name, f.title
LIMIT 20;

Step 2 — Actors Ranked by Film Count (Easy)

Objective: Show all actors and the number of films they appear in, ordered from most to fewest.

Tables involved: actor, film_actor

Task: Write a query that returns actor.first_name, actor.last_name, and a column film_count containing the number of films for that actor. Order by film_count descending. Show the top 15.

Expected shape:

SELECT a.first_name,
       a.last_name,
       COUNT(fa.film_id) AS film_count
FROM actor a
INNER JOIN film_actor fa ON a.actor_id = fa.actor_id
GROUP BY a.actor_id, a.first_name, a.last_name
ORDER BY film_count DESC
LIMIT 15;

Step 3 — Customers Who Have Never Rented (Medium)

Objective: Find all customers who have zero rentals on record.

Tables involved: customer, rental

Task: Write a query using LEFT JOIN that returns the customer_id, first_name, last_name, and email of all customers with no rental history. Order by last name.

Expected shape: The result may be empty if all customers have at least one rental — that is a valid finding. Confirm by checking SELECT COUNT(DISTINCT customer_id) FROM rental and comparing it with the total customer count.

SELECT c.customer_id, c.first_name, c.last_name, c.email
FROM customer c
LEFT JOIN rental r ON c.customer_id = r.customer_id
WHERE r.rental_id IS NULL
ORDER BY c.last_name;

Step 4 — Last 30 Rentals With Film and Customer Info (Medium)

Objective: Show detailed information about the 30 most recent rentals (by rental_id).

Tables involved: rental, inventory, film, customer

Task: Return the following columns: rental.rental_id, film.title, a concatenated customer_name (first_name || ' ' || last_name), and rental.rental_date. Order by rental_id descending. Limit to 30.

Expected shape:

SELECT r.rental_id,
       f.title,
       c.first_name || ' ' || c.last_name AS customer_name,
       r.rental_date
FROM rental r
INNER JOIN inventory i ON r.inventory_id = i.inventory_id
INNER JOIN film f      ON i.film_id       = f.film_id
INNER JOIN customer c  ON r.customer_id   = c.customer_id
ORDER BY r.rental_id DESC
LIMIT 30;

Step 5 — Staff Revenue Including Zero-Payment Staff (Hard)

Objective: Show each staff member's full name and the total payment amount they processed. Staff members who processed no payments must still appear with a total of 0.00.

Tables involved: staff, payment

Task: Return staff.first_name, staff.last_name, and total_payments (the sum of payment.amount, defaulting to 0 if none). Use COALESCE to handle the NULL case. Order by total_payments descending.

Expected shape:

SELECT s.first_name,
       s.last_name,
       COALESCE(SUM(p.amount), 0) AS total_payments
FROM staff s
LEFT JOIN payment p ON s.staff_id = p.staff_id
GROUP BY s.staff_id, s.first_name, s.last_name
ORDER BY total_payments DESC;

Step 6 — Top 5 Categories by Rental Revenue (Hard)

Objective: Find the five film categories that generated the most rental revenue.

Tables involved: category, film_category, film, inventory, rental, payment

Task: Return category.name and total_revenue (sum of all payments for rentals of films in that category). Order by total_revenue descending. Show only the top 5.

Expected shape:

SELECT cat.name,
       SUM(p.amount) AS total_revenue
FROM payment p
INNER JOIN rental       r   ON p.rental_id     = r.rental_id
INNER JOIN inventory    i   ON r.inventory_id  = i.inventory_id
INNER JOIN film         f   ON i.film_id       = f.film_id
INNER JOIN film_category fc ON f.film_id       = fc.film_id
INNER JOIN category     cat ON fc.category_id  = cat.category_id
GROUP BY cat.category_id, cat.name
ORDER BY total_revenue DESC
LIMIT 5;

Bonus Tasks

Bonus 1 — Average Rental Duration by Category

Extend Step 6 to also return the average rental duration in days per category. Use r.return_date - r.rental_date to compute duration. Exclude rentals where return_date IS NULL. Round the average to 2 decimal places using ROUND(...::numeric, 2).

Bonus 2 — Actors Who Covered All Five Ratings

Find actors who have appeared in at least one film for each of the five MPAA ratings (G, PG, PG-13, R, NC-17). Return their full name and the count of distinct ratings they cover. Order by full name.