[Oracle SQL] SELECT 실습_ DECODE, CASE

[Oracle SQL] SELECT 실습_ DECODE, CASE 

I DECODE, CASE

▶ 조건절 연산자

▶ DECODE : EQUAL 연산만 가능

▶ CASE     : 다양한 연산 가능

1. DECODE, CASE

1) DECODE

---

SELECT DEPTNO,ENAME,DECODE(DEPTNO,10,'ACCOUNTING',20,'RESEARCH',30,'SALES','ETC')

FROM EMP

ORDER BY DEPTNO;

---

2) CASE

---

SELECT DEPTNO,ENAME,CASE DEPTNO WHEN 10 THEN 'ACCOUNTING'

                    WHEN 20 THEN 'RESEARCH'

WHEN 30 THEN 'SALES'

                          ELSE 'ETC'

              END AS DNAME

FROM EMP

ORDER BY DEPTNO;

---

2. DECODE  v.  CASE

CASE : 범위 지정 가능

---

SELECT DEPTNO,ENAME,SAL,CASE WHEN SAL >= 3000 THEN 'HIGH'

WHEN SAL >= 2000 THEN 'MID'

WHEN SAL BETWEEN 0 AND 2000 THEN 'LOW'

ELSE 'UNKNOWN'

END AS SAL_RANK

FROM EMP

ORDER BY DEPTNO;

---

● 부서별 차등 보너스 계산 SQL 작성

 -- 10 번 부서 급여의 0.3%, 20번 부서 급여의 20%, 30번 부서 급여의 10%, 나머지 모든 부서 1%

 -- 부서번호, 이름, 직무, 급여, 보너스

 -- 부서별, 최고 보너스 순으로 정렬

 -- 소수점 절삭

1) DECODE

---

SELECT DEPTNO,ENAME,JOB,SAL,TRUNC(DECODE(DEPTNO,10,SAL*0.003,20,SAL*0.2,30,SAL*0.1,SAL*0.01),0) AS BONUS

FROM EMP

ORDER BY BONUS DESC;

---

2) CASE

---

SELECT DEPTNO,ENAME,JOB,SAL,CASE WHEN DEPTNO = 10 THEN TRUNC(SAL*0.003,0)

                         WHEN DEPTNO = 20 THEN TRUNC(SAL*0.2,0)

                        WHEN DEPTNO = 30 THEN TRUNC(SAL*0.1,0)

                        ELSE TRUNC(SAL*0.01,0)

                     END AS BONUS

FROM EMP

ORDER BY BONUS DESC;

---