Simple CASE syntax in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of Simple CASE with numeric grades

Given the table Grades with a column score containing numeric values, what is the output of this query?

SELECT score, CASE score WHEN 90 THEN 'A' WHEN 80 THEN 'B' ELSE 'F' END AS grade FROM Grades;

SQL

CREATE TABLE Grades(score INT);
INSERT INTO Grades VALUES (90), (80), (70);

A [{"score":90,"grade":"A"},{"score":80,"grade":"B"},{"score":70,"grade":"F"}]

B [{"score":90,"grade":"A"},{"score":80,"grade":"F"},{"score":70,"grade":"F"}]

C [{"score":90,"grade":"F"},{"score":80,"grade":"B"},{"score":70,"grade":"F"}]

D [{"score":90,"grade":"A"},{"score":80,"grade":"B"},{"score":70,"grade":"B"}]

💡 Hint

Simple CASE compares the expression to each WHEN value exactly.

The CASE expression compares the score to each WHEN value. 90 matches 'A', 80 matches 'B', and 70 matches none, so ELSE 'F' applies.

🧠 Conceptual

intermediate

Understanding Simple CASE vs Searched CASE

Which statement correctly describes the difference between Simple CASE and Searched CASE expressions in SQL?

A Simple CASE requires ELSE clause; Searched CASE does not.

B Simple CASE evaluates multiple conditions independently; Searched CASE compares one expression to multiple values.

C Simple CASE can only be used with numeric values; Searched CASE only with strings.

D Simple CASE compares one expression to multiple values; Searched CASE evaluates multiple conditions independently.

💡 Hint

Think about how the CASE expression compares or evaluates conditions.

Simple CASE compares one expression to multiple values. Searched CASE evaluates independent conditions in WHEN clauses.

📝 Syntax

advanced

Identify the syntax error in this Simple CASE query

Which option contains a syntax error in the Simple CASE expression?

SQL

SELECT id, CASE WHEN status = 'active' THEN 'A' WHEN status = 'inactive' THEN 'I' ELSE 'U' END AS state FROM Users;

A SELECT id, CASE status WHEN 'active' THEN 'A' WHEN 'inactive' THEN 'I' ELSE 'U' END AS state FROM Users;

B SELECT id, CASE status WHEN 'active' THEN 'A' WHEN 'inactive' THEN 'I' ELSE 'U' END state FROM Users;

C ;sresU MORF etats SA DNE 'U' ESLE 'I' NEHT 'evitcani' NEHW 'A' NEHT 'evitca' NEHW sutats ESAC ,di TCELES

D ELECT id, CASE status WHEN 'active' THEN 'A' WHEN 'inactive' THEN 'I' ELSE 'U' END AS state FROM Users;

💡 Hint

Check the FROM clause syntax carefully.

Option A is missing the FROM keyword before the table name, causing a syntax error.

❓ optimization

advanced

Optimizing Simple CASE for performance

You have a large table with a column category containing values 'A', 'B', 'C', or others. Which Simple CASE query is most efficient to categorize rows as 'Group1' for 'A' and 'B', 'Group2' for 'C', and 'Other' otherwise?

A SELECT category, CASE WHEN category IN ('A','B') THEN 'Group1' WHEN category = 'C' THEN 'Group2' ELSE 'Other' END AS group_name FROM table;

B SELECT category, CASE category WHEN 'A' THEN 'Group1' WHEN 'B' THEN 'Group1' WHEN 'C' THEN 'Group2' ELSE 'Other' END AS group_name FROM table;

C SELECT category, CASE category WHEN 'A' THEN 'Group1' WHEN 'C' THEN 'Group2' ELSE 'Other' END AS group_name FROM table;

D SELECT category, CASE category WHEN 'A' THEN 'Group1' WHEN 'B' THEN 'Group2' WHEN 'C' THEN 'Group2' ELSE 'Other' END AS group_name FROM table;

💡 Hint

Simple CASE compares one expression to values; using multiple WHENs for same output is valid.

Option B uses Simple CASE correctly with multiple WHENs for 'Group1'. Option B uses Searched CASE, which may be less efficient. Options C and D misclassify or omit 'B'.

🔧 Debug

expert

Debugging unexpected output in Simple CASE

Given this query:

SELECT id, CASE status WHEN 'active' THEN 'Active' WHEN 'inactive' THEN 'Inactive' ELSE 'Unknown' END AS state FROM Users;

Why might rows with status = NULL show 'Unknown' instead of NULL?

A Because NULL is treated as a string 'NULL' in Simple CASE, matching ELSE.

B Because NULL values are automatically converted to 'Unknown' by the database engine.

C Because Simple CASE compares using equality, NULL never matches any WHEN value, so ELSE applies.

D Because the query syntax is invalid and defaults to ELSE.

💡 Hint

Think about how NULL behaves in comparisons.

NULL is not equal to any value, so Simple CASE never matches WHEN clauses for NULL, resulting in ELSE output.