DDD10 — SQL: Updating Data, Testing, and Evaluation

Learning intentions

Understand how to add, change, and remove records in a database using SQL.
Understand how to test database changes by running SELECT queries before and after.
Understand how to evaluate a database solution against the three standard criteria.

Success criteria

I can write an INSERT INTO … VALUES statement to add a new row to a table.
I can write an UPDATE … SET … WHERE statement to change values in existing rows.
I can write a DELETE FROM … WHERE statement to remove a row safely.
I can use SELECT before and after a change to verify the database is correct.
I can evaluate a database using the two DDD criteria: fitness for purpose and accuracy of output.

💾

Films database — MySQL

Use the same films database from previous SQL lessons. If you need a fresh copy, download and re-run the file in DataGrip.

↓ Download films.sql

Key vocabulary

INSERT

SQL command that adds one or more new rows to a table.

UPDATE

SQL command that changes the value(s) in one or more existing rows.

DELETE

SQL command that removes rows from a table.

WHERE (in UPDATE/DELETE)

Limits which rows are affected. Without it, every row in the table is changed or deleted.

referential integrity

A rule enforced by the foreign key — you cannot delete a parent row if a child row still references it.

fitness for purpose

DDD evaluation criterion: does the database store the right data and allow the end user to carry out the tasks they need?

accuracy of output

DDD evaluation criterion: does running the query return the correct records, in the correct fields, matching what was expected?

SQL: Updating Data, Testing, and Evaluation

INSERT — adding new rows

The INSERT INTO command adds a new row to a table. You list the table name followed by VALUES and then the data for each column in order, separated by commas. Text values are wrapped in quote marks; numbers are not.

INSERT INTO director VALUES (16, 'Stanley', 'Kubrick', 'American');

The values must match the column order defined in the table. For the director table, that order is director_id, first_name, last_name, nationality. If the table has a foreign key, you must insert the parent row before the child row — in the films database, you must always insert the director before you insert the film that references them.

UPDATE — changing existing rows

The UPDATE command changes data that is already stored in the table. You name the table, use SET to specify which column to change and its new value, and use WHERE to specify which row(s) to change.

UPDATE film
SET rating = 8.5
WHERE film_id = 53;

The WHERE clause is critical. If you leave it out, every row in the table is updated. For example, UPDATE film SET rating = 5.0; with no WHERE would change the rating of all 52 films to 5.0. Always double-check your WHERE condition before running an UPDATE.

You can update more than one column at once by separating them with commas inside the SET clause:

UPDATE film
SET certificate = '15', runtime_mins = 146
WHERE film_id = 53;

DELETE — removing rows

The DELETE FROM command removes rows from a table. Like UPDATE, it uses WHERE to target specific rows. Without WHERE, all rows in the table are deleted.

DELETE FROM film WHERE film_id = 53;

When a foreign key is involved, you must delete the child row first (the row that contains the foreign key) before you delete the parent row. In the films database, film is the child table — it references director. If you try to delete a director who still has films, MySQL will refuse with a foreign key constraint error. The safe order is: delete the film, then delete the director.

Testing changes with SELECT

Whenever you run an INSERT, UPDATE, or DELETE, you should test that the change worked correctly. The standard approach is:

Before: run a SELECT to see the current state of the relevant rows.
Run your INSERT / UPDATE / DELETE.
After: run the same SELECT again and check the result matches what you expected.

For an INSERT, you expect the SELECT to return the new row. For an UPDATE, you expect the changed column to show the new value. For a DELETE, you expect the SELECT to return zero rows. This before-and-after approach is a key part of database testing and is directly examined in the N5 question paper.

Referential integrity in practice

Before deleting a parent row (such as a director), it is good practice to run a SELECT first to check whether any child rows still reference it. This prevents the foreign key constraint error.

-- Check for films by this director before deleting
SELECT * FROM film WHERE director_id = 16;

If the SELECT returns rows, delete those films first. If it returns zero rows, it is safe to delete the director.

Evaluation — the two DDD criteria

After building or testing a database, you may be asked to evaluate it. The N5 specification (Appendix 8) requires you to evaluate a database using exactly two criteria:

Fitness for purpose: Does the database store the right data and allow the end user to carry out the tasks they need? To answer this, describe what the user's requirements are and explain whether the database meets them. For example: "The films database stores title, year, rating, genre, and director, which allows users to search and filter the film collection as required — it is fit for purpose."
Accuracy of output: When a query is run, does it return the correct records, in the correct fields, matching what was expected? To answer this, describe the expected output, run the query, and compare. For example: "The query was expected to return all films rated above 8.0. It returned 4 rows — all with rating > 8.0 and no other rows included — so the output is accurate."

Note: Robustness and efficient use of constructs are SDD (software) criteria — they do not appear in the DDD evaluation spec. Do not use them in a database evaluation question.

In the exam, always name the criterion, then explain it in the context of the specific database described in the question. A vague answer like "it works well" scores no marks.

Worked examples

Example 1 — INSERT a new director and film

Task: Add director Stanley Kubrick (director_id 16, American) and his film The Shining (film_id 53, 1980, certificate '18', 146 minutes, rating 8.4, Horror) to the films database.

Insert the director first — the director table is the parent. The foreign key in film references it, so the director must exist before the film is added.

Insert the film, giving a value for every column in the order they appear in the table: film_id, title, year, certificate, runtime_mins, rating, genre, director_id.

Verify both inserts with SELECT queries.

-- Step 1: insert the director
INSERT INTO director VALUES (16, 'Stanley', 'Kubrick', 'American');

-- Step 2: insert the film
INSERT INTO film VALUES (53, 'The Shining', 1980, '18', 146, 8.4, 'Horror', 16);

-- Step 3: verify
SELECT * FROM film WHERE film_id = 53;

Verified result: the SELECT returns 1 row — The Shining, 1980, '18', 146, 8.4, Horror, director_id 16.

Example 2 — UPDATE with a WHERE condition

Task: A reviewer notes that The Shining should have both the certificate updated to '15' and the runtime corrected to 144 minutes. Update both columns in a single statement and verify.

Run a SELECT first to confirm what is currently stored. This is the "before" snapshot.

Write the UPDATE with a WHERE clause targeting only film_id = 53. List both columns to change, separated by a comma inside the SET clause.

Run the same SELECT again as the "after" snapshot to confirm both values changed.

-- Before snapshot
SELECT film_id, title, certificate, runtime_mins FROM film WHERE film_id = 53;

-- Update two columns at once
UPDATE film
SET certificate = '15', runtime_mins = 144
WHERE film_id = 53;

-- After snapshot
SELECT film_id, title, certificate, runtime_mins FROM film WHERE film_id = 53;

Verified result: the after SELECT shows The Shining with certificate '15' and runtime_mins 144. The before SELECT had '18' and 146.

Example 3 — safe DELETE using referential integrity

Task: Remove The Shining and Kubrick from the database safely. There is a foreign key linking the film to the director, so the order of deletions matters.

Check whether any films reference director_id 16 before deleting the director.

Delete the child row (the film) first. Only then delete the parent row (the director).

Verify both are gone with SELECT queries. Both should return 0 rows.

-- Step 1: check for films referencing this director
SELECT * FROM film WHERE director_id = 16;
-- Returns 1 row, so delete that film first

-- Step 2: delete child row, then parent row
DELETE FROM film WHERE film_id = 53;
DELETE FROM director WHERE director_id = 16;

-- Step 3: verify both gone
SELECT * FROM film WHERE film_id = 53;
SELECT * FROM director WHERE director_id = 16;

Verified result: both SELECT queries return 0 rows. If you delete the director before the film, MySQL returns: Cannot delete or update a parent row: a foreign key constraint fails.

Example 4 — evaluation of the films database

Task: Write a short evaluation of the films database covering the two N5 DDD criteria.

Fitness for purpose: The films database is fit for purpose because it stores all the information an end user needs — title, year, certificate, runtime, rating, genre, and director. Users can search and filter the film collection using SQL queries, which meets the stated requirements.

Accuracy of output: A query was run to find all films with a rating above 8.0. The expected output was a list of high-rated films showing title, year, and rating. The query returned 3 rows, all with rating > 8.0 and the correct fields displayed — no extra rows were included. The output is accurate.

Now you try

A new director needs to be added to the films database: Hayao Miyazaki, director_id 17, Japanese. His film Spirited Away also needs to be added: film_id 54, year 2001, certificate 'PG', runtime 125 minutes, rating 8.6, genre 'Animation'. After adding both, a reviewer asks you to update the rating to 8.7.

Answer the following:

Write the INSERT statement to add Miyazaki to the director table.
Write the INSERT statement to add Spirited Away to the film table.
Write the UPDATE statement to change the film's rating to 8.7. Include the WHERE clause.

INSERT INTO director VALUES (17, 'Hayao', 'Miyazaki', 'Japanese');
INSERT INTO film VALUES (54, 'Spirited Away', 2001, 'PG', 125, 8.6, 'Animation', 17);
UPDATE film
SET rating = 8.7
WHERE film_id = 54;

The WHERE clause must target the specific film. Without it, every film's rating would be set to 8.7.

Common mistakes

Leaving out WHERE in UPDATE or DELETE. Without a WHERE clause, the command affects every row in the table. Always check: "which specific rows do I want to change or remove?" and add a WHERE condition.

Deleting the parent before the child. In the films database, you must delete the film before deleting the director. Attempting to delete a director who still has films causes a foreign key constraint error in MySQL.

Wrong data type formatting. Text values (VARCHAR) must be wrapped in single quotes: 'Horror'. Numbers (INT, DECIMAL) must not have quotes: 8.4, not '8.4'. Putting quotes around a number, or removing quotes from text, causes a MySQL error.

Vague evaluation answers. Writing "the database is fit for purpose because it works" scores no marks. You must name the criterion, then explain it specifically: what user needs does the database meet, or what constraints prevent invalid data?

Exam tip

DDD evaluation questions ask about exactly two criteria: fitness for purpose and accuracy of output. Do not write about robustness or efficient use of constructs — those are SDD criteria and will score no marks in a DDD question. For each criterion use this structure: (1) state the criterion by name, (2) explain what it means, (3) apply it to the specific database in the question. For example: "The query output is accurate because it returned all films with rating > 8.0 — these are the expected records and no incorrect rows were included." Name, meaning, application. A vague one-sentence answer rarely scores full marks.

Teacher notes — Shift+T to hide

Suggested timing: 60 minutes. Warm up 8 min; notes + examples 20 min; Now you try 5 min; task set 25 min (Q1–5 auto, Q6–7 written, Q8–9 DataGrip); wrap-up 2 min.

Key misconceptions to address:

Pupils frequently omit WHERE from UPDATE or DELETE — run a live demo of UPDATE film SET rating = 1.0; (with Laragon in demo mode or on a test database) to show all rows affected. Roll back or reinstate afterwards.
Pupils confuse DELETE (removes rows) with DROP (removes an entire table). Stress the difference explicitly.
Evaluation answers tend to be too vague. Model the two-part structure: (1) name the criterion, (2) apply it to the specific database — "The output is accurate because…". Stress that there are exactly TWO DDD criteria: fitness for purpose and accuracy of output. Robustness and efficient use of constructs are SDD criteria — do not let pupils write these in a DDD question.

Live demo suggestion: Work through the Kubrick/Shining INSERT example (film_id 53, director_id 16) in DataGrip with the class watching. After inserting, attempt to delete the director before the film to show the FK constraint error message live. Then delete in the correct order.

Note on worked example data: The worked examples add director_id 16 (Kubrick) and film_id 53 (The Shining) to the films database. These can be left in as valid additions, or pupils can delete them after the example. The Now you try adds director_id 17 (Miyazaki) and film_id 54 (Spirited Away) — again, valid data that can remain. Practical tasks Q8–Q9 use IDs 99 specifically because they are clearly test records intended to be deleted at the end.

Extension question: Ask pupils to write a query that would update the runtime for all films where runtime_mins is NULL. This requires identifying which rows have NULL (WHERE runtime_mins IS NULL) and choosing a sensible value — there is no single correct answer, which makes it a good discussion point.

SQA command words covered: "describe", "explain", "evaluate", "identify".

SQL: Updating Data, Testing, and Evaluation

Key vocabulary

SQL: Updating Data, Testing, and Evaluation

INSERT — adding new rows

UPDATE — changing existing rows

DELETE — removing rows

Testing changes with SELECT

Referential integrity in practice

Evaluation — the two DDD criteria

Worked examples

Practical Task 1 of 2 — INSERT and UPDATE test records

Practical Task 2 of 2 — DELETE and verify