LEARN WITH ABDOU / DATABASE TEAM LAB
Movie studio: connect & summarize
Practice relationships, computed values, groups, and evidence.
Team: __________________________ Date: ______________
Dataset: Pixar & Box Office · Worksheet version: 2026-09-09
Open this exact lab: https://learnwithabdou.com/courses/mis3220/labs/movie-joins
Practice companion. Confirm official assignment and submission instructions in Canvas. Rotate query writer, evidence checker, and explainer. Record SQL, evidence, and reasoning.
Before class
P1. Know the row
Inspect the schema. What does one row represent in the table you will query? Which column identifies it?
P2. Read before you run
Read the matching lecture. Explain the difference between selecting columns and filtering rows. For a JOIN lab, also predict the number of matches.
P3. Bring a question
Run a small preview. Record one result you predicted and one point you want to discuss in class.
Database schema
boxoffice · 14 rows
| Column | Type | Key / required |
|---|---|---|
| movie_id | INTEGER | PK |
| rating | REAL | |
| domestic_sales | INTEGER | |
| international_sales | INTEGER |
movies · 14 rows
| Column | Type | Key / required |
|---|---|---|
| id | INTEGER | PK |
| title | TEXT | |
| director | TEXT | |
| year | INTEGER | |
| length_minutes | INTEGER |
Types and declared keys reflect the original teaching database. Check business meanings and logical relationships in the lecture.
Team investigation
1. Investigate 1
Inspect movies and boxoffice. Name the columns that connect a movie to its sales record and predict the number of matched rows.
SQL / evidence / explanation:
2. Investigate 2
Return title, domestic_sales, and international_sales for each matched movie, ordered by movie id.
SQL / evidence / explanation:
3. Investigate 3
Return title and worldwide_sales as domestic plus international sales, ordered by worldwide_sales descending then movie id.
SQL / evidence / explanation:
4. Investigate 4
Return title and rating for movies rated at least 8.0, ordered by rating descending then movie id.
SQL / evidence / explanation:
5. Investigate 5
Return director and movie_count for each director, ordered by director.
SQL / evidence / explanation:
6. Investigate 6
Return director and total_revenue across their movies, ordered by total_revenue descending then director. Explain why a duplicated JOIN row could inflate the total.
SQL / evidence / explanation:
7. Find the missing relationship
This query runs. Explain what pairs it creates, why summing sales afterward would be misleading, and how to fix it.
SELECT m.title, b.domestic_sales FROM movies AS m JOIN boxoffice AS b ORDER BY m.id, b.movie_id;
SQL / evidence / explanation:
8. Which is better?
Two queries produce the same totals: one uses a CTE and one uses a subquery. Define better in terms of readability, correctness checks, and measured performance. Do not infer speed from query length.
SQL / evidence / explanation:
Reflect and submit
Choose your strongest answer and one revision. Explain the evidence behind each. Export your SQL and notes, then follow Canvas for the assigned report, grading, and submission requirements.