Checking data quality with SQL queries

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

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;

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;

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.

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;

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;

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 house IS NULL;		ALists every student record that has no value in the house field, so incomplete records can be found.
2SELECT * FROM results WHERE mark < 0 OR mark > 100;		BThis finds valid prices, not impossible ones. To find inaccurate records the condition should test for out-of-range values, such as WHERE price < 0.
3Count how many records are incomplete because a field has no value		CFinds results with impossible marks that fall outside the valid range of 0 to 100.
4Find duplicate records by grouping on a field and keeping groups that appear more than once		DNULL 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.
5SELECT * FROM students WHERE house = NULL;		ESELECT COUNT(*) FROM students WHERE points IS NULL;
6SELECT * FROM products WHERE price > 0;		FSELECT firstName, COUNT() FROM students GROUP BY firstName HAVING COUNT() > 1;

Definitions

IS NULL

Fill in the Blanks

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

SELECT * FROM students WHERE house _______;

SELECT _____ (*) FROM students WHERE house IS NULL;

SELECT * FROM results WHERE mark < 0 __ mark > 100;

SELECT lastName, COUNT(*) FROM students GROUP BY lastName ______ COUNT(*) > 1;

SELECT * FROM products WHERE stock _ 0;

Spot the Error

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

Question 1

A SELECT * FROM students

B WHERE house = NULL

C ORDER BY lastName;

Question 2

A SELECT COUNT(*)

B FROM products

C WHERE price IS 0;

Question 3

A SELECT * FROM results

B WHERE mark < 0 AND mark > 100;

C ORDER BY studentId;

Question 4

A SELECT firstName, COUNT(*)

B FROM students

C GROUP BY firstName

D WHERE COUNT(*) > 1;

Question 5

A -- how many have no house?

B SELECT COUNT(house)

C FROM students

D WHERE house IS NULL;

Answer Key

Checking data quality with SQL queries

Section A: Quick Questions

B — SELECT * FROM students WHERE house IS NULL;

A — SELECT COUNT(*) FROM products WHERE price IS NULL;

A — The data is incomplete: 3 records are missing a last name.

A — SELECT * FROM results WHERE mark < 0 OR mark > 100;

B — SELECT * FROM products WHERE stock < 0;

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

Section C: Definitions

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.

Section D: Fill in the Blanks

SELECT * FROM students WHERE house IS NULL;

SELECT COUNT
(*) FROM students WHERE house IS NULL;

SELECT * FROM results
WHERE mark < 0 OR mark > 100;

SELECT lastName, COUNT(*)
FROM students GROUP BY lastName
HAVING COUNT(*) > 1;

SELECT * FROM products WHERE stock < 0;

Section E: Spot the Error

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.

C — A price of 0 is a value, not a missing value. To count products with no price recorded, use WHERE price IS NULL.

B — No mark can be below 0 and above 100 at once, so AND returns nothing. Impossible marks are found with OR.

D — WHERE cannot filter on an aggregate like COUNT(*). Use HAVING COUNT(*) > 1 after GROUP BY to find repeated values.

B — COUNT(house) skips NULL values, so it would count only students who do have a house. Use COUNT(*) to count the matching rows.