반응형
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
???
728x90
반응형