Testing that stored data changed correctly

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

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

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

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

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

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

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left column		Right column
1SELECT * FROM students WHERE studentId = 3;		AA 'before' check: view the row you plan to change so you know its original values.
2SELECT COUNT(*) FROM students;		BSELECT * FROM students WHERE studentId <> 1;
3After an UPDATE, query the changed row to confirm it now holds the expected value.		CThe UPDATE has no WHERE clause, so every row changed. The test only checked one row and missed the unintended changes to the other rows.
4To check that an UPDATE did not change other rows, select every row except the one you edited and compare with the before data.		DCounts the rows so you can check that an INSERT added one row and a DELETE removed one.
5DELETE FROM students WHERE studentId = 4; SELECT * FROM students WHERE studentId = 4; -- Test passed: 1 row returned		EAfter a DELETE the check query should return no rows. Returning a row means the delete failed, so this test should be marked as failed.
6UPDATE students SET points = 0; SELECT * FROM students WHERE studentId = 2; -- Test passed		FSELECT points FROM students WHERE studentId = 1;

Definitions

SELECT * FROM students;

Fill in the Blanks

Complete each line of code by filling in the missing parts.

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

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

DELETE FROM students WHERE studentId = 10; SELECT _____(*) FROM students;

UPDATE students SET house = 'Red' _____ studentId = 3; SELECT * FROM students;

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

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A SELECT * FROM students;

B UPDATE students SET points=130

C WHERE yearLevel = 7;

D SELECT * FROM students;

Question 2

A SELECT COUNT(*) FROM results;

B DELETE FROM orders

C WHERE orderId = 10;

D 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

Question 4

A SELECT COUNT(*) FROM students;

B DROP TABLE students

C WHERE studentId = 5;

D SELECT COUNT(*) FROM students;

Question 5

A SELECT * FROM products;

B UPDATE products price = 5

C WHERE productId = 2;

D SELECT * FROM products;

Answer Key

Testing that stored data changed correctly

Section A: Quick Questions

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

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

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

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

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

Section B: 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.

Section C: Definitions

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.

Section D: Fill in the Blanks

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

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

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

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

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

Section E: Spot the Error

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.

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;

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;

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.

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.