Challenge - 5 Problems
CASE Mastery Badge
Get all challenges correct to earn this badge!
Test your skills under time pressure!
❓ query_result
intermediate2:00remaining
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);
Attempts:
2 left
💡 Hint
Remember the CASE checks conditions in order and stops at the first true condition.
✗ Incorrect
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
intermediate1:30remaining
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;
Attempts:
2 left
💡 Hint
Check the syntax of CASE with and without an expression after CASE keyword.
✗ Incorrect
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
advanced2:30remaining
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');
Attempts:
2 left
💡 Hint
Look carefully at the nested CASE inside the WHEN status = 'pending' condition.
✗ Incorrect
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
advanced1:30remaining
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?Attempts:
2 left
💡 Hint
Think about the order of conditions and how SQL evaluates CASE WHEN clauses.
✗ Incorrect
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
expert3:00remaining
Debug the unexpected output from CASE in SELECT
A developer wrote this query:
But the output shows 'Poor' for a score of 85. What is the most likely cause?
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?
Attempts:
2 left
💡 Hint
Think about how data types affect comparison operators in SQL.
✗ Incorrect
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'.