Database Design & Development · Implementation

DDD9 — SQL: INSERT, UPDATE, DELETE

⏱ ~50–60 minutes · single period ⚠️ see teacher notes — content overflow risk

💻 DataGrip · standard SQL

Learning intentions

I can write a SQL INSERT statement to add a new row to a table.
I can write a SQL UPDATE statement to change existing data.
I can write a SQL DELETE statement to remove rows, using WHERE correctly.

Success criteria

I can exemplify all three mutation operations with correct, working syntax.
I can explain why a missing WHERE clause in UPDATE or DELETE is a serious error, not a style choice.
I can decide which of INSERT, UPDATE, or DELETE is appropriate for a given real-world change.

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? Write the two SQL keywords in uppercase with one space between them.

WU2

2. Which SQL clause states the condition(s) records must meet, and appears in SELECT, UPDATE, and DELETE alike?

ORDER BY
ORDER BY sorts results — it doesn't filter which records are affected.
WHERE
Correct — WHERE filters records in SELECT, UPDATE, and DELETE alike.
GROUP BY
GROUP BY categorises records for aggregation — it doesn't filter which are affected.
FROM
FROM names the table — it doesn't filter which records are affected.

WU3

3. Which SQL command changes values in existing rows? Write the one SQL keyword in uppercase.

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 tableName (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. These booking references are labelled as new and are not in the setup data:

INSERT INTO Booking (bookingRef, memberID, classCode, bookingDate, attended)
VALUES
  ('BKG046', 'M014', 'PRT01', '2026-07-20', False),
  ('BKG047', 'M015', 'YOG01', '2026-07-20', False);

UPDATE: consolidating the DDD8 preview and multi-row changes

DDD8 previewed one computed-value UPDATE, SET feePerSession = feePerSession * 1.05, without asking you to run it. This lesson now consolidates the full syntax and safe use. UPDATE changes the value of one or more fields in existing rows that match a WHERE condition:

UPDATE tableName
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 tableName
WHERE condition;

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

The supplied MySQL schema declares foreign keys, so DataGrip refuses to delete a Member who still has Booking rows and reports error 1451. If that Member genuinely must be removed, delete the matching child rows from Booking first, then delete the Member. MySQL does not leave orphaned Booking rows.

Why WHERE is non-negotiable

UPDATE and DELETE without a WHERE clause apply to every single row in the table, not just one. Recognition only — do not run: DELETE FROM Booking; would delete every booking the club has recorded in that database copy, with no confirmation prompt. Always write and check the WHERE clause before running an UPDATE or DELETE statement, never after. The only controlled exception in this lesson is the teacher-gated final practical step on a disposable personal copy.

Using real setup rows: INSERT appends the clearly labelled new M016 training row, UPDATE changes YOG01 from 6.50 to 7.50 while PIL01 stays 6.50, and targeted DELETE removes only BKG045. The no-WHERE panel is recognition-only — do not run it here.

Worked examples

Example 1 — INSERT a new member

A new person joins the club and needs a Member record.

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 ('M016', 'Marcus', 'Reid', '1 Example Street', 'Edinburgh', 'EH1 1AA', '2026-07-11');

Example 2 — UPDATE an existing class's fee

The club raises the Yoga class's fee to £7.50.

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

A member cancels a booking, and the record needs removing entirely.

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);

Practical activity — in DataGrip

⚠️ Your own copy only

INSERT, UPDATE, and DELETE change real data — every pupil must run this activity against their own personal copy of the Craigmillar database, imported fresh from craigmillar-sports-club-setup.sql, not a shared class database. If your copy gets into a state you don't understand, re-import the setup script to reset it and start again — it's disposable by design.

⬇️ Download craigmillar-sports-club-setup.sql (fresh copy)

💻 Now do this for real

On your own copy of the database, in order:

Run SELECT * FROM Instructor; and confirm you have exactly 3 rows before you start.
Insert this canned fictional training row, using the specified unused memberID: INSERT INTO Member (memberID, firstName, surname, address, town, postcode, dateJoined) VALUES ('M016', 'Marcus', 'Reid', '1 Example Street', 'Edinburgh', 'EH1 1AA', '2026-07-11'); Then run SELECT * FROM Member WHERE memberID = 'M016'; and confirm that one new row appears; SELECT COUNT(*) FROM Member; should now return 16.
Update the YOG01 Yoga class's fee to £7.50, then run SELECT feePerSession FROM Class WHERE classCode = 'YOG01'; to confirm the change.
Expected: 7.50
Delete the booking with bookingRef = 'BKG045', then run SELECT * FROM Booking WHERE bookingRef = 'BKG045'; to confirm it returns no rows.
FINAL practical step — only if your teacher gives the go-ahead: on your disposable personal copy, deliberately run DELETE FROM Booking; with no WHERE clause, then run SELECT COUNT(*) FROM Booking;.
Expected: 0 rows remain

Mandatory reset after the final step: re-import craigmillar-sports-club-setup.sql immediately so your next lesson starts with all 20 canonical Booking rows. Do not continue working in the emptied copy.

⚠️ Common mistakes — examiner feedback

Running UPDATE or DELETE with no WHERE clause. This changes or removes every row in the table — always write WHERE before running either statement, never as an afterthought.
Mismatching the column list and VALUES order in INSERT. Values are matched to columns purely by position, not by name — a swapped order silently puts data in the wrong fields.
Forgetting quotes around text and date values. WHERE classCode = YOG01 is a syntax error; it must be WHERE classCode = 'YOG01'.

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

Which SQL command adds a new row to a table? Write the one SQL keyword in uppercase.

What happens if DELETE FROM Booking; is run with no WHERE clause?

Nothing — SQL requires a WHERE clause and will refuse to run
SQL does not require WHERE — the statement runs successfully, which is exactly the danger.
Only the first row in the table is deleted
Without WHERE, every row matches — not just the first.
The table structure itself is deleted
DELETE removes rows of data, not the table structure — that would need a different command entirely.
Every row in the Booking table is deleted
Correct — with no WHERE clause, every row matches and is deleted.

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.50.

INSERT INTO Class (classCode, className, instructorRef, dayOfWeek, startTime, capacity, feePerSession)
VALUES ('SPN04', 'Spin 4', 'INS03', 'Thursday', '18:00', 20, 6.50);

Write a SQL statement to update Member 'M012' so their town is now 'Musselburgh'.

UPDATE Member
SET town = 'Musselburgh'
WHERE memberID = 'M012';

Read and explain this SQL statement: DELETE FROM Booking WHERE attended = False;

Model answer

Write a SQL statement to remove the real booking with bookingRef 'BKG019' from Booking.

DELETE FROM Booking
WHERE bookingRef = 'BKG019';

Which statement correctly adds two new bookings in one INSERT?

INSERT INTO Booking VALUES ('BKG050'); INSERT INTO Booking VALUES ('BKG051');
This is two separate statements, not one INSERT with two rows, and neither supplies full column values.
INSERT INTO Booking (bookingRef, memberID, classCode) VALUES ('BKG050', 'BKG051', 'M010', 'M011', 'PIL01', 'YOG01');
This mixes values from two rows together in one flat list instead of grouping each row's values with parentheses.
INSERT INTO Booking (bookingRef, memberID, classCode) VALUES ('BKG050', 'M010', 'PIL01'), ('BKG051', 'M011', 'YOG01');
Correct — each row's values are grouped in their own parentheses, comma-separated after VALUES.
INSERT INTO Booking (bookingRef, memberID, classCode) AND VALUES ('BKG050', 'M010', 'PIL01')
AND is not valid syntax for connecting a column list to VALUES.

In an INSERT statement, values are matched to columns by their ___, not by their name. Write one lowercase word.

Read and explain this calculated-value update, including which rows it changes: UPDATE Class SET feePerSession = feePerSession * 1.05 WHERE dayOfWeek = 'Monday';

Model answer

Task Set C — Optional extra practice

Challenge applications of required INSERT, UPDATE, DELETE, calculated-value, wildcard, and multiple-condition skills.

Write one UPDATE that adds 1.00 to the existing fee of classes that run on Wednesday and currently cost less than 7.00. Explain which canonical row changes and its exact new fee.

One possible answer

Write one UPDATE using a wildcard to set town to 'Musselburgh' for every Member whose postcode starts with EH11. Explain exactly which canonical members it changes.

One possible answer

Write one single-table DELETE to remove Booking rows that are both unattended and earlier than 2026-01-08. Explain exactly which canonical booking is removed and why the other unattended bookings remain.

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 · 15 min practical activity (each pupil on their own disposable copy — see the warning box before this section) · 20 min Task Set B · 5–10 min Task Set C / review, if time allows. Every pupil needs their own fresh import of craigmillar-sports-club-setup.sql for this lesson specifically — a shared database would let one pupil's DELETE affect everyone else's data. The no-WHERE live run must be the final practical action, followed immediately by the mandatory re-import.

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).

⚠️ Pacing risk (calendar.md §4 / ddd/DDD.md §11) — highest risk in the unit: this lesson is scheduled as a single ~50-60 min period, but teaches three full, separately-examinable statement types (INSERT, UPDATE, DELETE) — judged double-scoped in the 13 Jul 2026 period audit, with no safe content trim available (the spec names all three individually; none can be cut without leaving an examinable gap). DDD8 now mitigates first exposure by previewing the calculated-value UPDATE; consolidate that preview here rather than reteaching the idea from scratch. If any slack opens up elsewhere in the Oct–Dec block, this remains the strongest candidate for a slot swap to a double, rather than attempting to compress its own content.