SQL query design table
Purpose, inputs, query logic and expected output of significant queries.
What it explains and when it is useful
A query design table describes each significant database query before it is coded: why it is needed, its inputs, the logic (as SQL or structured text) and the expected output, with an example.
When to use it: Solutions where the database does important work: rules, searches, reports, aggregates. Simple single-row lookups do not need one.
Worked example
Illustrative: PracticePal is a fictional project used throughout this guide.
| ID | Purpose (SC) | Inputs | Query logic | Expected output |
|---|---|---|---|---|
| Q1 | Clash check (SC4) | :room, :start, :end | SELECT COUNT(*) FROM booking WHERE room_code = :room AND status IN ('confirmed','checked_in') AND start_time < :end AND end_time > :start | 0 if free. P5 12:30–13:00 with a 12:00–12:30 booking → 0 |
| Q2 | Weekly cap (SC5) | :student, :monday, :next_monday | SELECT COUNT(*) FROM booking WHERE student_id = :student AND status <> 'cancelled' AND start_time >= :monday AND start_time < :next_monday | 0–3; 3 means refuse |
| Q3 | Loud neighbours (SC6) | :room, :start, :end | SELECT b.room_code FROM booking b JOIN neighbour n ON (n.room_a = :room AND n.room_b = b.room_code) OR (n.room_b = :room AND n.room_a = b.room_code) WHERE b.loud = 1 AND b.status IN ('confirmed','checked_in') AND b.start_time < :end AND b.end_time > :start | List of rooms; empty means no clash. P5 → ['P6'] |
| Q4 | Usage report (SC10) | :monday, :next_monday | SELECT r.room_code, COUNT(b.booking_id), SUM(b.status = 'completed') * 30, SUM(b.status = 'no_show') FROM room r LEFT JOIN booking b ON b.room_code = r.room_code AND b.start_time >= :monday AND b.start_time < :next_monday AND b.status <> 'cancelled' GROUP BY r.room_code ORDER BY r.room_code | 6 rows, one per room, including rooms with 0 bookings |
| Q5 | Front of queue (SC7) | :room, :slot | SELECT entry_id, student_id FROM waitlist_entry WHERE room_code = :room AND slot_start = :slot AND state = 'waiting' ORDER BY joined_at LIMIT 1 | One row, or none if nobody is waiting |
Parameters (:room) are always passed separately, never pasted into the SQL string, which prevents SQL injection.
Notation and legend
| Symbol or part | Meaning |
|---|---|
| ID | Referred to in the sequence diagram, code comments and tests |
| Purpose | What the query is for and the success criterion it serves |
| Query logic | SQL, or structured English if you have not fixed the syntax yet |
| Expected output | What correct looks like, ideally with an example |
Design decisions it communicates
A diagram earns marks when you explain the decisions it shows. In PracticePal's documentation these would each be one sentence:
- Overlap uses start < other end AND end > other start, so back-to-back bookings are allowed (test T5).
- Q4 uses a LEFT JOIN so rooms with no bookings still appear with 0.
- Cancelled bookings are excluded from counts in one consistent way.
Common mistakes
- Listing trivial queries (SELECT * FROM room).
- No expected output, so the query cannot be tested.
- Building SQL by string concatenation.
- Queries in the table that do not match the ERD's names.
Checklist for your own version
Ticks are saved in this browser only, so you can use the list while you draft.