← QCE Digital Solutions Unit 2

Checking data quality with SQL queries

Tests whether a field has no value. Use WHERE field IS NULL, often with COUNT(*), to find and count incomplete records when checking data quality.

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

Understanding Checking data quality with SQL queries in QCE Digital Solutions Unit 2

What it does: COUNT(*) combined with IS NULL counts the records with a missing points value. GROUP BY collects matching values and HAVING COUNT(*) > 1 keeps only those repeated, which shows duplicates. No mark can be both below 0 and above 100, so AND returns nothing. OR finds marks outside the valid range. A data quality check must target the bad values. The condition is the wrong way round, so it returns correct records instead of impossible ones.

Common mistakes: NULL means no value, so it can never be equal to anything. Use IS NULL, not = NULL, to find students with a missing house. A price of 0 is a value, not a missing value. To count products with no price recorded, use WHERE price IS NULL.

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 Checking data quality with SQL queries 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
Which query finds students who have no house recorded?
SELECT * FROM students WHERE house = NULL;SELECT * FROM students WHERE house IS NULL;
Matches
SELECT * FROM students WHERE house = NULL;
↓
NULL cannot be tested with the = operator. A comparison with NULL is never true, so no rows are returned. It should be WHERE house IS NULL.
Applying
Meanings
Term
IS NULL
Meaning
Tests whether a field has no value. Use WHERE field IS NULL, often with COUNT(*), to find and count incomplete records when checking data quality.
Practice
UPDATE students SET points = NULL WHERE studentId = 4;
Testing
Gaps
SELECT * FROM students WHERE house ;
Check Answer
Spots
1SELECT * FROM students
2WHERE house = NULL

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

The 'Oryx' badge — Desert Navigator, one of Cloodit's 106 collectible badges
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 query finds students who have no house recorded?
A. SELECT * FROM students WHERE house = NULL;
B. SELECT * FROM students WHERE house IS NULL; ✓
NULL means no value, so it can't be compared with =. Use IS NULL to find incomplete records.
2. Which query counts how many products have no price recorded?
A. SELECT COUNT(*) FROM products WHERE price IS NULL; ✓
B. SELECT COUNT(price) FROM products WHERE price = 0;
COUNT(*) with IS NULL counts the incomplete records. A price of 0 is a value, not a missing one.
3. A data quality check finds 3 rows where lastName IS NULL. What does this tell you?
A. The data is incomplete: 3 records are missing a last name. ✓
B. The data is inaccurate: 3 last names are spelled wrongly.
NULL means a value is missing. That is a completeness problem, not an accuracy problem.
4. Which query finds impossible marks in the results table?
A. SELECT * FROM results WHERE mark < 0 OR mark > 100; ✓
B. SELECT * FROM results WHERE mark < 0 AND mark > 100;
No mark can be both below 0 and above 100, so AND returns nothing. OR finds marks outside the valid range.
5. Which query finds products with a negative stock level, an impossible value?
A. SELECT * FROM products WHERE stock IS NULL;
B. SELECT * FROM products WHERE stock < 0; ✓
A negative stock is inaccurate, so test it with a range condition. IS NULL only finds missing values.

Match the Pairs

SELECT * FROM students WHERE house IS NULL; → Lists every student record that has no value in the house field, so incomplete records can be found.
SELECT * FROM results WHERE mark < 0 OR mark > 100; → Finds results with impossible marks that fall outside the valid range of 0 to 100.
Count how many records are incomplete because a field has no value → SELECT COUNT(*) FROM students WHERE points IS NULL;
Find duplicate records by grouping on a field and keeping groups that appear more than once → SELECT firstName, COUNT(*) FROM students GROUP BY firstName HAVING COUNT(*) > 1;
SELECT * FROM students WHERE house = NULL; → NULL cannot be tested with the = operator. A comparison with NULL is never true, so no rows are returned. It should be WHERE house IS NULL.
SELECT * FROM products WHERE price > 0; → This finds valid prices, not impossible ones. To find inaccurate records the condition should test for out-of-range values, such as WHERE price < 0.

Key Term

IS NULL
Tests whether a field has no value. Use WHERE field IS NULL, often with COUNT(*), to find and count incomplete records when checking data quality.

Fill in the Blanks

1. SELECT * FROM students WHERE house IS NULL;
Hint: Type the two-word condition that finds records where the field has no value.
2. SELECT COUNT (*) FROM students WHERE house IS NULL;
Hint: Type the aggregate function that counts how many incomplete records there are.
3. SELECT * FROM results WHERE mark < 0 OR mark > 100;
Hint: Type the Boolean operator that returns a row if either impossible-value condition is true.
4. SELECT lastName, COUNT(*) FROM students GROUP BY lastName HAVING COUNT(*) > 1;
Hint: Type the clause that filters groups, so only repeated values (possible duplicates) are shown.
5. SELECT * FROM products WHERE stock < 0;
Hint: Type the comparison operator that finds impossible negative stock values.

Spot the Error

Question 1
A SELECT * FROM students
B WHERE house = NULL
C ORDER BY lastName;
Line B — NULL means no value, so it can never be equal to anything. Use IS NULL, not = NULL, to find students with a missing house.
Question 2
A SELECT COUNT(*)
B FROM products
C WHERE price IS 0;
Line C — A price of 0 is a value, not a missing value. To count products with no price recorded, use WHERE price IS NULL.
Question 3
A SELECT * FROM results
B WHERE mark < 0 AND mark > 100;
C ORDER BY studentId;
Line B — No mark can be below 0 and above 100 at once, so AND returns nothing. Impossible marks are found with OR.
Question 4
A SELECT firstName, COUNT(*)
B FROM students
C GROUP BY firstName
D WHERE COUNT(*) > 1;
Line D — WHERE cannot filter on an aggregate like COUNT(*). Use HAVING COUNT(*) > 1 after GROUP BY to find repeated values.
Question 5
A -- how many have no house?
B SELECT COUNT(house)
C FROM students
D WHERE house IS NULL;
Line B — COUNT(house) skips NULL values, so it would count only students who do have a house. Use COUNT(*) to count the matching rows.

Student with a class code? Join your class →