Subqueries Practice

Goals

• Write subqueries in WHERE, FROM, and SELECT clause positions
• Use EXISTS and NOT EXISTS to test for the presence or absence of related data
• Build derived tables to pre-aggregate data before filtering
• Use correlated subqueries to compute per-row context values
• Identify when a subquery is clearer than a JOIN

Warm-up

Run this query and study the output:

SELECT category_id, name FROM category ORDER BY name;

Note the category_id for 'Comedy'. You will need it in Step 1 — but you should not hardcode it. The goal of this session is to let SQL resolve IDs dynamically.

Steps

Step 1 — Films in the Comedy Category (Easy)

Objective: Use a subquery in WHERE to find all films in the 'Comedy' category — without hardcoding any ID.

Tables involved: film, film_category, category

Task: Return film.title and film.rental_rate for all films whose film_id appears in film_category rows linked to the category named 'Comedy'. Order by title. Limit to 20 rows.

Expected shape:

SELECT title, rental_rate
FROM film
WHERE film_id IN (
    SELECT film_id
    FROM film_category
    WHERE category_id = (
        SELECT category_id FROM category WHERE name = 'Comedy'
    )
)
ORDER BY title
LIMIT 20;

Step 2 — Customer Count as Context Column (Easy)

Objective: Use a scalar subquery in SELECT to show a global count alongside individual rows.

Tables involved: customer

Task: Write a query that returns every customer's customer_id, first_name, last_name, and a column total_customers that shows the same number on every row — the total count of all customers in the database. Order by customer_id. Limit to 10 rows.

Expected shape:

SELECT customer_id,
       first_name,
       last_name,
       (SELECT COUNT(*) FROM customer) AS total_customers
FROM customer
ORDER BY customer_id
LIMIT 10;

Step 3 — Film Rental Rate Tier (Medium)

Objective: Use a derived table in FROM to classify each film as 'below avg', 'at avg', or 'above avg' based on its rental rate.

Tables involved: film

Task: Write a query that uses a derived table to compute the overall average rental rate, then joins or cross-joins it to the film table to classify each film. Return title, rental_rate, avg_rate (rounded to 2 decimal places), and a tier column with one of the three labels. Order by rental_rate, then title. Limit to 20 rows.

Expected shape:

SELECT f.title,
       f.rental_rate,
       ROUND(avg_stats.avg_rate::numeric, 2) AS avg_rate,
       CASE
           WHEN f.rental_rate < avg_stats.avg_rate THEN 'below avg'
           WHEN f.rental_rate > avg_stats.avg_rate THEN 'above avg'
           ELSE 'at avg'
       END AS tier
FROM film f
CROSS JOIN (
    SELECT AVG(rental_rate) AS avg_rate FROM film
) AS avg_stats
ORDER BY f.rental_rate, f.title
LIMIT 20;

Step 4 — Customers With Unreturned Rentals (Medium)

Objective: Use EXISTS to find all customers who have at least one rental where return_date IS NULL.

Tables involved: customer, rental

Task: Write a query using WHERE EXISTS (...) that returns customer_id, first_name, last_name, and email for every customer who currently has an outstanding (unreturned) rental. Order by last name.

Expected shape:

SELECT c.customer_id, c.first_name, c.last_name, c.email
FROM customer c
WHERE EXISTS (
    SELECT 1
    FROM rental r
    WHERE r.customer_id = c.customer_id
      AND r.return_date IS NULL
)
ORDER BY c.last_name;

Step 5 — Films That Have Never Been Rented (Hard)

Objective: Use NOT EXISTS to find films with absolutely no rental history.

Tables involved: film, inventory, rental

A film can only be rented through an inventory record. A film with no inventory can never have been rented. A film with inventory but no rental rows also qualifies.

Task: Return film.title and film.rental_rate for all films that appear in zero rental records. Order by rental rate descending, then by title.

Expected shape: There will likely be a small number of films (around 42 in the standard dataset). If your result is 0, check your join path in the NOT EXISTS clause.

SELECT f.title, f.rental_rate
FROM film f
WHERE NOT EXISTS (
    SELECT 1
    FROM inventory i
    JOIN rental r ON i.inventory_id = r.inventory_id
    WHERE i.film_id = f.film_id
)
ORDER BY f.rental_rate DESC, f.title;

Step 6 — Each Customer's Most Recent Rental Date (Hard)

Objective: Use a correlated subquery to show each customer alongside their most recent rental date.

Tables involved: customer, rental

Task: Return customer.customer_id, first_name, last_name, and last_rental — the most recent rental_date for that customer, computed via a correlated scalar subquery. If a customer has no rentals, last_rental should be NULL. Order by last_rental descending. Limit to 20 rows.

Expected shape:

SELECT c.customer_id,
       c.first_name,
       c.last_name,
       (
           SELECT MAX(r.rental_date)
           FROM rental r
           WHERE r.customer_id = c.customer_id
       ) AS last_rental
FROM customer c
ORDER BY last_rental DESC NULLS LAST
LIMIT 20;

Bonus Tasks

Bonus 1 — Films Above Their Category's Average Rental Rate

For each film, compute the average rental rate within its category using a correlated subquery. Return films whose rental rate exceeds their category average. Show the film title, rental rate, and the category average (rounded to 2 decimal places).

SELECT f.title, f.rental_rate,
       ROUND((
           SELECT AVG(f2.rental_rate)
           FROM film f2
           JOIN film_category fc2 ON f2.film_id = fc2.film_id
           WHERE fc2.category_id = fc.category_id
       )::numeric, 2) AS category_avg
FROM film f
JOIN film_category fc ON f.film_id = fc.film_id
WHERE f.rental_rate > (
    SELECT AVG(f2.rental_rate)
    FROM film f2
    JOIN film_category fc2 ON f2.film_id = fc2.film_id
    WHERE fc2.category_id = fc.category_id
)
ORDER BY f.title;

Bonus 2 — Staff Who Processed the Most Payments in a Single Day

Use a derived table to find, for each staff member, the calendar day they processed the most payments (by count). Return staff name, date, and payment count for that peak day.