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;
