2.5 (단일행 함수) 일반 함수 & pivot 사용법

 

---

※ EMP 테이블 구조

---

※ 일반 함수

nvl	null을 바꾸는 함수
nvl2	null을 조건부로 출력하는 함수
decode	(equal) 조건부 함수
case	조건부 함수

 

1. nvl 함수

# 문제 55. 이름과 커미션을 출력하는데, 커미션이 null인 사원들은 0으로 출력하시오.   select ename, nvl(comm,0)   from emp;

# 문제 56. 이름과 커미션을 출력하는데, 커미션이 null인 사원들은 no comm이란 글자를 출력하시오.   select ename, nvl(to_char(comm), 'no comm')   from emp;

- comm의 형태는 int만 받을 수 있기 때문에 꼭 to_char을 써서 문자형으로 바꾼 후 사용해야 한다.

 

2. nvl2 함수

# 문제 57. 이름, 월급, 커미션을 출력하는 데 커미션이 null이면 월급을, null이 아니면 월급+커미션 컬럼을 추가해서 출력하시오.   select ename, sal, comm, nvl2(comm, sal+comm, sal)   from emp;

 

3. decode 함수

# 문제 58. 이름, 직업, bonus를 출력하는데 직업이 SALESMAN이면 보너스 1000, ANALYST면 2000, 나머지는 0으로 출력하시오.   select ename, job, decode(job, 'SALESMAN', 1000, 'ANALYST', 2000, 0) as bonus   from emp;

# 문제 59. 이름, 입사한 년도(4자리), 보너스를 출력하는데, 입사한 년도가 1981이면 보너스를 9000으로 해서 출력하시오.   select ename, to_char(hiredate,'RRRR'), decode(to_char(hiredate,'RRRR'), '1981', 9000, 0) as '보너스'   from emp;

 

4. case 함수

# 문제 60. 이름, 월급, 보너스를 출력하는데, 월급이 3000 이상이면 자신의 월급의 10%, 월급이 2000 이상이면 월급의 20%, 나머지는 0원이 보너스가 되게 출력하시오.   select ename, sal, case when sal>= 3000 then sal*0.1 when sal>=2000 then sal*0.3 else 0 end as '보너스'   from emp;

# 문제 61. 이름, 부서번호, 직업, 보너스를 출력하는데, 보너스가 부서번호가 20번이면서 직업이 clerk인 사원들은 보너스 9000, 부서번호가 20번이면서 직업이 ANALYST인 사원들은 보너스 1200, 나머지는 0으로 출력하시오. (decode 중첩을 case로)   select ename, deptno, job, decode(decode(deptno, 20, job, 0), 'CLERK', 9000, 'ANALYST', 1200, 0) as '보너스'   from emp;   select ename, deptno, job, case when deptno = 20 and job = 'CLERK' then 9000 when deptno = 20 and job = 'ANALYST' then 1200 else 0 end as '보너스'   from emp;

 

※ Pivot ~ 현업의 튜닝 방법

# 열을 행으로 변경하는 방법 : pivot  //  세로 --> 가로   select *   from (select deptno, sal from emp)   pivot(sum(sal) for deptno in (10,20,30));   이를 해석하면 컬럼은 10, 20, 30이 되고  각 deptno의 sal을 합한 숫자가 튜플이 될 것이다.

10	20	30
8750	10875	9400

# 문제 62. 다음과 같이 출력하시오.  - decode version select job, sum(decode(deptno,10,sal,0)) as '10', sum(decode(deptno,20,sal,0)) as '20', sum(decode(deptno,30,sal,0)) as '30'   from emp   group by job;    - pivot version select *   from (select job, deptno, sal from emp)   pivot(sum(sal) fro deptno in (10,20,30));

# 문제 63. (JOIN) 다음의 dept 테이블이 있다. emp와 dept 테이블을 이용해 아래와 같은 결과를 도출하시오.  - decode version select count( decode(d.loc, 'NEW YORK', 1, null)) as "NEW YORK",     count( decode(d.loc, 'DALLAS', 1, null)) as "DALLAS",     count( decode(d.loc, 'CHICAGO', 1, null)) as "CHICAGO",     count( decode(d.loc, 'BOSTON', 1, null)) as "BOSTON"   from emp e, dept d   where e.deptno = d.deptno;    - pivot version select *   from (select d.loc 지역 from emp e, dept d                   where e.deptno = d.deptno)   pivot( count(*) for 지역 in ('NEW YORK','DALLAS','CHICAGO','BOSTON'));

# 문제 64. (JOIN, group) 다음과 같은 결과를 도출하시오.  - decode version select job,     count( decode(d.loc, 'NEW YORK', 1, null)) as "NEW YORK",     count( decode(d.loc, 'DALLAS', 1, null)) as "DALLAS",     count( decode(d.loc, 'CHICAGO', 1, null)) as "CHICAGO",     count( decode(d.loc, 'BOSTON', 1, null)) as "BOSTON"   from emp e, dept d   where e.deptno = d.deptno   group by job;    - pivot version select *   from (select d.loc 지역,e.job from emp e, dept d                                      where e.deptno = d.deptno)   pivot( count(*) for 지역 in ('NEW YORK','DALLAS','CHICAGO','BOSTON'));