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.
Success criteria
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.
Warm up — what do you already know?
Answer before the lesson begins — it's fine if you're unsure.
WU1
1. In DDD5, which validation type checks a value against a fixed list, e.g. Monday–Sunday?
WU2
2. Which of these is a genuine aggregate-style question you might want a query to answer?
WU3
3. Which entity would you need to check to find out which instructor teaches a particular class?
Key vocabulary
Query
A request that retrieves and processes specific data from a database — the answer to a real question.
Search criteria
The condition(s) a record must meet to be included in a query's results.
Grouping
Collecting records into sets that share a common value, so a calculation can be applied to each set separately.
Sort order
The order results are arranged in — ascending or descending, by one or more fields.
Calculated field
A value produced by processing existing data (an aggregate or an arithmetic expression), rather than a value stored directly.
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.
Each stage adds precision but no new decisions — the SQL in DDD7 is a direct translation of the template completed here, which is itself a direct translation of the written requirement.
Worked examples
Example 1 — Single-table query with criteria and sort
1
Requirement: "List the class name, day, start time, and fee for every class that runs on a Monday, in order of start time."
2
Fields needed all live in one entity: Class. No calculation is needed — every value is stored directly.
✓
See the completed template below.
Query design — Monday classes
Field(s) and calculation(s)
className, dayOfWeek, startTime, feePerSession — no calculations
Table(s) and query
Class only
Search criteria
dayOfWeek = "Monday"
Grouping
None
Sort order
startTime, ascending
Example 2 — Multi-table query linking Member, Booking, and Class
1
Requirement: "List each member's surname and first name, alongside the class name and booking date, for every booking, most recent first."
2
Fields span three entities: surname/firstName from Member, className from Class, bookingDate from Booking.
✓
No search criteria are stated — every booking is wanted — so that row is genuinely "none". See the template below.
Query design — every booking with member and class details
Field(s) and calculation(s)
Member.surname, Member.firstName, Class.className, Booking.bookingDate — no calculations
Table(s) and query
Member, Booking, Class
Search criteria
None — every booking is included
Grouping
None
Sort order
bookingDate, descending
Example 3 — Aggregate query with grouping
1
Requirement: "For each instructor, show their name and how many classes they teach."
2
"For each instructor" is the signal for grouping — the count must be calculated separately per instructor, not as one overall total.
✓
COUNT(classCode) is the calculation; grouping is by instructor. See the template below.
Query design — number of classes per instructor
Field(s) and calculation(s)
Instructor.firstName, Instructor.surname, COUNT(Class.classCode) — a calculated count
Table(s) and query
Instructor, Class
Search criteria
None
Grouping
Instructor.instructorRef
Sort order
None specified
Now you try
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.
Field(s) and calculation(s)
firstName, surname, dateJoined — no calculations
Table(s) and query
Member only
Search criteria
dateJoined between 01/01/2026 and 31/12/2026
Grouping
None
Sort order
surname, ascending
⚠️ Common mistakes — examiner feedback
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.
📝 Exam tip
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.
Task Set B — Core questions
Work through all questions. Written answers use self-assessment — compare with the model answer.
B1
Name the five rows of the query-design template, in order (comma-separated).
B2
Requirement: "List every class's name and capacity, with no particular order." What should the Sort order row say?
B3
Requirement: "How many members joined the club in total?" Should the Grouping row say "None" or name a field? Answer with just "None" or the field name.
B4
Requirement: "For each class, show how many bookings it has received." Which row should say "Class.classCode"?
B5
Requirement: "List the names of all classes that run on a Wednesday, alphabetically." Complete the five-row query design.
Model answer
B6
Requirement: "Show the average fee per session, across all classes taught by qualified instructors only." What belongs in the Search criteria row?
B7
Requirement: "List each member's full name and how many classes they've booked, from most to fewest bookings." Which single word should the Sort order row's direction be?
B8
Explain how to decide whether a query needs data from more than one table.
Model answer
B9
What is the difference between grouping and sort order?
Task Set C — Extension · Beyond the specification
Optional challenge questions. Not required for your exam.
C1
Could a single query need both search criteria and grouping at the same time? Give an example using the sports club scenario and explain how the two rows would work together.
One possible answer
C2
Could a query's sort order ever need more than one field (a "sort by this, then by that" requirement)? Give an example.
One possible answer
C3
What makes a query design "complete enough" to hand to someone else to turn directly into SQL, without them needing to ask you anything first?
One possible answer
📁 File this in OneNote under: Higher Computing Science → Database Design & Development → DDD6
📌 Teacher notes — Shift+T to hide
Suggested timing: 8 min warm up · 15 min notes (five-row template) · 15 min worked examples · 5 min now you try · 20 min Task Set B · 5–10 min Task Set C / review, if time allows.
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 scope limit: this lesson deliberately writes no SQL at all — DDD7 is where every one of these three worked query designs becomes a real SQL statement pupils can run in DataGrip. Resist the temptation to "just show them the SQL" here; the separation of design from implementation is itself part of what's being taught.
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.