LECTURES / MODULE 5
Answer questions across tables
Connect records with the right keys, preserve missing matches, and audit totals. Transfer Marathon pre-lab ideas to the Tool Library for Lab 8.
Detailed lessons in this module
Open the full explanations, examples, and SQL exercises directly.
1 / 12
Join on a relationship, not a coincidence
INNER JOIN returns matching pairs. The ON condition expresses the relationship: movies.id matches boxoffice.movie_id. Similar row positions or similar-looking names are not a relationship.
Before running, predict the unit and possible multiplicity of the result. A JOIN can duplicate a source fact even when the SQL is valid.
SELECT m.title, b.rating FROM movies AS m JOIN boxoffice AS b ON b.movie_id = m.id ORDER BY m.id;
The ON condition pairs a movie with its related box-office row using the identifier. Aliases m and b shorten references without changing the matching rule.
The output is one row per matched pair. Check uniqueness of movie_id in boxoffice before treating it as one row per movie in a different dataset.
Explore this concept →Explain it in your own words
What evidence would you collect before assuming there is exactly one sales record per movie?
2 / 12
Preserve missing matches
LEFT JOIN keeps every left-side row, including those with no match. Right-side fields are NULL for unmatched rows. That is a missing match, not a stored employee with an empty name.
A right-side filter in WHERE can remove the rows you intended to preserve. Put a restriction on eligible matches in ON when every left row must remain.
SELECT b.building_name, e.name FROM buildings AS b LEFT JOIN employees AS e ON e.building = b.building_name ORDER BY b.building_name, e.name;
The building table is on the left because every building must remain in the output. An empty building appears with a NULL employee name.
Several employees in the same building produce several rows for that building. Count distinct buildings to test preservation; counting result rows measures something else.
Explore this concept →Explain it in your own words
Compare restricting engineers in ON with restricting them in WHERE. Which preserves buildings with no engineers?
3 / 12
Classify and combine results
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.
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.
Explore this concept →Explain it in your own words
Why would comparing a rating to its own movie-specific average fail to identify above-average movies?
4 / 12
From the Marathon pre-lab to a new schema
The official Pre-Lab 8 uses grandmas_marathon.db; the assigned Lab 8 uses twin_ports_tools.db. This first exercise connects the familiar runner/result relationship before you switch databases. Do not carry table names into the Tool Library without inspecting its schema.
A runner can have many result records. Match results.runner_id to runners.id, so one joined row represents one result with the runner attached. The results snapshot also contains name fields; use the key relationship to identify the person rather than joining on repeated name text.
After this bridge, the new readings use the six-table Tool Library. A loan plays a similar linking role: it refers to a member, a tool and the staff member who checked it out. The same matching principle transfers even though the domain changes.
SELECT s.id AS result_id, s.runner_id, r.city FROM results AS s JOIN runners AS r ON r.id = s.runner_id ORDER BY s.id LIMIT 3;
The result ID remains visible so you can see the row being described. A repeated runner ID is legitimate when someone has several results.
Open Quick schema again after moving to the next topic: the practice database changes from Marathon to Tool Library.
Explore this concept →Explain it in your own words
Why can five joined result rows contain fewer than five runners? What plays the linking role in the Tool Library?
5 / 12
Match the foreign key, then state the row meaning
The Tool Library contains branches, staff, members, tools, loans and workshops. Use the tables required by the question. In particular, workshops are part of the schema but do not need to be joined into a loan report.
For a tool and its owning branch, match tools.branch_id to branches.id. Matching tools.id to branches.id can return plausible names, but those identifiers refer to different kinds of records. An INNER JOIN cannot tell you that the relationship is semantically wrong.
Give tables aliases and qualify columns so every id and name has an owner. Predict one row per tool when each tool has exactly one matching branch. Use the tool ID to check that no tool was duplicated or lost.
SELECT t.id AS tool_id, t.branch_id, b.name AS owning_branch FROM tools AS t JOIN branches AS b ON b.id = t.branch_id ORDER BY t.id LIMIT 3;
The example displays branch_id next to the resolved branch so the join can be audited.
Compare the requested tool IDs with SELECT id, branch_id FROM tools. Counting rows is useful, but checking the actual ID-to-branch mapping is stronger.
Explore this concept →Explain it in your own words
What independent check would expose a query that joins tool id to branch id but runs without an error?
6 / 12
Keep every branch while restricting eligible matches
When the output must include every branch, branches belongs on the left of a LEFT JOIN. A branch without an eligible tool still contributes a row, with NULL tool fields. That placeholder is not an actual tool.
COUNT(t.id) counts matched tools. COUNT(*) counts the placeholder too and can report one when there are no matches. Group by the branch identifier and its displayed name so the output is explicitly one row per branch.
Put a category restriction in ON when it determines which tools are eligible but every branch must remain. Putting t.category in WHERE rejects the NULL placeholders after the join. Adding OR t.id IS NULL does not fix branches that own tools but none in the desired category.
SELECT b.id AS branch_id, b.name AS branch, COUNT(t.id) AS tool_count FROM branches AS b LEFT JOIN tools AS t ON t.branch_id = b.id GROUP BY b.id, b.name ORDER BY b.id;
The example counts all tools per branch. The primary key t.id is NULL only on an unmatched placeholder here.
The exercise changes eligible matches without changing the six-row population. Check both the branch ID set and its counts.
Explore this concept →Explain it in your own words
Why does LEFT JOIN plus a WHERE filter on the right side fail the promise to keep all six branches?
7 / 12
Distinguish no loan from an unreturned loan
A LEFT JOIN gap test asks whether a matching row exists at all. Test loans.id IS NULL, since the primary key is populated for every real loan. Testing return_date IS NULL asks a different question: it also accepts real loans that have not been returned.
NOT EXISTS provides another way to ask for no matching row. The inner query refers to the outer tool ID and succeeds when a matching loan exists; NOT reverses that existence test. It avoids multiplying the outer tool rows.
An unused tool is a finding about this snapshot, not a reason by itself to discard it. Consider its branch opening date, purpose and replacement cost before recommending action.
SELECT t.id AS tool_id, t.name AS tool_name FROM tools AS t LEFT JOIN loans AS l ON l.tool_id = t.id WHERE l.id IS NULL ORDER BY t.id;
The LEFT JOIN example rejects all real matches and keeps only tools with no recorded loan.
Both correct forms should return the same tool IDs, not merely the same number of rows.
Explore this concept →Explain it in your own words
How could return_date IS NULL make a tool that is currently borrowed appear never borrowed?
8 / 12
Give one table two roles in a staff chart
A self-join reads staff twice with different aliases. e means employee; manager means the staff record referenced by e.supervisor_id. The aliases name roles, not two independently stored tables.
Use e.supervisor_id = manager.id. Matching e.id = manager.id would make people their own supervisors. INNER JOIN drops the director whose supervisor_id is NULL, so preserving every employee requires LEFT JOIN.
A missing supervisor reference in this snapshot represents the top of the reporting structure. It does not imply that the employee record itself is missing. Preserve the employee ID and the raw supervisor ID when checking the chart.
SELECT e.id AS employee_id, e.supervisor_id, manager.id AS matched_supervisor FROM staff AS e LEFT JOIN staff AS manager ON manager.id = e.supervisor_id ORDER BY e.id;
The diagnostic example displays both reference and matched key. For the director both supervisor fields remain NULL.
A correct row count does not prove the labels are right. Check that each matched supervisor ID equals the employee’s reference.
Explore this concept →Explain it in your own words
Which row would an INNER JOIN remove, and why is that a different mistake from joining an employee to themselves?
9 / 12
Follow a loan through member and tool keys
A loan links one member to one tool and one checkout staff member. Start from loans when each output row is meant to describe a loan event, then follow member_id and tool_id to their respective primary keys.
Joining members to tools through a shared branch would pair unrelated people and tools. A common branch is not evidence of borrowing. For each proposed join, name the fact that makes the pair meaningful.
The owning branch of a tool, the home branch of a member and the home branch of a staff member are separate relationships. The official replacement-value report attributes value to the branch that owns the tool. Choose the branch based on the business question, not whichever join is easiest.
SELECT l.id AS loan_id, m.id AS member_id, t.id AS tool_id FROM loans AS l JOIN members AS m ON m.id = l.member_id JOIN tools AS t ON t.id = l.tool_id ORDER BY l.id LIMIT 3;
Two many-to-one joins should preserve one row per loan when the references have valid matches.
Compare the loan IDs before and after joining. If a loan repeats, inspect the uniqueness of the matching keys before summing values.
Explore this concept →Explain it in your own words
Why might a report grouped by a member’s home branch differ from one grouped by the tool’s owning branch even if both run correctly?
10 / 12
Count repetitions before adding money
A staff hourly wage is one fact per staff member. Joining staff to loans repeats that wage for every matching checkout. SUM after that join can therefore count the same wage several times. A valid join can be wrong for the intended measure.
First state the unit of the measure. A sum of listed hourly wages across staff is a comparison baseline in this exercise; it is not payroll expense, because hours worked are absent. Calculate it from staff, adding branches only for labels.
SUM(DISTINCT hourly_wage) is not a general repair. Two different employees may share a wage and both should contribute. Deduplicate records by identity when appropriate, or aggregate each fact at its natural level before combining summaries.
SELECT s.id AS staff_id, COUNT(l.id) AS matching_loans FROM staff AS s LEFT JOIN loans AS l ON l.staff_id = s.id GROUP BY s.id ORDER BY s.id;
The example counts child records per staff member. A value larger than one warns that joining those loans repeats staff-level facts.
For richer dashboards, combine separate branch-level aggregates. Check identifiers and measures, not just the number of final branch rows.
Explore this concept →Explain it in your own words
Why can a loan join inflate the total, while SUM(DISTINCT hourly_wage) can undercount it? What data is missing for payroll expense?
11 / 12
Choose a benchmark and handle NULL in a membership test
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.
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.
Explore this concept →Explain it in your own words
Why can above the overall average and above the same-role average identify different people without either query having a syntax error?
12 / 12
Classify return status and choose a defensible denominator
The official snapshot is the close of business on 2026-09-30. A NULL return_date means still out. A completed return is late when return_date > due_date. Check NULL first, then lateness, then the remaining returned-on-time category.
For a late-return rate, the denominator is returned loans, not every loan. Still-out loans have no observed return outcome. A branch with no returned loans has an undefined rate; NULLIF protects the division and preserves that distinction from a zero late-return rate.
After a LEFT JOIN, also distinguish an unmatched loan placeholder from an actual loan with NULL return_date. COUNT(CASE WHEN l.id IS NOT NULL AND l.return_date IS NULL THEN 1 END) counts actual open loans. CASE order and the left-join gap test protect different meanings.
SELECT l.id AS loan_id, CASE WHEN l.return_date IS NULL THEN 'Still out' WHEN l.return_date > l.due_date THEN 'Returned late' ELSE 'Returned on time' END AS loan_status FROM loans AS l ORDER BY l.id LIMIT 5;
The first branch preserves unfinished outcomes. Only actual returns can reach the late/on-time split.
Keep numerator and denominator next to the percentage. A tidy percentage alone hides the evidence needed to interpret it.
Explore this concept →Explain it in your own words
Why does a rate with no returned loans differ from 0%, and why should still-out loans not be silently called returned on time?
SQL reference & course materials
Use these reference chapters alongside the detailed lessons above. Each example identifies its database.
Put it to work in class
Readiness practice and team activities complement the assigned Canvas lab. Official instructions and submission requirements stay in Canvas.