Database Design & Development · Implementation

DDD7 — SQL: SELECT, WHERE, ORDER BY, wildcards

⏱ ~100–110 minutes · double period

💻 DataGrip · standard SQL

Learning intentions

I can write a SQL SELECT statement to retrieve specific fields and records.
I can write a multi-table query using a WHERE-clause join.
I can use LIKE with % and _ wildcards, and sort results with ORDER BY.

Success criteria

I can turn a completed DDD6 query design directly into a working SQL statement.
I can join two or more tables correctly using matching key fields in WHERE.
I can read an unfamiliar SQL statement and explain, in plain English, what it returns.

Warm up — what do you already know?

Answer before the lesson begins — it's fine if you're unsure.

WU1

1. In DDD6's Example 1 query design, what was stated in the "Search criteria" row? Write dayOfWeek, one space, =, one space, then Monday in double quotes.

WU2

2. DDD6's Example 2 query needed data from which three tables?

Member, Instructor, Class
Instructor wasn't needed for that query — Booking was, to link Member and Class.
Member, Booking, Class
Correct — surname/firstName from Member, className from Class, bookingDate from Booking.
Instructor, Class, Booking
Member was needed for surname/firstName, not Instructor.
Member only
Member alone doesn't store className or bookingDate.

WU3

3. What primary key does Class use to link back to Instructor, as a foreign key? Write the camelCase field name only.

Key vocabulary

SELECT

The SQL clause naming which fields should appear in the results.

FROM

The SQL clause naming which table(s) the data comes from.

WHERE

The SQL clause stating the condition(s) records must meet — used for both search criteria and table joins.

Wildcard

A symbol used with LIKE to match part of a text value: % for any number of characters, _ for exactly one.

ORDER BY

The SQL clause that sorts results, ascending (ASC) or descending (DESC).

Join

Combining rows from two or more tables where a matching key value exists in both, written as a WHERE condition.

From query design to SQL

SELECT, FROM, WHERE — the basic shape of a query

Every SQL query that retrieves data starts with the same two-clause core: SELECT names the fields to return, then FROM names the table(s) the data comes from. Add WHERE only when rows must be filtered or tables must be linked. This maps directly onto DDD6's query-design template: SELECT matches the "Field(s) and calculation(s)" row, FROM matches "Table(s) and query", and an optional WHERE matches "Search criteria". Text values in WHERE are written in single quotes, e.g. WHERE dayOfWeek = 'Monday'.

Multi-table joins using WHERE

When a query needs fields from more than one table, every table involved is listed in FROM, separated by commas, and WHERE states how each pair of tables is linked — the foreign key in one table must equal the primary key it refers to in the other. This project's house style writes joins as plain WHERE table1.key = table2.key conditions; SQL's JOIN keyword is not used here, since it doesn't appear anywhere in SQA's own worked examples. Every join condition is combined with AND when more than two tables are linked.

Wildcards: % and _

The LIKE operator matches text against a pattern containing wildcards. % matches zero, one, or many characters — WHERE className LIKE 'Spin%' matches "Spin", "Spin1", or "Spin Cycle". _ matches exactly one character — WHERE classCode LIKE '_2%' matches any class code with "2" as its second character. Wildcards only work with LIKE, never with a plain = comparison.

Sorting results with ORDER BY

ORDER BY sorts the final results by one or more fields, either ASC (ascending — the default if nothing is stated) or DESC (descending). It comes after WHERE when a WHERE clause is present. For a multi-key sort, list the fields in priority order, separated by commas: ORDER BY surname ASC, firstName ASC sorts by surname first, then uses firstName to order rows that share a surname. It maps directly onto DDD6's "Sort order" row. When a query design states "None specified" for sort order, the SQL simply omits ORDER BY altogether.

Running queries in DataGrip

This department uses DataGrip to write and run SQL against the sports club database — DataGrip runs standard SQL, so every form taught in this lesson works exactly as written, with no product-specific translation needed. Run a query with the green "execute" arrow (or Ctrl+Enter/Cmd+Enter), and the results appear as a table beneath the editor pane.

Every clause in a SELECT statement answers exactly one row of DDD6's query-design template — writing SQL is largely a direct, clause-by-clause translation of a completed design.

Worked examples

Example 1 — DDD6's Monday-classes query design, in SQL

Fields → SELECT className, dayOfWeek, startTime, feePerSession.

Table → FROM Class.

Criteria → WHERE dayOfWeek = 'Monday'.

✓

Sort → ORDER BY startTime ASC. See the full statement below.

SELECT className, dayOfWeek, startTime, feePerSession
FROM Class
WHERE dayOfWeek = 'Monday'
ORDER BY startTime ASC;

Example 2 — DDD6's multi-table booking query, in SQL

Fields from three entities → SELECT Member.surname, Member.firstName, Class.className, Booking.bookingDate.

All three tables → FROM Member, Booking, Class.

Two join conditions, combined with AND: Member links to Booking via memberID; Class links to Booking via classCode.

✓

Sort by booking date, most recent first. See the full statement below.

SELECT Member.surname, Member.firstName, Class.className, Booking.bookingDate
FROM Member, Booking, Class
WHERE Member.memberID = Booking.memberID
AND Class.classCode = Booking.classCode
ORDER BY Booking.bookingDate DESC;

Example 3 — Wildcards with LIKE

Find every class whose name starts with "Spin", regardless of what follows: WHERE className LIKE 'Spin%'.

Find every member whose postcode is exactly 7 characters, with "EH8" as the first three: WHERE postcode LIKE 'EH8____' (three literal characters, then four underscores for exactly four more).

✓

% and _ can be combined in the same pattern, and used anywhere within it, not only at the end.

SELECT className
FROM Class
WHERE className LIKE 'Spin%';

Now you try

Turn DDD6's "Now you try" query design into a real SQL statement: list the full name and date joined of every member who joined in 2026, sorted alphabetically by surname.

SELECT firstName, surname, dateJoined
FROM Member
WHERE dateJoined >= '2026-01-01' AND dateJoined <= '2026-12-31'
ORDER BY surname ASC;

Practical activity — in DataGrip

💻 Now do this for real

Import craigmillar-sports-club-setup.sql into DataGrip before starting — it builds the four Craigmillar Community Sports Club tables and loads them with real data. Every example above has already been run against this exact database, so your results should match.

⬇️ Download craigmillar-sports-club-setup.sql

Run the setup script, then check you can see 15 rows in Member, 3 in Instructor, 7 in Class, and 20 in Booking.
Write and run a query listing every class on a Tuesday or Thursday, sorted by fee, highest first.
Expected: 2 rows — Spin1 (£8.00), Spin2 (£8.00)
Write and run a query listing the first name, surname, and postcode of every member whose postcode starts with "EH11".
Expected: 3 rows — Dickson, Scott, Coulter
Write and run a join query listing the booking date and class name for every booking made by member M001, sorted oldest first.
Expected: 5 rows, from 2026-01-05 (Pilates Beginners) to 2026-01-13 (Zumba)

If your row count or results don't match the expected result, re-read your WHERE clause and any join conditions before asking for help — this is exactly the kind of self-checking the exam-tip box above describes.

⚠️ Common mistakes — examiner feedback

Forgetting a join condition in a multi-table query. Without a matching WHERE table1.key = table2.key for every pair of tables in FROM, the query returns every possible combination of rows (a "cross join"), not the sensible linked result.
Using * or ? instead of % and _. Those are MS Access's wildcard symbols, not standard SQL's — DataGrip needs % and _.
Forgetting quotes around text values. WHERE dayOfWeek = Monday is a syntax error; it must be WHERE dayOfWeek = 'Monday'.
Placing ORDER BY before WHERE. When a query needs both clauses, the fixed order is SELECT, FROM, WHERE, then ORDER BY last — reversing them causes a syntax error. A query with no filtering or joins can omit WHERE.

📝 Exam tip

The Higher course also requires you to read and explain SQL you're given, not just write it. When asked to explain a statement, describe what it returns in plain English — which fields, from which table(s), under what condition, in what order — rather than simply re-reading the SQL keywords back.

Task Set B — Core questions

Work through all questions. Written and code answers use self-assessment — compare with the model answer.

Which SQL clause names the table(s) a query's data comes from? Write the one SQL keyword in uppercase.

Which wildcard matches exactly one character?

%
% matches zero, one, or many characters — not exactly one.
*
* is MS Access's wildcard, not standard SQL's.
_
Correct — the underscore matches exactly one character.
?
? is MS Access's single-character wildcard, not standard SQL's.

Write a SQL statement to list every instructor's firstName and surname where qualified is True.

SELECT firstName, surname
FROM Instructor
WHERE qualified = True;

Write a SQL statement to list every class's name and fee, sorted from highest fee to lowest, then alphabetically by className when fees are equal.

SELECT className, feePerSession
FROM Class
ORDER BY feePerSession DESC, className ASC;

Read and explain this SQL statement, in plain English: SELECT className, dayOfWeek FROM Class WHERE className LIKE 'Y%' ORDER BY className ASC;

Model answer

Write a SQL statement to list, for every booking, the member's surname and the class name, joining Member, Booking, and Class correctly.

SELECT Member.surname, Class.className
FROM Member, Booking, Class
WHERE Member.memberID = Booking.memberID
AND Class.classCode = Booking.classCode;

What is wrong with this statement: SELECT className FROM Class WHERE dayOfWeek = Monday;?

'Monday' needs single quotes around it, since it's a text value
Correct — text values must be quoted; without quotes, DataGrip treats Monday as an unknown field or object name and raises an error.
SELECT should come after FROM
SELECT correctly comes first — clause order is fine here.
Class should be in quotes
Table names aren't quoted like text values are.
Nothing is wrong
This statement would cause a syntax or reference error without quotes around Monday.

If a multi-table query has no WHERE join condition linking two of its tables at all, what does it return instead of the expected linked result? Write the two-word term in lowercase.

Explain how DDD6's five-row query design template maps onto SQL's SELECT/FROM/WHERE/ORDER BY clauses.

Model answer

Task Set C — Optional extra practice

C1 and C3 deepen required wildcard and multi-key ORDER BY skills; C2 is an optional extension.

What would WHERE className LIKE '%class%' match, and how is this different from using % on only one side of the pattern?

One possible answer

If a query needed data from four tables instead of three, how many join conditions would the WHERE clause need, and how would they be combined?

One possible answer

DDD6's Task Set C asked whether a query's sort order could use more than one field. How would sorting members by surname, then by firstName within each surname, actually be written in SQL?

One possible answer

📁 File this in OneNote under:
Higher Computing Science → Database Design & Development → DDD7

📌 Teacher notes — Shift+T to hide

Suggested timing: 8 min warm up · 15 min notes (SELECT/FROM/WHERE, joins, wildcards, ORDER BY) · 15 min worked examples, ideally live in DataGrip · 15 min practical activity (real queries, real DataGrip, real results) · 20 min Task Set B · 5–10 min Task Set C / review, if time allows. Practical activity requires craigmillar-sports-club-setup.sql imported to pupil machines/accounts in advance — flag to pupils at the end of DDD6 so it's ready.

Key misconception: pupils very commonly forget a join condition entirely when a query spans three tables, producing a huge, meaningless cross-joined result. Live-demonstrating a cross join (by deliberately omitting one AND clause) and showing the inflated row count is a strong way to make the mistake memorable.

Tooling note: this lesson assumes DataGrip, confirmed by the user (11 Jul 2026) as the department's actual tool — standard ANSI SQL throughout, no MS Access-specific wildcard forms taught or referenced.

SQA command words covered: "describe" and "exemplify" (writing SQL), plus "read and explain code" (B5 and the exam tip box) — the Higher spec explicitly requires both directions, not just writing.

Extension suggestion: pupils who finish Task Set C early can extend the practical activity — pick any Task Set B question and actually run it in DataGrip against the real database rather than just typing the SQL into the textarea, then compare row-by-row against the model answer.

DDD7 — SQL: SELECT, WHERE, ORDER BY, Wildcards

DDD7 — SQL: SELECT, WHERE, ORDER BY, wildcards

Key vocabulary

From query design to SQL

SELECT, FROM, WHERE — the basic shape of a query

Multi-table joins using WHERE

Wildcards: % and _

Sorting results with ORDER BY

Running queries in DataGrip

Worked examples

Practical activity — in DataGrip