if_statement_examples.sql

-- Using IIF() function (SQL Server)
SELECT 
    product_name,
    price,
    IIF(price > 100, 'Expensive', 'Affordable') as price_category

FROM products;

-- Multiple conditions using nested IIF (SQL Server)
SELECT 
    order_id,
    total_amount,
    IIF(total_amount > 1000, 
        'High Value',
        IIF(total_amount > 500, 'Medium Value', 'Low Value')
    ) as order_value

FROM orders;

-- Alternative to IF using CASE (Works in all SQL flavors)
SELECT 
    customer_name,
    credit_score,
    CASE 
        WHEN credit_score > 700 THEN 'Excellent'
        ELSE 'Needs Improvement'
    END as credit_rating

FROM customers;

-- IF logic in WHERE clause
SELECT *
FROM orders
WHERE 
    CASE 
        WHEN DATEPART(MONTH, order_date) IN (12, 1, 2) THEN 
            total_amount > 1000  -- Winter season threshold
        ELSE 
            total_amount > 500   -- Regular season threshold
    END;

-- IF logic in UPDATE statement using IIF
UPDATE employees
SET bonus = 
    IIF(years_of_service > 5,
        salary * 0.1,    -- 10% bonus for > 5 years
        salary * 0.05    -- 5% bonus for <= 5 years
    );

-- Complex IF logic using CASE
SELECT 
    student_name,
    score,
    CASE 
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        WHEN score >= 70 THEN 'C'
        WHEN score >= 60 THEN 'D'
        ELSE 'F'
    END as grade,
    IIF(score >= 60, 'Pass', 'Fail') as pass_status

FROM student_scores;