INTERACTIVE LECTURES
Answer questions across tables
Guided class activity · apply the ideas from the full SQL lessons.
Open the detailed lessons for this activity
Detailed lessons in this module
Open the full explanations, examples, and SQL exercises directly.
Preparing for Lab 8 · After practice, try the team challenge.
Opening your saved work…
Sign in to save across devices → · Download your guest work before switching accounts.
Classify and combine results
Step 3 of 12 · Database: Pixar & Box Office · New dataset: inspect its schema before continuing.
READ & UNDERSTAND
CASE chooses the first matching branch. State boundaries explicitly: a rating of exactly 8 belongs somewhere, and a missing rating may need a separate category.
UNION removes duplicate result rows; UNION ALL preserves them. Use the form that matches the question, not a habit of always removing duplicates.
A scalar subquery supplies a single value, such as an overall average. Check whether its population matches the population in your main query.
Worked example
SELECT movie_id, CASE WHEN rating >= 8 THEN 'High' ELSE 'Other' END AS rating_band FROM boxoffice ORDER BY movie_id;
A rating at least eight receives High; all other rows reach ELSE in this example. If a rating is NULL it also reaches ELSE, which may be too coarse for a real report.
The above-average practice uses the average of all boxoffice ratings. It asks for individual rows above a shared benchmark rather than one summary per movie.
Your question
Return movie_id and rating for boxoffice rows whose rating is above the overall average, ordered by movie_id.
Explain your evidence
Why would comparing a rating to its own movie-specific average fail to identify above-average movies?
Try a query to unlock the worked-answer discussion.
Query results
Predict the result, then run your query.Predict the result, then run your query.
Read-only activity · One statement per run · First 200 rows displayed · Checks compare the complete bounded result, not just this preview.
Explore this query freely in the playground ↗Check the save status above. Account-saved work is available in My Progress. Explanations are for discussion, not automatically graded. No prerequisite locks.