← VCE Spreadsheets, SQL & Visualisation

Testing a query against the data

Predict the rows a query should return from a small sample, run it, then compare the actual result with your prediction. Check row counts, boundary values and JOIN matches.

Part of: VCE Applied Computing - U1O1 - Data analysis — Module 58, VCE Data Analytics - U3O1 - Data Analytics — Module 145, ACARA Digital Technologies Years 7–8 — Structured Data and Queries — Module 42, ACARA Digital Technologies Years 9–10 — Entities, Relationships and Queries — Module 48, QCE Digital Solutions Unit 2 — Prototype Data Solutions — Module 101, WA Computer Science ATAR Year 11 — Data Management — Module 87

Understanding Testing a query against the data in VCE Spreadsheets, SQL & Visualisation

What it does: Each result row should combine an order with its matching customer, so you compare the output with the matches you predicted from the sample data. Checking the row count against your prediction shows the query is wrong. Returning some rows does not prove it is correct. Boundary testing checks the exact value at the edge. With >= 50, the row with 50 must appear, and 49 must not. The boundary value 50 is the one that exposes the difference between > and >=. Values far from the boundary behave the same either way. Testing boundary values shows whether >= and > have been used correctly.

Common mistakes: To check a row count you need COUNT(*), which counts rows. SUM(*) is invalid and adds up values instead of counting rows. The boundary value 50 must be included, so the condition needs >= 50. Using > 50 would leave out a student who scored exactly 50, and the test would show a missing row.

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 a query against the data to a class →

This module's key terms are also in a printable crossword (Modules 55–70) and word search (Modules 55–86).

Take this worksheet further — free with Cloodit

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

Learning

Duo

Which is the best first step when testing a SQL query against a small sample table?

Predict which rows the query should return, then run itRun the query first and assume the result is correct

Matches

WHERE Age > 16 (a test row has Age 16 and should appear in the results)

↓

The boundary value 16 is left out because > excludes it. Use >= 16 so the Age 16 row is included.

Applying

Meanings

Term

Test a query

Meaning

Predict the rows a query should return from a small sample, run it, then compare the actual result with your prediction. Check row counts, boundary values and JOIN matches.

Practice

-- Predicted: 3 rows (mark 80 is included)

Testing

Gaps

SELECT (*) FROM Students;

Check Answer

Spots

1-- Sample has 4 rows

2SELECT SUM(*)

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. Which is the best first step when testing a SQL query against a small sample table?

A. Predict which rows the query should return, then run it ✓

B. Run the query first and assume the result is correct

Testing means comparing the actual result with a prediction you made beforehand. Without a prediction you have nothing to compare against.

2. A sample table has 10 students and 4 are in Year 11. You run SELECT * FROM Students WHERE Year = 11; and get 6 rows. What does this tell you?

A. The query has an error, because the row count does not match the prediction of 4 ✓

B. The query is correct, because it returned some rows

Checking the row count against your prediction shows the query is wrong. Returning some rows does not prove it is correct.

3. Your sample data has scores of 49, 50 and 51. You want everyone who scored 50 or more. Which test confirms the boundary is correct?

A. Check only that the row with 49 is left out

B. Check that the row with 50 is included in the results ✓

Boundary testing checks the exact value at the edge. With >= 50, the row with 50 must appear, and 49 must not.

4. You wrote WHERE Score > 50 but meant 50 or more. Which sample row would reveal the mistake?

A. A row where Score is 90, which is correctly included

B. A row where Score is exactly 50, which is wrongly missing ✓

The boundary value 50 is the one that exposes the difference between > and >=. Values far from the boundary behave the same either way.

5. When testing an INNER JOIN between Orders and Customers, what should you check?

A. Only orders with a matching customer appear in the result ✓

B. Every order appears, even those with no matching customer

An INNER JOIN returns matching rows only. If you predict 5 matches in the sample data, the result should have 5 rows.

Match the Pairs

SELECT COUNT(*) FROM Students; → Counts the rows in the table so you can compare the total with the number of rows you expected

WHERE Mark >= 50 → A condition where a test row with Mark 50 should be included, so you check that the boundary row appears

Check that a JOIN returns only the rows where the foreign key matches a primary key → SELECT * FROM Orders JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Predict which rows a query should return, then run it and compare the actual result with the prediction → SELECT * FROM Students WHERE Year = 11;

WHERE Age > 16 (a test row has Age 16 and should appear in the results) → The boundary value 16 is left out because > excludes it. Use >= 16 so the Age 16 row is included.

Predicted 5 rows, query returned 8 rows, so the query is marked as correct → The actual row count does not match the prediction, so the query fails the test. Investigate the query, such as a missing WHERE condition or a bad JOIN, and fix it.

Key Term

Test a query

Predict the rows a query should return from a small sample, run it, then compare the actual result with your prediction. Check row counts, boundary values and JOIN matches.

Fill in the Blanks

1. SELECT COUNT(*) FROM Students;

Hint: Type the aggregate function that checks the row count to compare with your prediction.

2. SELECT * FROM Students WHERE Mark >= 50;

Hint: Type the comparison operator that includes a row whose mark is exactly on the boundary value.

3. SELECT * FROM Orders INNER JOIN Customers ON Orders.CustID = Customers.CustID;

Hint: Type the join type that returns matching rows only.

4. SELECT * FROM Orders JOIN Customers ON Orders.CustID = Customers.CustID;

Hint: Type the foreign key column that links the Orders rows to the matching Customers rows.

5. SELECT COUNT(*) FROM Students WHERE Mark > 50;

Hint: Type the clause keyword that filters rows, so you can test the boundary value of 50.

Spot the Error

Question 1

A -- Sample has 4 rows

B SELECT SUM(*)

C FROM Students

D WHERE Year = 11;

Line B — To check a row count you need COUNT(*), which counts rows. SUM(*) is invalid and adds up values instead of counting rows.

Question 2

A -- Pass mark is 50 or more

B SELECT Name FROM Results

C WHERE Score > 50;

D -- Predict: 50 is included

Line C — The boundary value 50 must be included, so the condition needs >= 50. Using > 50 would leave out a student who scored exactly 50, and the test would show a missing row.

Question 3

A SELECT Orders.ID, Cust.Name

B FROM Orders

C INNER JOIN Cust

D ON Orders.ID = Cust.ID;

E -- Predict: only matched rows

Line D — The JOIN must match the foreign key Orders.CustID to the primary key Cust.ID. Matching Orders.ID to Cust.ID pairs the wrong rows, so the result will not match your prediction.

Question 4

A SELECT COUNT(*)

B FROM Orders

C INNER JOIN Cust

D ON Orders.CustID == Cust.ID;

Line D — SQL uses a single = to compare values. The double == is not valid SQL, so the JOIN will not run and cannot be checked against the predicted count.

Question 5

A -- Teens are aged 13 to 19

B SELECT Name FROM Members

C WHERE Age BETWEEN 13 AND 20;

Line C — The upper boundary should be 19. Using 20 would wrongly include 20-year-olds, which a boundary test with a 20-year-old in the sample data would reveal.

Student with a class code? Join your class →