Q14 of 26 · SQL

How do you use a CASE statement in SQL, and give a QA example.

SQLMidsqlcaseconditionaldata-transformation

Short answer

Short answer: CASE is SQL's if/else — it maps values or conditions to output values in a SELECT, useful for bucketing data, transforming status codes, or flagging anomalies in a result set.

Detail

Two forms:

Simple CASE — compares a column against fixed values:

SELECT order_id,
  CASE status
    WHEN 'pending'   THEN 'Awaiting payment'
    WHEN 'paid'      THEN 'Payment received'
    WHEN 'shipped'   THEN 'In transit'
    ELSE 'Unknown'
  END AS status_label
FROM orders;

Searched CASE — arbitrary conditions per branch:

SELECT order_id, total_amount,
  CASE
    WHEN total_amount < 50   THEN 'small'
    WHEN total_amount < 200  THEN 'medium'
    ELSE 'large'
  END AS order_tier
FROM orders;

QA use — flag anomalies inline:

SELECT order_id, shipped_at, status,
  CASE
    WHEN status = 'completed' AND shipped_at IS NULL THEN 'MISSING_SHIP_DATE'
    WHEN status = 'pending'   AND shipped_at IS NOT NULL THEN 'PREMATURE_SHIP'
    ELSE 'OK'
  END AS data_quality_flag
FROM orders
WHERE CASE
    WHEN status = 'completed' AND shipped_at IS NULL THEN 1
    WHEN status = 'pending'   AND shipped_at IS NOT NULL THEN 1
    ELSE 0
  END = 1;

// EXAMPLE

-- Pivot test results into a pass/fail summary
SELECT
  test_suite,
  SUM(CASE WHEN result = 'pass' THEN 1 ELSE 0 END) AS passed,
  SUM(CASE WHEN result = 'fail' THEN 1 ELSE 0 END) AS failed,
  COUNT(*) AS total
FROM test_runs
GROUP BY test_suite;

// WHAT INTERVIEWERS LOOK FOR

Simple vs searched CASE. A QA-specific use — the pivot/flag pattern is stronger than a generic example.

// Related questions

How do GROUP BY and HAVING work, and how would you use them to find duplicate records?

SQL