CASE in SELECT for computed columns in SQL

Introduction

Use CASE in SELECT to create new columns that change values based on conditions, making data easier to understand.

You want to label data rows based on their values, like marking scores as 'Pass' or 'Fail'.

You need to group ages into categories like 'Child', 'Adult', or 'Senior' in your results.

You want to show different text or numbers depending on a column's value without changing the original data.

You want to highlight special cases in your report, such as marking orders as 'Late' or 'On Time'.

Syntax

SQL

SELECT column1, column2,
       CASE
         WHEN condition1 THEN result1
         WHEN condition2 THEN result2
         ELSE default_result
       END AS new_column_name
FROM table_name;

The CASE expression checks conditions in order and returns the result for the first true condition.

The ELSE part is optional; if no conditions match and ELSE is missing, the result is NULL.

Examples

Labels each student as 'Pass' or 'Fail' based on their score.

SQL

SELECT name, score,
       CASE
         WHEN score >= 60 THEN 'Pass'
         ELSE 'Fail'
       END AS result
FROM students;

Shows stock status based on quantity.

SQL

SELECT product, quantity,
       CASE
         WHEN quantity = 0 THEN 'Out of stock'
         WHEN quantity < 5 THEN 'Low stock'
         ELSE 'In stock'
       END AS stock_status
FROM inventory;

Groups employees by age categories.

SQL

SELECT employee, age,
       CASE
         WHEN age < 18 THEN 'Minor'
         WHEN age BETWEEN 18 AND 64 THEN 'Adult'
         ELSE 'Senior'
       END AS age_group
FROM employees;

Sample Program

This creates a students table, adds three students with scores, and selects their names, scores, and a computed column 'result' showing 'Pass' or 'Fail'.

SQL

CREATE TABLE students (
  id INT,
  name VARCHAR(50),
  score INT
);

INSERT INTO students (id, name, score) VALUES
(1, 'Alice', 85),
(2, 'Bob', 55),
(3, 'Charlie', 70);

SELECT name, score,
       CASE
         WHEN score >= 60 THEN 'Pass'
         ELSE 'Fail'
       END AS result
FROM students;

Important Notes

CASE expressions can be used anywhere in SELECT, WHERE, ORDER BY, or GROUP BY clauses.

Always test your CASE logic with sample data to ensure it covers all cases.

Use meaningful names for computed columns to make results clear.

Summary

CASE lets you create new columns based on conditions in your SELECT query.

It works like an IF-THEN-ELSE to choose values for each row.

Use it to make your query results easier to read and understand.

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