Module 10intermediate
CASE Statements
Lessons
CASE Syntax
1 / 1CASE allows you to return different values based on conditions, similar to IF-THEN-ELSE logic.
Syntax
text-blue-600 dark:text-blue-400 font-semibold">CASE text-blue-600 dark:text-blue-400 font-semibold">WHEN condition1 text-blue-600 dark:text-blue-400 font-semibold">THEN result1 text-blue-600 dark:text-blue-400 font-semibold">WHEN condition2 text-blue-600 dark:text-blue-400 font-semibold">THEN result2 text-blue-600 dark:text-blue-400 font-semibold">ELSE default_result text-blue-600 dark:text-blue-400 font-semibold">ENDSample Tables Used in This Course
employeesEmployee records
| Column | Type | Key |
|---|---|---|
| id | INTEGER | PK |
| name | TEXT | |
| department | TEXT | |
| salary | INTEGER | |
| country | TEXT | |
| manager_id | INTEGER | FK → employees.id |
| TEXT | ||
| hire_date | TEXT |
Sample Data (first 5 rows)
| id | name | department | salary | country | manager_id | hire_date | |
|---|---|---|---|---|---|---|---|
| 1 | Ahmed Al-Thani | IT | 25000 | Qatar | NULL | ahmed@qatarco.qa | 2018-03-15 |
| 2 | Fatima Al-Kuwari | HR | 18000 | Qatar | 1 | fatima@qatarco.qa | 2019-06-01 |
| 3 | Mohammed Al-Sulaiti | Finance | 22000 | Qatar | 1 | mohammed@qatarco.qa | 2017-09-20 |
| 4 | Sara Al-Mansouri | IT | 20000 | Qatar | 1 | sara@qatarco.qa | 2020-01-10 |
| 5 | Khalid Hassan | IT | 17000 | Egypt | 4 | khalid@qatarco.qa | 2021-04-05 |
ordersOrder transactions
| Column | Type | Key |
|---|---|---|
| id | INTEGER | PK |
| customer_id | INTEGER | FK → customers.id |
| amount | DECIMAL | |
| order_date | TEXT | |
| region | TEXT | |
| status | TEXT |
Sample Data (first 5 rows)
| id | customer_id | amount | order_date | region | status |
|---|---|---|---|---|---|
| 1 | 1 | 15000 | 2024-01-15 | Doha | completed |
| 2 | 2 | 8500 | 2024-01-20 | Doha | completed |
| 3 | 3 | 22000 | 2024-02-01 | Doha | pending |
| 4 | 1 | 5000 | 2024-02-10 | Al Wakrah | completed |
| 5 | 4 | 12000 | 2024-02-15 | Lusail | shipped |
Relationships: Primary Keys (PK) uniquely identify rows. Foreign Keys (FK) reference other tables.
Practice with these tables in the SQL Console. Click "Open Console" in the sidebar.
Example
text-blue-600 dark:text-blue-400 font-semibold">SELECT name, salary,
text-blue-600 dark:text-blue-400 font-semibold">CASE
text-blue-600 dark:text-blue-400 font-semibold">WHEN salary >= text-amber-600 dark:text-amber-400">20000 text-blue-600 dark:text-blue-400 font-semibold">THEN text-green-text-amber-600 dark:text-amber-400">600 dark:text-green-text-amber-600 dark:text-amber-400">400">'Senior'
text-blue-600 dark:text-blue-400 font-semibold">WHEN salary >= text-amber-600 dark:text-amber-400">10000 text-blue-600 dark:text-blue-400 font-semibold">THEN text-green-text-amber-600 dark:text-amber-400">600 dark:text-green-text-amber-600 dark:text-amber-400">400">'Mid-Level'
text-blue-600 dark:text-blue-400 font-semibold">ELSE text-green-text-amber-600 dark:text-amber-400">600 dark:text-green-text-amber-600 dark:text-amber-400">400">'Junior'
text-blue-600 dark:text-blue-400 font-semibold">END text-blue-600 dark:text-blue-400 font-semibold">AS level
text-blue-600 dark:text-blue-400 font-semibold">FROM employees;Categorizes employees by salary level
Learning Objectives
- Write CASE expressions
- Categorize data dynamically