SQL Exercises

SQL CASE WHEN Exercises (With Answers)

8 Exercises
~40 min
4 Intermediate4 Advanced

Exercise 1

intermediate

Question

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

Table Schema

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

Solution

SQL
1SELECT 
2  name,
3  salary,
4  CASE 
5    WHEN salary < 40000 THEN 'Low'
6    WHEN salary BETWEEN 40000 AND 70000 THEN 'Medium'
7    ELSE 'High'
8  END AS salary_category
9FROM 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 1

intermediate

Question

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

Table Schema

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

Solution

SQL
1SELECT 
2  name,
3  salary,
4  CASE 
5    WHEN salary < 40000 THEN 'Low'
6    WHEN salary BETWEEN 40000 AND 70000 THEN 'Medium'
7    ELSE 'High'
8  END AS salary_category
9FROM 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 1

intermediate

Question

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

Table Schema

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

Solution

SQL
1SELECT 
2  name,
3  salary,
4  CASE 
5    WHEN salary < 40000 THEN 'Low'
6    WHEN salary BETWEEN 40000 AND 70000 THEN 'Medium'
7    ELSE 'High'
8  END AS salary_category
9FROM 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 1

intermediate

Question

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

Table Schema

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

Solution

SQL
1SELECT 
2  name,
3  salary,
4  CASE 
5    WHEN salary < 40000 THEN 'Low'
6    WHEN salary BETWEEN 40000 AND 70000 THEN 'Medium'
7    ELSE 'High'
8  END AS salary_category
9FROM 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

advanced

Question

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

Table Schema

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

Solution

SQL
1SELECT 
2  COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending_count,
3  COUNT(CASE WHEN status = 'shipped' THEN 1 END) AS shipped_count,
4  COUNT(CASE WHEN status = 'delivered' THEN 1 END) AS delivered_count
5FROM 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.

Exercise 2

advanced

Question

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

Table Schema

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

Solution

SQL
1SELECT 
2  COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending_count,
3  COUNT(CASE WHEN status = 'shipped' THEN 1 END) AS shipped_count,
4  COUNT(CASE WHEN status = 'delivered' THEN 1 END) AS delivered_count
5FROM 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.

Exercise 2

advanced

Question

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

Table Schema

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

Solution

SQL
1SELECT 
2  COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending_count,
3  COUNT(CASE WHEN status = 'shipped' THEN 1 END) AS shipped_count,
4  COUNT(CASE WHEN status = 'delivered' THEN 1 END) AS delivered_count
5FROM 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.

Exercise 2

advanced

Question

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

Table Schema

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

Solution

SQL
1SELECT 
2  COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending_count,
3  COUNT(CASE WHEN status = 'shipped' THEN 1 END) AS shipped_count,
4  COUNT(CASE WHEN status = 'delivered' THEN 1 END) AS delivered_count
5FROM 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.

Related Content

Continue Learning

exerciseMySQL: SQL CASE WHEN Exercises (With Answers)
exerciseSQL Server: SQL CASE WHEN Exercises (With Answers)
exercisePostgreSQL: SQL CASE WHEN Exercises (With Answers)
exerciseSQL SELECT Statement Exercises (With Answers)
exerciseSQL WHERE Clause Exercises (With Answers)

From Our Blog

The SQL Interview ToolkitSQL Interviews: What Companies Actually Test

Ready for more practice?

Join SQL Mastery and get access to interactive exercises, quizzes, and more.