CASE in SELECT for computed columns in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of CASE in SELECT for grade classification

Given the table Students with columns id, name, and score, what is the output of this query?

SELECT name,
 CASE
   WHEN score >= 90 THEN 'A'
   WHEN score >= 80 THEN 'B'
   WHEN score >= 70 THEN 'C'
   ELSE 'F'
 END AS grade
FROM Students
ORDER BY id;

SQL

CREATE TABLE Students (id INT, name VARCHAR(20), score INT);
INSERT INTO Students VALUES (1, 'Alice', 85), (2, 'Bob', 92), (3, 'Charlie', 68);

A [{"name": "Alice", "grade": "B"}, {"name": "Bob", "grade": "A"}, {"name": "Charlie", "grade": "F"}]

B [{"name": "Alice", "grade": "C"}, {"name": "Bob", "grade": "B"}, {"name": "Charlie", "grade": "F"}]

C [{"name": "Alice", "grade": "B"}, {"name": "Bob", "grade": "A"}, {"name": "Charlie", "grade": "C"}]

D [{"name": "Alice", "grade": "A"}, {"name": "Bob", "grade": "A"}, {"name": "Charlie", "grade": "F"}]

💡 Hint

Remember the CASE checks conditions in order and stops at the first true condition.

The CASE expression checks score >= 90 first, then >= 80, then >= 70. Alice has 85, so grade B; Bob has 92, grade A; Charlie has 68, which is less than 70, so grade F.

📝 Syntax

intermediate

Identify the syntax error in CASE usage

Which option contains a syntax error in the CASE expression used in a SELECT statement?

SQL

SELECT id, name, CASE score WHEN score >= 90 THEN 'A' ELSE 'F' END AS grade FROM Students;

A SELECT id, name, CASE score WHEN score >= 90 THEN 'A' ELSE 'F' END AS grade FROM Students;

B SELECT id, name, CASE WHEN score >= 90 THEN 'A' ELSE 'F' END AS grade FROM Students;

C SELECT id, name, CASE WHEN score >= 90 THEN 'A' ELSE 'F' END grade FROM Students;

D ;stnedutS MORF edarg SA DNE 'F' ESLE 'A' NEHT 09 => erocs NEHW ESAC ,eman ,di TCELES

💡 Hint

Check the syntax of CASE with and without an expression after CASE keyword.

Option A uses CASE with an expression (score) but then uses WHEN with a condition (score >= 90), which is invalid syntax. When CASE has an expression, WHEN must compare to values, not conditions.

❓ query_result

advanced

Result of nested CASE expressions in SELECT

Consider the table Orders with columns order_id, amount, and status. What is the output of this query?

SELECT order_id,
 CASE
   WHEN status = 'shipped' THEN 'Completed'
   WHEN status = 'pending' THEN CASE WHEN amount > 100 THEN 'High Pending' ELSE 'Low Pending' END
   ELSE 'Unknown'
 END AS order_status
FROM Orders
ORDER BY order_id;

SQL

CREATE TABLE Orders (order_id INT, amount INT, status VARCHAR(10));
INSERT INTO Orders VALUES (1, 150, 'pending'), (2, 50, 'pending'), (3, 200, 'shipped'), (4, 30, 'cancelled');

A [{"order_id":1,"order_status":"Low Pending"},{"order_id":2,"order_status":"High Pending"},{"order_id":3,"order_status":"Completed"},{"order_id":4,"order_status":"Unknown"}]

B [{"order_id":1,"order_status":"High Pending"},{"order_id":2,"order_status":"Low Pending"},{"order_id":3,"order_status":"Unknown"},{"order_id":4,"order_status":"Unknown"}]

C [{"order_id":1,"order_status":"Completed"},{"order_id":2,"order_status":"Low Pending"},{"order_id":3,"order_status":"Completed"},{"order_id":4,"order_status":"Unknown"}]

D [{"order_id":1,"order_status":"High Pending"},{"order_id":2,"order_status":"Low Pending"},{"order_id":3,"order_status":"Completed"},{"order_id":4,"order_status":"Unknown"}]

💡 Hint

Look carefully at the nested CASE inside the WHEN status = 'pending' condition.

For order_id 1, status is 'pending' and amount 150 > 100, so 'High Pending'. For order_id 2, status 'pending' and amount 50 <= 100, so 'Low Pending'. order_id 3 is 'shipped' so 'Completed'. order_id 4 status is 'cancelled', so 'Unknown'.

❓ optimization

advanced

Optimizing CASE expressions for performance

Which option is the most efficient way to write a CASE expression that assigns 'Adult' if age >= 18, 'Teen' if age between 13 and 17, and 'Child' otherwise, assuming the age column is indexed?

A CASE WHEN age BETWEEN 13 AND 17 THEN 'Teen' WHEN age >= 18 THEN 'Adult' ELSE 'Child' END

B CASE age WHEN 18 THEN 'Adult' WHEN 13 THEN 'Teen' ELSE 'Child' END

C CASE WHEN age >= 18 THEN 'Adult' WHEN age >= 13 THEN 'Teen' ELSE 'Child' END

D CASE WHEN age > 18 THEN 'Adult' WHEN age >= 13 THEN 'Teen' ELSE 'Child' END

💡 Hint

Think about the order of conditions and how SQL evaluates CASE WHEN clauses.

Option C checks age >= 18 first, which is the broadest condition for adults, then age >= 13 for teens, else child. This order avoids unnecessary checks and uses simple comparisons. Option C checks teens before adults, which can cause more comparisons. Option C uses CASE with expression but compares exact values, which is incorrect for ranges. Option C uses > 18 which excludes age 18 from adults.

🔧 Debug

expert

Debug the unexpected output from CASE in SELECT

A developer wrote this query:

SELECT id,
 CASE WHEN score > 90 THEN 'Excellent'
 WHEN score > 80 THEN 'Good'
 WHEN score > 70 THEN 'Average'
 ELSE 'Poor'
 END AS rating
FROM Results;

But the output shows 'Poor' for a score of 85. What is the most likely cause?

A The ELSE clause is overriding all previous WHEN conditions.

B The score column is stored as text, so comparisons are lexicographical, not numeric.

C The query is missing an ORDER BY clause, causing wrong results.

D The CASE expression syntax is invalid and defaults to ELSE for all rows.

💡 Hint

Think about how data types affect comparison operators in SQL.

If score is stored as text, '85' > '90' is false because string comparison compares character by character. So '85' is considered less than '90', causing the CASE to fall through to ELSE 'Poor'.