Drill 4 of 10 in Database Analyst
0

Database Analyst · Validation rules · OR · NULL-safe checks

4. Payments that break the rules

Concept: Validation rules · OR · NULL-safe checksshow

Data validation is a WHERE clause listing every way a row can be wrong, joined with OR. Each rule needs a NULL check of its own: NULL NOT LIKE '%@%' is NULL, not true, so a missing value slips past a pattern test unless you ask for IS NULL too.

Example (different data):

SELECT id, sku, qty, shipped_on
FROM shipments
WHERE qty <= 0
   OR shipped_on > '2024-12-31'
   OR sku IS NULL
   OR sku NOT LIKE 'SKU-%';

Your task

The payments export taken on 2024-08-31 is about to be loaded into the ledger, and anything that breaks a business rule must go to a review queue first.

A payment is valid when its amount is greater than 0, paid_on is on or before 2024-08-31 (the export date), and payer_email is present and contains an @ with a dot somewhere after it (LIKE '%@%.%'). Return id, payer_email, amount and paid_on of every raw_payments row that breaks at least one rule, once per row. Sort by id.

Starting Postgres in your browser…

Checking your access…