Please wait while we prepare your content

JOIN practice lab

Tip: For the best layout, collapse the left course sidebar while you work.

This page is hands-on practice after the SQL JOINs lesson. Use the schema as your map, work through the prompts in order, and run queries in the Code Editor panel (split view on wide screens) against the built-in movie database.

Dataset: Movie-style practice data bundled with this course’s SQL sandbox (sample rows and schema below).

Sample data

Sample rows from the movie reviews practice tables

Schema

Table names, keys, and how movies, reviewers, and ratings connect—use this before you write FROM / JOIN / ON.

Assignments

1. Select the title and year of every movie together with the reviewer name who rated that movie.

Each row in movie_ratings becomes one output row (e.g. Wanted 2008 appears twice for Sarah Martinez’s two ratings).

2. Select the reviewer name, movie title, and number of stars for each rating.

One row per rating event (14 rows on the sample data).

3. Select each movie title and the average star rating across all reviewers for that movie.

On the sample data, Wanted averages 3.0 (ratings 2, 4, 3); Raiders of the Lost Ark averages about 3.33 (4, 2, 4). Movies with no ratings do not appear with an inner join.

4. Same as question 2, but only for movies released in 2008.

Only Wanted and Twilight have 2008 release years and ratings in the sample (Cloverfield has no ratings).

5. Join movies, movie_reviewers, and movie_ratings to list each reviewer and the number of distinct movies they rated with at least 3 stars.

Example counts on the sample: John White → 2 distinct movies (≥3 stars on Twilight and Raiders); James Cameron (reviewer) → 1 (Avatar). Reviewers with no rating at or above 3 stars are omitted here.

6. For each movie, return the title and rating spread (highest stars minus lowest stars for that movie). Sort by rating spread from highest to lowest, then by movie title ascending.

Tied spread 2: Avatar, Raiders of the Lost Ark, Wanted — ordered A, R, W by title.

7. Return reviewer name, movie title, and stars for ratings where the reviewer is the same person as the movie's director (use the schema columns to express same person).

Reviewer James Cameron (id 207) matches director James Cameron on Avatar; the sample yields one row (5 stars).

Please wait while we prepare your content

JOIN practice lab

Tip: For the best layout, collapse the left course sidebar while you work.

Dataset: Movie-style practice data bundled with this course’s SQL sandbox (sample rows and schema below).

Sample data

Schema

Table names, keys, and how movies, reviewers, and ratings connect—use this before you write FROM / JOIN / ON.

Assignments

1. Select the title and year of every movie together with the reviewer name who rated that movie.

Each row in movie_ratings becomes one output row (e.g. Wanted 2008 appears twice for Sarah Martinez’s two ratings).

2. Select the reviewer name, movie title, and number of stars for each rating.

One row per rating event (14 rows on the sample data).

3. Select each movie title and the average star rating across all reviewers for that movie.

On the sample data, Wanted averages 3.0 (ratings 2, 4, 3); Raiders of the Lost Ark averages about 3.33 (4, 2, 4). Movies with no ratings do not appear with an inner join.

4. Same as question 2, but only for movies released in 2008.

Only Wanted and Twilight have 2008 release years and ratings in the sample (Cloverfield has no ratings).

5. Join movies, movie_reviewers, and movie_ratings to list each reviewer and the number of distinct movies they rated with at least 3 stars.

6. For each movie, return the title and rating spread (highest stars minus lowest stars for that movie). Sort by rating spread from highest to lowest, then by movie title ascending.

Tied spread 2: Avatar, Raiders of the Lost Ark, Wanted — ordered A, R, W by title.

7. Return reviewer name, movie title, and stars for ratings where the reviewer is the same person as the movie's director (use the schema columns to express same person).

Reviewer James Cameron (id 207) matches director James Cameron on Avatar; the sample yields one row (5 stars).

JOIN practice lab

Sample data

Schema

Assignments

1. Select the title and year of every movie together with the reviewer name who rated that movie.

Sample solution

2. Select the reviewer name, movie title, and number of stars for each rating.

Sample solution

3. Select each movie title and the average star rating across all reviewers for that movie.

Sample solution

4. Same as question 2, but only for movies released in 2008.

Sample solution

5. Join movies, movie_reviewers, and movie_ratings to list each reviewer and the number of distinct movies they rated with at least 3 stars.

Sample solution

6. For each movie, return the title and rating spread (highest stars minus lowest stars for that movie). Sort by rating spread from highest to lowest, then by movie title ascending.

Sample solution

7. Return reviewer name, movie title, and stars for ratings where the reviewer is the same person as the movie's director (use the schema columns to express same person).

Sample solution

JOIN practice lab

Sample data

Schema

Assignments

1. Select the title and year of every movie together with the reviewer name who rated that movie.

Sample solution

2. Select the reviewer name, movie title, and number of stars for each rating.

Sample solution

3. Select each movie title and the average star rating across all reviewers for that movie.

Sample solution

4. Same as question 2, but only for movies released in 2008.

Sample solution

5. Join movies, movie_reviewers, and movie_ratings to list each reviewer and the number of distinct movies they rated with at least 3 stars.

Sample solution

6. For each movie, return the title and rating spread (highest stars minus lowest stars for that movie). Sort by rating spread from highest to lowest, then by movie title ascending.

Sample solution

7. Return reviewer name, movie title, and stars for ratings where the reviewer is the same person as the movie's director (use the schema columns to express same person).

Sample solution