Turn a requirement or a business rule into an SQL check — “the query must return zero rows” or “the share is not below the threshold” — and read the result as confirmation or violation of the requirement.
Requirement → assertion → query
A query that looks for violations
A requirement becomes an assertion about data, and the assertion a query that finds violations. Empty result: the requirement holds on this data (not forever). Non-empty: concrete cases to investigate.
Mandatory data. “A delivered order has a courier and a delivery time”:
SELECT order_id FROM orders
WHERE status = 'delivered'
AND (courier_id IS NULL OR delivered_at IS NULL)
Expected: 0 rows.
Uniqueness. “An order has at most one successful payment”:
SELECT order_id, COUNT(*) FROM payments
WHERE status = 'succeeded'
GROUP BY order_id
HAVING COUNT(*) > 1
Any row found is a double charge and a defect.
A share (NFR). “At least 90% of orders are delivered within 60 minutes”:
SELECT 100.0 * SUM(CASE WHEN delivery_minutes <= 60 THEN 1 ELSE 0 END) / COUNT(*)
FROM orders WHERE status = 'delivered'
CASE turns the condition into 1 or 0; their sum counts matching orders (the training database computes delivery_minutes). 100.0, not 100, avoids integer division.
SQL can't check what isn't in the database: API response time and availability live in logs and monitoring, screen clarity in user research.
Common mistakes: “= NULL”; a forgotten status filter (cancelled orders get counted); a condition on the LEFT JOIN's right table (p.status = 'succeeded') in WHERE instead of ON: unmatched rows drop out and the join becomes INNER. But an anti-join's IS NULL goes in WHERE.
Check templates
| Requirement type | What the query looks for | Expected |
|---|---|---|
| mandatory data | rows with NULL in a mandatory field | 0 rows |
| uniqueness | groups with COUNT(*) > 1 | 0 rows |
| referential integrity | rows without a match via LEFT JOIN … IS NULL | 0 rows |
| lifecycle | delivered orders without a successful payment | 0 rows |
| share | percentage of matching rows via CASE | not below the threshold |
Lesson notes
A query that looks for violations
A requirement becomes an assertion about data, and the assertion a query that finds violations. Empty result: the requirement holds on this data (not forever). Non-empty: concrete cases to investigate.
Mandatory data. “A delivered order has a courier and a delivery time”:
SELECT order_id FROM orders
WHERE status = 'delivered'
AND (courier_id IS NULL OR delivered_at IS NULL)
Expected: 0 rows.
Uniqueness. “An order has at most one successful payment”:
SELECT order_id, COUNT(*) FROM payments
WHERE status = 'succeeded'
GROUP BY order_id
HAVING COUNT(*) > 1
Any row found is a double charge and a defect.
A share (NFR). “At least 90% of orders are delivered within 60 minutes”:
SELECT 100.0 * SUM(CASE WHEN delivery_minutes <= 60 THEN 1 ELSE 0 END) / COUNT(*)
FROM orders WHERE status = 'delivered'
CASE turns the condition into 1 or 0; their sum counts matching orders (the training database computes delivery_minutes). 100.0, not 100, avoids integer division.
SQL can't check what isn't in the database: API response time and availability live in logs and monitoring, screen clarity in user research.
Common mistakes: “= NULL”; a forgotten status filter (cancelled orders get counted); a condition on the LEFT JOIN's right table (p.status = 'succeeded') in WHERE instead of ON: unmatched rows drop out and the join becomes INNER. But an anti-join's IS NULL goes in WHERE.