CASE Statements

Question 1

How does a CASE statement work?

Answer 1

A CASE statement evaluates a list of conditions and
returns the first condition that resolves to true. The statement can contain one or more WHEN conditions. WHEN…THEN condition is similar to an IF…THEN condition. If no condition resolves to true then the ELSE condition resolves. If there is no ELSE condition, then the
statement returns NULL.

Question 2

What are the two CASE statement expressions?

Answer 2

Simple CASE expression
Searched CASE expression

Question 3

What’s the simple CASE Syntax

Answer 3

You start with the keyword CASE and then mention the column or expression you want to evaluate.

CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
…
WHEN valueN THEN resultN
ELSE default_result
END

SELECT
employee_id,
salary,
CASE department_id
WHEN 1 THEN ‘Sales’
WHEN 2 THEN ‘Engineering’
WHEN 3 THEN ‘HR’
ELSE ‘Other’
END AS department_name
FROM employees;

Question 4

What’s a caveat with a simple case statement?

Answer 4

No operators. Instead, its only equality comparisons to avoid complexity.

Question 5

What keyword must you terminate your CASE statement with?

Answer 5

END

Question 6

Where do you place the ELSE statement and what does it do?

Answer 6

The ELSE keyword resolves when none of the WHEN THEN
clauses resolve to true

ELSE is inserted after all the WHEN THEN clauses

Question 7

How is a SIMPLE CASE STATEMENT different than a SEARCH CASE STATEMENT?

Answer 7

A Simple CASE statement evaluates a single expression against a set of fixed values.

Each WHEN clause in a Searched CASE statement is independent and can use any column, constant, or function. This independence allows for more complex and varied conditions.

Question 8

What’s another way to create complex conditional logic using CASE statements?

Answer 8

You can NEST them?

Question 9

How many statements can you nest at a time?

Answer 9

10