Apprendre Nested and Multiple CASE WHEN Conditions

When working with real-world data, you often need to handle complex logic that goes beyond simple single-condition checks. There are scenarios where you must evaluate multiple criteria at once or even build layered decision trees inside your SQL queries. In such cases, using nested or multiple CASE WHEN statements allows you to express advanced logic directly within your SQL, making your queries powerful and adaptable to a wide range of business rules.


              123456789101112131415161718
            
SELECT
    name,
    department,
    years_at_company,
    CASE
        WHEN department = 'Engineering' THEN
            CASE
                WHEN years_at_company >= 5 THEN 'Eligible for Senior Bonus'
                ELSE 'Eligible for Standard Bonus'
            END
        WHEN department = 'Sales' THEN
            CASE
                WHEN years_at_company >= 3 THEN 'Eligible for Sales Bonus'
                ELSE 'Not Eligible'
            END
        ELSE 'Not Eligible'
    END AS bonus_eligibility
FROM employees;

Nesting works by embedding one CASE WHEN statement inside another, allowing you to apply different logic based on an initial condition. In the query above, the outer CASE checks the employee's department. If the department is "Engineering", it uses an inner CASE to decide between a Senior or Standard bonus based on years_at_company. If the department is "Sales", another inner CASE checks if the employee qualifies for a sales bonus. This structure is helpful when your decision-making depends on more than one factor and those factors are related in a hierarchy. You should use nesting when your logic naturally breaks down into main categories with sub-decisions under each category.


              12345678910
            
SELECT
    name,
    salary,
    CASE
        WHEN salary >= 100000 THEN 'Executive Band'
        WHEN salary >= 80000 AND salary < 100000 THEN 'Senior Band'
        WHEN salary >= 60000 OR department = 'Human Resources' THEN 'Mid Band'
        ELSE 'Entry Band'
    END AS salary_band
FROM employees;

1. What is a key reason to use nested CASE WHEN statements?

2. How can you combine multiple conditions in a single CASE WHEN branch?

3. Fill in the blanks to assign 'Veteran' if `years_at_company >= 10` and `department` is `'HR'`:

Tout était clair ?

Merci pour vos commentaires !

Section 2. Chapitre 1

Demandez à l'IA

Posez n'importe quelle question ou essayez l'une des questions suggérées pour commencer notre discussion

Glissez pour afficher le menu


              123456789101112131415161718
            
SELECT
    name,
    department,
    years_at_company,
    CASE
        WHEN department = 'Engineering' THEN
            CASE
                WHEN years_at_company >= 5 THEN 'Eligible for Senior Bonus'
                ELSE 'Eligible for Standard Bonus'
            END
        WHEN department = 'Sales' THEN
            CASE
                WHEN years_at_company >= 3 THEN 'Eligible for Sales Bonus'
                ELSE 'Not Eligible'
            END
        ELSE 'Not Eligible'
    END AS bonus_eligibility
FROM employees;


              12345678910
            
SELECT
    name,
    salary,
    CASE
        WHEN salary >= 100000 THEN 'Executive Band'
        WHEN salary >= 80000 AND salary < 100000 THEN 'Senior Band'
        WHEN salary >= 60000 OR department = 'Human Resources' THEN 'Mid Band'
        ELSE 'Entry Band'
    END AS salary_band
FROM employees;

1. What is a key reason to use nested CASE WHEN statements?

2. How can you combine multiple conditions in a single CASE WHEN branch?

3. Fill in the blanks to assign 'Veteran' if `years_at_company >= 10` and `department` is `'HR'`:

Tout était clair ?

Merci pour vos commentaires !

Section 2. Chapitre 1

Nested and Multiple CASE WHEN Conditions

1. What is a key reason to use nested CASE WHEN statements?

2. How can you combine multiple conditions in a single CASE WHEN branch?

3. Fill in the blanks to assign 'Veteran' if years_at_company >= 10 and department is 'HR':

Nested and Multiple CASE WHEN Conditions

1. What is a key reason to use nested CASE WHEN statements?

2. How can you combine multiple conditions in a single CASE WHEN branch?

3. Fill in the blanks to assign 'Veteran' if years_at_company >= 10 and department is 'HR':

3. Fill in the blanks to assign 'Veteran' if `years_at_company >= 10` and `department` is `'HR'`:

3. Fill in the blanks to assign 'Veteran' if `years_at_company >= 10` and `department` is `'HR'`: