Lesson 11.4

SQL JOIN Practice: Linking Customers, Rentals, and Movies

Stitch three tables together so a rental reads as a name and a title, not a pair of ids.

The rentals table records a customer_id and a movie_id, but not the customer's name or the film's title. Those live in the customers and movies tables, reached by their keys.

A single query can join more than two tables. Start from the table in the middle: rentals, and add one JOIN for each related table you need.

Build the joins before you filter

Get the relationships working first, then add the WHERE clause. Because the join brings customer columns into the same result, you can filter on c.city even though the city never appears in the rentals table. Table aliases liker, c, and m keep the column references short and unambiguous.

Pattern

select a.detail, b.detail, c.detail
from fact_table as a
join dim_one as b on a.b_id = b.b_id
join dim_two as c on a.c_id = c.c_id
where b.filter_column = 'value'

Schema · Movie RentalsTable · rentals6 columns · 26 rows

Table · rentals

One row per rental. Movies still out have a null returned_date.

6 columns · 26 rows

rental_id intcustomer_id intmovie_id intrental_date datereturned_date dateamount decimal

Your task

For rentals by Portland customers, return the customer's first name, last name, the movie title, the rental date, and the amount. Order by rental date, oldest first.

SQL Workbench

query.sqlmovie_rentals · SQL engine loading

⌘↵ to run

Expected answer