SQL CASE Statement: From Basics to Advanced

CASE
    WHEN condition_1 THEN result_1
    WHEN condition_2 THEN result_2
    WHEN condition_N THEN result_N
    ELSE default_result          -- optional; returns NULL if omitted
END

CASE expression
    WHEN value_1 THEN result_1
    WHEN value_2 THEN result_2
    ELSE default_result
END

SELECT
    name,
    department,
    salary,
    CASE
        WHEN salary >= 90000 THEN 'Band A  (Senior)'
        WHEN salary >= 75000 THEN 'Band B  (Mid-Senior)'
        WHEN salary >= 65000 THEN 'Band C  (Mid)'
        ELSE                      'Band D  (Junior)'
    END AS salary_band
FROM employees
ORDER BY salary DESC;

SELECT
    name,
    department,
    CASE department
        WHEN 'Engineering' THEN 'Tech'
        WHEN 'Marketing'   THEN 'Biz Dev'
        WHEN 'HR'          THEN 'People Ops'
        ELSE                    'Other'
    END AS dept_label
FROM employees
ORDER BY department;

SELECT
    order_id,
    customer_id,
    amount,
    status,
    CASE status
        WHEN 'Delivered'  THEN 'COMPLETED'
        WHEN 'Shipped'    THEN 'IN TRANSIT'
        WHEN 'Processing' THEN 'PENDING'
        WHEN 'Cancelled'  THEN 'CLOSED'
        ELSE                   'UNKNOWN'
    END AS status_flag
FROM orders
ORDER BY order_id;

-- Return only active employees in Engineering,
-- OR all employees in other departments
SELECT name, department, salary, status
FROM   employees
WHERE
    CASE
        WHEN department = 'Engineering' AND status = 'Active' THEN 1
        WHEN department <> 'Engineering'                      THEN 1
        ELSE 0
    END = 1
ORDER BY department, name;

-- Return products that are either in stock, OR Electronics regardless of stock
SELECT product_id, name, category, price, stock
FROM   products
WHERE
    CASE
        WHEN category = 'Electronics' THEN 1    -- always include Electronics
        WHEN stock > 0                THEN 1    -- include others only if in stock
        ELSE 0
    END = 1
ORDER BY category, price DESC;

-- Sort orders by business priority: Processing first, then Shipped,
-- then Delivered, Cancelled last
SELECT order_id, customer_id, amount, status
FROM   orders
ORDER BY
    CASE status
        WHEN 'Processing' THEN 1
        WHEN 'Shipped'    THEN 2
        WHEN 'Delivered'  THEN 3
        WHEN 'Cancelled'  THEN 4
        ELSE                   5
    END,
    amount DESC;

SELECT name, department, status, salary
FROM   employees
ORDER BY
    CASE status
        WHEN 'Active'   THEN 1
        WHEN 'On Leave' THEN 2
        ELSE                 3
    END,
    department, name;

SELECT
    CASE
        WHEN salary >= 90000 THEN 'High (90k+)'
        WHEN salary >= 70000 THEN 'Mid  (70k-90k)'
        ELSE                      'Low  (<70k)'
    END AS salary_bucket,
    COUNT(*)       AS headcount,
    AVG(salary)    AS avg_salary,
    MIN(salary)    AS min_salary,
    MAX(salary)    AS max_salary
FROM employees
GROUP BY
    CASE
        WHEN salary >= 90000 THEN 'High (90k+)'
        WHEN salary >= 70000 THEN 'Mid  (70k-90k)'
        ELSE                      'Low  (<70k)'
    END
ORDER BY AVG(salary) DESC;

SELECT
    CASE
        WHEN amount >= 2000 THEN 'Large order (2000+)'
        WHEN amount >= 1000 THEN 'Medium order (1000-2000)'
        WHEN amount >= 500  THEN 'Small order (500-1000)'
        ELSE                     'Micro order (<500)'
    END AS order_tier,
    COUNT(*)        AS order_count,
    SUM(amount)     AS total_revenue,
    ROUND(AVG(amount), 2) AS avg_order_value
FROM orders
GROUP BY
    CASE
        WHEN amount >= 2000 THEN 'Large order (2000+)'
        WHEN amount >= 1000 THEN 'Medium order (1000-2000)'
        WHEN amount >= 500  THEN 'Small order (500-1000)'
        ELSE                     'Micro order (<500)'
    END
ORDER BY total_revenue DESC;

SELECT
    department,
    COUNT(*)                                             AS total_emp,
    SUM(CASE WHEN status = 'Active'   THEN 1 ELSE 0 END) AS active_count,
    SUM(CASE WHEN status = 'On Leave' THEN 1 ELSE 0 END) AS on_leave_count,
    SUM(CASE WHEN status = 'Resigned' THEN 1 ELSE 0 END) AS resigned_count
FROM employees
GROUP BY department
ORDER BY department;

SELECT
    customer_id,
    SUM(amount)                                                   AS total_spent,
    SUM(CASE WHEN region = 'North' THEN amount ELSE 0 END)        AS north_spend,
    SUM(CASE WHEN region = 'South' THEN amount ELSE 0 END)        AS south_spend,
    SUM(CASE WHEN region IN ('East','West') THEN amount ELSE 0 END) AS other_spend
FROM orders
GROUP BY customer_id
ORDER BY total_spent DESC;

SELECT
    department,
    ROUND(AVG(salary), 0)                                          AS overall_avg,
    ROUND(AVG(CASE WHEN status = 'Active' THEN salary END), 0)     AS active_avg,
    ROUND(AVG(CASE WHEN hire_year >= 2021 THEN salary END), 0)     AS recent_hire_avg
FROM employees
GROUP BY department
ORDER BY department;

-- Return departments where the average salary is below 70,000
-- AND at least one employee is not Active
SELECT
    department,
    COUNT(*)         AS headcount,
    ROUND(AVG(salary),0) AS avg_salary,
    SUM(CASE WHEN status <> 'Active' THEN 1 ELSE 0 END) AS non_active
FROM employees
GROUP BY department
HAVING
    CASE
        WHEN AVG(salary) < 70000
         AND SUM(CASE WHEN status <> 'Active' THEN 1 ELSE 0 END) >= 1 THEN 1
        ELSE 0
    END = 1;

-- Give different raises based on department
UPDATE employees
SET salary = salary *
    CASE department
        WHEN 'Engineering' THEN 1.10    -- 10% raise
        WHEN 'Marketing'   THEN 1.08    -- 8% raise
        WHEN 'HR'          THEN 1.05    -- 5% raise
        ELSE                    1.00    -- no change
    END
WHERE status = 'Active';
-- Verify
SELECT name, department, salary FROM employees ORDER BY department;

-- Normalise inconsistent status values to standard terms
UPDATE employees
SET status =
    CASE
        WHEN status IN ('Resigned', 'Terminated', 'Left')  THEN 'Inactive'
        WHEN status IN ('On Leave', 'Medical', 'Sabbatical') THEN 'On Leave'
        WHEN status = 'Active'                              THEN 'Active'
        ELSE 'Unknown'
    END;

-- Classify employees on both salary AND seniority (hire_year)
SELECT
    name,
    hire_year,
    salary,
    CASE
        WHEN salary >= 85000 THEN
            CASE
                WHEN hire_year <= 2019 THEN 'Senior Principal'
                ELSE                       'Principal'
            END
        WHEN salary >= 70000 THEN
            CASE
                WHEN hire_year <= 2020 THEN 'Senior Engineer'
                ELSE                       'Engineer'
            END
        ELSE
            CASE
                WHEN hire_year <= 2020 THEN 'Associate (Exp)'
                ELSE                       'Associate'
            END
    END AS role_level
FROM employees
ORDER BY salary DESC;

-- Replace NULLs with meaningful labels
SELECT
    order_id,
    CASE
        WHEN status IS NULL THEN 'Status Unknown'
        WHEN status = ''   THEN 'Status Empty'
        ELSE status
    END AS safe_status
FROM orders;
-- COALESCE is simpler for simple NULL replacement:
-- COALESCE(status, 'Status Unknown')

SELECT
    product_id,
    name,
    rating,
    stock,
    CASE
        WHEN rating >= 4.5 AND stock > 50   THEN 'Hero Product'
        WHEN rating >= 4.5 AND stock <= 50  THEN 'Star  (Low Stock)'
        WHEN rating >= 4.0 AND stock > 50   THEN 'Reliable'
        WHEN rating >= 4.0 AND stock = 0    THEN 'Out of Stock'
        ELSE                                     'Review Needed'
    END AS product_tier
FROM products
ORDER BY rating DESC, stock DESC;

SELECT
    name,
    salary,
    hire_year,
    CASE
        WHEN salary BETWEEN 90000 AND 100000     THEN 'Top Tier'
        WHEN hire_year IN (2017, 2018, 2019)     THEN 'Veteran'
        WHEN department LIKE '%Eng%'             THEN 'Tech Team'
        ELSE                                          'General'
    END AS employee_tag
FROM employees
ORDER BY salary DESC;