쌍용교육 -SQL 수업 18일차 일반함수-(1.NVL,NVL2,2.NULLIF,COALESCE3.CASE ..WHEN..THEN..ELSE..END,4.DECODE5.실습문제)

-NVL,NVL2

NVL(value1,valuse2)  value1dl null 이면value2를 쓴다
value1과 value2의 [자료형이 일치해야됨]
SELECT ename,sal,comm, (sal+NVL(comm,0))*12 FROM emp;
SELECT ename,NVL(TO_CHAR(comm) ,'NO Commission') FROM emp;

NVL2(value,value2,value3) : value1dl  null인지 평가. null이면 value3, null이 아니면 value2 [자료형이 일치하지않아도됨]
SELECT NVL2(comm, 'commission', 'no commission') FROM emp;

NULLIF,COALESCE

NULLIF(value1,value2) : 두 개의 값이 일치하면 NULL, 두개의 값이 일치하지 않으면 value1
SELECT NULLIF(LENGTH(ename),LENGTH(job)) "NULLIF" FROM emp;

COALESCE(value1,value2,value3,....) ; null값이 아닌 값을 사용(자료형 일치) 
SELECT comm,sal,COALESCE(comm,sal,0) FROM emp;--comm의 null값이 있다면 sal의 값을 쓴다.
SELECT comm,mgr,sal,COALESCE(comm,mgr,sal) FROM emp;

CASE ..WHEN..THEN..ELSE..END

CASE 컬럼 WHEN 비교값 THEN 결과값
              WHEN           THEN
            WHEN             THEN
            (ELSE 결과값)
END

SELECT ename,sal,job,
    CASE job WHEN 'SALESMAN' THEN sal*0.1
                  WHEN 'MANAGER' THEN sal*0.2
                  WHEN 'ANALYST' THEN sal*0.3
                  ELSE sal*0.4
    END "BONUS"
FROM emp;

SELECT ename,sal,job,
    CASE WHEN sal>=4000 AND sal<=5000 THEN 'A'
            WHEN sal>=3000 AND sal<=4000 THEN 'B'
            WHEN sal>=2000 AND sal<=3000 THEN 'C'
            WHEN sal>=1000 AND sal<=2000 THEN 'D'
            ELSE 'F'
    END "Grade"
FROM emp;

DECODE

DECODE : = 비교만 가능, 오라클 전용
            DECODE(컬럼, 비교값,반환값,
                            비교값,반환값,
                            비교값,반환값,
                            반환값)
SELECT ename, sal, job,
        DECODE(job,'SALESMAN',sal*0.1,
                        'MANAGER' ,sal*0.2,
                      'ANALYST' ,sal*0.3,
                      sal*0.4) "Bonus"
FROM emp;

SELECT ename,sal,job,
        DECODE(TRUNC(sal/1000),5,'A',
                                          4,'A',
                                          3,'B',
                                          2,'C',
                                          1,'D',
                                          'F')" Grade"
FROM emp;

실습문제

[실습문제]
1.사원이름,월급,월급과 커미션을 더한 값을 컬럼명 실급여라고 해서 출력하시오.
단 null값은 나타나지 않게 작성하시오.
SELECT ename,sal,NVL(sal+comm,sal) "실급여" FROM emp;
SELECT ename,sal,sal+NVL(comm,0) "실급여" FROM emp;
2.월급과 커미션을 합친 금액이 2,000,이상인 급여를 받은 사원의 이름,업무,월급,커미션,고용날짜를 출력하시오.
단, 고용날짜는 1980-12-17 형태로 출력하시오.
SELECT ename,job,sal,comm,TO_CHAR(hiredate, 'YYYY-MM-DD') hiredate
        FROM emp WHERE sal+NVL(comm,0) >=2000;