SQL - scott(개념 + 실습)9

NULL 처리 함수  : NVL(), NVL2()

DCODE 함수

CASE문

 

1) comm이 null

SELECT
    empno,
    ename,
    sal,
    comm,
    sal + comm
FROM
    emp;

 

2) nvl(널값 열, 널인 경우 사용할 값)

SELECT
    empno,
    ename,
    sal,
    nvl(comm, 0),
    sal + nvl(comm, 0)
FROM
    emp;

null 인 경우 0을 대입

 

3) nvl2(널값 열, 널이 아닐 경우 사용할 값, 널인 경우 사용할 값)

SELECT
    empno,
    ename,
    sal,
    nvl2(comm, 'O', 'X'),
    sal + nvl2(comm, sal * 12 + comm, sal * 12)
FROM
    emp;

 

 

※ decode ( 대상이 될 열 혹은 데이터,

                조건1, 조건1과 일치시 반환할 결과

                조건2, 조건2와 일치시 반환할 결과 (else, defult에 해당)

                조건1 ~조건 n까지 만족안할 때 반환할 결과)

 

4) job에 따라 비율을 다르게 준 다음 연산

SELECT
    empno,
    ename,
    job,
    sal,
    decode(job, 'MANAGER', sal * 1.1, 'SALESMAN', sal * 1.05,
           'ALALYST', sal, sal * 1.03) AS upsal
FROM
    emp;

 

5) case문 = job에 따라 비율을 다르게 준 다음 연산

SELECT
    empno,
    ename,
    job,
    sal,
    CASE job
        WHEN 'MANAGER'  THEN
            sal * 1.1
        WHEN 'SALESMAN' THEN
            sal * 1.05
        WHEN 'ALALYST'  THEN
            sal
        ELSE
            sal * 1.03
    END AS upsal
FROM
    emp;

괄호 대신 end를 사용

 

6) case문 변형

SELECT
    empno,
    ename,
    job,
    sal,
    CASE
        WHEN comm IS NULL THEN
            '해당사항없음'
        WHEN comm = 0 THEN
            '수당없음'
        WHEN comm > 0 THEN
            '수당 :' || comm
    END AS comm_text
FROM
    emp;

실습 ) emp 테이블에서 사원들의 월 평균 근무일수는 21.5일. 하루 근무시간을 8시간으로 보았을 때 사원들의 하루 급여(DAY_PAY)와 시급(TIME_PAY)를 계산하여 결과를 출력(단, 하루 급여는 수소점 세째자리에서 버리고, 시급은 두번째 소수점에서 반올림) 

SELECT
    empno,
    ename,
    sal,
    trunc(sal / 21.5, 2)     AS day_pay,
    round(sal / 21.5 / 8, 1) AS time_pay
FROM
    emp;

 

실습 ) emp 테이블에서 사원들은 입사일(hiredate)을 기준으로 3개월이 지난 후 첫 월요일에 정직원이 됨. 사원들이 정직원이 되는 날짜(R_JOB)를 YYYY-MM-DD 형식으로 아래와 같이 출력 (단, 추가수당(COMM)이 없는 사원의 추가 수당은 M/A로 출력)

SELECT
    empno,
    ename,
    hiredate,
    next_day(add_months(hiredate, 3), '월요일') AS r_job,
    nvl(to_char(comm), 'N/A')                AS comm 
FROM
    emp;

 

 

-- 타입이 다른데 같이 담아서 오류가 남. ==>  nvl(comm, 'N/A') 

SELECT
    comm,
    nvl(comm, 'N/A') 
FROM
    emp;

comm은 number 타입만 출력이 가능. 'N/A'이 number가 아닌 문자 타입이기 때문에 오류  

 

-- 오류 수정 : 숫자도 나오고 'N/A'도 출력

SELECT
    comm,
    nvl(to_char(comm), 'N/A')
FROM
    emp;

 

실습 ) emp 테이블의 모든 사원을 대상으로 직속 상관의 사원 번호(mgr)를 다음과 같은 조건을 기준으로 변환해서 CHG_MGR열에 출력
① mgr 존재하지 않으면 0000
② mgr 75인 경우 : 5555
③ mgr 76인 경우 : 6666
④ mgr 77인 경우 : 7777
⑤ mgr 78인 경우 : 8888
⑥ 그 외 원래대로 mgr 출력

 

방법 1)  decode

SELECT
    empno,
    ename,
    mgr,
    decode(substr(to_char(mgr), 1, 2), NULL, '0000', '75', '5555',
           '76', '6666', '77', '7777', '78',
           '8888', substr(to_char(mgr), 1)) AS chg_mgr
FROM
    emp;

 ==> substr(to_char(mgr), 1, 2) 조건 부분

방법 2) case 문

SELECT
    empno,
    ename,
    mgr,
    CASE
        WHEN mgr IS NULL THEN
            '0000'
        WHEN substr(to_char(mgr), 1, 2) = '75' THEN
            '5555'
        WHEN substr(to_char(mgr), 1, 2) = '76' THEN
            '6666'
        WHEN substr(to_char(mgr), 1, 2) = '77' THEN
            '7777'
        WHEN substr(to_char(mgr), 1, 2) = '78' THEN
            '8888'
        ELSE
            to_char(mgr)
    END AS chg_mgr
FROM
    emp;