← QCE Digital Solutions Unit 2

Testing that stored data changed correctly

Run a SELECT before and after an INSERT, UPDATE or DELETE, then compare both results with the expected data and check that no other rows changed.

Part of: QCE Digital Solutions Unit 2 — Prototype Data Solutions — Module 104

Understanding Testing that stored data changed correctly in QCE Digital Solutions Unit 2

What it does: This 'after' SELECT shows the stored points value, which you compare with the expected result of the UPDATE. Rows other than studentId 1 should be identical to the before results. Any difference is an unintended change. The before result is your baseline. Without it you cannot tell what the DELETE actually removed. Checking the whole table lets you confirm the Red rows are gone and every other row is still there. A correct target row is not enough. The test must also show that no other rows changed. Working out the expected data first means you are testing against a requirement, not just accepting whatever the table shows.

Common mistakes: The WHERE clause must match only the student being tested (studentId = 1). Using yearLevel = 7 changes every Year 7 student, so the before and after SELECTs would show unintended changes to other rows. The 'before' query must run on the same table that the DELETE changes. Counting results tells you nothing about orders, so it should be SELECT COUNT(*) FROM orders;.

Class: _____________________ · Name: _____________________ · Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add Testing that stored data changed correctly to a class →

This module's key terms are also in a printable crossword (Modules 337–409) and word search (Modules 234–409).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning

Duo

You run UPDATE students SET points = 130 WHERE studentId = 1;. What is the best way to test that it worked?

SELECT the row before and after, then compare both results with the expected dataCheck that the database tool did not show an error message

Matches

DELETE FROM students WHERE studentId = 4; SELECT * FROM students WHERE studentId = 4; -- Test passed: 1 row returned

↓

After a DELETE the check query should return no rows. Returning a row means the delete failed, so this test should be marked as failed.

Applying

Meanings

Term

SELECT * FROM students;

Meaning

Run a SELECT before and after an INSERT, UPDATE or DELETE, then compare both results with the expected data and check that no other rows changed.

Practice

SELECT studentId, points FROM students;

Testing

Gaps

* FROM students;

Check Answer

Spots

2UPDATE students SET points=130

3WHERE yearLevel = 7;

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy

Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games

Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print

Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. You run UPDATE students SET points = 130 WHERE studentId = 1;. What is the best way to test that it worked?

A. SELECT the row before and after, then compare both results with the expected data ✓

B. Check that the database tool did not show an error message

No error only means the statement ran. It does not prove the right data was stored. Compare the before and after SELECT results with what you expected.

2. Why do you run a SELECT BEFORE running a DELETE statement?

A. To make the DELETE run faster

B. To record the original data so you can compare it with the table afterwards ✓

The before result is your baseline. Without it you cannot tell what the DELETE actually removed.

3. After an INSERT into students, which check is part of testing it?

A. Assume the row is there because INSERT is a valid command

B. SELECT from the table and confirm the new row appears with the expected values ✓

A valid command can still store wrong values, such as the wrong house or points. Querying the table shows what is really stored.

4. You run UPDATE students SET points = 0; with no WHERE clause. What will the after SELECT show?

A. Every student now has 0 points, which is an unintended change to other rows ✓

B. Only the student you meant to change has 0 points

Without WHERE, UPDATE changes every row. Comparing before and after the whole table reveals unintended changes to other rows.

5. Which before and after check is better when testing DELETE FROM students WHERE house = 'Red';?

A. SELECT * FROM students; before and after, checking that only Red house rows are gone ✓

B. SELECT only one Red student after the DELETE and stop there

Checking the whole table lets you confirm the Red rows are gone and every other row is still there.

Match the Pairs

SELECT * FROM students WHERE studentId = 3; → A 'before' check: view the row you plan to change so you know its original values.

SELECT COUNT(*) FROM students; → Counts the rows so you can check that an INSERT added one row and a DELETE removed one.

After an UPDATE, query the changed row to confirm it now holds the expected value. → SELECT points FROM students WHERE studentId = 1;

To check that an UPDATE did not change other rows, select every row except the one you edited and compare with the before data. → SELECT * FROM students WHERE studentId <> 1;

DELETE FROM students WHERE studentId = 4; SELECT * FROM students WHERE studentId = 4; -- Test passed: 1 row returned → After a DELETE the check query should return no rows. Returning a row means the delete failed, so this test should be marked as failed.

UPDATE students SET points = 0; SELECT * FROM students WHERE studentId = 2; -- Test passed → The UPDATE has no WHERE clause, so every row changed. The test only checked one row and missed the unintended changes to the other rows.

Key Term

SELECT * FROM students;

Run a SELECT before and after an INSERT, UPDATE or DELETE, then compare both results with the expected data and check that no other rows changed.

Fill in the Blanks

1. SELECT * FROM students; UPDATE students SET points = 130 WHERE studentId = 1;

Hint: Type the keyword that reads rows, so you can record the data before the change.

2. UPDATE students SET points = 130 WHERE studentId = 1; SELECT * FROM students WHERE studentId = 1;

Hint: Type the table name you query again after the update to see the new data.

3. DELETE FROM students WHERE studentId = 10; SELECT COUNT(*) FROM students;

Hint: Type the name of the aggregate function that counts rows, so you can compare with the expected total after the delete.

4. UPDATE students SET house = 'Red' WHERE studentId = 3; SELECT * FROM students;

Hint: Type the keyword that limits the update to one row, so no other rows change by mistake.

5. UPDATE students SET points = 130 WHERE studentId = 1; SELECT * FROM students WHERE studentId <> 1;

Hint: Type the operator meaning 'not equal to', so you can check that the other rows were not changed.

Spot the Error

Question 1

A SELECT * FROM students;

B UPDATE students SET points=130

C WHERE yearLevel = 7;

D SELECT * FROM students;

Line C — The WHERE clause must match only the student being tested (studentId = 1). Using yearLevel = 7 changes every Year 7 student, so the before and after SELECTs would show unintended changes to other rows.

Question 2

A SELECT COUNT(*) FROM results;

B DELETE FROM orders

C WHERE orderId = 10;

D SELECT COUNT(*) FROM orders;

Line A — The 'before' query must run on the same table that the DELETE changes. Counting results tells you nothing about orders, so it should be SELECT COUNT(*) FROM orders;

Question 3

A SELECT COUNT(*) FROM products;

B INSERT INTO products VALUES

C (11,'Pen','Stationery',2,50);

D SELECT SUM(price) FROM products;

E -- Expect: 11 rows

Line D — The 'after' query must be the same as the 'before' query so the two results can be compared with the expected 11 rows. It should be SELECT COUNT(*) FROM products;

Question 4

A SELECT COUNT(*) FROM students;

B DROP TABLE students

C WHERE studentId = 5;

D SELECT COUNT(*) FROM students;

Line B — DROP TABLE removes the whole table, not one row, so the after SELECT would fail. To remove one record, use DELETE FROM students followed by the WHERE clause.

Question 5

A SELECT * FROM products;

B UPDATE products price = 5

C WHERE productId = 2;

D SELECT * FROM products;

Line B — An UPDATE needs the SET keyword before the column assignment. It should be UPDATE products SET price = 5 so the change can be checked in the after SELECT.

Student with a class code? Join your class →