[SQL] ADVANCED SQL 1

1. NVL 함수

• NULL값을 포함하는 컬럼을 지정된 값으로 변경하는데 사용
• NVL 함수는 모든 데이터 타입에 적용할 수 있다.
• NVL 함수를 사용할 때 변경되는 값의 데이터 타입을 일치시켜야 한다.

NVL(형식1, 형식2)
-- 형식1 : NULL값을 포함하고 있는 컬럼이나 식
-- 형식2 : 변경하려는 값

NVL 함수의 문형	전환 예
NUMBER	NVL(commission_pct, 0)
DATE	NVL(start_date, '17/01/01')
CHAR 또는 VARCHAR2	NVL(mailid, 'ID없음')

 

2. DECODE 함수

DECODE = 값을 비교하여 해당하는 값을 돌려주는 함수

DECODE (형식, 비교값1, 결과치1, 비교값2, 결과치2, ... 기본치)

-- 형식 : 컬럼이나 값
-- 비교값1 : 형식이 비교값1에 맞는지를 비교
-- 결과값1 : 형식이 비교값1에 맞을 때 갖는 값
-- 기본치 : 형식이 비교값1,2,... 에 맞지 않을 때 가지는 값 (생략시 NULL)

예제

-- S_EMP테이블에서 각 사원의 이름과 급여, 급여등급을 나타내시오.
-- 급여가 4000이상이면 A, 3000이상 B, 2000이상 C, 1000이상 D, 1000이하 E
SELECT name 이름, salary 급여, DECODE(TRUNC(salary/1000),0,'E',1,'D',2,'C',3,'B','A') 급여등급 
FROM S_EMP;

 

3. CASE문

CASE문 = 조건문

SELECT id, name, 
    CASE
        WHEN salary < 1000 THEN 'Very Low'
        WHEN salary >= 1000 AND salary < 5000 THEN 'Low'
        WHEN salary >= 5000 THEN 'High'
        ELSE 'Medium'
    END
FROM S_EMP;

 

4. PIVOT 함수

데이터를 PIVOT 테이블의 형태로 보여준다.

SELECT * FROM (SELECT dept_id, title FROM s_emp)
PIVOT (
    count(*)
    FOR title IN ('사원','과장','부장','이사','사장')
)
ORDER BY DEPT_ID;

 

PIVOT 함수가 지원되지 않을 경우, DECODE 함수로 처리할 수 있다.

SELECT dept_id,
    COUNT(DECODE(title, '사원', 0)) "사원",
    COUNT(DECODE(title, '과장', 0)) "과장",
    COUNT(DECODE(title, '부장', 0)) "부장",
    COUNT(DECODE(title, '이사', 0)) "이사",
    COUNT(DECODE(title, '사장', 0)) "사장"
FROM s_emp
GROUP BY dept_id
ORDER BY dept_id;

 

5. ROLLUP 함수 

그룹별 합계를 보여주는 함수

SELECT DEPT_ID , title, count(*) FROM S_EMP
WHERE DEPT_ID IN (106, 112, 113)
GROUP BY ROLLUP(DEPT_ID, TITLE)
ORDER BY DEPT_ID;

 

6. CUBE 함수

그룹별 합계 & 소계를 보여주는 함수

SELECT DEPT_ID , TITLE ,COUNT(*) FROM S_EMP
WHERE DEPT_ID IN (106, 112, 113)
GROUP BY CUBE(DEPT_ID, TITLE)
ORDER BY DEPT_ID;

 

7. RANK 함수

행별, 그룹별 순위를 계산해서 보여주는 함수

-- 행별 순위를 계산
RANK() OVER (ORDER BY 컬럼명 [ASC | DESC])

-- 그룹별 순위를 계산
RANK() OVER(
    PARTITION BY 컬럼명1
    ORDER BY 컬럼명2 [ASC | DESC] )

예제

-- 행별 순위
SELECT id, name, salary, RANK() OVER (ORDER BY salary DESC) AS RANK 
FROM S_EMP;

-- 그룹별 순위
SELECT dept_id, name, salary, RANK() OVER (PARTITION BY DEPT_ID ORDER BY salary desc) AS RANK 
FROM S_EMP;