How to Write a Case Statement in SQL?

Case Statement facilitates conditional enquiries by doing  the work of  an if-then else statement

The general form of case expression usage is:

case expr when comarison_expr1 then return_expr1

                [when comarison_expr2 then return_expr2

                when comarison_expr3 then return_expr3

                when comarison_exprN then return_exprN

                else else_expr]

end

Example:

select ename,job,sal,

case job when 'ANALYST' then 1.10*sal

         when 'CLERK' then 1.05*sal

         when 'SALESMAN' then 1.50*sal

         else sal*1.00

           end REVISED_SAL

from emp;

ENAME      JOB              SAL REVISED_SAL

---------- --------- ---------- -----------

SMITH      CLERK            800         840

ALLEN      SALESMAN        1600        2400

WARD       SALESMAN        1250        1875

JONES      MANAGER         2975        2975

MARTIN     SALESMAN        1250        1875

BLAKE      MANAGER         2850        2850

CLARK      MANAGER         2450        2450

SCOTT      ANALYST         3000        3300             

KING       PRESIDENT       5000        5000

TURNER     SALESMAN        1500        2250

ADAMS      CLERK           1100        1155

ENAME      JOB              SAL REVISED_SAL

---------- --------- ---------- -----------

JAMES      CLERK            950       997.5

FORD       ANALYST         3000        3300

MILLER     CLERK           1300        1365

14 rows selected.