Stop 05 · SQL & Databases for QATopics · 4/8

Data Validation Techniques

In short

Bad data sneaks in through imports, old bugs and missing checks. Validation queries sweep a table for empty fields, impossible values and broken formats.

Confirming that data meets expected format, range, and consistency rules — checking for null values where they shouldn't exist, correct data types, and referential integrity between related tables.

The purple blob holds a magnifying glass over a stack of blank marksheets on a desk.

Think of it like this

The board checking marksheets

Before results go out, the board checks every marksheet: marks between 0 and 100, a roll number in the right format, no blank names. One impossible 112 out of 100 means something went wrong upstream.

Watch it happen

Sweeping the users table

riya@test.com, 21

NULL, 34

amit@test, 19

neha@test.com, 250

sam@test.com, 28

1/5Five user rows: email and age. The app should never allow bad values, but let's check.

The big ideas

What you need to know

Completeness

Required columns must never be NULL or empty.

Range and type

Numbers within allowed limits, dates not in the future when they shouldn't be, prices not negative.

Format

Emails, phone numbers, PIN codes and IDs follow the expected pattern.

Consistency

Related values agree, like an order total matching the sum of its items.

See it in code

Walk through the code

sql

1-- Required fields that are missing2SELECT id FROM users WHERE email IS NULL OR email = '';3 4-- Values outside the allowed range5SELECT id, age FROM users WHERE age < 0 OR age > 120;6 7-- Badly formatted emails (a rough check)8SELECT id, email FROM users WHERE email NOT LIKE '%_@_%._%';

1/3Completeness: NULL and empty string are different, so check both.

Each query should return zero rows. Anything returned is a data bug to investigate.

Save yourself the pain

Common mistakes

Oops

Only validating data you created during your test.

Do this instead

Sweep the whole table. Old imports and past bugs leave bad rows that break new features.

Oops

Treating NULL and empty string as the same.

Do this instead

Check both. email IS NULL and email = '' catch different problems.

Your mission

Write a validation sweep

~25 min

Quick check

Which query finds orders with a negative total?

Go deeper

1Write a query to find rows violating an expected data rule (e.g. negative prices).
2Learn to build a personal checklist of common data validation checks to run.
3Practice validating data consistency between two related tables.