CASE in SELECT for computed columns in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the CASE statement do in a SELECT query?

It allows you to create new columns with values that depend on conditions, like an IF-THEN-ELSE in your query.

beginner

Write a simple CASE expression to show 'Adult' if age >= 18, else 'Minor'.

CASE WHEN age >= 18 THEN 'Adult' ELSE 'Minor' END

intermediate

Can CASE be used to create multiple computed columns in one SELECT?

Yes, you can use multiple CASE expressions in the same SELECT to create several computed columns.

intermediate

What happens if no WHEN condition matches and there is no ELSE in CASE?

The CASE expression returns NULL for that row.

beginner

Why use CASE in SELECT instead of filtering rows with WHERE?

CASE lets you keep all rows but change or add column values based on conditions, while WHERE filters out rows.

What keyword starts a conditional expression inside SELECT to create computed columns?

A CASE

B IF

C WHEN

D SELECT

The CASE keyword starts the conditional expression in SQL SELECT statements.

What does ELSE do in a CASE expression?

A Specifies the default value if no WHEN matches

B Ends the CASE expression

C Starts a new condition

D Filters rows

ELSE provides a default value when none of the WHEN conditions are true.

If you want to label ages >= 65 as 'Senior', which WHEN clause is correct?

A WHEN age <= 65 THEN 'Senior'

B WHEN age < 65 THEN 'Senior'

C WHEN age = 65 THEN 'Senior'

D WHEN age >= 65 THEN 'Senior'

The condition 'age >= 65' correctly selects seniors.

What will this CASE return if age = 20? CASE WHEN age < 18 THEN 'Child' ELSE 'Adult' END

A 'Child'

B 'Adult'

C NULL

D Error

Since 20 is not less than 18, ELSE 'Adult' applies.

Can CASE expressions be nested inside each other in SELECT?

A Only in WHERE clause

B No

C Yes

D Only in UPDATE statements

CASE expressions can be nested to handle complex conditions.

Explain how to use CASE in a SELECT statement to create a new column based on conditions.

Think of CASE as an IF-THEN-ELSE inside your query.

Describe what happens if no WHEN condition matches and there is no ELSE clause in a CASE expression.

What does SQL do when no condition fits and no default is given?

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