7일차 # 3-54: Conditional Expressions

 - short-circuit evaluation

  * CASE expression: ANSI SQL - simple, searched

  * DECODE function: Oracle proprietary

  문제) 부서 번호에 따라 월급을 차등 인상한다면 어떻게 할 것인가?

 1) decode 함수 사용

select deptno, sal

    , decode(deptno, 10, sal*1.10

   , 20, sal*1.15

   , 30, sal*1.05

   , sal) as inc_sal

from emp;

  2) simple CASE expression

select deptno, sal

    , case deptno when 10 then sal*1.10

          when 20 then sal*1.15

          when 30 then sal*1.05

                           else sal

               end as inc_sal

from emp;

  3) searched CASE expression

select deptno, sal

    , case when deptno = 10 then sal*1.10

   when deptno = 20 then sal*1.15

   when deptno = 30 then sal*1.05

                           else sal

               end as inc_sal

from emp;

  문제) 월급이 2000 미만이면 Low, 

               2000 이상 4000 미만이면 Mid,

               4000 이상이면 High가 표현되도록 Select 문장을 작성하세요.

  1) decode

select ename, sal

    , decode(trunc(sal/2000), 0, 'Low'

                                     , 1, 'Mid'

, 'High') as sal_grade

from emp;

  2) simple case

select ename, sal

    , case trunc(sal/1000) when 0 then 'Low'

                                    when 1 then 'Low'

                                    when 2 then 'Mid'

                                    when 3 then 'Mid'

                           else 'High'

               end as sal_grade

from emp;

↓ ↓ ↓

select ename, sal

    , case trunc(sal/2000) when 0 then 'Low'

                                    when 1 then 'Mid'

                             else 'High'

               end as sal_grade

from emp;

  3) searched case

select ename, sal

    , case when sal < 2000 then 'Low'

                    when sal < 4000 then 'Mid'

   else 'High'

               end as sal_grade

from emp;