2️⃣4️⃣ CASE in Data Cleaning

CASE can also standardize inconsistent values.

Suppose a dataset contains:

• M

• Male

• male

• MALE

You can standardize them:

SELECT
    employee_name,
    CASE
        WHEN LOWER(gender) = 'm'
          OR LOWER(gender) = 'male'
        THEN 'Male'

        WHEN LOWER(gender) = 'f'
          OR LOWER(gender) = 'female'
        THEN 'Female'

        ELSE 'Unknown'
    END AS standardized_gender
FROM employees;

This is a practical data-cleaning technique.

2️⃣5️⃣ CASE for Business Rules

Imagine a company wants to classify customers:

• Spend ≥ ₹100,000 → VIP

• Spend ≥ ₹50,000 → High Value

• Spend ≥ ₹10,000 → Regular

• Otherwise → Low Value

SQL:

SELECT
    customer_name,
    total_spend,
    CASE
        WHEN total_spend >= 100000 THEN 'VIP'
        WHEN total_spend >= 50000 THEN 'High Value'
        WHEN total_spend >= 10000 THEN 'Regular'
        ELSE 'Low Value'
    END AS customer_segment
FROM customers;

This is an important Data Analyst mindset:

• Convert business rules into SQL logic.

🧠 Common CASE Mistakes

• ❌ Mistake 1: Forgetting END

• Wrong: CASE WHEN salary > 500000 THEN 'High'

•

Correct: CASE WHEN salary > 500000 THEN 'High' ELSE 'Low' END

•

❌ Mistake 2: Incorrect condition order

• Wrong: CASE WHEN salary > 500000 THEN 'Medium' WHEN salary > 1000000 THEN 'High' END

• The second condition won't be reached for salaries above ₹1 million because they already satisfy the first condition.

•

Better: CASE WHEN salary > 1000000 THEN 'High' WHEN salary > 500000 THEN 'Medium' ELSE 'Low' END

•

❌ Mistake 3: Forgetting ELSE

• You can omit ELSE, but if no WHEN condition matches, SQL generally returns NULL.

•

Better when appropriate: CASE WHEN status = 'Completed' THEN 'Success' WHEN status = 'Cancelled' THEN 'Failure' ELSE 'Other' END

•

❌ Mistake 4: Confusing CASE with filtering

• CASE creates or transforms a value.

• WHERE filters rows.

• For example: CASE WHEN salary > 800000 THEN 'High' ELSE 'Low' END doesn't remove rows. It categorizes them.

💼 SQL Interview Questions

•

Q1. What is CASE in SQL? CASE is an expression used to implement conditional logic and return different values based on specified conditions

.

•

Q2. Can CASE be used with aggregate functions? Yes. SUM(CASE WHEN status = 'Completed' THEN 1 ELSE 0 END)

• Q3. What happens if no WHEN condition matches? If there is an ELSE, its value is returned. Otherwise, the result is generally NULL.

• Q4. Does CASE stop after the first matching condition? For a searched CASE, SQL returns the result associated with the first matching WHEN condition.

• Q5. Can CASE be used with GROUP BY? Yes. You can group by a CASE expression or, depending on the SQL dialect, an alias representing that expression.

• Q6. What is conditional aggregation? Using expressions such as CASE inside aggregate functions to calculate metrics for selected conditions.

🎯 Practice Questions

Try solving these yourself first.

• Q1. Classify employees as: High → salary >= 1,000,000, Medium → salary >= 600,000, Low → everything else

• Q2. Classify orders as: Large → amount >= 10,000, Medium → amount >= 5,000, Small → everything else

• Q3. Count completed and cancelled orders using conditional aggregation.

• Q4. Calculate successful transaction value.

• Q5. Classify customers as VIP if spending is greater than ₹100,000.

• Q6. Create a column that says Has Manager or No Manager based on manager_id.

• Q7. Create salary bands and count employees in each band.

• Q8. Calculate completed revenue and cancelled revenue in the same query.

✅ Answers

Answer 1