Query a Real Movie Database

Steps

1. Set up the database and import the CSVs

   Before you can query anything, you need the data in MySQL.

   Create the schema

   Unzip films-dataset.zip, then run the included schema.sql. From the mysql CLI:

   mysql -u root -p < schema.sql
   

   This creates the films database and the four tables with their primary and foreign keys.
   The foreign keys mean import order matters: a roles row references a films row and a
   people row, so those must exist first. Import in this order: films → people → reviews → roles.

   Import the data

   Pick one method:

   Option A — LOAD DATA (fast, CLI). Enable local infile and load each file:

   USE films;
   LOAD DATA LOCAL INFILE 'films.csv'   INTO TABLE films
     FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES;
   LOAD DATA LOCAL INFILE 'people.csv'  INTO TABLE people
     FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES;
   LOAD DATA LOCAL INFILE 'reviews.csv' INTO TABLE reviews
     FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES;
   LOAD DATA LOCAL INFILE 'roles.csv'   INTO TABLE roles
     FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES;
   

   (If you get ERROR 3948, start the client with mysql --local-infile=1 and set
   SET GLOBAL local_infile = 1;.)

   Option B — Workbench wizard. Right-click each table → Table Data Import Wizard → pick the
   matching CSV. Do it in the films → people → reviews → roles order.

   Verify the load

   Never trust an import you didn't check. Confirm the row counts:

   SELECT
     (SELECT COUNT(*) FROM films)   AS films,
     (SELECT COUNT(*) FROM people)  AS people,
     (SELECT COUNT(*) FROM reviews) AS reviews,
     (SELECT COUNT(*) FROM roles)   AS roles;
   

   You should see 4968, 8397, 4968, 19791. If any count is off, re-import that table.

   Deliverable for this step: the row-count result proving all four tables loaded.

2. Filter and sort: WHERE and ORDER BY

   Start with a single table, films, and answer questions by narrowing and sorting rows.

   Question 1 — the longest recent films

   List the 10 longest films released from 2000 onward, longest first, showing title, year and duration.

   SELECT title, release_year, duration
   FROM films
   WHERE release_year >= 2000
     AND duration IS NOT NULL
   ORDER BY duration DESC
   LIMIT 10;
   

   Read what each clause does:

   • WHERE release_year >= 2000 keeps only recent films.
   • AND duration IS NOT NULL drops rows with no runtime — critical, because ORDER BY would
     otherwise sort NULLs in and mislead you. NULL is "unknown", not zero.
   • ORDER BY duration DESC puts the longest first; LIMIT 10 takes the top slice.

   Try the variations

   Practice the other filtering operators from the course:

   -- IN: films from a set of countries
   SELECT title, country FROM films
   WHERE country IN ('Brazil', 'France', 'Japan')
   ORDER BY country, title;
   
   -- BETWEEN: films from the 1990s
   SELECT title, release_year FROM films
   WHERE release_year BETWEEN 1990 AND 1999
   ORDER BY release_year;
   
   -- LIKE: films whose title starts with "The Lord"
   SELECT title FROM films
   WHERE title LIKE 'The Lord%';
   

   Deliverable for this step: save Question 1's query and its top-10 result into queries.sql.

3. Connect tables: INNER JOIN

   A film's rating lives in reviews, not films. To use both, you join them on the shared key.

   Question 2 — the best-rated films

   Show the 10 highest-rated films (title, year, IMDb score), but only those with real audience
   weight — at least 50,000 votes — so a single 10/10 vote can't top the list.

   SELECT f.title, f.release_year, r.imdb_score, r.num_votes
   FROM films AS f
   INNER JOIN reviews AS r ON r.film_id = f.id
   WHERE r.num_votes >= 50000
   ORDER BY r.imdb_score DESC, r.num_votes DESC
   LIMIT 10;
   

   The key ideas:

   • INNER JOIN reviews AS r ON r.film_id = f.id glues each film to its review row via the foreign key.
   • Table aliases (f, r) keep the query short and make f.title vs r.imdb_score unambiguous.
   • INNER means only rows that match on both sides appear — films without a review row drop out.
   • The num_votes >= 50000 filter is a real analyst instinct: guard against tiny-sample noise.

   Join three tables — who directed it

   To bring in the director's name, travel films → roles → people:

   SELECT f.title, p.name AS director, r.imdb_score
   FROM films AS f
   INNER JOIN reviews AS r ON r.film_id = f.id
   INNER JOIN roles   AS rl ON rl.film_id = f.id AND rl.role = 'director'
   INNER JOIN people  AS p  ON p.id = rl.person_id
   WHERE r.num_votes >= 50000
   ORDER BY r.imdb_score DESC
   LIMIT 10;
   

   Notice the join condition rl.role = 'director' — without it you'd also match every actor,
   multiplying the rows. Filtering inside the join keeps the grain at "one row per film".

   Deliverable for this step: save Question 2's query and its result into queries.sql.

4. Summarize with GROUP BY and aggregates

   Aggregation collapses many rows into one summary row per group. This is where SQL answers
   "how many", "on average", "the most".

   Question 3 — average score by country

   For each country, show how many rated films it has and their average IMDb score — only
   countries with at least 30 rated films, best average first.

   SELECT
     f.country,
     COUNT(*)                 AS rated_films,
     ROUND(AVG(r.imdb_score), 2) AS avg_score
   FROM films AS f
   INNER JOIN reviews AS r ON r.film_id = f.id
   WHERE f.country IS NOT NULL
   GROUP BY f.country
   HAVING COUNT(*) >= 30
   ORDER BY avg_score DESC;
   

   The mechanics that trip people up:

   • GROUP BY f.country makes one output row per distinct country; the aggregates describe each group.
   • Every non-aggregated column in SELECT must appear in GROUP BY (here, f.country).
   • AVG(r.imdb_score) ignores NULL scores automatically; ROUND(..., 2) makes it readable.
   • HAVING COUNT(*) >= 30 filters groups — you can't use WHERE for that, because WHERE
     runs before grouping. WHERE filters rows; HAVING filters groups. That distinction is a
     classic interview question.

   Question 4 — the most prolific directors

   Who directed the most films? Show the top 10 directors by film count.

   SELECT p.name AS director, COUNT(*) AS films_directed
   FROM roles AS rl
   INNER JOIN people AS p ON p.id = rl.person_id
   WHERE rl.role = 'director'
   GROUP BY p.id, p.name
   ORDER BY films_directed DESC
   LIMIT 10;
   

   Grouping by p.id (not just p.name) is the safe habit — two different people could share a
   name, and the id keeps them distinct.

   Deliverable for this step: save Questions 3 and 4 with their results into queries.sql.

5. Answer comparison questions with subqueries

   Some questions compare each row to a value you have to compute first. That inner computation is
   a subquery.

   Question 5 — films above the overall average

   How many films score above the average IMDb score of the whole dataset?

   SELECT COUNT(*) AS above_average_films
   FROM reviews
   WHERE imdb_score > (SELECT AVG(imdb_score) FROM reviews);
   

   The parenthesized (SELECT AVG(imdb_score) FROM reviews) runs first and returns a single number
   — the global average. The outer query then compares every film's score to it. You couldn't write
   WHERE imdb_score > AVG(imdb_score) directly, because an aggregate can't sit in a WHERE over
   the same rows — the subquery is how you get around that.

   Question 6 — directors who beat the average

   List directors whose average film score is above the overall average, with their average
   and film count — most films first. (At least 5 films, to be meaningful.)

   SELECT
     p.name AS director,
     COUNT(*)                       AS films,
     ROUND(AVG(r.imdb_score), 2)    AS avg_score
   FROM roles AS rl
   INNER JOIN people  AS p ON p.id = rl.person_id
   INNER JOIN reviews AS r ON r.film_id = rl.film_id
   WHERE rl.role = 'director'
   GROUP BY p.id, p.name
   HAVING COUNT(*) >= 5
      AND AVG(r.imdb_score) > (SELECT AVG(imdb_score) FROM reviews)
   ORDER BY films DESC, avg_score DESC;
   

   This one query uses everything: a three-way join, GROUP BY, two aggregates, a HAVING that
   combines a count filter with a subquery, and a multi-key ORDER BY. If you can read it and
   explain each clause, you've got the core of SQL.

   Deliverable for this step: save Questions 5 and 6 with their results into queries.sql.

6. Submit: your queries.sql

   Package the six queries into one reproducible file and submit it.

   Build your submission

   Create a file queries.sql containing your six answers, each under a comment header, with the
   result pasted below it as a comment. Structure:

   -- Films Dataset Lab — <your name>
   
   -- Q1: 10 longest films released from 2000 onward
   SELECT title, release_year, duration
   FROM films
   WHERE release_year >= 2000 AND duration IS NOT NULL
   ORDER BY duration DESC
   LIMIT 10;
   /* Result (top 3):
      title | release_year | duration
      ...
   */
   
   -- Q2: top 10 best-rated films with >= 50000 votes
   -- ... query + result ...
   
   -- Q3: average IMDb score by country (>= 30 rated films)
   -- Q4: top 10 most prolific directors
   -- Q5: how many films score above the overall average
   -- Q6: directors whose average beats the overall average (>= 5 films)
   

   Submit

   git init
   git add queries.sql
   git commit -m "SQL lab — films dataset queries"
   # push to a public repo or gist, then submit that URL
   

   Submit the repository or gist URL as your lab deliverable.

   Submission criteria (self-check)

   • Row-count check from Step 1 shows 4968 / 8397 / 4968 / 19791
   • All six queries run without error on a fresh import of the dataset
   • Q2 and Q6 use JOIN across at least three tables correctly
   • Q3 uses GROUP BY + HAVING (not WHERE) to filter groups
   • Q5 and Q6 use a subquery for the average comparison
   • Each query has its real result pasted below it (not invented)

   What's next

   You've queried a real relational database end to end. In the Backend Developer Path, this
   becomes the data layer of an API you build and deploy — the same films tables, now behind
   HTTP endpoints you design.