when-then.sql

-- https://towardsdev.com/sql-case-statement-with-code-examples-04f77dab8d5a

-- 1. Simple case
SELECT
    order_id,
    CASE customer_id
        WHEN 1 THEN 'Premium'
        WHEN 2 THEN 'Gold'
        WHEN 3 THEN 'Silver'
        ELSE 'Regular'
    END AS customer_type
FROM orders;


-- 2. Searched CASE Statement
SELECT
    order_id,
    CASE
        WHEN order_amount > 1000 THEN 'High'
        WHEN order_amount > 500 THEN 'Medium'
        ELSE 'Low'
    END AS order_priority
FROM orders;

-- 3. Using CASE in WHERE Clause
SELECT *
FROM customers
WHERE CASE
    WHEN country = 'USA' THEN sales_region = 'North America'
    WHEN country = 'UK' THEN sales_region = 'Europe'
    ELSE FALSE
END;

-- 4. Using CASE with Aggregate Functions
SELECT
    department,
    COUNT(CASE WHEN salary > 50000 THEN 1 END) AS high_salary_count,
    COUNT(CASE WHEN salary <= 50000 THEN 1 END) AS low_salary_count
FROM employees
GROUP BY department;


-- 5. Nesting CASE Statements
SELECT
    order_id,
    CASE
        WHEN payment_status = 'paid' THEN
            CASE
                WHEN shipping_status = 'shipped' THEN 'Delivered'
                ELSE 'Processing'
            END
        ELSE 'Pending'
    END AS order_status
FROM orders;

-- 6. Using CASE in JOIN Conditions
SELECT
    o.order_id,
    o.order_date,
    c.customer_name
FROM orders o
JOIN customers c
ON CASE
    WHEN o.customer_id = 1 THEN c.customer_id = o.customer_id
    WHEN o.customer_id = 2 THEN c.country = 'USA'
    ELSE c.country = 'UK'
END;


-- INSERT
INSERT INTO employees (employee_id, name, salary)
VALUES
 (101, 'John Doe', 
 CASE
 WHEN department = 'Engineering' THEN 80000
 WHEN department = 'Marketing' THEN 70000
 ELSE 60000
 END);