Case In SQL

In this article I am going to explain how to use case statement in sql.

Example 1

Case <Exp> when <Match1> then <Result1> when <Match2> then <Result2> else <else Result> END

If the Exp = Match1, the output will be ‘Result1’ or else checks with the other matches and returns the corresponding Results. If it is not equal to any match the output will be 'else Result'.
 
Example

select EMPNO, Job, (Case Job                      when 'President' then 'BIGBOSS'                      when 'Manager' then 'BOSS'                      when 'Analyst' then 'SCIENTIST'                      else  'Employee'                            End )  as Comments from Emp

OutPut

Question Write a query to generate the comments based on the salary which should be as following:
If Sal < 3000 Comment should be 'Below Target'.
If Sal = 3000 Comment should be 'On Target'.
If Sal > 3000 Comment should be 'Above Target'.

Answer

select Empno, Sal, (Case Sign (Sal-3000)

                when 1 then 'Above Target'

                when 0 then 'On Target'

                when -1 then 'Below Target'

            End )  as Comments from Emp

Example 2

Case when <condition> then <Result> when <condition> then <Result> else <else Result> End

 

select Empno, Sal (Case 

    when Sal < 3000 then 'Below Target'

                               when Sal = 3000 then 'On Target'

                               when Sal > 3000 and Sal <= 1000   then 'Above Target'

                               when Sal > 5000 then 'Very High Target'

                         End ) as Comments from Emp