general function

반드시 알아둬야 할 일반 함수
1. NVL : NULL 값 처리하기

• null은 그 자체로 ‘값이 없다’는 의미
• 할당되지 않았거나 알려져 있지 않아 적용이 불가능한 값
• 0이나 공백(space)과는 다름
• null 값을 포함하는 산술 연산의 결과는 null

SELECT *
FROM employees
ORDER BY commission_pct;

NVL(열 이름, 치환 값)

(1) employees 테이블에서 salary에 commission_pct를 곱하되 commission_pct가 null일 때는 1로 치환하여 commission_pct를 곱한 결과를 출력하세요.

SELECT salary * NVL(commission_pct, 1)
FROM employees
ORDER BY commission_pct;

2. DECODE : 조건 논리 처리하기

• IF-THEN-ELSE-END
• 데이터 값이 조건 값과 일치하면 치환 값을 출력하고 일치하지 않으면 기본 값을 출력

  DECODE(열 이름, 조건 값, 치환 값, 기본 값)

(1) employees 테이블에서 first_name, last_name, department_id, salary를 출력하되 department_id가 60인 경우에는 급여를 10% 인상한 값을 계산하여 출력하고 나머지 경우에는 원래의 값을 출력하세요. 그리고 department_id가 60인 경우에는 ‘10% 인상’을 출력하고 나머지 경우에는 ‘미인상’을 출력하세요.

SELECT first_name,
                last_name,
                department_id,
                salary
                DECODE (department_id, 60, salary * 1.1, salary) 조정된급여,
                DECODE (department_id, 60, ‘10% 인상’, ‘미인상’) 인상여부,
FROM employees;

3. CASE 표현식 : 복잡한 조건 논리 처리하기

• 등급, 나이, 지역, 날짜를 구분하여 출력 가능

CASE
                WHEN 조건 1 THEN 출력 값 1
                WHEN 조건 1 THEN 출력 값 2
                …
                ELSE 출력 값 3
END

(1) employees 테이블에서 job_id가 IT_PROG라면 employee_id, first_name, last_name, salary를 출력하되 salary가 9000 이상이면 ‘상위급여’, 6000과 8999 사이면 ‘중위급여’, 그 외는 ‘하위급여’라고 출력하세요.

SELECT employee_id, first_name, last_name, salary,
CASE
                WHEN salary >= 9000 THEN ‘상위급여’
                WHEN salary BETWEEN 6000 AND 8999 THEN ‘중위급여’
                ELSE ‘하위급여’
END AS 급여등급
FROM employees
WHERE job_id = ‘IT_PROG’;

4. RANK, DENSE_RANK, ROW_NUMBER : 데이터 값에 순위 매기기

RANK () OVER([PARTITION BY 열 이름] ORDER BY 열 이름)

• 차이점

함수	설명	순위 예
RANK	공통 순위를 출력하되 공통 순위만큼 건너뛰어 다음 순위를 출력	1, 2, 2, 4, …
DENSE_RANK	공통 순위를 출력하되 건너뛰지 않고 바로 다음 순위를 출력	1, 2, 2, 3, …
ROW_NUMBER	공통 순위 없이 출력	1, 2, 3, 4, …

(1) RANK, DENSE_RANK, ROW_NUMBER 함수를 각각 이용해 employees 테이블의 salary 값이 높은 순서대로 순위를 매겨 출력해 보세요.

SELECT employeeid,
                salary,
                RANK()                  OVER(ORDER BY salary DESC) RANK급여,
                DENSERANK()   OVER(ORDER BY salary DESC) DENSE_RANK급여,
                ROWNUMBER() OVER(ORDER BY salary DESC) ROW_NUMBER급여
FROM employees;

(2) RANK, DENSE_RANK, ROW_NUMBER 함수를 각각 이용해 employees 테이블 직원이 속한 department_id 안에서 salary 값이 높은 순서대로 순위를 매겨 출력해 보세요.

SELECT A.employeeid,
                A.department_id,
                B.department_name,
                salary,
                RANK()                 OVER(PARTITION BY A.department_id ORDER BY salary DESC) RANK급여,
                DENSERANK()  OVER(PARTITION BY A.department_id ORDER BY salary DESC) DENSE_RANK급여,
                ROWNUMBER() OVER(PARTITION BY A.department_id ORDER BY salary DESC) ROW_NUMBER급여
FROM employees A, departments B
WHERE A.department_id = B.department_id
ORDER BY B.department_id, A.salary DESC;

자료출처: 모두의 SQL