[Oracle] SQL Class 7 - [고급함수] 서브쿼리

2023.11.06

 

** TOP & N 서브쿼리

- 위에서부터 N개(최대값 or 최소값을 가진 일분의 행을 추출하는 서브쿼리 기법

- 반드시 rownum 함수가 where절과 함께 이용해야 됨.

- from절에 서브쿼리(inline-view)가 나올 수 밖에 없음. 가상 테이블 개념이며, 남발금지(성능저하)

 

수업1)

급여를 가장 많이 받는 3명을 추출

select name, pay from (select saname name, sapay pay from sawon order by 2 desc) where rownum<=3;

 

연습1)

가장 오래 근무한 사람 5명 추출

 

연습2) 

부하직원이 없는 사람을 추출

select sabun, saname from sawon

where sabun not in(select distinct samgr from sawon);    // X

--> 안되는 이유: 서브쿼리는 in( ) 연산자를 써야 되며, or(또는)라는 의미라 결과값은 not A or B or C로 보여지므로

서브쿼리에서 not in은 못씀. --> exists 써야 됨. 서브쿼리에 영향을 주지 않으므로...

exists는 상관되는 테이블과 함께 써야 되는 쿼리명령문.

 select *from dept

where exists(select dname from dept where deptno=30);

 

exists 함수는 참일때만 값이 출력됨

 

연습3)

부서(명)별 급여합계를 추출하고, 급여합계가 전체 합계의 30%를 초과하는 부서만 추출

 

with 명령

- from절 서브쿼리를 보완하기 위한 명령.

- 복잡한 쿼리를 단순한 테이블로 사용하기 위함

- from절보단 with절을 사용해야 됨

 

 

Level 쿼리

- 데이터가 카테고리 형태로 구성된 경우(조직도) 상위 or 하위 데이터 추출하는 기법(자신은 1로 시작)

 

select level, sabun, saname, sajob from sawon

connect by  sabun=prior samgr  // 참조관계에서 상위 조직에 prior를 붙여줌

start with sabun=14;

 

연습4)

2번 사원의 하위 조직도를 추출

 

 

## 테이블 생성

-- 제약조건은 복사되지 않는다(not null 제외)

-- 형식: create table table_name(컬럼명, ... ) as select ~;

 

 

 

-- 컬럼이 조작된 경우, 컬럼명을 부여하여 생성

create table dname_sum(컬럼명, 컬럼명, ...)

as

select dname, sum(sapay) from sawon natural join dept group by dname;

 

-- 테이블 구조만 복사하는 경우 조건절을 부정형으로 해서 출력하면 됨.

create table dept_ex

as

select *from dept where 1=2;