Database Design & Development · Implementation

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

📅 To be scheduled

⏱ ~50–60 minutes · single 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?

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?

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 three clauses, in this order: SELECT names the fields to return, FROM names the table(s) the data comes from, and WHERE states any condition records must meet. 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 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 comes after WHERE and sorts the final results by one or more fields, either ASC (ascending — the default if nothing is stated) or DESC (descending). 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.

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;

⚠️ 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. Clause order is fixed: SELECT, FROM, WHERE, then ORDER BY last — reversing this causes a syntax error.

📝 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?

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.

SELECT className, feePerSession
FROM Class
ORDER BY feePerSession DESC;

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?

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

Model answer

Task Set C — Extension · Beyond the specification

Optional challenge questions. Not required for your exam.

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 · 5 min now you try · 20 min Task Set B · 5–10 min Task Set C / review, if time allows.

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 could open DataGrip and actually run each Task Set B query against a populated version of the database, comparing their own SQL's output row-by-row against the model answer's.

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