1.4Common Query Patterns for QA

Verify Record Existence

-- Does this user exist?
SELECT COUNT(*) FROM users WHERE email = 'test@example.com';
-- Expected: 1

-- Does this order have line items?
SELECT COUNT(*) FROM line_items WHERE order_id = 'order-123';
-- Expected: > 0

Verify Default Values

-- Check that defaults are applied on creation
SELECT role, active, email_verified, created_at, updated_at
FROM users
WHERE email = 'newuser@test.com';
-- Expected: role='viewer', active=true, email_verified=false,
-- created_at is recent, updated_at equals created_at

Verify Timestamps

-- Check that updated_at changes on modification
SELECT updated_at > created_at AS was_modified
FROM users
WHERE id = 123;
-- Expected: true (if user was updated)

-- Check that timestamps are reasonable (not in the future, not from 1970)
SELECT *
FROM orders
WHERE created_at > NOW()
   OR created_at < '2020-01-01';
-- Expected: no rows (no invalid timestamps)

Find Data Anomalies

-- Find orphaned records (line items without orders)
SELECT li.id, li.order_id
FROM line_items li
LEFT JOIN orders o ON o.id = li.order_id
WHERE o.id IS NULL;

-- Find duplicate emails (data integrity issue)
SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- Find NULL values in required fields
SELECT id, name, email
FROM users
WHERE name IS NULL OR email IS NULL;