Example 1: CASE in SELECT — Salary Categories

We’ll categorize employees based on salary range.

SELECT
name,
department,
salary,
CASE
WHEN salary >= 90000 THEN ‘High’
WHEN salary BETWEEN 60000 AND 89999 THEN ‘Medium’
ELSE ‘Low’
END AS salary_level
FROM employees;

Result:

name	department	salary	salary_level
Alice	IT	95000	High
Bob	HR	65000	Medium
Carol	IT	55000	Low
David	Finance	75000	Medium
Eva	Marketing	45000	Low

Explanation:
Each row is evaluated, and a new derived column (salary_level) is added based on the salary.

Example 2: CASE in ORDER BY — Custom Sort

Sort employees by salary_level, but in Low → Medium → High order (not alphabetically).

SELECT
name,
department,
salary
FROM employees
ORDER BY
CASE
WHEN salary >= 90000 THEN 3 — High
WHEN salary BETWEEN 60000 AND 89999 THEN 2 — Medium
ELSE 1 — Low
END;

Result (sorted):

name	department	salary
Eva	Marketing	45000
Carol	IT	55000
Bob	HR	65000
David	Finance	75000
Alice	IT	95000

Explanation:
Instead of sorting alphabetically, we map each range to a numeric rank using CASE.

Example 3: CASE in UPDATE — Conditional Raise

Give different salary raises based on department.

UPDATE employees
SET salary =
CASE
WHEN department = ‘IT’ THEN salary * 1.10
WHEN department = ‘HR’ THEN salary * 1.05
ELSE salary * 1.03
END;

Explanation:

IT employees get a 10% raise.

HR employees get a 5% raise.

Everyone else gets 3%.

Example 4: CASE with Aggregate Functions (SUM)

Suppose we have a sales table:

We can compute conditional totals using SUM(CASE...).

Result:

Explanation:

• Each CASE filters amounts by region inside the aggregate.

• This is like pivoting data horizontally.

---

Example 5: Nested CASE

Combine multiple CASE layers for complex rules:

Result:

Explanation:
The outer CASE checks salary, and the inner one refines classification for IT staff.

---

Summary SQL CASE Statement:

SELECT
region,
SUM(amount) AS total_sales,
SUM(CASE WHEN product = ‘Laptop’ AND amount > 1000 THEN amount ELSE 0 END) AS premium_laptop_sales,
SUM(CASE WHEN product = ‘Phone’ AND amount < 700 THEN amount ELSE 0 END) AS budget_phone_sales
FROM sales
GROUP BY region;

Output:

region	total_sales	premium_laptop_sales	budget_phone_sales
East	8300.00	3800.00	1350.00
West	5550.00	2300.00	0.00
South	2270.00	700.00	900.00
North	3400.00	2000.00	450.00

3. Using CASE with Window Functions

Question:
Show each salesperson’s total sales and flag the top performer per region.

SELECT
region,
salesperson,
SUM(amount) AS total_sales,
CASE
WHEN SUM(amount) = MAX(SUM(amount)) OVER (PARTITION BY region)
THEN ‘Top Performer’
ELSE ”
END AS performance_flag
FROM sales
GROUP BY region, salesperson;

Output:

region	salesperson	total_sales	performance_flag
East	Alice	4700.00	Top Performer
East	Frank	2300.00
East	Carol	800.00
West	Dave	2300.00	Top Performer
West	Bob	1700.00
West	Gina	750.00
South	Eve	1720.00	Top Performer
South	Hank	550.00
North	Ivy	3400.00	Top Performer

4. CASE inside ORDER BY for Custom Multi-Logic Sorting

Question:
Sort products: Laptops (high first), Phones (low first), Tablets (alphabetical salesperson).

SELECT product, amount, salesperson
FROM sales
ORDER BY
CASE
WHEN product = ‘Laptop’ THEN amount * -1
WHEN product = ‘Phone’ THEN amount
ELSE 0
END,
CASE WHEN product = ‘Tablet’ THEN salesperson END;

Output (Top 8 rows)

product	amount	salesperson
Laptop	1500.00	Alice
Laptop	1300.00	Dave
Laptop	1200.00	Alice
Laptop	1100.00	Alice
Laptop	1000.00	Dave
Laptop	900.00	Alice
Phone	300.00	Hank
Phone	450.00	Ivy

5. Multi-Conditional GROUP Filtering

Question:
Show only those regions where Laptop sales > 3000 and Phone sales < 2000.

SELECT region
FROM sales
GROUP BY region
HAVING
SUM(CASE WHEN product = ‘Laptop’ THEN amount ELSE 0 END) > 3000
AND SUM(CASE WHEN product = ‘Phone’ THEN amount ELSE 0 END) < 2000;

Output:

region
East

6. Time-Based Classification with CASE

Question:
Categorize each sale as Q1, Q2, Q3, or Q4 based on its sale_date.

SELECT
sale_id,
sale_date,
CASE
WHEN MONTH(sale_date) BETWEEN 1 AND 3 THEN ‘Q1’
WHEN MONTH(sale_date) BETWEEN 4 AND 6 THEN ‘Q2’
WHEN MONTH(sale_date) BETWEEN 7 AND 9 THEN ‘Q3’
ELSE ‘Q4’
END AS quarter
FROM sales;

Output:

sale_id	sale_date	quarter
1	2025-01-05	Q1
10	2025-02-12	Q1
16	2025-03-01	Q1

7. CASE with NULL Handling

Question:
Replace missing product names or regions with placeholders.

SELECT
sale_id,
CASE WHEN product IS NULL THEN ‘Unknown Product’ ELSE product END AS product_name,
CASE WHEN region IS NULL THEN ‘Unknown Region’ ELSE region END AS region_name
FROM sales;

sale_id	product_name	region_name
1	Laptop	East
2	Phone	West
…	…	…

8. Combining CASE with Joins

Question:
If a salesperson has total sales > 4000, label them as “Elite”; else “Regular”.

SELECT
s.salesperson,
SUM(s.amount) AS total_sales,
CASE
WHEN SUM(s.amount) > 4000 THEN ‘Elite’
ELSE ‘Regular’
END AS category
FROM sales s
GROUP BY s.salesperson;

Output(Top 4 rows):

salesperson	total_sales	category
Alice	4700.00	Elite
Frank	2300.00	Strong
Eve	1720.00	Regular
Ivy	3400.00	Strong

9. Pivot Simulation using CASE

Question:
Create a “pivot-like” summary: each product as a column with its total amount per region.

SELECT
region,
SUM(CASE WHEN product = ‘Laptop’ THEN amount ELSE 0 END) AS laptop_total,
SUM(CASE WHEN product = ‘Phone’ THEN amount ELSE 0 END) AS phone_total,
SUM(CASE WHEN product = ‘Tablet’ THEN amount ELSE 0 END) AS tablet_total
FROM sales
GROUP BY region;

region	laptop_total	phone_total	tablet_total
East	4700.00	2300.00	800.00
West	2300.00	1700.00	750.00
South	700.00	900.00	670.00
North	2800.00	450.00	150.00

Summary of Advanced CASE Use Cases:

#	Use Case	Concept	Clause Used
1	Nested logic	Multi-condition classification	SELECT
2	Conditional aggregation	Multi-filter SUMs	SELECT + GROUP BY
3	Ranking	Window + CASE	SELECT
4	Custom sorting	Multi-layer ORDER BY	ORDER BY
5	Conditional group filter	Multi-condition HAVING	HAVING
6	Conditional update	Bulk transformation	UPDATE
7	Time categorization	Date-based CASE	SELECT
8	NULL-safe logic	Handle missing values	SELECT
9	Aggregate classification	Conditional label after SUM	SELECT + GROUP BY
10	Pivot simulation	Convert rows → columns	SELECT + GROUP BY