Part VIII — Laboratory Exercises and Projects

Chapter 28. SQL Laboratory Exercises

This chapter is the book's laboratory manual: ten graded labs, in dependency order, covering the full SQL arc from Chapter 8 to Chapter 19 against the canonical university database. Each lab states its objective, its tasks with numbered steps, its expected outputs (deterministic against Appendix H unless marked dev-only), and its deliverables. The labs are designed as weekly sessions for a course — Chapter 29's case studies and Chapter 30's capstone then consume the skills — and every one of them assumes the environment of Chapter 14 (both platforms loaded, university_dev beside the canonical database) and the discipline of every chapter since: predict, run, verify.

Working rules for the semester's labs: run mutating exercises in university_dev and leave the canonical university pristine (Lab 1 establishes the reset script); every lab ends with a commit of the lab script and its captured output; and the standing grading criterion across all ten labs is predicted outputs — a correct result you cannot explain earns half the credit of a stated prediction that a run then corrects.

After completing these labs you will be able to:

• Execute the full DDL/DML/DDL-verification cycle unaided.
• Query, join, aggregate, and window over the canonical schema fluently.
• Build views, indexes, and constraints with justifications.
• Demonstrate transaction and rollback behavior on demand.
• Read and improve query plans with evidence.

28.1 Creating databases and tables

Objective. The Chapter 9 cycle, unaided: database, tables, constraints, load, verify.

1. Create lab28_dev on both platforms; write and run the full Appendix H DDL (dependency order — parents first), with every constraint named.
2. Load the canonical dataset (dependency order again) with multi-row INSERTs.
3. Run the six-count verification and the three illegal-insert demonstrations (duplicate PK, NULL in NOT NULL, dangling FK), capturing each error with its constraint name.
4. Break-and-fix: drop the schema in the wrong order (department first), capture the dependency error, then drop correctly and reload from your script.

Expected outputs: counts 5, 6, 12, 10, 13, 28; three named constraint violations; a wrong-order drop refused, a correct-order drop leaving zero tables, and a one-command reload to the verified state. *Deliverable: lab28_01.sql plus the captured verification output.*

28.2 Implementing primary and foreign keys

Objective. Key design made executable: composite, alternate, and foreign keys with referential actions.

1. In lab28_dev, add a natural-key UNIQUE to course (title), and demonstrate the duplicate-title rejection.
2. Create dept_phone(dept_id, phone) (multivalued-attribute repair) with composite PK and ON DELETE CASCADE; insert two phones for CSE; delete the CSE department; show the phones went with it.
3. Create prerequisite(course_id, prereq_course_id, min_grade) with two self-referencing FKs (ON DELETE CASCADE on the course, RESTRICT on the prereq); load CSE221←CSE215 and CSE251←CSE215; attempt deleting CSE215 (refused — still referenced as a prereq) and deleting CSE221 (cascades its prereq row).
4. Predict each action's effect before running; reconcile any surprise.

Expected outputs: one UNIQUE rejection; phones 2 → 0 after the cascade delete; CSE215 delete refused, CSE221 delete removing exactly its one prereq row. *Deliverable: lab28_02.sql with one-line justifications per referential action.*

28.3 Inserting, updating, and deleting data

Objective. The DML discipline of Chapter 10 under graded pressure: predictions before every mutation.

1. Insert a new department (Physics, 6), an instructor (107, 'Rashed Karim', 6, hired 2026-08-01, 95000), and two students (21600002 'Ayesha Rahman', major 6, 2026; 21600003 'Tanvir Rahman', major 1, 2026) — predicting row counts.
2. INSERT ... SELECT: enroll every 2026-admit student into a new Fall 2026 section of MAT116 (section 14, room MAB-103, capacity 40, instructor 105).
3. An update with verification: raise every instructor hired before 2018 by 5% — predict rows (two: Nazmul, Mahmudul) and verify with RETURNING (PostgreSQL) or a transaction (MySQL).
4. The mistake drill, wrapped safely: run one deliberately unscoped UPDATE student SET total_credits = 0 inside BEGIN/ROLLBACK, capture the affected-row count (12 — every student), then roll back and verify the counts restored.
5. Delete the two new students; predict the cascade; verify.

Expected outputs: counts at each step — 1 department, 1 instructor, 2 students, 1 section inserted; the SELECT-insert enrolls 3 students (the 2026 admits: Zara Hossain and the two new students); the salary update touches exactly 2 rows (Nazmul Chowdhury, Mahmudul Islam); the unscoped update reports 12 and rolls back cleanly; deleting the two new students cascades their section-14 enrollments (2 rows). *Deliverable: lab28_03.sql with every prediction as a comment beside its statement.*

28.4 Basic and advanced SELECT queries

Objective. Chapters 10–11 fluency: filters, patterns, NULLs, ORDER BY, DISTINCT, LIMIT, CASE.

1. Write and predict: students admitted 2022+ with GPA ≥ 3.5 (2 rows — Tanvir, Arif); names containing 'ah' (3); Fall 2026 sections' rooms (3).
2. The NULL set: gpa IS NULL (Zara), COUNT(*) vs COUNT(gpa) (12/11), the NOT IN trap query and its fixed form (0 rows, then 2).
3. Sorts: GPA DESC with the portable NULL-last idiom on both platforms; keyset page 2 of size 3 (Mehjabin, Sumaiya, Tanvir).
4. CASE: the standing report for all 12 students (Zara as 'Not yet computed'); grade points for section 8 (4 rows: 3.3, 4.0, 3.7, 2.3).
5. One free-choice question of your own, stated in English first, predicted, then run.

Expected outputs: as listed, plus your own question's reconciliation. *Deliverable: lab28_04.sql with English → prediction → output for each.*

28.5 Joins, subqueries, and CTEs

Objective. Chapter 12's full toolkit on the canonical schema.

1. The four-table transcript for Arif Mahmud (3 rows — one graded, two in progress).
2. The occupancy LEFT JOIN with capacity and seats-free (13 sections, section 5 visible at 0 enrolled, seats-free = capacity).
3. The fan-out exercise: courses per instructor with and without DISTINCT (Farhana 4→3), with the reconciliation sentence.
4. The honor roll three ways (correlated subquery, derived table, CTE pipeline) — same five students each time.
5. The anti-join: instructors teaching nothing this fall (3 — Nazmul, Sharmin, Mahmudul) and students not enrolled this fall (6).
6. Chapter 3's division, in NOT EXISTS form, both divisors (CSE221-Fall-2026: Arif, Shahriar; all-Fall-2026: empty).

Expected outputs: as listed per task, every count predicted first. *Deliverable: lab28_05.sql.*

28.6 Aggregate and analytical queries

Objective. Grouping, HAVING, windows, pivots — the Chapter 11/13 machinery, unaided.

1. Per-major summaries (4/3/2/2/1 students; Mathematics averaging 2.98 over two) and the cohort study (the 2026 NULL row).
2. HAVING: sections with ≥ 3 enrollments (1, 8, 10, 11) and majors above the university average of 3.48 (CSE 3.66, EEE 3.61 — two).
3. Windows: the per-row department average listing; the grade distribution with running total (7/11/15/19/20); RANK/ROW_NUMBER on the instructor tie (1, 1 → 3).
4. Top-N per group: each department's best student (NULL-handling decision stated).
5. The pivot: enrollments by year × semester (11/9/8 across three columns).

Expected outputs: as listed; every rounded figure reconciled with the chapter texts. *Deliverable: lab28_06.sql.*

28.7 Views, indexes, and constraints

Objective. The derived structures of Chapter 9/13/19, built with reasons.

1. The cse_student view plus a transcript_view (student, course, semester, grade rendered as 'IP'); query both; demonstrate WITH CHECK OPTION refusing an out-of-scope update.
2. Index builds with one-line workload justifications: enrollment(section_id), the partial WHERE grade IS NULL, the expression index on LOWER(full_name) (PostgreSQL form and MySQL 8.0.13 form).
3. The constraint add-on: add a CHECK to a loaded table (grades scale) on both platforms (MySQL 8.0.16+), then VALIDATE-style reasoning: does the existing data pass?
4. Verify each structure exists in the catalog (\d / SHOW CREATE TABLE) — metadata as the source of truth.

Expected outputs: view rows (4 CSE students; the transcript's rendered grades); CHECK OPTION's refusal; catalog listings showing every built object named. *Deliverable: lab28_07.sql plus the justification comment per object.*

28.8 Transactions and rollback exercises

Objective. Chapter 18, produced live: the two-terminal experiments.

1. The TCL cycle: BEGIN, delete the in-progress enrollments, count (20), ROLLBACK, count (28) — both platforms.
2. Savepoints: the Section 18.3 script, predicted at each stage.
3. Two sessions, defaults: the non-repeatable read on PostgreSQL's READ COMMITTED (28 → 29) and the snapshot refusal on MySQL's REPEATABLE READ (28 → 28).
4. The lost-update, produced and cured: two sessions, SELECT-then-UPDATE on the same GPA (conflict observed), then the atomic-update cure.
5. The deadlock clinic: the two-row cycle, both error messages captured (40P01/40001), then the sorted-order fix.
6. SELECT ... FOR UPDATE seat-guarding on a capacity-pinned section (oversell prevented).

Expected outputs: each experiment's numbers as the chapter predicts them — with the divergence of task 3 stated in level names. *Deliverable: lab28_08.sql (both sessions' scripts, labeled A/B) plus the captured outputs.*

28.9 Query-plan analysis

Objective. Chapter 19's method on data big enough to tell the truth.

1. Build the 100,000-row enrollment_history (the Section 19.12 generator, adapted to MySQL with a recursive CTE or a numbers table), ANALYZE it.
2. The three plans: unindexed Seq/ALL, post-index Bitmap/ref, single-row Index — pasted with one sentence each.
3. Leftmost-prefix proof: the (section_id, grade) index against three predicates.
4. Covering and sargability: the A/B pairs of Section 19.12's exercises 4 and 5, with ratios.
5. The write-side ledger: timed bulk inserts at 0, 2, 5 indexes; the verdict paragraph.

Expected outputs: every plan pasted; ratios recorded; the prefix-violating plan showing the full scan; insert times increasing with index count. *Deliverable: lab28_09.sql plus the plan/ratio log.*

28.10 PostgreSQL/MySQL comparison exercises

Objective. Chapter 17's ledger, executed: the same questions, both platforms, differences documented.

1. The dialect transformer: port your Lab 28.04 and 28.06 scripts between platforms using the Chapter 17 tables; log each transform.
2. The behavior defaults: run Lab 28.08 task 3's script on both platforms' defaults; state the isolation-level explanation of each result.
3. The function maps: tenure (AGE vs TIMESTAMPDIFF — 8/6/11/7/10/5 years), honor line (string_agg vs GROUP_CONCAT — identical string), date truncation (date_trunc vs the MySQL idiom).
4. The type map: rebuild the canonical DDL for the other platform (NUMERIC↔DECIMAL and the rest); load and verify the six counts.
5. The parity proof: run Lab 28.06's pivot on both; write the paragraph on every difference (rounding, NULL placement) and normalize one of them deliberately.

Expected outputs: two complete runs; the ledger of transforms; the parity report with every difference classified as spelling, behavior, or bug (the last being zero). *Deliverable: lab28_10_both.sql (two files) plus PARITY.md.*

Chapter Summary

• Ten labs in dependency order: environment and DDL, keys and actions, DML with predictions, SELECT fluency, the join/subquery/CTE toolkit, aggregation and windows, derived structures, live transaction behavior, plan analysis at scale, and the platform comparison.
• Every lab follows the book's discipline: English question, predicted output, executed statement, reconciled result — and mutating work stays in the dev database.
• The deterministic expected outputs anchor every lab to the canonical dataset; dev-only tasks are marked.
• The grading criterion is explanation: a predicted-then-verified result earns full credit where an unexplained correct answer earns half.
• The ten scripts and their captured outputs are the portfolio Chapter 29's case studies and Chapter 30's capstone build on.

Key Terms

Review Questions and Exercises

1. Why does the manual insist on predicted outputs as the grading criterion? Predictions separate understanding from pattern-matching — a run can confirm knowledge or expose luck, and only one of those survives Chapter 30.
2. Which lab task exists purely to be rolled back, and why is it valuable? Lab 3's unscoped update — its 12-row blast radius, safely experienced, is the cheapest way to make Chapter 10's warning permanent.
3. In Lab 2, why is CSE215's delete refused while CSE221's cascades? CSE215 is still referenced as a prereq (RESTRICT holds); CSE221 is referenced only as a course with CASCADE on that edge — the action lives on the referencing FK.
4. Lab 5 asks for the honor roll three ways. What does the repetition train? Equivalence — that correlated, derived-table, and CTE forms are the same algebra (Chapter 3/12), and the choice is readability.
5. Which expected output in Lab 6 is the chapter's NULL lesson, stated as a number? Mathematics' 2.98 over two students — the average of one, because AVG skips Zara.
6. What does Lab 7's catalog check enforce as a habit? Metadata as the source of truth — built objects are verified in the catalog, not assumed from the build's silence.
7. Lab 8's two-session task runs on both platforms' defaults. Name the one-line explanation of the differing results. PostgreSQL READ COMMITTED sees committed changes mid-transaction; MySQL REPEATABLE READ holds the first-read snapshot.
8. Why does Lab 9 refuse the canonical database and build 100,000 rows instead? Plans only prefer indexes when scans cost — the planner's honest answers need scale (Chapter 19's opening note).
9. In Lab 9's covering exercise, what changes in the MySQL Extra column and the PostgreSQL plan node? Using index (MySQL) and Index Only Scan (PostgreSQL) — the same covering verdict in two dialects.
10. Lab 10's parity report has three classes of difference. Define each with one example. Spelling — CONCAT vs ||; behavior — NULL ordering and isolation defaults; bug — none, and the report says so.
11. Which two labs would you rerun after the course as maintenance drills, and why? Lab 8 (concurrency behaviors decay from memory fastest) and Lab 9 (plan reading is a perishable, marketable skill) — the maintenance calendar for the skills themselves.
12. What single artifact do the ten labs produce for the capstone? A verified, versioned portfolio: ten scripts, their outputs, and the parity report — the evidence base Chapter 30's documentation section cites.

Mini-Project

Assemble the lab portfolio, PORTFOLIO.md plus the ten scripts: an index page listing each lab, its objective, the three skills it drills, and your verified-output evidence links; a self-assessment per lab (which tasks ran clean first try, which needed reconciliation — and what each reconciliation taught); the platform parity report from Lab 10 as its capstone section; and the maintenance plan (the two rerun drills of Review 11, scheduled). The portfolio is Chapter 30's Appendix E — your semester's evidence, organized by the person who produced it.