Supporting reference and preserved earlier practice. Open the current classroom lesson →
SUPPORTING SQL REFERENCE · LESSON 21
Lesson 21: Develop an analysis
Test your comprehensive SQL knowledge with advanced challenges covering all concepts from basic queries to complex joins and calculations.
Practice database: Five-table movie analysis fixture. Historical and illustrative movie data for SQL practice. Revenue and budget calculations are simplified teaching measures, not audited profit. Use the actual five-table schema below.
Read the explanation, then use the practice section below. Generic syntax and illustrative tables explain the idea; the exercise schema shows the exact names available in Five-table movie analysis fixture.
Bring the pieces together
Start with the question, identify the grain of the result, and find the tables that supply each value. The schema below is read from the actual five-table practice database.
Plan a join before writing it
movies joins boxoffice on id = movie_id. movie_studios connects movies to studios and records Production or Distribution roles. A movie can have several studio credits: joining too broadly can multiply rows and inflate a SUM. Filter the role deliberately and group at the level the question asks for.
Check units and definitions
International sales exclude domestic sales in this fixture. Their sum is worldwide revenue, not profit. The revenue-to-budget exercise is a simplified calculation that omits marketing, distribution, and other costs. Budgets and studio details are illustrative teaching data.
Explain an advanced query
A correlated subquery can compare each film’s revenue with the maximum for its year. It includes ties when you use equality. This engine does not support window functions. Order tied results explicitly so another student can reproduce the same output.
Check the entire result
Each exercise checks names, values, row counts, duplicates, and requested order. A plausible column heading alone does not establish a correct answer. Use the schema previews to test assumptions before running the full query.
Try a worked example
SELECT m.title, m.year, b.rating as imdb_rating FROM movies m JOIN boxoffice b ON m.id = b.movie_id WHERE m.year > 2005 AND b.rating > 8.0;
Read the query, predict its result, then inspect the rows or schema it produces.
Open example in playground ↗TRY IT YOURSELF
Lecture 19: SQL Mastery Challenge
Five-table movie analysis fixture · Develop and defend an analysis
Historical and illustrative movie data for SQL practice. Revenue and budget calculations are simplified teaching measures, not audited profit. Use the actual five-table schema below.
Opening your saved work…
Sign in to save across devices → · Download your guest work before switching accounts.
YOUR LESSON DATABASE
Tables, keys & sample rows
PK = primary key · FK = declared foreign key. Expand a table to see its columns and the first five stored rows.
Loading schema…
Query results
Run a query to explore. Check my answer checks the selected exercise.Run a query to explore. Check my answer checks the selected exercise.
Read-only practice · One statement at a time · Ctrl / ⌘ + Enter runs your query