[오라클로 배우는 데이터베이스 개론과 실습]실무 실습 A.8 조건부 논리 표현식 제어

 

A.8 조건부 논리 표현식 제어 : CASE & DECODE

 

샘플 문제

SELECT EMPLOYEE_ID, LAST_NAME || ' '|| FIRST_NAME AS "Name", JOB_ID, SALARY
, DECODE (JOB_ID, 'HR_REP', 1.10* SALARY,
                                     'MK_REP', 1.12*SALARY,
                                     'PR_REP', 1.15*SALARY,
                                     'SA_REP', 1.18*SALARY,
                                     'IT_PROG', 1.20*SALARY,
                                     SALARY) "New Salary"
FROM EMPLOYEES;

샘플 문제

SELECT EMPLOYEE_ID, LAST_NAME || ' ' || FIRST_NAME AS "Name", HIRE_DATE, SALARY
                ,CASE WHEN HIRE_DATE < TO_DATE('2002-01-01', 'YYYY-MM-DD') THEN SALARY * 1.05
                              WHEN HIRE_DATE < TO_DATE('2003-01-01', 'YYYY-MM-DD') THEN SALARY * 1.03
                              WHEN HIRE_DATE < TO_DATE('2004-01-01', 'YYYY-MM-DD') THEN SALARY * 1.01
                ELSE SALARY
                END "New Salary"
                FROM EMPLOYEES
                ORDER BY HIRE_DATE;

문제 1

SELECT DEPARTMENT_ID, SUM(SALARY),
            CASE WHEN SUM(SALARY) > 100000 THEN 'Excellent'
                        WHEN SUM(SALARY) > 50000 THEN 'Good'
                        WHEN SUM(SALARY) > 10000 THEN 'Medium'
                        WHEN SUM(SALARY) <= 10000 THEN 'Well'
            END "Department Grade Salary"
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
ORDER BY DEPARTMENT_ID ASC;

문제 2

SELECT EMPLOYEE_ID, LAST_NAME || ' ' || FIRST_NAME AS "Name", JOB_ID, HIRE_DATE, SALARY
    ,CASE WHEN HIRE_DATE < TO_DATE('2005-01-01', 'yyyy-mm-dd') THEN CASE WHEN JOB_ID LIKE '%MGR' THEN SALARY*1.15
                                                                         WHEN JOB_ID LIKE '%MAN' THEN  SALARY *1.20
                                                                         ELSE SALARY
                                                                         END
    WHEN HIRE_DATE > TO_DATE('2005-01-01',  'yyyy-mm-dd') THEN CASE WHEN JOB_ID LIKE '%MGR' THEN SALARY*1.25
                                                                    ELSE SALARY
                                                                    END                                                                                                                                                 
    END "New Salary"                                                
FROM EMPLOYEES
WHERE JOB_ID LIKE '%MGR' OR JOB_ID LIKE '%MAN';

 

 

 

 

문제 3

 

???