SQL Server: SQL CASE WHEN Exercises (With Answers)

This guide is specifically for SQL Server syntax.

Learn SQL CASE WHEN with hands-on practice exercises. Each exercise includes the table schema, solution query, expected output, and detailed explanation.

What You'll Practice

These exercises cover CASE WHEN from basic to advanced concepts. Work through each problem to build your SQL skills.

Exercise 1

Question: Categorize employees by salary range (Low, Medium, High).

Table Schema

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  salary DECIMAL(10,2)
);

Solution

SELECT 
  name,
  salary,
  CASE 
    WHEN salary < 40000 THEN 'Low'
    WHEN salary BETWEEN 40000 AND 70000 THEN 'Medium'
    ELSE 'High'
  END AS salary_category
FROM employees;

Expected Output

| name | salary | salary_category | |---|---|---| | John | 35000 | Low | | Alice | 80000 | High |

Explanation

CASE WHEN provides if-then-else logic in SQL. ELSE handles all unmatched conditions.

Exercise 2

Question: Count orders by status (Pending, Shipped, Delivered).

Table Schema

CREATE TABLE orders (
  id INT,
  status VARCHAR(20)
);

Solution

SELECT 
  COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending_count,
  COUNT(CASE WHEN status = 'shipped' THEN 1 END) AS shipped_count,
  COUNT(CASE WHEN status = 'delivered' THEN 1 END) AS delivered_count
FROM orders;

Expected Output

| pending_count | shipped_count | delivered_count | |---|---|---| | 15 | 23 | 42 |

Explanation

CASE inside aggregate functions allows pivoting data. This turns row values into columns.

Common Mistakes to Avoid

• Forgetting to use proper syntax for CASE WHEN
• Not considering NULL values in comparisons
• Confusing similar operators or clauses

Related SQL Exercises

Continue practicing with these related topics:

SQL Server-Specific Notes

This page covers SQL Server syntax. Other databases may have different syntax for similar operations.