[SQL]DECODE문 , CASE WHEN문

-- DECODE
-- 10번 부서는 20% 인상, 80번 부서 15%, 50번 부서 10% 그이외 동결
SELECT
    LAST_NAME,
    SALARY,
    DECODE (DEPARTMENT_ID,10,SALARY*1.2,
                          80,SALARY*1.15,
                          50,SALARY*1.1,
                          SALARY) AS "REVISED SALARY",
    DEPARTMENT_ID
FROM EMPLOYEES;

--10번부서 사원은 50%,20번부서 사원은 40% 30번부서사원 -10%
select 
    last_name,
    salary,
    decode(department_id,10,salary*1.5,
                        20,salary*1.4,
                        30,salary*0.9) as 변경급여
from employees;

--searched case:CASE WHEN

select 
    last_name,
    salary,
    case department_id when 10 then salary*1.5
                       when 20 then salary*1.4
                       when 30 then salary*0.9 end as 변경급여
from employees;
---simple case
select 
    last_name,
    salary,
    case when department_id=10 then salary*1.5
         when department_id=20 then salary*1.4
         when department_id=30 then salary - salrary*0.1 end as 변경급여
from employees;

--2.회사에서 체육대회를 하려고 한다
--사번이 짝수이면 '백군', 홀수이면 '청군'
--사원명  팀
--King    백군
--Kochhar 청군
-- decode
select 
    last_name,
    employee_id,
    decode(MOD(employee_id,2),0,'백군',
                                '청군') as 군
from employees;
        
/*3.salary가 1~10000 달러는 'Low'
           10001~20000달러는 'Mid'
           20001~     'High' 라고 출력
-- decode
last_name | salary | gubun
King         29000   High
*/

SELECT 
    LAST_NAME,
    SALARY,
    DECODE(CEIL(SALARY/10000),1,'Low',
                              2,'Mid',
                              3,'High') AS 구분
FROM EMPLOYEES;
-- searched case
select last_name
      ,salary
      ,case ceil(salary/10000) when 1 then 'Low'
                               when 2 then 'Mid'
                                      else 'High' 
       end as Gubun
from   employees;

--SIMPLE CASE
SELECT 
    LAST_NAME,
    SALARY,
    CASE WHEN SALARY<=10000 THEN 'Low'
         WHEN SALARY<=10000 THEN 'Mid'
         WHEN SALARY<=10000 THEN 'High' END AS 구분
FROM EMPLOYEES;