서브쿼리

[서브쿼리 subQuery]

: SQL구문 안에 또 다른 SQL문이 포함되는 것

Q1. emp테이블에서 SCOTT 사원이 받는 급여보다 많이 받는 사원을 조회해서 그 사원의 사번, 이름, 업무, 급여 순으로 출력하시오.

1) scott 사원이 받는 급여 -> 서브쿼리

select sal from emp where ename='SCOTT';

  SAL

----------

  3000

2) 사번, 이름, 업무, 급여 출력 -> 메인(주)쿼리

select empno, ename, job, sal

 from emp

 where sal > 3000;

  EMPNO ENAME JOB SAL

--------- ---------- --------- ----------

  7839 KING PRESIDENT 5000

3) 서브쿼리를 활용하기(1과 2를 합치기)

select empno, ename, job, sal

 from emp

 where sal > (select sal from emp where ename='SCOTT');

SQL> ed

file afiedt.buf(이)가 기록되었습니다

  1* select empno, ename, job, sal from emp where sal > (select sal from emp where ename='

SCOTT')

  2 /

  EMPNO ENAME JOB SAL

---------- ---------- --------- ----------

  7839 KING PRESIDENT 5000

- 서브쿼리문을 반드기 괄호() 안에 작성

- 서브쿼리문 안에 세미콜론 ;을 삽입하지 않는다.

Q2. emp테이블에서 가장 입사일이 오래된 사원의 정보를 출력하시오. (사번, 이름, 입사날짜)

1) 서브쿼리 : 입사일이 오래된 사원(min) <> 가장 최근에 입사한 사원(max)

select min(hiredate) from emp;

MIN(HIRE

--------

80/12/17

2) 메인쿼리

select empno, ename, hiredate

 from emp

 where hiredate='80/12/17';

  EMPNO ENAME HIREDATE

---------- ---------- --------

  7369 SMITH 80/12/17

3) 서브쿼리 적용하기

select empno, ename, hiredate

 from emp

 where hiredate=(select min(hiredate) from emp);

    EMPNO ENAME HIREDATE

--------- ---------- --------

  7369 SMITH 80/12/17

4) 응용, 가장 최근에 입사한 사원의 정보

SQL> ed

file afiedt.buf(이)가 기록되었습니다

  1* select empno, ename, hiredate from emp where hiredate=(select max(hiredate) from emp)

SQL> /

  EMPNO ENAME HIREDATE

---------- ---------- --------

  7876 ADAMS 87/05/23

Q3. emp테이블에서 사원번호가 7521인 사원과 업무가 같고(and) 급여가 사원번호 7934인 사원보다 많이 받는 사원을 조회하여 그 사원의 사번, 이름, 업무, 급여 순으로 출력하시오.

-> 서브쿼리 문장이 두개( 사번이 7521인 사원의 업무, 사번이 7934인 사원의 급여)

-> 서브쿼리를 두개 이상 사용할 수 있다

1) 서브쿼리 1 : 사번이 7521인 사원의 업무

select job from emp where empno='7521';

JOB

---------

SALESMAN

2) 서브쿼리 2 :   사번이 7934인 사원의 급여

select sal from emp where empno='7934';

  SAL

----------

  1300

-> 업무가 SALESMAN이고 급여가 1300이상인 사원의 정보를 출력하라는 의미

3) 메인쿼리 작성

select empno, ename, job, sal

 from emp

 where job=upper('salesman') and sal>=1300;

  EMPNO ENAME JOB SAL

---------- ---------- --------- ----------

  7499 ALLEN SALESMAN 1600

  7844 TURNER SALESMAN 1500

4) 서브쿼리 적용하기

SQL> ed

file afiedt.buf(이)가 기록되었습니다

  1 select empno, ename, job, sal

  2 from emp

  3 where job=(select job from emp where empno='7521')

  4* and sal>=(select sal from emp where empno='7934')

SQL> /

  EMPNO ENAME JOB SAL

---------- ---------- --------- ----------

  7499 ALLEN SALESMAN 1600

  7844 TURNER SALESMAN 1500

*서브쿼리 8,90%는 where 조건에 나옴

Q4. emp테이블에서 급여의 평균보다 적은 사원을 찾아서 그 사원의 이름, 업무, 급여순으로 출력하시오.

-> 서브쿼리에 그룹함수를 사용할 수 있다.

1) 서브쿼리 : 급여의 평균값

select avg(sal) from emp;

  AVG(SAL)

----------

2073.21429

2) 메인쿼리 

select ename, job, sal

 from emp

 where sal <  2073.21429;

ENAME JOB SAL

---------- --------- ----------

SMITH SALES MAN 800

ALLEN SALESMAN 1600

WARD SALESMAN 1250

MARTIN SALESMAN 1250

TURNER SALESMAN 1500

ADAMS CLERK 1100

JAMES CLERK 950

MILLER CLERK 1300

8 개의 행이 선택되었습니다.

3) 서브쿼리 적용

SQL> ed

file afiedt.buf(이)가 기록되었습니다

  1 select ename, job, sal

  2 from emp

  3* where sal < (select avg(sal) from emp)

SQL> /

ENAME JOB SAL

---------- --------- ----------

SMITH SALES MAN 800

ALLEN SALESMAN 1600

WARD SALESMAN 1250

MARTIN SALESMAN 1250

TURNER SALESMAN 1500

ADAMS CLERK 1100

JAMES CLERK 950

MILLER CLERK 1300

8 개의 행이 선택되었습니다.

Q5. emp테이블에서 사원의 급여가 20번 부서의 최소 급여보다 많이 받는 부서별로 출력하되, 부서번호, 최소 급여 순으로 출력하시오.

1) 서브쿼리 20번 부서의 최소급여

select min(sal) from emp where deptno=20;

  MIN(SAL)

----------

  800

2) 메인쿼리

select deptno, min(sal)

 from emp

 having min(sal)>800

 group by deptno;

  DEPTNO MIN(SAL)

---------- ----------

  30 950

  10 1300

3) 적용

SQL> ed

file afiedt.buf(이)가 기록되었습니다

  1 select deptno, min(sal)

  2 from emp

  3 having min(sal)>(select min(sal) from emp where deptno=20)

  4* group by deptno

SQL> /

  DEPTNO MIN(SAL)

---------- ----------

  30 950

  10 1300

-> 서브쿼리는 Having 절에도 사용 가능하다.

---

[서브쿼리의 종류]

1) 단일행 : 실행결과가 행이 한개

    관계연산자 : =, >=, <, <=

2) 다중행 : 실행결과가 행이 한개 이상

    범위연산자 :  in, any, all

3) 서브쿼리 실행결과를 말함, 최종 실행결과를 의미하는 것이 아님.

Q6.  부서별로 최소급여를 받는 사원의 이름, 업무, 급여, 부서번호 순으로 출력하시오.

-> 다중행의 물리적인 경우

1) 서브쿼리 : 부서별로 최소급여

select min(sal) from emp group by deptno;

  MIN(SAL)

----------

  950

  800

  1300

-> 출력된 테이터(실행결과)가 다중행

2) 메인쿼리

select ename, job, sal, deptno

 from emp

 where sal=(select min(sal) from emp group by deptno);

1행에 오류:

ORA-01427: 단일 행 하위 질의에 2개 이상의 행이 리턴되었습니다.

-> 서브쿼리를 실행할 때, 한개 이상의 행을 구하는 경우, 연산자가 잘못되어 발생하는 오류

3) 범위연산자를 사용

SQL> ed

file afiedt.buf(이)가 기록되었습니다

  1 select ename, job, sal, deptno

  2 from emp

  3* where sal in (select min(sal) from emp group by deptno)

SQL> /

ENAME JOB SAL DEPTNO

---------- --------- ---------- ----------

SMITH SALES MAN 800 20

JAMES CLERK 950 30

MILLER CLERK 1300 10

[any 비교연산자 사용] 

: 다중행의 논리적인 경우

1) 메인쿼리 > (서브쿼리)를 메인쿼리 > any(서브쿼리)로 변경 가능

 

2) any 조건식

: 이 조건식에 해당하는 가장 적은 값을 구한 뒤 기존의 조건에 만족하는 값을 하나씩 비교하여 큰 값순으로 내림차순으로 정렬

: > any는 적은 값을 구해주는 SQL, < any 큰 값을 구해주는 SQL

Q7. emp테이블에서 30번 부서의 최소급여를 받는 사원보다 많은 급여를 받는 사원을 조회하여 그 사원의 이름, 업무, 급여, 부서번호를 출력하시오. (단, 30번 부서는 제외한다)

-> 겉으로는 다중행이 아님, 물리적으로는 단일행이나 논리적(내부적)으로 다중행

1) 서브쿼리 : 30번 부서의 최소급여

select min(sal) from emp where deptno=30;

  MIN(SAL)

----------

  950

-> 겉(물리적)으로는 단일행

2) 메인쿼리

select ename, job, deptno

 from emp

 where deptno!=30 and sal>950; 

ENAME JOB SAL DEPTNO

---------- --------- ---------- ----------

JONES MANAGER 2975 20

CLARK MANAGER 2450 10

SCOTT ANALYST 3000 20

KING PRESIDENT 5000 10

ADAMS CLERK 1100 20

FORD ANALYST 3000 20

MILLER CLERK 1300 10

7 개의 행이 선택되었습니다.

3) 적용하기

SQL> ed

file afiedt.buf(이)가 기록되었습니다

  1 select ename, job, sal, deptno

  2 from emp

  3* where deptno!=30 and sal>(select min(sal) from emp where deptno=30)

SQL> /

ENAME JOB SAL DEPTNO

---------- --------- ---------- ----------

JONES MANAGER 2975 20

CLARK MANAGER 2450 10

SCOTT ANALYST 3000 20

KING PRESIDENT 5000 10

ADAMS CLERK 1100 20

FORD ANALYST 3000 20

MILLER CLERK 1300 10

7 개의 행이 선택되었습니다.

-> 문제차제는 단일행

-> 다중행에서 사용하는 연산자 any 사용

4) any를 사용하여 구분 작성

where deptno!=30 and sal>(select min(sal) from emp where deptno=30)

where deptno!=30 and sal>any(select sal from emp where deptno=30) -> 그룹함수 삭제

SQL> ed

file afiedt.buf(이)가 기록되었습니다

  1 select ename, job, sal, deptno

  2 from emp

  3* where deptno!=30 and sal>any(select sal from emp where deptno=30)

SQL> /

ENAME JOB SAL DEPTNO

---------- --------- ---------- ----------

KING PRESIDENT 5000 10

SCOTT ANALYST 3000 20

FORD ANALYST 3000 20

JONES MANAGER 2975 20

CLARK MANAGER 2450 10

MILLER CLERK 1300 10

ADAMS CLERK 1100 20

7 개의 행이 선택되었습니다.

-> sal이 내림차순으로 정렬됨

Q8. 30번 부서의 최대 급여를 받는 사원보다 적은 급여를 받는 사원 ->  < any 변경

SQL> ed

file afiedt.buf(이)가 기록되었습니다

  1 select ename, job, sal, deptno

  2 from emp

  3* where deptno!=30 and sal<any(select sal from emp where deptno=30)

SQL> /

ENAME JOB SAL DEPTNO

---------- --------- ---------- ----------

SMITH SALES MAN 800 20

ADAMS CLERK 1100 20

MILLER CLERK 1300 10

CLARK MANAGER 2450 10

-> 30번 부서에서 최대급여를 구해서 그 값보다 적은 값을 일일히 비교하여 적은값으로 오름차순

SQL> select max(sal) from emp where deptno=30;

  MAX(SAL)

----------

  2850

*정리

- 물리적으로 여러개의 행이 나오는 서브쿼리 : in

- 비교해서 큰 값 또는 작은 값으로 정렬하고자 한다면 : any

[All 연산자] : 7일자 과제풀이 문제 4 참고

1) ~ > (max(sal)~) : > all(sal) 최대값보다 큰 값을 구하고 오름차순으로 정렬

2)  ~ > (max(sal)~) : < all(sal) 최소값보다 작은 값을 구하고 내림차순?

3) ~ > (min(sal)~) : >any(sal)

[exist 연산자]

- 서브쿼리에서 적어도 1개의 행을 리턴(실행결과를 보임)하면 참

- 리턴해주는 행이 없을 경우(실행결과 없음) 거짓

Q9. emp테이블에서 적어도 한 명의 사원으로부터 보고를 받을 수 있는 사원을 조회하여 그 사원의 사번, 이름, 업무, 급여 순으로 출력하시오.

1) 직속상관

select * from emp where empno=mgr; // 14명의 직원을 전부 조사하여 

2) 서브쿼리에 만족하는 행을 비교하여 만족하는 행이 한개라고 존재하면 결과를 보여줌

select empno, ename, job, sal

 from emp e

 where exists(select * from emp where e.empno=mgr);  EMPNO ENAME JOB SAL

---------- ---------- --------- ----------

  7902 FORD ANALYST 3000

  7698 BLAKE MANAGER 2850

  7839 KING PRESIDENT 5000

  7566 JONES MANAGER 2975

  7788 SCOTT ANALYST 3000

  7782 CLARK MANAGER 2450

6 개의 행이 선택되었습니다.

*오라클의 객체(구성요소)

: 테이블, 뷰, 시컨스, 시노님, 인덱스, 사용권한

[서브쿼리 패턴]

: where 조건식 이외의 나오는 패턴

- 인라인뷰 : SQL 구문에서 from 뒤에 서브쿼리가 나오는 문

   장점 : 테이블에 저장된 데이터가 많아도 검색속도가 빠르고

=> 튜닝기법 - SQL 구문 실행 -> 검색속도를 빠르게 해주는 기법

- rownum : 페이징 처리기법(http://jspstudy.co.kr)

  페이징 처리기법 : 게시판, 또는 회원관리 등 한페이지에 모든 데이터를 다 보여줄 수가 없어서 페이지당 10개씩 데이터를 끊어서 보여주는 기법

Q10. 업무가 MANAGER인 사원의 이름, 업무, 부서명, 근무지를 출력하시오.

1) 조인

-> 출력순서 : from 테이블 메모기 모두 올림, 만족하는 결과를 찾아 출력

 단점 : 테이블의 필드개수가 많고 데이터가 많이 들어가 검색 속도가 느림

select e.ename, e.job, d.dname, d.loc

 from emp e, dept d  // 테이블 필드 모두를 메모리에 올림

 where e.deptno=d.deptno and e.job='MANAGER';

ENAME JOB DNAME LOC

---------- --------- -------------- -------------

JONES MANAGER RESEARCH DALLAS

BLAKE MANAGER SALES CHICAGO

CLARK MANAGER ACCOUNTING NEW YORK

-> emp e, dept d 테이블 데이터를 모두 불러와서 where 조건에 만족하는 데이터를 뽑아내어 출력

2) 서브쿼리 활용 : from 서브쿼리(페이징 처리 기법)

-> 조건에 만족하는 필드를 먼저 불러옴

장점 : 테이블에 저장된 테이더가 많아도 검색속도를 빠르게 하여 원하는 데이터를 화면에 출력

SQL> ed

file afiedt.buf(이)가 기록되었습니다

  1 select e.ename, e.job, d.dname, d.loc

  2 from (select ename, job, deptno from emp where job='MANAGER') e, dept d

  3* where e.deptno=d.deptno

SQL> /

ENAME JOB DNAME LOC

---------- --------- -------------- -------------

JONES MANAGER RESEARCH DALLAS

BLAKE MANAGER SALES CHICAGO

CLARK MANAGER ACCOUNTING NEW YORK

-> from 서브쿼리 조건에 만족하는 데이터만 테이블에서 불러옴

[서브쿼리 - select ~ from 사이에도 나올 수가 있다]

Q11. dept 테이블에서 부서위치가 NEW YORK을 중앙(center), 제외한 나머지 도시는 변두리에 근무, 부서번호, 부서명을 출력하시오

select deptno, dname, (

 case when deptno in (select deptno from dept where loc='NEW YOK')

 then 'Center'

 else 'Assist'

 end

 ) "부서 위치"

 from dept;

  DEPTNO DNAME 부서

---------- -------------- ------

  10 ACCOUNTING Center

  20 RESEARCH Assist

  30 SALES Assist

  40 OPERATIONS Assist

Q12. 서브쿼리 사용시 주의할 점

1) 서브쿼리를 이용하여 테이블 생성

*테이블 생성

1) create table~

2) create table 백업t as sql구문

    -> sql 구문에 해당하는 조건을 찾아서 그 데이터를 출력

    -> 테이블을 만들어서 출력(테이블에 저장)

EX1) emp테이블에서 부서별로 부서번호, 인원수, 평균급여, 급여합계, 최소급여, 최대급여를 조회하여 emp_deptno백업 테이블로 저장하시오.

create table emp_dptno

 as select deptno, count(*), avg(sal), sum(sal), min(sal), max(sal)

 from emp

 group by deptno;

1행에 오류:

ORA-00998: 이 식은 열의 별명과 함께 지정해야 합니다

-> 가상필드는 복사가 안되기 때문에 발생이 되는 오류

-> 테이블이 만들어지지 않음

*백업 테이블을 생성할 경우, 가상필드에 필드명 부여

형식) create table 백업테이블명(가상필드에 대응되는 필드명1, 필드명2)

SQL> ed

file afiedt.buf(이)가 기록되었습니다

  1 create table emp_dptno(deptno, e_count, e_avg, e_sum, e_min, e_max)

  2 as select deptno, count(*), avg(sal), sum(sal), min(sal), max(sal)

  3 from emp

  4* group by deptno

SQL> /

테이블이 생성되었습니다.

SQL> desc emp_dptno

 이름 널? 유형

 ----------------------------------------- -------- ----------------------------

 DEPTNO NUMBER(2)

 E_COUNT NUMBER

 E_AVG NUMBER

 E_SUM NUMBER

 E_MIN NUMBER

 E_MAX NUMBER

SQL> select * from emp_dptno;

  DEPTNO E_COUNT E_AVG E_SUM E_MIN E_MAX

---------- ---------- ---------- ---------- ---------- ----------

  30 6 1566.66667 9400 950 2850

  20 5 2175 10875 800 3000

  10 3 2916.66667 8750 1300 5000

EX2) 테이블의 구조(schema)만 복사하기 -> 필드명만 복사하라(데이터 없음)

create table 백업t as select * from emp // 풀백업으로 필드, 내용 모두 복사

create table 백업t as select * from emp where 조건식(거짓이 되는 조건식)

EX1) empno, ename 구조만 복사

create table b_emp2

 as select empno, ename from emp

 where 1=0; // 조건이 거짓이기 때문에 데이터가 없음

테이블이 생성되었습니다.

SQL> desc b_emp2

 이름 널? 유형

 ----------------------------------------- -------- ----------------------------

 EMPNO NUMBER(4)

 ENAME VARCHAR2(10)

SQL> select * from b_emp2;

선택된 레코드가 없습니다.

-> 구조를 생성되고 데이터는 없음

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Q13.  DML을 이용하여 서브쿼리 사용을 가능하다.  (insert)

insert into 서브쿼리 valeus(필드명1, 필드명2,,,);

1) 백업테이블 생성

create table b_emp3 as select * from emp;

2) 서브쿼리 이용 : 부서번호가 40번부서에 근무할 데이터 입력

insert into (

 select empno, ename, sal, hiredate, job, deptno

 from b_emp3

 where deptno=40)

 values(7777,'JANG',4000,to_date('17-05-08','rr-mm-dd'),'MANAGER',40)

  EMPNO ENAME JOB MGR HIREDATE SAL COMM

---------- ---------- --------- ---------- -------- ---------- ----------

  DEPTNO

----------

  7902 FORD ANALYST 7566 81/12/03 3000

  20

  7934 MILLER CLERK 7782 82/01/23 1300

  10

  7777 JANG MANAGER 17/05/08 4000

  40

15 개의 행이 선택되었습니다.

= insert into b_emp3 values (7777,'JANG',4000,to_date('17-05-08','rr-mm-dd'),'MANAGER',40);

3) rollback; 롤백 : 실행취소, 데이터를 잘못 넣어 원상태로 돌아가기(insert, update, delete만 가능)

SQL> rollback;

롤백이 완료되었습니다.

SQL> select count(*) from b_emp3;

  COUNT(*)

----------

  14

Q14. update와 서브쿼리 적용하기

형식)

update 수정할테이블명 set (수정할필드명1, 필드명2,,,)=(서브쿼리를 이용한)수정할값

 where 조건식

EX) b_emp3 테이블에서 SCOTT의 업무와 급여가 일치하도록 JONES의 업무와 급여를 수정하는 SQL을 작성하시오.

1) scott 업무와 급여

SQL> select job, sal from b_emp3 where ename=upper('&ename');

ename의 값을 입력하십시오: scott

구 1: select job, sal from b_emp3 where ename=upper('&ename')

신 1: select job, sal from b_emp3 where ename=upper('scott')

JOB SAL

--------- ----------

ANALYST 3000

ename의 값을 입력하십시오: jones

구 1: select job, sal from b_emp3 where ename=upper('&ename')

신 1: select job, sal from b_emp3 where ename=upper('jones')

JOB SAL

--------- ----------

MANAGER 2975

2) update

update b_emp3

 set (job, sal)=(select job, sal from b_emp3 where ename='SCOTT')

 where ename=upper('jones');

1 행이 갱신되었습니다.

SQL> select ename, job, sal from b_emp3 where ename=upper('jones')

  2 ;

ENAME JOB SAL

---------- --------- ----------

JONES ANALYST 3000

Q15. delete와 서브쿼리 적용하기

형식)

delete from 테이블명; // 모든 데이터 삭제

delete from 테이블명 where 조건식=(서브쿼리) // 조건식에 만족하는 데이터만 삭제

문제. b_emp3의 자료 중에서 부서명이 'SALES'인 사원의 정보를 삭제하시오

1) 부서명이 'SALES'인 부서번호

select deptno from dept

 where dname='SALES';

  DEPTNO

----------

  30

2) delete 

delete from b_emp3 where deptno=30;

3) delete + 서브쿼리

delete from b_emp3 

 where deptno=(select deptno from dept where dname=upper('sales'))

6 행이 삭제되었습니다.

---