CASE in ORDER BY in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to order the results by the CASE expression.

SQL

SELECT name, score FROM players ORDER BY CASE WHEN score > 50 THEN [1] ELSE score END;

Drag options to blanks, or click blank then click option'

A1

B0

Cname

Dscore

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'score' inside the CASE instead of a constant value.

Returning 1 for high scores which orders them after others.

The CASE expression returns 0 when score > 50, so ordering by this puts those players first.

2fill in blank

medium

Complete the code to order by priority using CASE, putting 'urgent' first.

SQL

SELECT task, priority FROM tasks ORDER BY CASE priority WHEN 'urgent' THEN [1] ELSE 2 END;

Drag options to blanks, or click blank then click option'

A5

B3

C1

D0

Attempts:

3 left

💡 Hint

Common Mistakes

Using a higher number like 5 which orders 'urgent' last.

Using 0 which might be confusing but is valid; here 1 is chosen for clarity.

Assigning 1 to 'urgent' tasks makes them appear before others when ordered ascending.

3fill in blank

hard

Fix the error in the CASE expression inside ORDER BY to sort by status.

SQL

SELECT id, status FROM orders ORDER BY CASE status WHEN 'shipped' THEN [1] WHEN 'pending' THEN 2 ELSE 3 END;

Drag options to blanks, or click blank then click option'

A1

B'1'

Cstatus

D'shipped'

Attempts:

3 left

💡 Hint

Common Mistakes

Returning strings like '1' instead of numeric 1.

Returning the column name instead of a value.

The CASE expression must return a number, not a string, to order correctly.

4fill in blank

hard

Fill both blanks to order by category priority and then by name alphabetically.

SQL

SELECT product, category FROM inventory ORDER BY CASE category WHEN 'electronics' THEN [1] WHEN 'furniture' THEN [2] ELSE 3 END, name;

Drag options to blanks, or click blank then click option'

A1

B2

C3

D0

Attempts:

3 left

💡 Hint

Common Mistakes

Assigning the same number to both categories causing no priority difference.

Using numbers that do not reflect intended priority order.

Assigning 1 to 'electronics' and 2 to 'furniture' orders electronics first, then furniture, then others.

5fill in blank

hard

Fill all three blanks to order employees by role priority, then by last name ascending.

SQL

SELECT employee_id, role, last_name FROM employees ORDER BY CASE role WHEN 'manager' THEN [1] WHEN 'developer' THEN [2] ELSE [3] END, last_name;

Drag options to blanks, or click blank then click option'

A1

B2

C3

D0

Attempts:

3 left

💡 Hint

Common Mistakes

Assigning higher numbers to managers which lowers their priority.

Using the same number for all roles causing no ordering.

Assigning 0 to 'manager' makes them highest priority, 2 to 'developer', and 3 to others.

Practice

1. What does the CASE statement do when used inside an ORDER BY clause in SQL?

easy

A. It filters rows before sorting them.

B. It allows sorting rows based on custom conditions you define.

C. It changes the data type of the sorted column.

D. It groups rows before sorting.

Solution

1. Step 1: Understand the role of CASE in ORDER BY

   The CASE statement inside ORDER BY assigns sorting values conditionally to each row.

2. Step 2: Differentiate from filtering or grouping

   CASE does not filter or group rows; it only affects sorting order.

3. Final Answer:

   It allows sorting rows based on custom conditions you define. -> Option B

4. Quick Check:

   CASE in ORDER BY = custom sorting [OK]

Hint: CASE in ORDER BY controls sorting order by conditions [OK]

Common Mistakes:

• Thinking CASE filters rows
• Assuming CASE changes data types
• Confusing CASE with GROUP BY

2. Which of the following is the correct syntax to use CASE inside an ORDER BY clause?

easy

A. ORDER BY CASE WHEN condition THEN value ELSE value END

B. ORDER BY IF condition THEN value ELSE value END

C. ORDER BY SWITCH condition THEN value ELSE value END

D. ORDER BY SELECT CASE condition THEN value ELSE value END

Solution

1. Step 1: Recall correct CASE syntax in ORDER BY

   The correct syntax uses CASE WHEN ... THEN ... ELSE ... END without extra keywords.

2. Step 2: Identify incorrect keywords

   Options A, B, and C use invalid keywords like SELECT, IF, or SWITCH which are not valid in ORDER BY CASE.

3. Final Answer:

   ORDER BY CASE WHEN condition THEN value ELSE value END -> Option A

4. Quick Check:

   CASE syntax = WHEN THEN ELSE END [OK]

Hint: Use CASE WHEN ... THEN ... ELSE ... END in ORDER BY [OK]

Common Mistakes:

• Using IF instead of CASE
• Adding SELECT inside ORDER BY
• Using SWITCH keyword

3. Given the table Tasks with column priority having values 'High', 'Medium', 'Low', what will the query below return?

SELECT priority FROM Tasks ORDER BY CASE priority WHEN 'High' THEN 1 WHEN 'Medium' THEN 2 ELSE 3 END;

medium

A. Rows sorted with 'High' first, then 'Medium', then 'Low'.

B. Rows sorted alphabetically by priority.

C. Rows sorted with 'Low' first, then 'Medium', then 'High'.

D. Syntax error due to CASE in ORDER BY.

Solution

1. Step 1: Understand CASE assigns sorting values

   CASE assigns 1 to 'High', 2 to 'Medium', and 3 to others (like 'Low').

2. Step 2: ORDER BY sorts ascending by these values

   Rows with priority 'High' come first, then 'Medium', then 'Low'.

3. Final Answer:

   Rows sorted with 'High' first, then 'Medium', then 'Low'. -> Option A

4. Quick Check:

   CASE assigns order numbers = sorted by priority [OK]

Hint: CASE assigns numbers to priorities to sort custom order [OK]

Common Mistakes:

• Assuming alphabetical sort
• Thinking CASE causes syntax error
• Confusing order direction

4. Identify the error in this SQL query:

SELECT name FROM Employees ORDER BY CASE WHEN department = 'Sales' THEN 1 WHEN department = 'HR' THEN 2 ELSE 3;

medium

A. ORDER BY cannot use CASE statements.

B. Incorrect use of ELSE in CASE.

C. Missing END keyword to close CASE statement.

D. Missing THEN keyword after conditions.

Solution

1. Step 1: Check CASE syntax completeness

   The CASE statement must end with the keyword END to close it.

2. Step 2: Identify missing END keyword

   The query lacks END after ELSE 3, causing syntax error.

3. Final Answer:

   Missing END keyword to close CASE statement. -> Option C

4. Quick Check:

   CASE must end with END [OK]

Hint: Always close CASE with END keyword [OK]

Common Mistakes:

• Omitting END keyword
• Misplacing ELSE clause
• Thinking CASE not allowed in ORDER BY

5. You want to sort products so that 'Electronics' appear first, 'Clothing' second, and all others last. Which ORDER BY clause using CASE achieves this?

hard

A. ORDER BY CASE category WHEN 'Clothing' THEN 1 WHEN 'Electronics' THEN 2 ELSE 3 END

B. ORDER BY CASE WHEN category = 'Electronics' THEN 3 WHEN category = 'Clothing' THEN 2 ELSE 1 END

C. ORDER BY category = 'Electronics' DESC, category = 'Clothing' DESC

D. ORDER BY CASE category WHEN 'Electronics' THEN 1 WHEN 'Clothing' THEN 2 ELSE 3 END

Solution

1. Step 1: Assign lowest number to 'Electronics' for first position

   ORDER BY CASE category WHEN 'Electronics' THEN 1 WHEN 'Clothing' THEN 2 ELSE 3 END assigns 1 to 'Electronics', 2 to 'Clothing', and 3 to others, sorting correctly.

2. Step 2: Check other options for order correctness

   ORDER BY CASE WHEN category = 'Electronics' THEN 3 WHEN category = 'Clothing' THEN 2 ELSE 1 END reverses order numbers, ORDER BY CASE category WHEN 'Clothing' THEN 1 WHEN 'Electronics' THEN 2 ELSE 3 END swaps categories, ORDER BY category = 'Electronics' DESC, category = 'Clothing' DESC uses boolean sorting which may not guarantee order.

3. Final Answer:

   ORDER BY CASE category WHEN 'Electronics' THEN 1 WHEN 'Clothing' THEN 2 ELSE 3 END -> Option D

4. Quick Check:

   Lowest number = highest priority in CASE [OK]

Hint: Assign 1 to highest priority category in CASE [OK]

Common Mistakes:

• Swapping priority numbers
• Using boolean expressions without CASE
• Forgetting ELSE clause