CASE in SELECT for computed columns in SQL - Step-by-Step Execution

Concept Flow - CASE in SELECT for computed columns

Start Query

↓

Read each row

↓

Evaluate CASE condition

↓

Condition True?

Yes→Return THEN value

↓

Check next WHEN or ELSE

↓

Add computed column value

↓

Return row with computed column

↓

Repeat for all rows

↓

End Query

The query reads each row, evaluates the CASE conditions to compute a new column value, then returns the row with this computed column.

Execution Sample

SQL

SELECT id, score,
  CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    ELSE 'C'
  END AS grade
FROM students;

This query assigns a grade based on the score for each student.

Execution Table

Step	Row (id, score)	CASE Condition Evaluated	Condition Result	Computed Column (grade)
1	(1, 95)	score >= 90	True	'A'
2	(2, 82)	score >= 90	False	Check next WHEN
3	(2, 82)	score >= 80	True	'B'
4	(3, 75)	score >= 90	False	Check next WHEN
5	(3, 75)	score >= 80	False	Check next WHEN
6	(3, 75)	ELSE	Default	'C'
7	All rows processed	-	-	-

💡 All rows processed, CASE evaluated for each row, query ends.

Variable Tracker

Variable	Start	After Row 1	After Row 2	After Row 3	Final
id	N/A	1	2	3	3
score	N/A	95	82	75	75
grade	N/A	'A'	'B'	'C'	'C'

Key Moments - 2 Insights

Why does the CASE stop checking conditions after the first true WHEN?

Because CASE returns the THEN value of the first WHEN condition that is true, as shown in execution_table rows 1 and 3 where it stops after finding a true condition.

What happens if none of the WHEN conditions are true?

The ELSE value is used as the computed column, as shown in execution_table rows 5 and 6 for the third row where both WHEN conditions are false.

Visual Quiz - 3 Questions

Look at the execution_table, what is the computed grade for the student with id=2?

A 'B'

B 'A'

C 'C'

D NULL

💡 Hint

Check rows 2 and 3 in the execution_table where id=2 is evaluated.

At which step does the CASE expression use the ELSE clause?

A Step 2

B Step 4

C Step 6

D Step 1

💡 Hint

Look at execution_table rows 5 and 6 where both WHEN conditions are false and ELSE is applied.

If the score for id=3 was 85, what would be the computed grade?

A 'A'

B 'B'

C 'C'

D NULL

💡 Hint

Refer to the CASE conditions and see which WHEN matches score=85.

Concept Snapshot

CASE in SELECT computes new columns by checking conditions in order.
Syntax: CASE WHEN condition THEN value ELSE value END.
Stops at first true WHEN.
If none true, uses ELSE.
Useful for categorizing or transforming data in query results.

Full Transcript

This visual execution shows how a SQL query uses CASE in SELECT to compute a new column called grade based on score. Each row is read, CASE conditions are checked in order. When a condition is true, its THEN value is assigned as grade. If no WHEN is true, ELSE value is used. The execution table traces each step for three rows, showing how grades A, B, and C are assigned. Variable tracker shows how id, score, and grade change per row. Key moments clarify why CASE stops at first true condition and how ELSE works. The quiz tests understanding of these steps.

Practice

1. What does the CASE statement do in a SQL SELECT query?

easy

A. Creates a new column with values based on conditions

B. Deletes rows that do not meet a condition

C. Joins two tables together

D. Sorts the results in ascending order

Solution

1. Step 1: Understand the purpose of CASE in SELECT

   The CASE statement is used to create new columns by checking conditions and assigning values accordingly.

2. Step 2: Compare with other SQL operations

   Deleting rows is done with DELETE, joining tables with JOIN, and sorting with ORDER BY, not CASE.

3. Final Answer:

   Creates a new column with values based on conditions -> Option A

4. Quick Check:

   CASE = computed column based on conditions [OK]

Hint: CASE creates conditional columns in SELECT queries [OK]

Common Mistakes:

• Thinking CASE deletes rows
• Confusing CASE with JOIN
• Assuming CASE sorts data

2. Which of the following is the correct syntax to use CASE in a SELECT statement?

easy

A. SELECT CASE WHEN score > 50 THEN 'Pass' ELSE 'Fail' END result FROM students

B. SELECT CASE score > 50 THEN 'Pass' ELSE 'Fail' END AS result FROM students;

C. SELECT CASE WHEN score > 50 THEN 'Pass' ELSE 'Fail' AS result FROM students;

D. SELECT CASE WHEN score > 50 THEN 'Pass' ELSE 'Fail' END AS result FROM students;

Solution

1. Step 1: Review correct CASE syntax

   The correct syntax requires WHEN conditions, THEN results, ELSE for default, and END to close, with AS to name the column.

2. Step 2: Identify errors in other options

   A omits AS before result (END result); B misses WHEN after CASE; C misses END before AS result.

3. Final Answer:

   SELECT CASE WHEN score > 50 THEN 'Pass' ELSE 'Fail' END AS result FROM students; -> Option D

4. Quick Check:

   CASE syntax = WHEN...THEN...ELSE...END AS alias [OK]

Hint: Always use WHEN, THEN, ELSE, END, and AS alias [OK]

Common Mistakes:

• Omitting WHEN keyword
• Forgetting END keyword
• Not using AS for alias

3. Given the table employees with column salary, what will this query return?

SELECT name, CASE WHEN salary >= 5000 THEN 'High' ELSE 'Low' END AS salary_level FROM employees;

medium

A. An error because CASE cannot be used in SELECT

B. Only employees with salary ≥ 5000

C. A list of employee names with salary_level showing 'High' if salary ≥ 5000, else 'Low'

D. A list of employee names with salary values doubled

Solution

1. Step 1: Understand the CASE condition

   The CASE checks if salary is 5000 or more, then assigns 'High', otherwise 'Low'.

2. Step 2: Analyze the SELECT output

   The query returns all employees with their name and a new column salary_level showing 'High' or 'Low' accordingly.

3. Final Answer:

   A list of employee names with salary_level showing 'High' if salary ≥ 5000, else 'Low' -> Option C

4. Quick Check:

   CASE creates conditional labels per row [OK]

Hint: CASE labels rows based on condition, does not filter [OK]

Common Mistakes:

• Thinking CASE filters rows
• Expecting numeric salary changes
• Assuming syntax error

4. Identify the error in this SQL query:

SELECT name, CASE salary > 10000 THEN 'Rich' ELSE 'Average' END AS status FROM users;

medium

A. Missing WHEN keyword after CASE

B. Missing END keyword

C. Missing ELSE keyword

D. Missing AS keyword for alias

Solution

1. Step 1: Check CASE syntax

   The CASE statement must have WHEN before the condition. Here, WHEN is missing.

2. Step 2: Verify other parts

   END and ELSE are present, AS is used correctly, so no errors there.

3. Final Answer:

   Missing WHEN keyword after CASE -> Option A

4. Quick Check:

   CASE requires WHEN before condition [OK]

Hint: Always write WHEN after CASE before condition [OK]

Common Mistakes:

• Omitting WHEN keyword
• Confusing ELSE with END
• Forgetting AS alias

5. You want to classify products by price into 'Cheap', 'Moderate', and 'Expensive' using CASE in SELECT. Which query correctly implements this?

hard

A. SELECT product_name, CASE price < 50 THEN 'Cheap' WHEN price BETWEEN 50 AND 100 THEN 'Moderate' ELSE 'Expensive' END price_category FROM products;

B. SELECT product_name, CASE WHEN price < 50 THEN 'Cheap' WHEN price BETWEEN 50 AND 100 THEN 'Moderate' ELSE 'Expensive' END AS price_category FROM products;

C. SELECT product_name, CASE WHEN price < 50 THEN 'Cheap' ELSE WHEN price BETWEEN 50 AND 100 THEN 'Moderate' ELSE 'Expensive' END AS price_category FROM products;

D. SELECT product_name, CASE WHEN price < 50 THEN 'Cheap' WHEN price > 100 THEN 'Expensive' ELSE 'Expensive' END AS price_category FROM products;

Solution

1. Step 1: Check correct CASE syntax with multiple WHEN

   SELECT product_name, CASE WHEN price < 50 THEN 'Cheap' WHEN price BETWEEN 50 AND 100 THEN 'Moderate' ELSE 'Expensive' END AS price_category FROM products; correctly uses multiple WHEN conditions with THEN, ELSE, and END, and aliases the column.

2. Step 2: Analyze logic correctness

   SELECT product_name, CASE WHEN price < 50 THEN 'Cheap' WHEN price BETWEEN 50 AND 100 THEN 'Moderate' ELSE 'Expensive' END AS price_category FROM products; classifies prices below 50 as 'Cheap', between 50 and 100 as 'Moderate', else 'Expensive', which matches the requirement.

3. Final Answer:

   SELECT product_name, CASE WHEN price < 50 THEN 'Cheap' WHEN price BETWEEN 50 AND 100 THEN 'Moderate' ELSE 'Expensive' END AS price_category FROM products; -> Option B

4. Quick Check:

   Multiple WHEN conditions with ELSE classify ranges [OK]

Hint: Use multiple WHEN for ranges, ELSE for default [OK]

Common Mistakes:

• Omitting WHEN keywords
• Misplacing ELSE before WHEN
• Not using AS for alias