comprehensive_conditional_examples.sql

-- 1. CASE Expression Examples
SELECT 
    product_name,
    price,
    -- Simple CASE
    CASE product_category
        WHEN 'Electronics' THEN 'Tech'
        WHEN 'Clothing' THEN 'Fashion'
        ELSE 'Other'
    END as simple_category,
    
    -- Searched CASE
    CASE 
        WHEN price > 1000 THEN 'Premium'
        WHEN price > 500 THEN 'Mid-range'
        ELSE 'Budget'
    END as price_category;

-- 2. IIF Function (SQL Server)
SELECT 
    order_id,
    amount,
    IIF(amount > 1000, 'High Value', 'Standard Value') as order_value,
    -- Nested IIF
    IIF(status = 'Completed',
        IIF(amount > 500, 'Large Complete', 'Small Complete'),
        'Pending'
    ) as order_status;

-- 3. COALESCE Function (Handling NULL values)
SELECT 
    customer_name,
    COALESCE(phone, email, 'No Contact') as primary_contact,
    COALESCE(preferred_address, billing_address, shipping_address) as contact_address,
    price * COALESCE(discount, 0) as discount_amount;

-- 4. NULLIF Function (Converting values to NULL)
SELECT 
    product_name,
    NULLIF(stock_count, 0) as available_stock,
    NULLIF(return_rate, 0) as actual_return_rate;

-- 5. ISNULL/NVL (SQL Server/Oracle)
SELECT 
    employee_name,
    ISNULL(commission, 0) as safe_commission,  -- SQL Server
    NVL(bonus, 0) as safe_bonus;              -- Oracle

-- 6. Combining Multiple NULL Handling
SELECT 
    order_id,
    COALESCE(NULLIF(status, 'Unknown'), 'Pending') as cleaned_status,
    ISNULL(NULLIF(quantity, 0), 1) as safe_quantity;

-- 7. Complex Conditional Logic
SELECT 
    product_name,
    price,
    stock_level,
    
    -- Complex CASE with multiple conditions
    CASE 
        WHEN price > 1000 AND stock_level = 0 THEN 'Premium Backorder'
        WHEN price > 1000 AND stock_level > 0 THEN 'Premium Available'
        WHEN price <= 1000 AND stock_level = 0 THEN 'Standard Backorder'
        ELSE 'Standard Available'
    END as product_status,
    
    -- Combining CASE with COALESCE
    COALESCE(
        CASE 
            WHEN discount_type = 'Percent' THEN price * (1 - discount_value/100)
            WHEN discount_type = 'Fixed' THEN price - discount_value
            ELSE price
        END,
        price
    ) as final_price;

-- 8. Conditional Aggregate Functions
SELECT 
    department,
    COUNT(*) as total_employees,
    SUM(CASE WHEN salary > 50000 THEN 1 ELSE 0 END) as high_paid_count,
    AVG(COALESCE(bonus, 0)) as avg_bonus,
    SUM(IIF(performance_rating >= 4, 1, 0)) as high_performers;

-- 9. CHOOSE Function (SQL Server)
SELECT 
    order_id,
    status_code,
    CHOOSE(status_code, 'Pending', 'Processing', 'Shipped', 'Delivered') as status_text;

-- 10. Conditional Window Functions
SELECT 
    employee_name,
    department,
    salary,
    FIRST_VALUE(salary) OVER (
        PARTITION BY department 
        ORDER BY CASE 
            WHEN position = 'Manager' THEN 1
            WHEN position = 'Senior' THEN 2
            ELSE 3
        END
    ) as dept_highest_salary;

-- 11. Conditional Joins
SELECT 
    p.product_name,
    p.category,
    CASE 
        WHEN p.category = 'Electronics' THEN e.warranty_period
        WHEN p.category = 'Clothing' THEN c.size_available
        ELSE 'N/A'
    END as additional_info
FROM products p
LEFT JOIN electronics_details e 
    ON p.product_id = e.product_id 
    AND p.category = 'Electronics'
LEFT JOIN clothing_details c 
    ON p.product_id = c.product_id 
    AND p.category = 'Clothing';

-- 12. Error Handling with TRY_CONVERT
SELECT 
    order_id,
    TRY_CONVERT(date, date_string) as safe_date,
    CASE 
        WHEN TRY_CONVERT(date, date_string) IS NULL THEN 'Invalid Date'
        ELSE 'Valid Date'
    END as date_validity;

-- 13. Bitwise Operations with Conditional Logic
SELECT 
    permissions,
    CASE 
        WHEN permissions & 4 = 4 THEN 'Admin'
        WHEN permissions & 2 = 2 THEN 'Editor'
        WHEN permissions & 1 = 1 THEN 'Viewer'
        ELSE 'No Access'
    END as permission_level;

-- 14. String Manipulation with Conditions
SELECT 
    email,
    CASE 
        WHEN CHARINDEX('@', email) > 0 
        THEN LEFT(email, CHARINDEX('@', email) - 1)
        ELSE email
    END as username,
    COALESCE(NULLIF(TRIM(phone), ''), 'No Phone') as clean_phone;