[오라클] NULL의 처리(NVL, NVL2, COALESCE()) / 급여, 연봉 산출

Notice

Recent Posts

Recent Comments

Link

Tags more

Today

Total

관리 메뉴

온풀투데이

[오라클] NULL의 처리(NVL, NVL2, COALESCE()) / 급여, 연봉 산출 본문

DATABASE

[오라클] NULL의 처리(NVL, NVL2, COALESCE()) / 급여, 연봉 산출

온풀 2022. 3. 11. 00:16

20220218_01_scott.sql / 오라클 4일 차

- NULL의 처리

SELECT 2, 10+2, 10-2, 10*2, 10/2
FROM DUAL;
--==>> 2    12    8    20    5


SELECT NULL, NULL+2, 10-NULL, NULL*2, 2/NULL
FROM DUAL;
--==>> (NULL) (NULL) (NULL) (NULL) (NULL)

--※ 관찰의 결과
-- NULL은 상태의 값을 의미하며, 물리적으로는 실제 존재하지 않는 값이기 때문에
-- 이 NULL 이 연산에 포함될 경우...
-- 그 결과는 무조건 NULL 이다.

- TBL_EMP 테이블에서 커미션(COMM, 수당)이 NULL인 직원의 사원명, 직종명, 급여, 커미션 항목을 조회한다.

SELECT *
FROM TBL_EMP;

SELECT ENAME "사원명", JOB "직종명", SAL "급여", COMM "커미션"  
FROM TBL_EMP
WHERE COMM = NULL;
--==>> 에러 발생하지 않음
--     조회 결과 없음

SELECT ENAME "사원명", JOB "직종명", SAL "급여", COMM "커미션"  
FROM TBL_EMP
WHERE COMM = 'NULL';
--==>> 에러 발생
--     (ORA-01722: invalid number)

SELECT ENAME "사원명", JOB "직종명", SAL "급여", COMM "커미션"  
FROM TBL_EMP
WHERE COMM IS NULL;
--==>>
/*
SMITH    CLERK         800     (NULL)
JONES    MANAGER     2975     (NULL)
BLAKE    MANAGER        2850     (NULL)
CLARK    MANAGER        2450     (NULL)
SCOTT    ANALYST        3000     (NULL)
KING    PRESIDENT    5000     (NULL)
ADAMS    CLERK        1100     (NULL)
JAMES    CLERK         950     (NULL)
FORD    ANALYST        3000     (NULL)
MILLER    CLERK        1300     (NULL)
*/

--※ NULL 은 실제 존재하는 값이 아니기 때문에
--   일반적인 연산자를 활용하여 비교할 수 없다.
--   NULL 을 대상으로 사용할 수 없는 연산자들...
--   >=, <=, =, >, <, !=, ^=, <>
--                    ----------같지 않다!

- TBL_EMP 테이블에서 20번 부서에 근무하지 않는 직원들의 사원명, 직종명, 부서 번호 항목을 조회한다.

SELECT *
FROM TBL_EMP;

SELECT ENAME "사원명", JOB "직종명", DEPTNO "부서 번호"
FROM TBL_EMP
WHERE DEPTNO <> 20;
--WHERE DEPTNO != 20;   -- 같은 의미
--WHERE DEPTNO ^= 20;   -- 같은 의미
--==>>
/*
ALLEN    SALESMAN    30
WARD    SALESMAN    30
MARTIN    SALESMAN    30
BLAKE    MANAGER     30
CLARK    MANAGER        10
KING    PRESIDENT    10
TURNER    SALESMAN    30
JAMES    CLERK        30
MILLER    CLERK        10
*/

- TBL_EMP 테이블에서 커미션이 NULL이 아닌 직원들의 사원명, 직종명, 급여, 커미션 항목을 조회한다.

SELECT ENAME "사원명", JOB "직종명", COMM "커미션"
FROM TBL_EMP
WHERE COMM IS NOT NULL;       -- NOT
--==>>
/*
ALLEN    SALESMAN    300
WARD    SALESMAN    500
MARTIN    SALESMAN    1400
TURNER    SALESMAN    0
*/

SELECT ENAME "사원명", JOB "직종명", COMM "커미션"
FROM TBL_EMP
WHERE NOT COMM IS NULL;       -- NOT 
--==>>
/*
ALLEN    SALESMAN    300
WARD    SALESMAN    500
MARTIN    SALESMAN    1400
TURNER    SALESMAN    0
*/

TBL_EMP 테이블에서 모든 사원들의 사원번호, 사원명, 급여, 커미션, 연봉 항목을 조회한다.

단, 급여(SAL)는 매월 지급한다.
또한, 수당(COMM)은 연 1회 지급하며(매년 지급), 연봉 내역에 포함된다.

SELECT *
FROM TBL_EMP;

-- ANNSAL "연봉" = SAL * 12 + COMM
SELECT EMPNO "사원번호", ENAME "사원명", SAL "급여", COMM "커미션", SAL * 12 "연봉"
FROM TBL_EMP;
-- COMM은 어떻게 하지? NULL 값 들어가면 NULL

-- ↓ 이렇게

SELECT 사원번호, 사원명, 급여, 커미션, 연봉
FROM TBL_EMP;

SELECT EMPNO "사원번호", ENAME "사원명", SAL "급여", COMM "커미션", SAL * 12 "연봉"
FROM TBL_EMP;

SELECT EMPNO "사원번호", ENAME "사원명", SAL "급여", COMM "커미션", SAL * 12 + COMM "연봉"
FROM TBL_EMP;
--> COMM이 NULL이면 연봉 연산 결과가 NULL이 됨

NVL(), NVL2(), COALESCE()

--○ NVL()
SELECT NULL "COL1", NVL(NULL, 10) "COL2", NVL(5, 10) "COL3"
FROM DUAL;
--==>>(null)    10  5
-- 첫 번째 파라미터 값이 NULL 이면, 두 번째 파라미터 값을 반환한다.
-- 첫 번째 파라미터 값이 NULL 이 아니면, 그 값을 그대로 반환한다.

SELECT ENAME "사원명", COMM "수당"
FROM TBL_EMP;

SELECT ENAME "사원명", NVL(COMM, 1234) "수당"
FROM TBL_EMP;

SELECT EMPNO "사원번호", ENAME "사원명", SAL "급여", COMM "커미션", NVL(SAL * 12 + COMM, SAL * 12) "연봉"
FROM TBL_EMP; 
--==>>
/*
7369    SMITH     800   (null)    9600
7499    ALLEN    1600     300    19500
7521    WARD    1250     500    15500
7566    JONES    2975   (null)   35700
7654    MARTIN    1250    1400    16400
7698    BLAKE    2850   (null)   34200
7782    CLARK    2450   (null)   29400
7788    SCOTT    3000   (null)   36000
7839    KING    5000   (null)   60000
7844    TURNER    1500       0    18000
7876    ADAMS    1100   (null)    13200
7900    JAMES     950   (null)   11400
7902    FORD    3000   (null)   36000
7934    MILLER    1300   (null)   15600
*/

SELECT EMPNO "사원번호", ENAME "사원명", SAL "급여", COMM "커미션", SAL * 12 + NVL(COMM,0) "연봉"
FROM TBL_EMP; 
--==>>
/*
7369    SMITH     800   (null)    9600
7499    ALLEN    1600     300    19500
7521    WARD    1250     500    15500
7566    JONES    2975   (null)   35700
7654    MARTIN    1250    1400    16400
7698    BLAKE    2850   (null)   34200
7782    CLARK    2450   (null)   29400
7788    SCOTT    3000   (null)   36000
7839    KING    5000   (null)   60000
7844    TURNER    1500       0    18000
7876    ADAMS    1100   (null)    13200
7900    JAMES     950   (null)   11400
7902    FORD    3000   (null)   36000
7934    MILLER    1300   (null)   15600
*/

--○ NVL2()
--> 첫 번째 파라미터 값이 NULL이 아닌 경우, 두 번째 파라미터 값을 반환하고
--  첫 번째 파라미터 값이 NULL인 경우, 세 번째 파라미터 값을 반환한다.

SELECT ENAME "사원명", NVL2(COMM, '청기올려', '백기올려')  "수당 확인"
FROM TBL_EMP;
--==>>
/*
SMITH    백기올려
ALLEN    청기올려
WARD    청기올려
JONES    백기올려
MARTIN    청기올려
BLAKE    백기올려
CLARK    백기올려
SCOTT    백기올려
KING    백기올려
TURNER    청기올려
ADAMS    백기올려
JAMES    백기올려
FORD    백기올려
MILLER    백기올려
*/

SELECT EMPNO "사원번호", ENAME "사원명", SAL "급여", COMM "커미션"
    , SAL * 12 + NVL2(COMM, COMM, 0) "연봉"
FROM TBL_EMP;

SELECT EMPNO "사원번호", ENAME "사원명", SAL "급여", COMM "커미션"
    , NVL2(COMM, SAL*12+COMM, SAL*12) "연봉"
FROM TBL_EMP;

--○ COALESCE()
--> 매개변수 제한이 없는 형태로 인지하고 활용한다.
--  맨 앞에 있는 매개변수부터 차례로 NULL 인지 아닌지 확인하여
--  NULL 이 아닐 경우 반환하고, 
--  NULL인 경우에는 그 다음 매개변수 값을 반환한다.
--  NVL() 이나 NVL2() 와 비교하여
--  모 ~~~ 든 경우의 수를 고려할 수 있다는 특징을 갖는다.

SELECT NULL "COL1"
    , COALESCE(NULL, NULL, NULL, 30) "COL2"
    , COALESCE(NULL, NULL, NULL, NULL, NULL, NULL, 100) "COL3"
    , COALESCE(10, NULL, NULL, NULL, NULL, NULL) "COL4"
    , COALESCE(NULL, NULL, NULL, 50, NULL, NULL) "COL5"
FROM DUAL;
--==>> (null)     30    100    10    50

--○ 실습을 위한 데이터 추가 입력
INSERT INTO TBL_EMP(EMPNO, ENAME, JOB, MGR, HIREDATE, DEPTNO)
VALUES(8000, '호석이', 'SALESMAN', 7369, SYSDATE, 10);
--==>> 1 행 이(가) 삽입되었습니다.

INSERT INTO TBL_EMP(EMPNO, ENAME, JOB, MGR, HIREDATE, DEPTNO, COMM)
VALUES(8001, '문정이', 'SALESMAN', 7369, SYSDATE, 10, 10);
--==>> 1 행 이(가) 삽입되었습니다.

SELECT *
FROM TBL_EMP;

COMMIT;
--==>> 커밋 완료.

데이터가 추가된 형재 상태의 TBL_EMP 테이블에서 모든 사원의 사원번호, 사원명, 급여, 커미션, 연봉 항목을 조회한다.

연봉 산출 기준은 위와 같다.

SELECT *
FROM TBL_EMP;

SELECT EMPNO "사원번호", ENAME "사원명", SAL "급여", COMM "커미션"
    , COALESCE(SAL*12+COMM, SAL*12, COMM, 0) "연봉"
FROM TBL_EMP;
--==>>
/*
7369    SMITH     800             9600
7499    ALLEN    1600     300    19500
7521    WARD    1250     500    15500
7566    JONES    2975            35700
7654    MARTIN    1250    1400    16400
7698    BLAKE    2850            34200
7782    CLARK    2450            29400
7788    SCOTT    3000            36000
7839    KING    5000            60000
7844    TURNER    1500       0    18000
7876    ADAMS    1100            13200
7900    JAMES     950            11400
7902    FORD    3000            36000
7934    MILLER    1300            15600
8000    호석이                        0
8001    문정이              10       10
*/

저작자표시 비영리 변경금지

'DATABASE' 카테고리의 다른 글

[오라클] OR, IN, =ANY / WHILD CARD / ESCAPE 문법 (0)	2022.03.11
[오라클] UPPER(), LOWER(), INITCAP() / TO_DATE() / BETWEEN ⓐ AND ⓑ (0)	2022.03.11
[오라클] 관계형 데이터베이스, 오라클의 주요 자료형 (0)	2022.03.10
[오라클] 사용자 계정 생성 및 설정, 테이블 스페이스(TABLESPACE) (0)	2022.03.10
[오라클] 데이터베이스 개념 (0)	2022.03.10

'DATABASE' Related Articles

온풀투데이

[오라클] NULL의 처리(NVL, NVL2, COALESCE()) / 급여, 연봉 산출 본문

[오라클] NULL의 처리(NVL, NVL2, COALESCE()) / 급여, 연봉 산출

20220218_01_scott.sql / 오라클 4일 차

- NULL의 처리

- TBL_EMP 테이블에서 커미션(COMM, 수당)이 NULL인 직원의 사원명, 직종명, 급여, 커미션 항목을 조회한다.

- TBL_EMP 테이블에서 20번 부서에 근무하지 않는 직원들의 사원명, 직종명, 부서 번호 항목을 조회한다.

- TBL_EMP 테이블에서 커미션이 NULL이 아닌 직원들의 사원명, 직종명, 급여, 커미션 항목을 조회한다.

TBL_EMP 테이블에서 모든 사원들의 사원번호, 사원명, 급여, 커미션, 연봉 항목을 조회한다.

NVL(), NVL2(), COALESCE()

데이터가 추가된 형재 상태의 TBL_EMP 테이블에서 모든 사원의 사원번호, 사원명, 급여, 커미션, 연봉 항목을 조회한다.

'DATABASE' 카테고리의 다른 글

티스토리툴바