[Oracle] SQL Class 7(남교수님 정리) - 서브쿼리, 다중컬럼 서브쿼리, 스칼라 서브쿼리, Top & N 서브쿼리, with 명령, Level 쿼리

*** 서브쿼리

-- SQL명령에서 데이터의 추출이 필요한 또다른 select명령 필요한 경우 사용하는 고급쿼리 기법(64개까지 가능)
-- 서브쿼리는 먼저 실행***되며, 서브쿼리만으로도 문법적인 오류가 없다! (상관쿼리 제외)
-- 서브쿼리는 반드시 ( )로 묶는다
-- 서브쿼리의 결과값***이 외부쿼리와 최종적으로 비교, 연산된 후 실행됨

--종류
1. 단일행 서브쿼리 : 서브쿼리의 결과 행이 단일행인 경우 (=,!=,>,<,>=,<=)
2. 다중행 서브쿼리 : 서브쿼리의 결과 행이 다중행인 경우 (in***, any, all, exists***)
--------------
3. 다중컬럼 서브쿼리 : 서브쿼리의 결과 컬럼이 다중컬럼인 경우
4. 상호관련 서브쿼리 : 외부쿼리의 단일* 후보행이 서브쿼리로 들어가서 비교/연산된 후, 
    다시 외부쿼리의 후보행과 최종적으로 비교/연산된 후 처리되는 서브쿼리

(연습1)

이순신과 같은 부서인 사람을 추출
select deptno, saname from sawon
where deptno = (select deptno from sawon where saname='이순신'); 

(연습2)

회사에서 가장 급여를 많이 받는 사람을 추출
select saname, sapay from sawon
where sapay = (select max(sapay) from sawon);

(연습3)

회사의 평균 급여보다 많이 받는 사람을 추출
select saname, sapay from sawon
where sapay > (select avg(sapay) from sawon);

(연습4)

10번 부서에서 전체 사원의 평균급여보다 많이 받는 사람을 추출
select saname,sapay,deptno from sawon
where deptno = 10 
   and sapay > (select avg(sapay) from sawon);

(연습5)

10번 부서에서 최대급여, 최소급여를 받는 사람을 추출
select deptno, saname, sapay from sawon
where (sapay = ( select max(sapay) from sawon where deptno = 10)
      or sapay = ( select min(sapay) from sawon where deptno = 10))

    and deptno=10; --***

(연습6)

where sapay=5000 or sapay=1000;

(연습7)

이순신과 부서와 직책이 같은 사람을 추출 // 다중컬럼
select saname, deptno, sajob from sawon
where (deptno, sajob) = (select deptno, sajob from sawon where saname='이순신');

(연습8)

where (nvl(deptno, -1), sajob) = (select nvl(deptno, -1), sajob from sawon where saname='이순신');

* 다중컬럼 서브쿼리 주의

1) 컬럼의 순서, 개수, 타입이 같아야 함
2) 비교 연산자는 같은 연산만 사용해야 함
3) 널을 포함한 컬럼이 존재하는 경우 널대체(음수)하여 비교해야 함

(연습9)

각 부서에서 최대급여를 받는 사람을 추출
select deptno, saname, sapay from sawon
where (deptno,sapay) in (select deptno, max(sapay) from sawon 
       group by deptno);

(연습10)
각 부서에서 부서의 평균급여보다 많이 받는 사람을 추출 // 상관쿼리
select saname, deptno, sapay from sawon s
where sapay > (select avg(sapay) from sawon t 
        where t.deptno = s.deptno);

 

(연습11)
-- 각 부서에서 최대급여를 받는 사람을 추출(상관쿼리)
select saname, deptno, sapay from sawon s
where sapay = (select max(sapay) from sawon t 
        where t.deptno = s.deptno);

* 컬럼 서브쿼리 = 스칼라 서브쿼리

 

(연습12)

 사원명, 직책, 부서명, 직책평균급여(자신이 속한)를 추출
select s.saname, s.sajob, d.dname, (select avg(t.sapay) from sawon t where t.sajob=s.sajob) "직책평균급여" from sawon s, dept d
where s.deptno = d.deptno;

* Top & N 서브쿼리

1) 최대값 또는 최소값을 가진 일부의 행을 추출하는 서브쿼리 기법
2) 반드시 rownum을 이용해야 함
3) from절 서브쿼리(inline-view) : 가상 테이블 개념, 남발 금지(성능저하)

(연습13)

급여를 가장 많이 받는 3명을 추출
select name, pay from (select saname name, sapay pay from sawon order by 2 desc)
where rownum<=3;

(연습14)

가장 오래 근무한 사람 5명을 추출
select saname, sahire from (select saname, sahire from sawon order by 2)
where rownum <= 5;

* exists 연산자

1) not in의 문제를 해결하기 위해 많이 사용됨
2) 서브쿼리의 결과가 존재하면 외부쿼리가 실행되고, 서브쿼리의 결과가 존재하지 않으면 외부쿼리는 실행되지 않는 연산자

SQL> select * from dept
  2  where exists(select dname from dept where deptno=77);
선택된 레코드가 없습니다.

SQL> select * from dept
  2  where exists(select dname from dept where deptno=30);

    DEPTNO DNAME      LOC
---------- ---------- ----------
        10 총무부     서울
        20 영업부     대전
        30 전산부     부산
        40 관리부     광주

(연습15) 부하직원이 없는 사람을 추출
select sabun, saname from sawon s
where not exists(select 1 from sawon t where t.samgr = s.sabun);

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

* with 명령

--from절 서브쿼리를 보완하기 위한 명령으로 복잡한 쿼리를 단순한 테이블로 사용하기 위한 명령
--컬럼이 조작된 경우, 컬럼 별칭은 반드시 준다
--형식
with table1 as (select~~~),
      table2 as (select~~~),
      .......
select ~~~ from table1, table2,....;

(연습16)

부서(명)별 급여합계를 추출하되, 급여합계가 전체 합계의 30%를 초과하는 부서만 추출
select d.dname, sum(sapay) from sawon s, dept d
where d.deptno = s.deptno
group by d.dname
having sum(s.sapay) > (select sum(sapay)*0.3 from sawon);

 

(연습17)

with
   dept_tot as (select dname, sum(sapay) d_tot from sawon natural join dept group by dname),
   all_tot  as (select sum(sapay) total from sawon)
select dname, d_tot from dept_tot dt, all_tot at
where d_tot > at.total*0.3;

*** Level 쿼리

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

(연습18)

14번 사원의 상위 조직도를 추출
SQL> select level, sabun, saname, sajob from sawon
  2  connect by sabun=prior samgr   --***
  3  start with sabun=14;

     LEVEL      SABUN SANAME     SAJOB
---------- ---------- ---------- ----------
         1         14 채시라     사원
         2          3 이순신     과장
         3          2 한국남     부장
         4          1 홍길동     회장

(연습19)

2번 사원의 하위 조직도를 추출
SQL> select level, sabun, saname, sajob from sawon where sabun != 2
  2  connect by prior sabun=samgr
  3  start with sabun=2;

     LEVEL      SABUN SANAME     SAJOB
---------- ---------- ---------- ----------
         2          3 이순신     과장
         3          5 이순라     사원
         3         14 채시라     사원
         2          7 놀기만     과장
         3         19 임꺽정     사원
         2         11 류별나     과장

 

남승윤 교수님 정리파일

서브쿼리.txt

0.01MB