James Gillespie's High School · Higher Computing Science 2026–27
Completed:
📁 File in OneNote: Higher Computing Science → Database Design & Development → DDD7
SELECT statement to retrieve specific fields and records.WHERE-clause join.LIKE with % and _ wildcards, and sort results with ORDER BY.WHERE.Answer before the lesson begins — it's fine if you're unsure.
dayOfWeek, one space, =, one space, then Monday in double quotes.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'.
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.
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.
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.
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.
SELECT className, dayOfWeek, startTime, feePerSession.FROM Class.WHERE dayOfWeek = 'Monday'.ORDER BY startTime ASC. See the full statement below.SELECT className, dayOfWeek, startTime, feePerSession
FROM Class
WHERE dayOfWeek = 'Monday'
ORDER BY startTime ASC;SELECT Member.surname, Member.firstName, Class.className, Booking.bookingDate.FROM Member, Booking, Class.AND: Member links to Booking via memberID; Class links to Booking via classCode.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;WHERE className LIKE 'Spin%'.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%';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;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
Member, 3 in Instructor, 7 in Class, and 20 in Booking.M001, sorted oldest first.
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.
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.* or ? instead of % and _. Those are MS Access's wildcard symbols, not standard SQL's — DataGrip needs % and _.WHERE dayOfWeek = Monday is a syntax error; it must be WHERE dayOfWeek = 'Monday'.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.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.
SELECT firstName, surname
FROM Instructor
WHERE qualified = True;SELECT className, feePerSession
FROM Class
ORDER BY feePerSession DESC, className ASC;SELECT className, dayOfWeek FROM Class WHERE className LIKE 'Y%' ORDER BY className ASC;SELECT Member.surname, Class.className
FROM Member, Booking, Class
WHERE Member.memberID = Booking.memberID
AND Class.classCode = Booking.classCode;SELECT className FROM Class WHERE dayOfWeek = Monday;?WHERE className LIKE '%class%' match, and how is this different from using % on only one side of the pattern?📁 File this in OneNote under:
Higher Computing Science → Database Design & Development → DDD7
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.