Testing a query against the data

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

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

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

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

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

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

Match the Pairs

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

Left column		Right column
1SELECT COUNT(*) FROM Students;		AThe boundary value 16 is left out because > excludes it. Use >= 16 so the Age 16 row is included.
2WHERE Mark >= 50		BA condition where a test row with Mark 50 should be included, so you check that the boundary row appears
3Check that a JOIN returns only the rows where the foreign key matches a primary key		CCounts the rows in the table so you can compare the total with the number of rows you expected
4Predict which rows a query should return, then run it and compare the actual result with the prediction		DThe 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.
5WHERE Age > 16 (a test row has Age 16 and should appear in the results)		ESELECT * FROM Students WHERE Year = 11;
6Predicted 5 rows, query returned 8 rows, so the query is marked as correct		FSELECT * FROM Orders JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Definitions

Test a query

Fill in the Blanks

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

SELECT _____(*) FROM Students;

SELECT * FROM Students WHERE Mark __ 50;

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

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

SELECT COUNT(*) FROM Students _____ Mark > 50;

Spot the Error

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

Question 1

A -- Sample has 4 rows

B SELECT SUM(*)

C FROM Students

D WHERE Year = 11;

Question 2

A -- Pass mark is 50 or more

B SELECT Name FROM Results

C WHERE Score > 50;

D -- Predict: 50 is included

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

Question 4

A SELECT COUNT(*)

B FROM Orders

C INNER JOIN Cust

D ON Orders.CustID == Cust.ID;

Question 5

A -- Teens are aged 13 to 19

B SELECT Name FROM Members

C WHERE Age BETWEEN 13 AND 20;

Answer Key

Testing a query against the data

Section A: Quick Questions

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

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

B — Check that the row with 50 is included in the results

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

A — Only orders with a matching customer appear in the result

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

Section C: Definitions

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.

Section D: Fill in the Blanks

SELECT COUNT(*) FROM Students;

SELECT * FROM Students WHERE Mark >= 50;

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

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

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

Section E: Spot the Error

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

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.

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.

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.

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.