Appendices

Appendix K. SQL Practice Problems and Solutions

Fifteen problems against the canonical university database (Appendix H), rising difficulty, each with its solution and — where deterministic — the expected output. Predict before running; the practice is the prediction.

Set 1 — Single table (Chapters 10–11)

K1. Students admitted in 2022 or later whose GPA is at least 3.5, ordered by GPA descending. Solution: SELECT full_name, admission_year, gpa FROM student WHERE admission_year >= 2022 AND gpa >= 3.5 ORDER BY gpa DESC; Output: two rows — Arif Mahmud (2023, 3.90), then Tanvir Alam (2022, 3.55).

K2. The number of students, the number with a GPA, and the average GPA rounded to two places. Solution: SELECT COUNT(*) AS students, COUNT(gpa) AS with_gpa, ROUND(AVG(gpa), 2) AS avg_gpa FROM student; Output: 12, 11, 3.48.

K3. Each department's student count and average GPA, largest count first. Solution: SELECT major_dept_id, COUNT(*) AS students, ROUND(AVG(gpa),2) AS avg_gpa FROM student GROUP BY major_dept_id ORDER BY students DESC, major_dept_id; Output: 1 (4, 3.66), 2 (3, 3.61), 3 (2, 3.10), 4 (2, 2.98), 5 (1, 3.60).

Set 2 — Joins and subqueries (Chapters 12, 3)

K4. Every section of Database Systems (CSE221) with its instructor, room, and enrollment count — including sections with zero enrollments. Solution: LEFT JOIN course_section to enrollment, filter by course_id. Output: section 3 (Farhana Rahman, SAC-401, 2), section 12 (Farhana Rahman, SAC-401, 2) — CSE221 has no empty section in the data; adjust the problem to CSE321 to see the zero row (section 5, 0).

K5. Students enrolled in both section 11 and section 12 (both in progress). Solution: intersect the two enrollment sets (or a self-join). Output: Shahriar Islam (21500002).

K6. The division question: students enrolled in every section taught by instructor 101 (sections 1, 4, 13). Solution: double NOT EXISTS (Chapter 3.9/12.8). Output: the empty set — no student is in all three.

K7. Each student's total grade points (sum of grade_points over graded enrollments, 0 for none), with the student's name, highest total first. Solution: LEFT JOIN enrollment, filter graded, GROUP BY, with the grade-point mapping (Chapter 11's CASE or Chapter 22's function). Output: Nusrat 11.0 (4.0+3.7+3.3), Rakib 10.3 (3.3+3.0+4.0), Sadia 7.7 (4.0+3.7), Tanvir 10.3 (3.7+3.3+4.0), Mehjabin 7.0 (4.0+3.7+3.0), Farhan 6.3 (2.3+4.0), Imran 3.0, Sumaiya 3.0, Nabil 3.3, Arif 4.0, Shahriar 0, Zara 0.

Set 3 — Aggregates, windows, sets (Chapters 11, 13)

K8. The grade distribution with each grade's percentage of the 20 graded enrollments. Solution: GROUP BY grade with 100.0 * COUNT(*) / 20, or the window form 100.0 * COUNT(*) / SUM(COUNT(*)) OVER (). Output: A 35.0%, A- 20.0%, B+ 20.0%, B 20.0%, C+ 5.0%.

K9. A running total of enrollments by section (chronological by year, semester, section). Solution: counts per section joined to course_section, SUM(COUNT(*)) OVER (ORDER BY ...). Output: 28 rows; the total reaches 28 (section 5 contributes 0).

K10. The top student by GPA per admission cohort (year). Solution: ROW_NUMBER() OVER (PARTITION BY admission_year ORDER BY gpa DESC NULLS LAST), filter rn = 1. Output: 2021 Sadia Afrin (3.88), 2022 Mehjabin Chowdhury (3.70), 2023 Arif Mahmud (3.90), 2025 Shahriar Islam (3.25), 2026 Zara Hossain (NULL — state the NULL-handling choice).

K11. Enrollments per year pivoted by semester (Fall / Spring / Summer columns). Solution: the conditional-aggregation pivot (Chapter 13.7). Output: 2024 (11, 0, 0), 2025 (0, 9, 0), 2026 (8, 0, 0).

Set 4 — Design and DDL (Chapters 9, 6, 22)

K12. Write the DDL for waitlist(student_id, section_id, position, entered_at) — one active wait per student per section, positions unique within a section, and a defensible ON DELETE choice per foreign key. Solution: PK (student_id, section_id); UNIQUE (section_id, position); FK student CASCADE (a waitlist entry dies with its student), FK section CASCADE (the wait dies with the section) — ownership lines (Chapter 6.7); position SMALLINT CHECK (position >= 1).

K13. A stored procedure post_grade(student, section, grade) that refuses re-grading (a posted grade may only change through the audit path) — with the audit trigger of Chapter 22.5 attached. Solution: SELECT ... FOR UPDATE the enrollment; if grade IS NOT NULL, SIGNAL/RAISE 'already graded'; else UPDATE (the audit trigger records NULL → grade). Test: posting Arif's section-12 NULL grade to 'A' succeeds and audits; re-posting fails with the custom signal.

K14. Why does WHERE gpa <> NULL return no rows, and give both correct forms? Solution: comparison with NULL is UNKNOWN, and WHERE keeps only TRUE; correct: WHERE gpa IS NOT NULL or WHERE gpa IS DISTINCT FROM NULL (Chapter 10.8).

Set 5 — Optimization (Chapter 19)

K15. On the 100,000-row enrollment_history (Chapter 19.12), make "students who enrolled in section 8 in each month of 2025" index-served; show the before and after plans. Solution: a composite index on (section_id, recorded_at) — equality left, range right; the plan moves from Seq/ALL to Bitmap/Range on (section_id) with a condition on recorded_at; estimates match after ANALYZE. The write-side note: the index earns its keep only if this query is hot — the Chapter 19 judgment.