DDD9 — SQL: INSERT, UPDATE, DELETE

James Gillespie's High School · Higher Computing Science 2026–27

Completed:

📁 File in OneNote: Higher Computing Science → Database Design & Development → DDD9

Database Design & Development · Lesson 9 of 11
Database Design & Development · Implementation

DDD9 — SQL: INSERT, UPDATE, DELETE

📅 To be scheduled
⏱ ~50–60 minutes · single period
💻 DataGrip · standard SQL
Learning intentions
Success criteria
Warm up — what do you already know?

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

WU1
1. In DDD8, which clause is required whenever a query mixes an aggregate field with a non-aggregate field?
WU2
2. Which SQL clause states the condition(s) records must meet, and appears in SELECT, UPDATE, and DELETE alike?
WU3
3. Recap from DDD4: what's the N5 term for the rule that a foreign key value must always exist as a primary key value in its own table?

Key vocabulary

INSERT
The SQL command that adds one or more new rows to a table.
UPDATE
The SQL command that changes the value of one or more fields in existing rows.
DELETE
The SQL command that removes one or more rows from a table entirely.
VALUES
The clause in INSERT that supplies the actual data for the new row, in the same order as the column list.
Mutation
Any SQL operation that changes the data stored in a database — INSERT, UPDATE, and DELETE, as opposed to SELECT, which only retrieves.

Changing the data, not just retrieving it

INSERT: adding new rows

INSERT adds a new row to a table. Its basic form names the table, lists the columns being filled, and supplies matching values with VALUES:

INSERT INTO table (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

If every column in the table is being given a value, the column list can be left out entirely — but the values must then be supplied in exactly the same order the columns were defined in the table. Naming the columns explicitly is safer and clearer, and is the form used throughout this lesson.

Adding more than one row at once

A single INSERT statement can add several rows by listing multiple comma-separated value sets after VALUES:

INSERT INTO Instructor (instructorRef, firstName, surname, qualified)
VALUES
  ('INS05', 'Priya', 'Nair', True),
  ('INS06', 'Tom', 'Baxter', False);

UPDATE: recap and multi-row changes

DDD8 already used UPDATE's syntax indirectly in its worked examples — this lesson makes it explicit. UPDATE changes the value of one or more fields in existing rows that match a WHERE condition:

UPDATE table
SET column1 = value1, column2 = value2, ...
WHERE condition;

An UPDATE statement isn't limited to a single row — every row matching the WHERE condition is changed in one statement, which is exactly why the condition has to be precise.

DELETE: removing rows

DELETE removes entire rows from a table, using WHERE to select which ones:

DELETE FROM table
WHERE condition;

Unlike UPDATE, DELETE doesn't specify individual columns — it removes the whole row, every field at once.

Why WHERE is non-negotiable

UPDATE and DELETE without a WHERE clause apply to every single row in the table, not just one. Running DELETE FROM Booking; with no WHERE deletes every booking the club has ever recorded, in one statement, with no confirmation prompt and no way to select the deleted rows back afterwards. This is the one part of SQL in this course with genuinely irreversible, real-data consequences — always write and check the WHERE clause before running an UPDATE or DELETE statement, never after.

Worked examples

Example 1 — INSERT a new member
1
A new person joins the club and needs a Member record.
2
Name every column, then supply matching values in the same order — text values in single quotes.
✓
See the full statement below.
INSERT INTO Member (memberID, firstName, surname, address, town, postcode, dateJoined)
VALUES ('M045', 'Aisha', 'Khan', '12 Marchmont Road', 'Edinburgh', 'EH9 1HA', '2026-07-11');
Example 2 — UPDATE an existing class's fee
1
The club raises the Yoga class's fee to £7.50.
2
SET the field to its new value, then use WHERE to target only that one class.
✓
Without the WHERE clause, every class's fee would change to £7.50. See the statement below.
UPDATE Class
SET feePerSession = 7.50
WHERE classCode = 'YOG01';
Example 3 — DELETE a cancelled booking
1
A member cancels a booking, and the record needs removing entirely.
2
Target the exact record using its primary key in WHERE — the safest possible criterion, since it can only ever match one row.
✓
See the statement below.
DELETE FROM Booking
WHERE bookingRef = 'BKG045';
Now you try

A new instructor, Marcus Reid, joins the club — instructorRef 'INS04', qualified = True. Write the SQL to add his record.

INSERT INTO Instructor (instructorRef, firstName, surname, qualified)
VALUES ('INS04', 'Marcus', 'Reid', True);
⚠️ Common mistakes — examiner feedback
📝 Exam tip

When asked to describe, exemplify, and use INSERT, UPDATE, or DELETE, write the complete, working statement — full column list for INSERT, a correctly targeted WHERE clause for UPDATE and DELETE — rather than a fragment. An UPDATE or DELETE answer with no WHERE clause at all should be treated as incomplete, even if every other part is correct, since it changes the entire table rather than the intended record.

Task Set B — Core questions
Work through all questions. Written and code answers use self-assessment — compare with the model answer.
B1
Which SQL command adds a new row to a table?
B2
What happens if DELETE FROM Booking; is run with no WHERE clause?
Write a SQL statement to add a new class: classCode 'SPN04', className 'Spin 4', instructorRef 'INS03', dayOfWeek 'Thursday', startTime '18:00', capacity 20, feePerSession 6.00.
INSERT INTO Class (classCode, className, instructorRef, dayOfWeek, startTime, capacity, feePerSession)
VALUES ('SPN04', 'Spin 4', 'INS03', 'Thursday', '18:00', 20, 6.00);
Write a SQL statement to update Member 'M012' so their town is now 'Musselburgh'.
UPDATE Member
SET town = 'Musselburgh'
WHERE memberID = 'M012';
B5
Read and explain this SQL statement: DELETE FROM Booking WHERE attended = False;
Model answer
Write a SQL statement to remove the instructor with instructorRef 'INS06' from Instructor.
DELETE FROM Instructor
WHERE instructorRef = 'INS06';
B7
Which statement correctly adds two new bookings in one INSERT?
B8
In an INSERT statement, values are matched to columns by their ___ (one word), not by their name.
B9
Explain what would go wrong if a Member record were deleted while Booking records still referenced that member's memberID, and what would need to happen first to avoid it.
Model answer
Task Set C — Extension · Beyond the specification
Optional challenge questions. Not required for your exam.
C1
Research or reason about what a "cascade delete" is, and how it would change the two-statement process discussed in B9.
One possible answer
C2
Could an UPDATE statement cause the same referential integrity problem as a DELETE? Give a specific example using the sports club scenario.
One possible answer
C3
DDD7 showed multi-table SELECT queries joining tables in FROM. Could DELETE work across more than one table in the same way? Research or reason about how this would work, and what it would and wouldn't delete.
One possible answer

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

📌 Teacher notes — Shift+T to hide

Suggested timing: 8 min warm up · 15 min notes (INSERT/UPDATE/DELETE, why WHERE matters) · 15 min worked examples, ideally live in DataGrip on a disposable copy of the database · 5 min now you try · 20 min Task Set B · 5–10 min Task Set C / review, if time allows.

Sourcing note: this lesson's INSERT and DELETE syntax comes from the Heriot-Watt Scholar study guide (confirmed 11 Jul 2026) — neither the SQA spec's own Appendix 11 nor Chris Meechan's materials (slides or EOTT answer keys, all directly checked) demonstrate INSERT or DELETE syntax at all, despite the spec naming both as separately examinable. UPDATE is independently confirmed across all three sources. See ddd/DDD.md §3, §5, and §9 for the full cross-check.

Live demo strongly recommended, with a safety net: this is the one lesson where a live mistake genuinely damages the class's shared data. Demonstrate on a disposable copy of the database (or take a backup first), and consider deliberately running an UPDATE or DELETE with no WHERE clause live, on the disposable copy, so pupils see the consequence rather than just being told about it.

Quote-style note: both source materials for INSERT/DELETE (Scholar guide, Chris's materials) use double-quoted string literals, matching MS Access convention. This lesson deliberately uses single quotes throughout instead, staying consistent with DDD6–8's DataGrip/ANSI-SQL convention — not an inconsistency with the sources, a deliberate choice.

SQA command words covered: "describe", "exemplify", "use" (all three operations with working syntax), "read and explain code" (B5).

File in OneNote: Higher Computing Science → Database Design & Development → DDD9 · James Gillespie's High School · Higher Computing Science 2026–27