DDD6 — Query design: tables, fields, criteria, sort, calculations, grouping
- I can plan a query using SQA's five-row query-design template.
- I can identify which fields, tables, criteria, grouping, and sort order a query needs from a written requirement.
- I can distinguish a query's search criteria from its sort order and its grouping.
- I can exemplify a query design using all five template rows, leaving none blank.
- I can correctly identify when a query needs more than one table.
- I can decide, from a written requirement alone, whether grouping and calculations are needed.
Answer before the lesson begins — it's fine if you're unsure.
Key vocabulary
Planning a query before writing SQL
Why plan a query before writing SQL?
DDD7–9 write real SQL statements that query the sports club database. Before any SQL is written, though, the Higher course requires a query to be designed first — deciding exactly what the query needs to do, in plain terms, using a standard planning template. This mirrors SDD's separation of design from implementation: a query plan can be checked for correctness against a written requirement without needing to know SQL syntax at all, the same way SDD3–4's structure diagrams and pseudocode are checked before any Python is written.
The five-row template
SQA's own query-design template has five rows, and every query design in this course uses exactly this structure, in this order:
- Field(s) and calculation(s) — which attributes should appear in the results, and whether any of them are calculated (an aggregate function or an arithmetic expression) rather than stored directly.
- Table(s) and query — which entity or entities the data comes from. More than one table means the query links records across entities, the same way DDD7's joins will.
- Search criteria — the condition(s) a record must meet to be included at all. Not every query needs criteria — some genuinely want every record.
- Grouping — whether records need to be collected into sets sharing a common value before a calculation is applied to each set. Only needed when the query calculates something per group, not overall.
- Sort order — the field(s) results should be ordered by, and whether ascending or descending. Not every query needs a specific order.
Every row must be filled in — even when the answer is "none"
A complete query design states an answer for all five rows, even where that answer is "none" or "not needed". Leaving a row blank is different from stating it isn't needed — SQA's marking rewards an explicit "no grouping required" over an empty cell, because it shows the design was actually considered rather than skipped.
Deciding whether a query needs more than one table
A query needs more than one table whenever the requirement mentions attributes that live in different entities. "List each booking's member surname and class name" needs Member (for surname), Class (for className), and Booking (to link the two together) — three tables, even though only two attributes beyond the linking keys are actually displayed.
Deciding whether a query needs grouping
Grouping is only needed when a calculation applies per category, not to the whole result set. "What is the average fee across all classes?" needs no grouping — one single average, over every row. "What is the average fee per instructor?" needs grouping by instructor, because the calculation must be repeated separately for each instructor's set of classes. This distinction — one overall answer versus one answer per group — is the test to apply every time.
Worked examples
surname/firstName from Member, className from Class, bookingDate from Booking.COUNT(classCode) is calculated separately for each instructor. Read the completed design below briefly; DDD8 teaches and implements this pattern in full.Requirement: "List the full name and date joined of every member who joined in 2026, sorted alphabetically by surname."
Complete the five-row query design template for this requirement.
- Leaving a row blank instead of writing "none". Every row needs an explicit answer, even when nothing is needed for that row.
- Confusing search criteria with sort order. Criteria decide which records are included; sort order decides what order the included records appear in — they answer different questions.
- Adding grouping when only one overall calculated value is needed. "The average fee across all classes" needs no grouping — grouping is only for a calculation repeated per category.
- Forgetting to list every table a field comes from. If a query displays fields from three entities, all three must appear in the "Table(s)" row, not just the one the requirement mentions first.
When asked to "exemplify a design of a solution to a query", produce the full five-row template with specific, real values in every row — not a written paragraph describing the query. State criteria and calculations precisely enough that someone else could write the SQL directly from your design, without needing to ask a clarifying question.
DDD7 turns the first two worked designs into real SQL; the short aggregate-and-grouping preview returns in DDD8. Download craigmillar-sports-club-setup.sql and import it into DataGrip before the next lesson, so the Craigmillar Community Sports Club database is ready to use.
📁 File this in OneNote under:
Higher Computing Science → Database Design & Development → DDD6
Suggested timing: 6 min warm up · 10 min notes and pipeline · 12 min worked examples 1–2 · 3 min DDD8 preview · 4 min now you try · 18 min Task Set B · 4 min review. Set Task Set C as optional homework.
Key misconception: pupils often conflate "search criteria" and "sort order" because both involve a field name. Model the distinct question each row answers: criteria = "which records are included at all?", sort order = "what order do the included records appear in?".
Live demo suggestion: present a query requirement live and ask pupils to shout out which of the five rows changes as you add a "for each X" phrase to the requirement — a fast way to drill the grouping/no-grouping distinction before Task Set B.
SQA command words covered: "exemplify" (produce the full five-row template with real values — see the exam tip box), "design".
Deliberate preview boundary: the pipeline SVG includes one complete SQL statement as a deliberate, non-taught preview of DDD7. Use it only to show that a finished design translates into implementation; do not teach the SQL clauses here. The first two worked designs carry into DDD7, while the explicitly labelled short third example previews the aggregate-and-grouping work taught in DDD8.
Extension suggestion: pupils who finish Task Set C early could design a query answering a question of their own choosing about the sports club scenario, then swap with a partner to check each other's five-row template for completeness.
Pacing adjustment applied: keep the third example to its three-minute preview slot and avoid expanding its COUNT/grouping syntax; DDD8 provides the full teaching and practice. This preserves the single-period focus on the two core patterns (single-table and multi-table query design) while retaining the required grouping exemplar.