Oracle sqlplus select sub-Query

select 실행 순서

1. from 테이블명

2. where 조건절

3. group by 칼럼명

4. having 조건절

5. select 칼럼명1, 칼럼명2, ...

6. order by 칼럼명 [asc/desc] > 오름차순 혹은 내림차순

 

※ 계정 파일은 oraclexe\app\oracle\product\10.2.0\server\RDBMS\ADMIN 폴더안에 있는 scott.sql 파일을 활용함.

예제 table

테이블 column 설명

- empno 사원번호

- ename 사원이름

- job 직책

- mgr 매니저의 사원번호

- hiredate 입사일

- sal 봉급

- comm 성과급

- deptno 부서번호

 

dept 예제 table

- deptno 부서번호

- dname 부서명

- loc 장소

 

 

Sub-Query

- 하나의 SQL 문장절에 포함된 또다른 select 문장, 따라서 두번 질의를 해야 얻을수 있는 결과를

한번의 질의로 해결이 가능하게 하는 쿼리

 

- 용어 : Main-Query 또는 Outer-Query

             Sub-Query 또는 Inner-Query 두쌍이 같은 의미이다.

- 특징 : 괄호를 반듯이 묶어야한다.

            서브쿼리는 메인 쿼리의 다음 부분에 위치할수 있다.

1) select / delect / update 문의 from 절과 where 절

2) select 문의 having 절

3) insert 문의 into 절

4) update 문의 set 절

 

종류 

1. 단일행 서브쿼리

- 서비쿼리의 실행결과가 하나의 칼럼과 하나의 행만을 리턴해주는 쿼리 (하나의 데이터만 리턴해주는 쿼리)

 

2. 복수행 서브쿼리

- 서브쿼리의 실행결과가 하나의 칼럼과 여러개의 행을 리턴해주는 쿼리 (여러개의 데이터만 리턴해주는 쿼리)

 

 

단일행 서브 쿼리 예제

 

- 'SMITH' 가 근무하는 부서명을 서브쿼리를 이용해서 출력해 보세요.

- select dname from dept where deptno = (select deptno from emp where ename='SMITH');

 

- 'ALLEN' 과 같은 부서에서 근무하는 사원의 이름과 부서의 번호를 출력해 보세요.

- select ename, deptno from emp where deptno = (select deptno from emp where ename='ALLEN');

 

- 'ALLEN' 과 동일한 직책(job) 을 가진 사원의 사번과 이름, 직책을 출력해 보세요.

- select deptno, ename, job from emp where job = (select job from emp where ename = 'ALLEN');

 

- 'ALLEN' 의 급여와 동일하거나 더 많이 받는 사원의 이름과 급여를 출력해 보세요.

- select ename, sal from emp where sal >= (select sal from emp where ename='ALLEN');

 

- 'DALLAS' 에서 근무하는 사원의 이름, 부서번호를 출력해보세요.

- select ename, deptno from emp where deptno = (select deptno from dept where loc='DALLAS');

 

- 'SALES' 부서에서 근무하는 모든 사원의 이름과 급여를 출력해보세요.

- select ename, sal from emp where deptno = (select deptno from dept where dname='SALES');

 

- 자신의 직속 상관이 'KING' 인 사원의 이름과 급여를 출력해 보세요.

- select ename, sal from emp where mgr = (select empno from emp where ename='KING');

 

 

다중행 서브 쿼리 예제

- 다중행 서브쿼리의 결과값을 조건절에서 사용할때는 반드시 다중행 연산자와 함께 사용해야한다.

( IN, ALL, ANY, EXIST)

 

- 급여를 3000 이상받는 사원이 소속된 부서와 동일한 부서에서 근무하는

사원들의 이름과 급여, 부서번호를 출력해 보세요.

- select ename, sal, deptno from emp where deptno in(select deptno from emp where sal >=3000);

 

- IN 연산자를 이용하여 부서별로 가장 급여를 많이 받는 사원의 사원번호, 급여, 부서번호를 출력해보세요.

- select empno, sal, deptno from emp where sal in(select max(sal) from emp group by deptno);

 

- 직책이 MANAGER 인 사원이 속한 부서의 부서번호와 부서명과 부서의 위치를 출력해보세요

- select deptno, dname, loc from dept where deptno in(select deptno from emp where job='MANAGER');

 

- 30번 부서의 사원중에서 급여를 가장 많이 받는 사원보다 더 많은 급여를 받는 사원의 이름과 급여를 출력해보세요

- select ename, sal from emp where sal > all(select sal from emp where deptno=30); 

 

- 직책이 'SALESMAN' 보다 급여를 많이 받는 사원들의 이름과 급여를 출력하라. (ANY 연산자 이용)

- select ename, sal from emp where sal > ANY(select sal from emp where job='SALESMAN');

 

- 부서번호가 30번인 사원들의 급여중 최저 급여보다 높은 급여를 받는 사원의 이름, 급여를 출력해보세요

- select ename, sal from emp where sal > any(select min(sal) from emp where deptno=30);

 

- 직책이 'SALESMAN' 인 사원의 최소 급여보다 많이 받는 사원들의 이름과 급여, 직책을 출력하되 'SALESMAN' 은 출력하지 않습니다. (ANY 연산자를 사용하세요

- select ename, sal, job from emp where sal > any(select min(sal) from emp where job='SALESMAN') and job !='SALESMAN';

 

- SMITH 와 동일한 직책을 가진 사원의 이름과 직책을 출력하세요

- select ename, job from emp where job = (select job from emp where ename='SMITH');

 

- 직책이 'SALESMAN' 인 사원이 받는 급여들의 최대 급여보다 많이 받는 사원들 의 이름과 급여를 출력하되 부서번호가 20번인 사원은 제외한다. (ALL 연산자 이용)

- select ename, sal from emp where sal > all(select sal from emp where job='SALESMAN') and deptno!=20;

 

- 직책이 'SALESMAN' 인 사원이 받는 급여들의 최소 급여보다 많이 받는 사원 들의 이름과 급여를 출력하되 부서번호가 20번인 사원은 제외한다. (ANY 연산자 이용)

- select ename, sal from emp where sal > any (select sal from emp where job='SALESMAN') and deptno!=20;