SQL Interview Questions

The CASE statement in SQL is a conditional expression that allows you to implement if-then-else logic within your queries. It is used to evaluate conditions and return a specific value when the first condition is met. If no conditions are true, an optional ELSE clause can return a default value.

Syntax of the CASE Statement

There are two types of CASE statements: the simple CASE statement and the searched CASE statement.

Simple CASE Statement

The simple CASE statement compares an expression to a set of simple expressions to determine the result.

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE default_result
END

Searched CASE Statement

The searched CASE statement evaluates a set of Boolean expressions to determine the result.

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

Examples of the CASE Statement

Example 1: Simple CASE Statement

Suppose you have an employees table with a job_title column, and you want to categorize employees based on their job title.

SELECT employee_id, job_title,
    CASE job_title
        WHEN 'Manager' THEN 'Management'
        WHEN 'Developer' THEN 'Technical'
        WHEN 'Analyst' THEN 'Technical'
        ELSE 'Other'
    END AS job_category
FROM employees;

Example 2: Searched CASE Statement

Suppose you have a sales table and you want to categorize sales amounts into different ranges.

SELECT sale_id, sale_amount,
    CASE
        WHEN sale_amount > 10000 THEN 'High'
        WHEN sale_amount BETWEEN 5000 AND 10000 THEN 'Medium'
        ELSE 'Low'
    END AS sale_category
FROM sales;

Using CASE in Other Clauses

CASE in SELECT Clause

You can use CASE in the SELECT clause to create new columns based on conditions.

SELECT employee_id, salary,
    CASE
        WHEN salary > 70000 THEN 'High'
        WHEN salary BETWEEN 50000 AND 70000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_category
FROM employees;

CASE in WHERE Clause

You can use CASE in the WHERE clause to add conditional logic to filter criteria.

SELECT *
FROM employees
WHERE 
    CASE
        WHEN department_id = 1 THEN salary > 50000
        WHEN department_id = 2 THEN salary > 60000
        ELSE salary > 70000
    END;

CASE in ORDER BY Clause

You can use CASE in the ORDER BY clause to sort rows based on different conditions.

SELECT employee_id, job_title, hire_date
FROM employees
ORDER BY
    CASE
        WHEN job_title = 'Manager' THEN hire_date
        ELSE employee_id
    END;

Practical Use Cases

Example: Dynamic Column Output

Display different messages based on employee performance.

SELECT employee_id, performance_score,
    CASE
        WHEN performance_score >= 90 THEN 'Excellent'
        WHEN performance_score >= 75 THEN 'Good'
        WHEN performance_score >= 50 THEN 'Average'
        ELSE 'Poor'
    END AS performance_rating
FROM performance;

Example: Aggregating Data Conditionally

Aggregate sales data based on conditional logic.

SELECT region,
    SUM(
        CASE
            WHEN sale_amount > 10000 THEN sale_amount
            ELSE 0
        END
    ) AS high_value_sales
FROM sales
GROUP BY region;

Summary

The CASE statement in SQL is a powerful tool for introducing conditional logic into your queries. It can be used in various clauses to manipulate and analyze data based on specific conditions. By understanding and utilizing the CASE statement, you can write more flexible and dynamic SQL queries that cater to complex business requirements.