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.
Choose a benchmark and handle NULL in a membership test
Step 11 of 12 · Database: Twin Ports Tool Library
READ & UNDERSTAND
A scalar subquery supplies a single comparison value, such as the average hourly wage. A global average uses the full chosen staff population. A correlated average restricts that population using a value from the outer row, such as its role.
A list subquery serves IN or NOT IN. If a NOT IN list contains NULL, an otherwise unmatched value can produce unknown rather than true and be removed by WHERE. Test this with SELECT 3 NOT IN (1, NULL) before trusting an empty result.
When expressing absence, NOT EXISTS usually makes the intent clearer. If you repair NOT IN by excluding NULL, say why the outer key is non-NULL too. Neither syntax automatically defines the right population for a benchmark.
Worked example
SELECT s.id AS staff_id, s.hourly_wage FROM staff AS s WHERE s.hourly_wage > (SELECT AVG(other.hourly_wage) FROM staff AS other) ORDER BY s.id;
The example uses one shared benchmark for every staff member. That may reflect differences between roles as well as differences within roles.
The practice recalculates the comparison population for each outer role. Discuss whether that is the appropriate comparison before judging compensation.
Your question
Return staff_id and hourly_wage for staff paid above the average for their own role, ordered by staff id. Use s for the outer staff table and other inside the subquery.
Explain your evidence
Why can above the overall average and above the same-role average identify different people without either query having a syntax error?
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.