On the values in your SELECT query, you wish to do IF-ELSE operations. If an employee is paid $2,000 or less, a message of UNDERPAID is returned; if they are paid $4,000 or more, a message of OVERPAID is returned; and if they are paid anywhere in the middle, a message of “OK” is returned.
Staff Table
| empno | ename | job | mgr_no | hiredate | sal | commission | deptno |
|---|---|---|---|---|---|---|---|
| 7369 | ALI | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | RAHUL | ANALYST | 7566 | 1982-12-09 | 3000.00 | NULL | 20 |
| 7839 | RAJ | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAM | CLERK | 7788 | 1983-01-12 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | ARHAM | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
Example
Use the CASE expression to perform conditional logic directly in your SELECT
statement:
SELECT ename,sal,
CASE WHEN sal <= 2000 then 'UNDERPAID'
WHEN sal >= 4000 then 'OVERPAID'
ELSE 'OK'
END as status
FROM Staff
Output
| ename | sal | status |
|---|---|---|
| ALI | 800 | UNDERPAID |
| ALLEN | 1600 | UNDERPAID |
| WARD | 1250 | UNDERPAID |
| JONES | 2975 | OK |
| MARTIN | 1250 | UNDERPAID |
| BLAKE | 2850 | OK |
| CLARK | 2450 | OK |
| RAHUL | 3000 | OK |
| RAJ | 5000 | OVERPAID |
| TURNER | 1500 | UNDERPAID |
| ADAM | 1100 | UNDERPAID |
| JAMES | 950 | UNDERPAID |
| ARHAM | 3000 | OK |
| MILLER | 1300 | UNDERPAID |
Discussion
The CASE statement enables conditional logic to be applied to values returned by a query. For a CASE expression, you can specify an alias to get a more comprehensible result set. You may find the alias STATUS used to refer to the CASE expression’s outcome in the solution. The ELSE clause is not required. The CASE statement will return NULL for any row that does not meet the test criterion if the ELSE clause is omitted.
Code Runner
Copy the below queries in the code runner to see the result
SELECT ename,sal,
CASE WHEN sal <= 2000 then 'UNDERPAID'
WHEN sal >= 4000 then 'OVERPAID'
ELSE 'OK'
END as status
FROM Staff