Part III — SQL: Structured Query Language

Chapter 12. SQL Joins and Subqueries

Chapter 3 declared joins the algebra's only way to combine tables; this chapter is the craft of writing them. The relational model deliberately split the university into six tables — students here, sections there, the connection recorded as matching values — and joins are how connections become answers: transcripts, timetables, honor rolls, workload reports. Subqueries are the second combining tool: asking one question inside another, from single-value lookups to correlated per-row tests.

The two tools overlap, and knowing which to reach for is a professional skill: this chapter builds both, then closes with the method — staging complex questions through Common Table Expressions until "hard" queries become pipelines.

After studying this chapter you will be able to:

• Write inner joins over two, three, and four tables, with ON and USING.
• Use left, right, and full outer joins, including MySQL's emulation of FULL.
• Apply (and avoid) cross joins and self joins.
• Distinguish equi- and natural joins and explain why production avoids NATURAL JOIN.
• Aggregate over joins correctly, dodging the fan-out trap.
• Write nested, derived-table, and correlated subqueries.
• Choose between IN, EXISTS, ANY, and ALL, and never trip the NOT IN trap.
• Factor queries with CTEs, write recursive queries, and stage complex problems.

12.1 Inner joins

The inner join keeps only matched rows — pairs whose join condition holds:

SELECT s.full_name, e.grade
FROM   enrollment e
JOIN   student     s ON s.student_id = e.student_id
WHERE  e.section_id = 1
ORDER  BY e.grade DESC;

   full_name   | grade
---------------+-------
 Nusrat Jahan  | A
 Arif Mahmud   | A
 Rakib Hasan   | B+

Read it mechanically: for each of section 1's three enrollment rows, find the student row whose student_id matches e.student_id, and emit the pair's selected columns. JOIN is INNER JOIN spelled short; ON states the match condition — the foreign-key-to-primary-key equality, exactly the arrow on the ERD. The three-table form chains two such matches:

SELECT c.course_id, c.title, cs.room, i.full_name AS instructor
FROM   course        c
JOIN   course_section cs ON cs.course_id     = c.course_id
JOIN   instructor    i  ON i.instructor_id   = cs.instructor_id
WHERE  cs.semester = 'Fall' AND cs.section_year = 2026
ORDER  BY c.course_id;

 course_id |       title        |  room   |   instructor
-----------+--------------------+---------+----------------
 CSE221    | Database Systems   | SAC-401 | Farhana Rahman
 CSE251    | Data Structures    | SAC-303 | Ahmed Kabir
 ENG105    | Academic Writing   | AAB-210 | Tahmina Karim

The Fall 2026 timetable: course joined to sections joined to instructors — three ERD arrows, two ON clauses, one answer. When both tables name the shared column identically, USING (course_id) abbreviates the ON and merges the duplicate column; WHERE filters after matching, and any condition that belongs to the match itself (especially in outer joins, next) belongs in ON — a distinction Section 12.2 makes sharp.

12.2 Left, right, and full outer joins

Inner joins silently drop unmatched rows; outer joins keep them, NULL-padding the missing side:

 INNER: matched pairs only
 LEFT : every left row  (matched + unmatched-left)
 RIGHT: every right row (matched + unmatched-right)
 FULL : every row of both sides

The canonical dataset hides a perfect example: section 5 (CSE321, Spring 2026) has no enrollments — an inner join of course_section to enrollment loses it; the left join keeps it:

SELECT cs.section_id, cs.course_id, COUNT(e.student_id) AS enrolled
FROM   course_section cs
LEFT   JOIN enrollment e ON e.section_id = cs.section_id
GROUP  BY cs.section_id, cs.course_id
ORDER  BY enrolled DESC, cs.section_id;

 section_id | course_id | enrolled
------------+-----------+----------
          8 | MAT116    |        4
         11 | ENG105    |        4
          1 | CSE215    |        3
         10 | BBA101    |        3
          2 | CSE215    |        1
          3 | CSE221    |        2
          4 | CSE251    |        2
          5 | CSE321    |        0
          6 | EEE163    |        2
          7 | EEE221    |        2
          9 | MAT214    |        1
         12 | CSE221    |        2
         13 | CSE251    |        2

Section 5 appears with enrolled 0 — the "which sections are empty" question answered in one statement. Two details carry the section. First, **COUNT(e.student_id), not COUNT(*): the unmatched section-5 row exists in the join output (NULL-padded), so COUNT() would report 1; counting the right table's column* counts matches only. Second, ON versus WHERE**: a right-table condition in WHERE (WHERE e.grade IS NOT NULL) silently converts the left join back to an inner join — the NULL-padded rows die in WHERE. The rule: right-table filters belong in the ON clause of an outer join; left-table filters may sit in WHERE.

RIGHT JOIN is the mirror (swap the tables and use LEFT — the professional spelling, since everyone reads left-to-right). **FULL JOIN** keeps both sides' unmatched rows; MySQL 8.0 does not implement it — the emulation is A LEFT JOIN B ... UNION ALL ... A RIGHT JOIN B WHERE B.key IS NULL, a pattern worth recognizing in MySQL codebases.

12.3 Cross joins

CROSS JOIN is the bare Cartesian product of Chapter 3 — every row of A with every row of B:

SELECT COUNT(*) AS candidate_seats
FROM   student s
CROSS  JOIN course_section cs
WHERE  cs.semester = 'Fall' AND cs.section_year = 2026;

 candidate_seats
-----------------
              36

Twelve students times three Fall 2026 sections — thirty-six candidate enrollments (a real registration system would subtract the taken ones and check capacity). Two uses justify CROSS JOIN: generating combinations for seeding, calendars, or matrices, and small report cross-tabs (departments × semesters). One production law: an accidental cross join — two tables in FROM with a forgotten join condition — is instantly recognizable by a row count that is the product of two table sizes and a query that never finishes. On MySQL, a comma-join (FROM a, b) is a cross join; on PostgreSQL, the comma form with no WHERE is the same trap.

12.4 Self joins

A self join pairs a table with itself under two aliases — the ρ-then-join of Chapter 3. Two canonical questions:

-- Which instructor pairs share a department?
SELECT i1.full_name AS one, i2.full_name AS two
FROM   instructor i1
JOIN   instructor i2 ON i1.dept_id = i2.dept_id
                   AND i1.instructor_id < i2.instructor_id;

     one      |      two
--------------+----------------
 Ahmed Kabir  | Farhana Rahman

-- Which pairs of sections share a room (different sections)?
SELECT a.section_id AS s1, b.section_id AS s2, a.room
FROM   course_section a
JOIN   course_section b ON a.room = b.room
                      AND a.section_id < b.section_id;

 s1 | s2 |  room
----+----+--------
  3 | 12 | SAC-401
  4 | 13 | SAC-303

Both listings share the same two disciplines: the alias makes one table into two distinguishable copies, and the symmetry breaker (i1.instructor_id < i2.instructor_id) makes each pair appear once and pairs nothing with itself. Without the breaker, every pair appears twice (plus self-pairs) — the classic self-join bug. Self joins are also how hierarchies are walked one level at a time: an employees table's manager_id → employee_id join reports each employee with their manager's name, and deeper chains go to the recursive queries of Section 12.10.

12.5 Equi-joins and natural joins

Nearly every join in practice is an equi-join — the ON condition is equality between a foreign key and the key it references (Sections 12.1's examples). The vocabulary matters mostly because non-equi joins exist and are useful: ON a.salary < b.salary pairs instructors for salary comparison; ON s.gpa BETWEEN c.min AND c.max could bucket students into honors bands — the join condition may be any predicate, and remembering that dissolves the "joins are only for keys" mental limit.

The natural join is the equi-join that finds its own condition — match all columns with identical names in both tables, merge them once:

SELECT course_id, title, room
FROM   course
NATURAL JOIN course_section
WHERE  section_id = 12;

course and course_section share exactly one column name (course_id), so this works — the Fall 2026 Database Systems section joins to its title. But the mechanism, not the result, is the problem: NATURAL JOIN is name-driven. Add a room column to course next semester, and this query's meaning silently changes (or errors); rename course_id in one table, and the join silently becomes a cross join. This is why the professional world writes explicit ON conditions — the join states its own meaning, and schema evolution cannot ambush it. USING (course_id) is the middle ground: the convenience of one merged column with an explicit, stable condition.

12.6 Joining multiple tables

The four-table chain answers the transcript question — student, enrollments, sections, courses:

SELECT c.course_id, c.title, cs.semester, cs.section_year, e.grade
FROM   student        s
JOIN   enrollment     e  ON e.student_id  = s.student_id
JOIN   course_section cs ON cs.section_id = e.section_id
JOIN   course         c  ON c.course_id   = cs.course_id
WHERE  s.full_name = 'Nusrat Jahan'
ORDER  BY cs.section_year, c.course_id;

 course_id |         title          | semester | section_year | grade
-----------+------------------------+----------+--------------+-------
 CSE215    | Programming Language II| Fall     |         2024 | A
 CSE221    | Database Systems       | Fall     |         2024 | A-
 MAT116    | Calculus I             | Fall     |         2024 | B+

Nusrat's record: three tables joined through the keys the ERD drew. Multi-table joins compose left-to-right in the FROM clause; the optimizer reorders them freely (Chapter 19), which is your license to write joins in readable order.

The fan-out trap — the one aggregate-over-joins rule to memorize. Joining two 1:N children of the same parent multiplies rows: a course with two sections joined to its instructor's rows... concretely, "how many distinct courses has each instructor taught?":

SELECT i.full_name, COUNT(DISTINCT cs.course_id) AS courses
FROM   instructor i
JOIN   course_section cs ON cs.instructor_id = i.instructor_id
GROUP  BY i.full_name
ORDER  BY courses DESC, i.full_name;

     full_name      | courses
--------------------+---------
 Farhana Rahman     |       3
 Ahmed Kabir        |       2
 Mahmudul Islam     |       2
 Nazmul Chowdhury   |       2
 Sharmin Ahmed      |       1
 Tahmina Karim      |       1

Farhana's three courses (CSE215, CSE221, CSE321) span four sections — the join produced four rows for her, and COUNT(cs.course_id) would have said 4, the section count wearing a course count's label. Whenever you aggregate over a join, ask: how many rows does each logical subject contribute? — and reach for COUNT(DISTINCT ...) when the answer is more than one. Chapter 25's warehouse design treats fan-out as a first-class design problem.

12.7 Nested and correlated subqueries

A subquery is a query inside another statement. Three placements cover the uses:

Scalar subquery (one row, one column, used as a value):

SELECT full_name,
       gpa,
       (SELECT ROUND(AVG(gpa), 2) FROM student) AS university_avg
FROM   student
WHERE  gpa > 3.80;

  full_name   | gpa  | university_avg
--------------+------+----------------
 Sadia Afrin  | 3.88 |           3.48
 Arif Mahmud  | 3.90 |           3.48

Table subqueries — in FROM they are derived tables (with a mandatory alias), letting you query a query:

SELECT t.section_id, t.n AS enrolled
FROM   (SELECT section_id, COUNT(*) AS n
        FROM   enrollment
        GROUP  BY section_id) AS t
WHERE  t.n >= 3
ORDER  BY t.n DESC, t.section_id;

 section_id | enrolled
------------+----------
          8 |        4
         11 |        4
          1 |        3
         10 |        3

The HAVING result of Chapter 11, rebuilt as a pipeline: group first, then filter the grouped result as if it were a table. Derived tables generalize — any intermediate result becomes queryable.

Correlated subqueries reference the outer query's row — evaluated once per outer row. The honor-roll test: students above their own major's average:

SELECT s1.full_name, s1.gpa
FROM   student s1
WHERE  s1.gpa > (SELECT AVG(s2.gpa)
                 FROM   student s2
                 WHERE  s2.major_dept_id = s1.major_dept_id)
ORDER  BY s1.gpa DESC;

   full_name    | gpa
----------------+------
 Arif Mahmud    | 3.90
 Sadia Afrin    | 3.88
 Nusrat Jahan   | 3.75
 Mehjabin Chowdhury | 3.70
 Imran Hossain  | 3.15

For each student, the inner query computes the average of that student's department (CSE 3.66, EEE 3.61, BBA 3.10, Mathematics 2.98 — Zara excluded from the average but also by the comparison, since NULL > anything is UNKNOWN). Five students clear their own bar. Correlation is powerful and, on large tables, expensive — the inner query runs per row unless the optimizer unrolls it (Chapter 19); when a correlated form slows down, the CTE reformulation of Section 12.11 is usually the fix.

12.8 IN, EXISTS, ANY, and ALL

The membership and quantification tests — SQL's "is in that set" and "for every":

• IN (subquery) — membership: WHERE student_id IN (SELECT student_id FROM enrollment WHERE section_id = 11) returns the four ENG105 students by ID.
• EXISTS / NOT EXISTS — row-presence, usually correlated; the anti-join (rows of A with no match in B) is NOT EXISTS, and it is the safe negation:

SELECT s.full_name
FROM   student s
WHERE  NOT EXISTS (SELECT 1
                   FROM   enrollment e
                   JOIN   course_section cs ON cs.section_id = e.section_id
                   WHERE  e.student_id = s.student_id
                     AND  cs.semester = 'Fall'
                     AND  cs.section_year = 2026)
ORDER  BY s.full_name;

     full_name
--------------------
 Nusrat Jahan
 Rakib Hasan
 Sadia Afrin
 Farhan Akter
 Tanvir Alam
 Mehjabin Chowdhury

Six students are not enrolled in anything this fall — EXISTS probes row presence (hence SELECT 1; the list is never fetched), NOT EXISTS is immune to the NOT IN NULL trap of Section 10.8, and the pair outperforms IN/NOT IN on large sets in practice (the optimizer can stop at the first matching row).

• ANY/SOME and ALL — quantified comparison: "greater than any / all values of the set":

SELECT full_name, salary
FROM   instructor
WHERE  salary > ALL (SELECT salary FROM instructor
                     WHERE dept_id = 3);

     full_name      |  salary
--------------------+-----------
 Ahmed Kabir        | 120000.00
 Nazmul Chowdhury   | 130000.00
 Mahmudul Islam     | 110000.00

Business Administration's sole salary is 98000, so > ALL (that one row) means > 98000 — three instructors clear it. > ANY would mean "exceeds at least one" (far weaker). ALL with an empty set is TRUE, ANY with an empty set is FALSE — the logical edge cases worth stating because they surprise. And the NULL law applies as everywhere: a NULL in the compared set makes ANY/ALL answers UNKNOWN — filter NULLs in the subquery, always.

12.9 Common Table Expressions (CTEs)

A CTE names a query for use within one statement — WITH name AS (...) — turning pipelines into readable programs:

WITH section_counts AS (
    SELECT section_id, COUNT(*) AS enrolled
    FROM   enrollment
    GROUP  BY section_id
)
SELECT cs.section_id, cs.course_id,
       COALESCE(sc.enrolled, 0) AS enrolled
FROM   course_section cs
LEFT   JOIN section_counts sc ON sc.section_id = cs.section_id
ORDER  BY cs.section_id;

The section-occupancy report (all 13 sections, 5 included with 0) — Section 12.2's result, but the grouping is named, staged, and reusable. Multiple CTEs chain with commas, each seeing the ones before it (WITH a AS (...), b AS (SELECT ... FROM a ...)) — the structure that replaced most derived-table nesting in modern SQL. A CTE is not a performance device: it is textual (the planner usually inlines it); it is a correctness and readability device, exactly like a well-named local variable. Both platforms fully support CTEs (MySQL since 8.0); the materialized-CTE performance dialectics belong to Chapter 19.

12.10 Recursive queries

WITH RECURSIVE lets a CTE refer to itself, iterating a seed and a step until no new rows appear — the SQL:1999 feature that lets plain SQL walk trees and graphs. The canonical warm-up, a number generator:

WITH RECURSIVE numbers AS (
    SELECT 1 AS n                 -- seed
    UNION ALL
    SELECT n + 1 FROM numbers     -- step: previous rows + 1
    WHERE n < 5                   -- stop condition
)
SELECT n FROM numbers;

 n
---
 1
 2
 3
 4
 5

The shape to memorize: seed, UNION ALL, self-referencing step, stop condition — UNION ALL (not UNION, which would deduplicate and can also de-duplicate the iteration, breaking termination subtly). The university-shaped use is chain-walking over a self-referencing table, for instance a prerequisite table (the Chapter 5 extension, not in the canonical schema):

WITH RECURSIVE course_chain AS (
    SELECT course_id, prereq_course_id, 1 AS depth
    FROM   prerequisite WHERE course_id = 'CSE321'
    UNION ALL
    SELECT p.course_id, p.prereq_course_id, cc.depth + 1
    FROM   prerequisite p
    JOIN   course_chain cc ON p.course_id = cc.prereq_course_id
)
SELECT prereq_course_id, depth FROM course_chain;

Recursive CTEs walk prerequisite chains, employee–manager hierarchies, bill-of-materials explosions, and category trees — always with two professional cautions: the stop condition must be guaranteed (an accidental cycle iterates forever; PostgreSQL enforces a default max recursion), and each platform sets its own limits (max_iteration_count in MySQL's older releases; PostgreSQL's is configurable). Both platforms support the standard WITH RECURSIVE spelling shown here.

12.11 Solving complex problems with joins and subqueries

The chapter's toolkit closes with a method. Complex queries decompose; the craft is staging. The honor roll with department names — a correlated subquery, a join, and an ordering — as a three-stage CTE pipeline:

WITH dept_avg AS (
    SELECT major_dept_id, AVG(gpa) AS avg_gpa
    FROM   student
    GROUP  BY major_dept_id
),
honor AS (
    SELECT s.full_name, s.gpa, s.major_dept_id
    FROM   student s
    JOIN   dept_avg d ON d.major_dept_id = s.major_dept_id
    WHERE  s.gpa > d.avg_gpa
)
SELECT h.full_name, h.gpa, dep.dept_name
FROM   honor h
JOIN   department dep ON dep.dept_id = h.major_dept_id

   full_name    | gpa  |          dept_name
----------------+------+------------------------------
 Arif Mahmud    | 3.90 | Computer Science and Engineering
 Sadia Afrin    | 3.88 | Electrical and Electronic Engineering
 Nusrat Jahan   | 3.75 | Computer Science and Engineering
 Mehjabin Chowdhury | 3.70 | Electrical and Electronic Engineering
 Imran Hossain  | 3.15 | Business Administration

The method, as a checklist you can apply to any spec:

1. Name the tables. Which entities hold the facts? (student, department for averages and names.)
2. Name the connections. Which arrows on the ERD connect them? (major_dept_id, dept_id.)
3. Stage the computation. Which intermediate results would make each step obvious? (dept_avg, then honor, then dress with names.)
4. Watch the NULLs and the fan-out. (AVG skips Zara; no aggregate crosses a 1:N join here without DISTINCT.)
5. Verify by count. Predict the row count before running (five students above their major's average — Arif, Sadia, Nusrat, Mehjabin, Imran), and reconcile any mismatch before trusting the result.

Every "hard query" you will meet in an interview or a ticket is three to six of these stages wearing a business vocabulary. The division query of Chapter 3 — students enrolled in every Fall 2026 CSE221 section — is the method's final exercise: recognize the ∀, negate it twice with NOT EXISTS, and it, too, becomes a pipeline.

Chapter Summary

• Inner joins match rows on ON conditions (equi-joins over keys in practice); USING merges identically named shared columns.
• Outer joins preserve unmatched rows: LEFT keeps every left row; right-table filters go in ON, never WHERE; COUNT the right table's column, not COUNT(*); MySQL lacks FULL JOIN and emulates it with UNION ALL.
• Cross joins generate combinations (36 candidate seats) and cause the classic accidental-product bug; self joins need aliases and a symmetry breaker.
• NATURAL JOIN is name-driven and fragile — explicit ON is the production form.
• Multi-table joins chain keys across four tables (the transcript); aggregates over joins must respect fan-out — COUNT(DISTINCT) when a subject spans multiple rows.
• Subqueries come in three placements: scalar values, derived tables in FROM, and correlated per-row tests (honor roll above own-major average).
• IN tests membership; EXISTS/NOT EXISTS test presence and form the anti-join, immune to the NOT IN NULL trap; ANY/ALL quantify comparisons (ALL over empty set TRUE, ANY FALSE).
• CTEs (WITH) name and stage queries; WITH RECURSIVE iterates seed → UNION ALL → step → stop for trees and chains.
• The method: tables → connections → stages → NULL/fan-out checks → verified counts; every hard query is a pipeline.

Key Terms

Laboratory Exercises

1. Write the transcript query of Section 12.6 for Arif Mahmud and predict the rows before running (his two Fall 2026 enrollments are in progress). Expected result: three rows — CSE215 Fall 2024 grade A, plus CSE221 and CSE251 Fall 2026 with NULL grades.
2. Run the section-occupancy left join of Section 12.2 twice — once with COUNT(e.student_id) and once with COUNT(*) — and explain the differing row for section 5. Expected result: enrolled 0 versus 1 for section 5; the NULL-padded row exists and COUNT() counts it.*
3. Self joins: run both Section 12.4 queries; then write a third listing all pairs of sections in the same semester and year (verify against the semester counts of Chapter 11: Fall 2024 pairs, Spring 2025 pairs, Fall 2026 pairs). Expected results: one instructor pair (Ahmed Kabir, Farhana Rahman); two room pairs (3–12, 4–13); 19 same-term pairs in total — Fall 2024 has 6, Spring 2025 has 10, Fall 2026 has 3 (Spring 2026 offers one section, so none).
4. Rebuild the honor roll three ways: the correlated subquery of Section 12.7, the CTE pipeline of Section 12.11, and a derived-table form — and confirm all three return the same five students. Expected result: Arif Mahmud, Sadia Afrin, Nusrat Jahan, Mehjabin Chowdhury, Imran Hossain — identical from all three forms.
5. Anti-join practice: instructors teaching nothing this fall (NOT EXISTS over Fall 2026 sections), and courses with no sections at all (none exist — verify the query is correct by testing it against course 'PHY101' in a dev database). Expected results: Nazmul Chowdhury, Sharmin Ahmed, Mahmudul Islam — the three instructors without Fall 2026 sections; zero rows for no-section courses in the canonical data, one row (PHY101) in the dev test.
6. Rerun Chapter 3's division query (students enrolled in every Fall 2026 CSE221 section) as a NOT EXISTS pipeline, then extend it to "students enrolled in every section taught by Ahmed Kabir (101)" and predict the result before running. Expected results: Arif Mahmud and Shahriar Islam for CSE221; the empty set for instructor 101 — nobody is enrolled in all three of sections 1, 4, and 13.

Review Questions and Exercises

1. What does an inner join do with rows that have no partner, and which join keeps them? Discards them; the appropriate outer join (LEFT keeps left-side orphans).
2. Why must right-table conditions in a LEFT JOIN sit in ON rather than WHERE? WHERE runs after the join and discards the NULL-padded unmatched rows, silently reverting the query to an inner join.
3. Explain the COUNT(e.student_id) versus COUNT(*) difference under the occupancy query. COUNT() counts output rows including the NULL-padded orphan (section 5 → 1); counting the right table's column counts only real matches (→ 0).*
4. Give one legitimate use and one classic failure of CROSS JOIN. Legitimate — generating combinations (students × open sections as candidate seats); failure — a forgotten join condition producing an accidental product.
5. Why does the self join on instructor pairs need i1.instructor_id < i2.instructor_id? To break symmetry — without it each pair appears twice and each instructor pairs with themselves.
6. Your query joins course to instructor through course_section and counts courses per instructor; it reports Farhana Rahman with 4. Diagnose and fix. Fan-out — her three courses span four sections; use COUNT(DISTINCT cs.course_id), which gives 3.
7. Distinguish a scalar subquery, a derived table, and a correlated subquery with one phrase each. Single value in an expression; named query result in FROM; inner query re-evaluated per outer row because it references it.
8. Which five students are above their major's average, and why is Zara not among them by two reasons? Arif, Sadia, Nusrat, Mehjabin, Imran; Zara's NULL is skipped by AVG (so she doesn't lift Mathematics' average) and NULL > 2.98 is UNKNOWN.
9. Why is NOT EXISTS the recommended anti-join rather than NOT IN? NOT IN returns nothing if the set contains NULL; NOT EXISTS is NULL-safe and lets the optimizer stop at the first match.
10. What do salary > ALL (empty set) and salary > ANY (empty set) evaluate to? TRUE and FALSE respectively — vacuous quantification; state it, then filter empty sets deliberately.
11. Rewrite Section 12.7's derived-table query as a CTE and state one thing gained and one thing not gained. Gained — a named, reusable, readable stage (correctness/maintainability); not gained — performance; CTEs are inlined by the planner, not cached.
12. Name the four parts of every WITH RECURSIVE query and the two professional cautions. Seed, UNION ALL, self-referencing step, stop condition; guarantee termination (cycles) and know the platform's recursion limits.

Mini-Project

Build the registrar's query library, queries.sql — ten named, commented, reusable queries a portal would actually call, each preceded by a one-line comment stating the question and the expected output: (1) a student's full transcript (four tables, NULL grades shown as 'In progress' via COALESCE/CASE); (2) the section-occupancy report with capacity and seats-free (join + arithmetic, section 5 visible); (3) the honor roll pipeline of Section 12.11; (4) instructor workload (sections per instructor this term, zero-section instructors included via LEFT JOIN — verify against the data: exactly three instructors teach this fall — Ahmed Kabir, Farhana Rahman, Tahmina Karim — and three do not); (5) the course-catalog listing with department names and course counts per department (DISTINCT against fan-out); (6) an anti-join "students not enrolled this fall"; (7) a correlated "sections whose enrollment exceeds its instructor's average section size"; (8) a WITH RECURSIVE prerequisite chain over a prerequisite table you add in university_dev (choose a small, sensible prerequisite graph for the CSE courses); (9) Chapter 3's division query, in NOT EXISTS form; (10) one free-choice question of your own. Every query: verified against the canonical dataset, with its row count predicted in the comment. This library is the seed of Chapter 24's application and Chapter 28's graded laboratory.