SQL Data Quality Practice Exercises

Finding missing data with IS NULL

NULL represents missing or unknown data — it isn't the same as zero or an empty string. Because NULL doesn't equal anything, not even itself, you can't check for it with = NULL. You have to use IS NULL instead.

-- Orders with no shipping method recorded
SELECT *
FROM online_bookstore_orders
WHERE shipping_method IS NULL;

To check for the opposite — rows where a value is present — use IS NOT NULL.

Replacing NULLs with COALESCE

COALESCE() returns the first non-NULL value from a list of arguments. It's commonly used to substitute a readable default wherever a column might be missing, without changing the underlying data.

-- Show 'Not Graded' instead of a blank letter_grade
SELECT student_name, subject,
       COALESCE(letter_grade, 'Not Graded') AS letter_grade
FROM student_grades;

Any row where letter_grade is NULL will show 'Not Graded' in the result instead — rows that already have a grade are left untouched.

Finding duplicate records

Duplicates show up as more than one row sharing the same key values. GROUP BY the columns that should be unique, then use HAVING COUNT(*) > 1 to surface only the combinations that repeat.

-- Employees with the same first and last name appearing more than once
SELECT first_name, last_name, COUNT(*) AS occurrences
FROM employee_records
GROUP BY first_name, last_name
HAVING COUNT(*) > 1;

HAVING is required here rather than WHERE, since the filter depends on COUNT(*) — an aggregate calculated after grouping.

Checking for invalid values

Not every data quality problem is a missing value — some columns hold values that are technically present but don't make sense, like a negative price or a stock count of zero when it shouldn't be.

-- Products that appear to have invalid stock data
SELECT *
FROM product_inventory
WHERE stock_quantity <= 0;

Common mistakes

Using = NULL instead of IS NULL — column = NULL never evaluates to true in SQL, even when the column really is NULL. Always use IS NULL or IS NOT NULL.

Filtering an aggregate with WHERE — you can't reference COUNT(*) in a WHERE clause, since it hasn't been calculated yet at that point. Use HAVING after the GROUP BY instead.

Assuming DISTINCT finds duplicates — SELECT DISTINCT only removes repeated rows from the output; it doesn't tell you which combinations were duplicated or how many times. For that, use GROUP BY with HAVING COUNT(*) > 1.

Forgetting COALESCE only replaces NULLs — it won't fix a value that's present but wrong (like an empty string or a placeholder like 'N/A') — only a true NULL.