CASE expression in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of CASE expression with simple conditions

What is the output of the following SQL query?

SELECT
  product_id,
  CASE
    WHEN price < 50 THEN 'Cheap'
    WHEN price BETWEEN 50 AND 100 THEN 'Moderate'
    ELSE 'Expensive'
  END AS price_category
FROM products
ORDER BY product_id
LIMIT 3;

PostgreSQL

CREATE TEMP TABLE products (product_id INT, price INT);
INSERT INTO products VALUES (1, 30), (2, 75), (3, 150);
SELECT
  product_id,
  CASE
    WHEN price < 50 THEN 'Cheap'
    WHEN price BETWEEN 50 AND 100 THEN 'Moderate'
    ELSE 'Expensive'
  END AS price_category
FROM products
ORDER BY product_id
LIMIT 3;

A [{"product_id":1,"price_category":"Cheap"},{"product_id":2,"price_category":"Moderate"},{"product_id":3,"price_category":"Expensive"}]

B [{"product_id":1,"price_category":"Expensive"},{"product_id":2,"price_category":"Cheap"},{"product_id":3,"price_category":"Moderate"}]

C [{"product_id":1,"price_category":"Moderate"},{"product_id":2,"price_category":"Moderate"},{"product_id":3,"price_category":"Cheap"}]

D [{"product_id":1,"price_category":null},{"product_id":2,"price_category":null},{"product_id":3,"price_category":null}]

💡 Hint

Think about the price ranges and how CASE matches conditions in order.

The CASE expression checks conditions in order. For product_id 1, price 30 is less than 50, so 'Cheap'. For product_id 2, price 75 is between 50 and 100, so 'Moderate'. For product_id 3, price 150 is greater than 100, so 'Expensive'.

📝 Syntax

intermediate

Identify the syntax error in CASE expression

Which option contains a syntax error in the CASE expression?

SELECT
  employee_id,
  CASE salary
    WHEN > 50000 THEN 'High'
    WHEN <= 50000 THEN 'Low'
  END AS salary_level
FROM employees;

A CASE WHEN salary > 50000 THEN 'High' WHEN salary <= 50000 THEN 'Low' END

B CASE salary WHEN > 50000 THEN 'High' WHEN <= 50000 THEN 'Low' END

C CASE salary WHEN 50000 THEN 'Equal' ELSE 'Other' END

D CASE WHEN salary = 50000 THEN 'Equal' ELSE 'Other' END

💡 Hint

Check how the simple CASE expression compares values.

In option B, the syntax 'WHEN > 50000' is invalid because simple CASE compares the expression to a value, not a condition. Conditions require the searched CASE syntax as in option B.

❓ query_result

advanced

Output of nested CASE expressions

What is the output of this query?

SELECT
  order_id,
  CASE
    WHEN status = 'shipped' THEN
      CASE
        WHEN shipped_date <= order_date + INTERVAL '3 days' THEN 'On time'
        ELSE 'Late'
      END
    ELSE 'Pending'
  END AS delivery_status
FROM orders
ORDER BY order_id
LIMIT 3;

PostgreSQL

CREATE TEMP TABLE orders (order_id INT, status TEXT, order_date DATE, shipped_date DATE);
INSERT INTO orders VALUES
(1, 'shipped', '2024-01-01', '2024-01-03'),
(2, 'shipped', '2024-01-01', '2024-01-06'),
(3, 'processing', '2024-01-01', NULL);
SELECT
  order_id,
  CASE
    WHEN status = 'shipped' THEN
      CASE
        WHEN shipped_date <= order_date + INTERVAL '3 days' THEN 'On time'
        ELSE 'Late'
      END
    ELSE 'Pending'
  END AS delivery_status
FROM orders
ORDER BY order_id
LIMIT 3;

A [{"order_id":1,"delivery_status":"Pending"},{"order_id":2,"delivery_status":"Pending"},{"order_id":3,"delivery_status":"Pending"}]

B [{"order_id":1,"delivery_status":null},{"order_id":2,"delivery_status":null},{"order_id":3,"delivery_status":null}]

C [{"order_id":1,"delivery_status":"Late"},{"order_id":2,"delivery_status":"On time"},{"order_id":3,"delivery_status":"Pending"}]

D [{"order_id":1,"delivery_status":"On time"},{"order_id":2,"delivery_status":"Late"},{"order_id":3,"delivery_status":"Pending"}]

💡 Hint

Check the nested CASE logic and date comparisons carefully.

Order 1 shipped within 2 days (<= 3 days), so 'On time'. Order 2 shipped after 5 days (> 3 days), so 'Late'. Order 3 is not shipped, so 'Pending'.

🔧 Debug

advanced

Find the logical error in CASE expression

The following query is intended to classify ages into groups but produces incorrect results. What is the logical error?

SELECT
  person_id,
  CASE
    WHEN age > 18 THEN 'Adult'
    WHEN age > 12 THEN 'Teen'
    ELSE 'Child'
  END AS age_group
FROM people;

A The order of WHEN conditions causes all ages above 12 to be classified as 'Adult'.

B The ELSE clause should be first to catch all ages.

C The CASE expression is missing an END keyword.

D The age comparisons should use BETWEEN instead of >.

💡 Hint

Think about how CASE evaluates conditions in order.

Since WHEN age > 18 comes first, ages above 18 are 'Adult'. But ages between 13 and 18 also satisfy age > 12, but that condition is never reached because the first WHEN is checked first. The order should be reversed to check smaller ranges first.

❓ optimization

expert

Optimize CASE expression for performance

Which option is the most efficient way to write this CASE expression for a large table?

SELECT
  user_id,
  CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    WHEN score >= 70 THEN 'C'
    WHEN score >= 60 THEN 'D'
    ELSE 'F'
  END AS grade
FROM exam_results;

A Use nested CASE expressions for each grade level.

B Use multiple OR conditions in a single WHEN clause to reduce checks.

C Use a derived table with ranges and join on score to get grade.

D Use a CASE expression with ordered WHEN conditions as shown.

💡 Hint

Consider how indexing and joins can improve performance over many CASE checks.

Using a derived table with score ranges and joining on score allows the database to use indexes and reduces repeated condition checks, improving performance on large datasets compared to multiple CASE WHEN checks.

Practice

1. What does the CASE expression do in PostgreSQL?

easy

A. Returns different results based on conditions

B. Creates a new table

C. Deletes rows from a table

D. Updates data in a table

Solution

1. Step 1: Understand the purpose of CASE

   The CASE expression evaluates conditions and returns a result based on which condition is true.

2. Step 2: Compare with other SQL commands

   Unlike CREATE, DELETE, or UPDATE, CASE does not change data but returns values conditionally.

3. Final Answer:

   Returns different results based on conditions -> Option A

4. Quick Check:

   CASE = conditional results [OK]

Hint: CASE returns values based on conditions, like if-then-else [OK]

Common Mistakes:

• Thinking CASE modifies data
• Confusing CASE with table operations
• Ignoring ELSE for default results

2. Which of the following is the correct syntax to start a CASE expression in PostgreSQL?

easy

A. CASE IF condition THEN result ELSE default END

B. CASE WHEN condition THEN result ELSE default END

C. CASE condition THEN result ELSE default

D. CASE WHEN condition THEN result ELSE default

Solution

1. Step 1: Recall correct CASE syntax

   The correct syntax starts with CASE, followed by WHEN conditions, THEN results, optional ELSE, and ends with END.

2. Step 2: Identify syntax errors in options

   The syntax with 'CASE IF ... END' uses 'IF' instead of 'WHEN'. Syntaxes omitting 'END' (such as 'CASE WHEN ... default' or 'CASE condition ...') are invalid. Only the full syntax is correct.

3. Final Answer:

   CASE WHEN condition THEN result ELSE default END -> Option B

4. Quick Check:

   CASE syntax requires WHEN and END [OK]

Hint: CASE always ends with END and uses WHEN for conditions [OK]

Common Mistakes:

• Using IF instead of WHEN
• Forgetting END at the end
• Omitting ELSE clause

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

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

medium

A. A list of salaries with labels 'High' or 'Low' based on salary value

B. Only salaries greater than 5000

C. Only salaries less than or equal to 5000

D. An error because ELSE is missing

Solution

1. Step 1: Understand the CASE condition

   The CASE checks if salary is greater than 5000; if true, returns 'High', else 'Low'.

2. Step 2: Analyze the SELECT output

   The query returns all salaries with a new column showing 'High' or 'Low' labels accordingly.

3. Final Answer:

   A list of salaries with labels 'High' or 'Low' based on salary value -> Option A

4. Quick Check:

   CASE labels salaries as High or Low [OK]

Hint: CASE adds labels based on conditions without filtering rows [OK]

Common Mistakes:

• Thinking CASE filters rows
• Assuming ELSE is mandatory (it is here but present)
• Confusing output with WHERE clause filtering

4. Identify the error in this CASE expression:

SELECT name, CASE WHEN age > 18 THEN 'Adult' WHEN age < 18 THEN 'Minor' END AS age_group FROM users;

medium

A. Syntax error in SELECT statement

B. Incorrect use of THEN keyword

C. Missing END keyword to close CASE

D. Missing ELSE clause for age = 18

Solution

1. Step 1: Check CASE conditions coverage

   The CASE covers age > 18 and age < 18 but misses age = 18 case.

2. Step 2: Understand ELSE role

   Without ELSE, age = 18 returns NULL. Adding ELSE handles this missing case.

3. Final Answer:

   Missing ELSE clause for age = 18 -> Option D

4. Quick Check:

   CASE needs ELSE for unmatched conditions [OK]

Hint: Add ELSE to handle all unmatched cases in CASE [OK]

Common Mistakes:

• Assuming CASE covers all values without ELSE
• Confusing END keyword placement
• Misusing THEN keyword

5. You want to categorize products by price into 'Cheap' (price < 20), 'Moderate' (20 ≤ price < 100), and 'Expensive' (price ≥ 100). Which CASE expression correctly implements this?

hard

A. CASE WHEN price < 20 THEN 'Cheap' WHEN price >= 20 AND price <= 100 THEN 'Moderate' ELSE 'Expensive' END

B. CASE WHEN price < 20 THEN 'Cheap' WHEN price > 20 AND price < 100 THEN 'Moderate' ELSE 'Expensive' END

C. CASE WHEN price < 20 THEN 'Cheap' WHEN price >= 20 AND price < 100 THEN 'Moderate' ELSE 'Expensive' END

D. CASE WHEN price <= 20 THEN 'Cheap' WHEN price >= 20 AND price < 100 THEN 'Moderate' ELSE 'Expensive' END

Solution

1. Step 1: Analyze price ranges for categories

   'Cheap' is price less than 20, 'Moderate' is price from 20 up to but not including 100, 'Expensive' is 100 or more.

2. Step 2: Check each option's conditions

   CASE WHEN price < 20 THEN 'Cheap' WHEN price >= 20 AND price < 100 THEN 'Moderate' ELSE 'Expensive' END correctly uses < 20, then >= 20 and < 100, else 'Expensive'. CASE WHEN price < 20 THEN 'Cheap' WHEN price > 20 AND price < 100 THEN 'Moderate' ELSE 'Expensive' END misses price = 20 in 'Moderate'. CASE WHEN price <= 20 THEN 'Cheap' WHEN price >= 20 AND price < 100 THEN 'Moderate' ELSE 'Expensive' END overlaps at 20 in both 'Cheap' and 'Moderate'. CASE WHEN price < 20 THEN 'Cheap' WHEN price >= 20 AND price <= 100 THEN 'Moderate' ELSE 'Expensive' END includes 100 in 'Moderate' but should be 'Expensive'.

3. Final Answer:

   CASE WHEN price < 20 THEN 'Cheap' WHEN price >= 20 AND price < 100 THEN 'Moderate' ELSE 'Expensive' END -> Option C

4. Quick Check:

   Use inclusive and exclusive ranges carefully in CASE [OK]

Hint: Use >= and < carefully to avoid overlapping ranges [OK]

Common Mistakes:

• Overlapping or missing ranges
• Using > instead of >= causing gaps
• Misplacing ELSE category boundaries