15. 고급 sql

• 종류
  • nvl
    
    
  • decode
  • case
  • pivot : 보기 좋게 table 형태
    
    
  • rollup: 1차원
  • cube: 다차원
    
    
  • rank
    
    
  • correlated subquery : main queary(1) -> sub queary (2) * main queary에 별명 꼭 필요
    
    
  • multi row 연산자 : in, not in, any, exists, not exists
    
    
  • pseudo column : rowid, rownum(무조건 1부터) 
    
    
  • index 효율적 사용
    
    
  • trigger 자동 실행 프로그램

 

1. nvl

• null 값을 포함하는 컬럼을 지정된 값으로 변경하는데 사용

--nvl 사용법
SELECT nvl(name2, '0') 
from MYTABLE m ;

--nvl2 사용법
SELECT NVL2(name, '01', '23') -- 값 존재시 01, null 일 시 23
FROM MYTABLE m ;

 

2.decode

• 등급 나누기

-- 사용법 switch문과동일
SELECT name, salary, DECODE(TRUNC(salary/1000),0,'e',1,'d',2,'c',3,'b','a')
from S_EMP se ;

 

3. case문

• 등급 나누기

-- case문 case, when then , else, end
SELECT id, name,
    case salary 
        when 1000 then 'low'
        WHEN 5000 then 'high'
        ELSE 'medium'
    END 
from S_EMP se ;

-- case문 salary가 when절 안에 존재
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 se ;

 

4. pivot함수

• 보기 편리한 표 제공

SELECT * 
from (select dept_id, title from s_emp)
pivot(
    count(*) -- 수행할 작업
    for title in ('사원','과장','부장','이사','사장') -- 피벗할 column
)
order by DEPT_ID 
;

-- 비교
select dept_id, title from s_emp;

 

5. Rollup 함수

• 중간 합계, 최종 합계 제공 - 현재는 count여서 합계를 제공하는 것. 다른 작업도 가능

SELECT dept_id, title, count(*) 
from S_EMP se 
WHERE DEPT_ID in (106, 112, 113)
group by rollup(DEPT_ID, TITLE)
order by DEPT_ID 
;

 

6. cube 함수

• 다차원 합계 제공
• 부서별 합계, title별 합계, 총합계

SELECT dept_id, title, count(*) 
from S_EMP se 
WHERE DEPT_ID in (106, 112, 113)
group by cube (DEPT_ID, TITLE)
order by DEPT_ID 
;

 

7. rank 함수

• 행별 순위 계산

SELECT id, name, salary,
    RANK() over (order by salary desc) as RANK -- rank라는 column이 추가 
from S_EMP se
;

• 그룹별 순위 계산

SELECT id, name, salary,
    RANK() over (
        PARTITION by DEPT_ID -- partition으로 group을 나눠버림 
        order by salary desc) as RANK -- rank라는 column이 추가 
from S_EMP se
;

 

8. 상관 함수 (correlated subquery)

• main queary 닉네임 필수

-- 자신의 급여가 자신이 속한 부서의 평균 급여보다 적은 경우 출력
SELECT name, salary, DEPT_ID
from S_EMP outer
WHERE SALARY < (select AVG(SALARY)
                from S_EMP se
                WHERE DEPT_ID = outer.dept_id)
;

 

 

9. multi row 연산자

• 종류
  1. in
  2. not in
  3. any
  4. all
  5. exists
  6. not exists
• 기본적으로 subquery는 나의 정보를 어떤 table에서 무언가와 비교해서 얻어낼 때 사용

-- 본인의 급여가 각 부서별 평균 급여중 어느한 부서의 평균 급여보다 적은 경우, 그사람 정보 출력하기
SELECT NAME , SALARY , DEPT_ID 
FRom S_EMP se 
WHERE se.SALARY < ANY (SELECT AVG(SALARY) from S_EMP se2 group by DEPT_ID);

-- 본인이 다른 사람의 관리자로 되어 있는 경우, 정보 출력
SELECT name, title, dept_id 
from S_EMP se 
WHERE EXISTS (select id from S_EMP se2 WHERE MANAGER_ID = se.ID)

 

 

10. pseudo column

• rowid, rownum 으로 구성
  • rowid: 고유 num
  • rownum: 1번 부터 순차적으로 붙어지는 값

-- rowNum은 정렬 후 생성되는 것이 아니라, 먼저 생성된 후 우리가 정렬하는 것이므로
-- subquery로 정렬하고 나서 거기에 rowNUm을 붙일 수 있도록 해야한다.
SELECT name
FROM (select * from S_EMP se order by name) name
WHERE rownum < 6
;

-- 잘못된 결과
SELECT se.NAME 
FROM S_EMP se
order by se.NAME 
;

-- 월급(s_emp.salary)이 상위 6위에서 10위 5명의 이름(s_emp.name)과 월급(s_emp.salary)을 나타내시오.

select inTable.name, inTable.salary 
from (select rownum rn, name, SALARY 
from (select * from S_EMP se order by salary DESC) inin order by inin.salary DESC) inTable
where inTable.rn BETWEEN 6 and 10 
;

-- rownum을 통한 나열 먼저하기
select rownum rn, name, SALARY from (select * from S_EMP se order by salary DESC) inin order by inin.salary DESC ;

 

11. index 활용하기

• index가 사용안되는 조건이 존재
  1. index column 비교전 변형 일어나는 경우
  2. 부정 조건 기술
  3. null로 비교할 경우 - index는 null을 저장하지 않음

 

-- 컬럼 변형
select id, name, TITLE from s_emp e
where SALARY*12 = 24000;
;
-- 컬럼 변형 안쓰는 방법 - index 사용
select id, name, TITLE from s_emp e
where SALARY = 24000/12;
;


-- 부정
select id, name, TITLE from s_emp e
where title <> '사원'
;

-- 부정 안쓰는 방법 - index 사용
select id, name, TITLE from s_emp e
where not exists (SELECT 'x' from S_EMP se WHERE e.title = '사원')
;

 

 

12. trigger

• 사용자가 부르는 것이 아니라 임의의 테이블에 특정한 dml 문이 수행 되었을 때, db에서 자동으로 동작하는 프로그램

예제 찾아봐야될 듯