1.정의 : 하나의 SQL 문장 내부에 존재하는 또 다른 SELECT 문
(비교) 메인쿼리 : 서브쿼리를 포함하고 있는 SQL문
(목적) 메인쿼리 내부에서 추가적인 정보 제공
2. 구분
1) 메인쿼리와 연관성에 따라
가. 연관성이 없는 서브쿼리 : 서브쿼리로 산출된 ROW들은 메인 쿼리와 독립적인 서브쿼리
(문제) emp 테이블에서 월급을 가장 많이 받는 사원정보를 조회하시오.
select * from emp e1 where e1.sal =(select max(sal) from emp e2 );
>>>출력
7839 김철호 대표 01/11/17 800 10
(문제) 본인이 받는 급여가 본인이 속한 부서의 평균 급여보다 적은 급여를 받는 직원에 대하여
이름, 급여, 부서번호를 출력하시오
select e1.ename, e1.sal, e1.deptno from emp e1 where sal <
( select avg(e2.sal) from emp e2 where e1.deptno =e2.deptno );
>>>출력
김철수 200 20
이찬수 260 30
박종수 325 30
나대호 325 30
구자철 445 10
황인태 210 20
박민성 295 30
최철호 230 10
(문제) 사원들의 평균 월급보다 많은 급여를 받는 사원의 이름, 부서명, 급여를 조회하시오.(단일행 서브쿼리)
select avg(e.sal) from emp e, dept d where e.deptno =d.deptno
select e.ename, d.dname, e.sal from emp e, dept d
where e.deptno =d.deptno and e.sal > (사원들의 평균월급);
select e.ename, d.dname, e.sal from emp e, dept d
where e.deptno =d.deptno and e.sal > (select avg(e.sal) from emp e, dept d where e.deptno =d.deptno);
>>>출력
임채호 연구팀 497
박지성 총무팀 485
구자철 경리팀 445
송기성 연구팀 500
김철호 경리팀 800
박진성 연구팀 600
(문제) 구매부서(부서코드:30) 에 속한 사원들의 월급보다 높은 월급을 받는 사원의 이름, 성
직첵명을 조회하시오.(복수행 서브쿼리 : any, all)
select e.sal from emp e where e.deptno =30;
여러행 출력
=>
260
325
325
485
350
295
-- any(집합) : 집합 중의 1개만 만족
-- all(집합) : 집합의 모든 원소 만족
-- 위 문제에서는 any(260, 325, 323, 485, 350, 290)
-- 를 쓸경우 여러 값들중에서 가장 작은 값만 만족하면 되므로 min 이 될 수 있다.
-- 반대로 all(260, 325, 323, 485, 350, 290) 에서 모든 것을 만족해야 하므로 제일 큰 값 이상을 만족해야 한다.
-- 따라서 max();
select e2.ename, d.dname from emp e2, dept d
where e2.deptno = d.deptno and e2.salary > (구매부서에 근무하는 사원들의 급여)
select e2.ename, d.dname from emp e2, dept d
where e2.deptno = d.deptno and e2.SAL > (select e.sal from emp e where e.deptno =30);
====>에러 출력
=>변경
select e2.ename, d.dname from emp e2, DEPT d
where e2.deptno = d.deptno and e2.SAL > any(select e.sal from emp e where e.deptno =30) ;
--> all: 서브쿼리의 결과값들 중의 최소값을 의미함
select e2.ename, d.dname from emp e2, DEPT d
where e2.deptno = d.deptno and e2.SAL > (select min(e.sal) from emp e where e.deptno =30) ;
-- 구매부서에 근무하는 사원들의 중의 최소 월급
구자철 경리팀
김철호 경리팀
박진성 연구팀
송기성 연구팀
임채호 연구팀
나대호 총무팀
박지성 총무팀
박민성 총무팀
성명준 총무팀
박종수 총무팀
select e2.ename, d.dname from emp e2, DEPT d
where e2.deptno = d.deptno and e2.SAL > all(select e.sal from emp e where e.deptno =30) ;
--> all: 서브쿼리의 결과값들 중의 최대값을 의미함
select e2.ename, d.dname from emp e2, DEPT d
where e2.deptno = d.deptno and e2.SAL > (select max(e.sal) from emp e where e.deptno =30) ;
-- 구매부서에 근무하는 사원들의 중의 최대 월급
===>출력
임채호 연구팀
송기성 연구팀
김철호 경리팀
박진성 연구팀
나. 연관성이 있는 서브쿼리(상호관련 서브쿼리) : 서브쿼리와 메인 쿼리 사이에 조인을 사용.
반드시 별칭 사용
(문제) 사원이름과 부서코드, 부서명을 조회하시오.
(조인을 이용해서 검색한 경우)
select e.ename, e.deptno, d.dname from emp e left outer join dept d
on e.deptno =d.deptno;
(서브쿼리, 조인을 이용한 검색의 경우)
select e.ename, e.deptno, (
select d.dname from dept d where e.deptno= d.deptno
) , e.DEPTNO from emp e;
=>출력
김철수 20 연구팀 20
이찬수 30 총무팀 30
박종수 30 총무팀 30
임채호 20 연구팀 20
나대호 30 총무팀 30
박지성 30 총무팀 30
구자철 10 경리팀 10
송기성 20 연구팀 20
김철호 10 경리팀 10
성명준 30 총무팀 30
황인태 20 연구팀 20
박민성 30 총무팀 30
박진성 20 연구팀 20
최철호 10 경리팀 10
(문제) 본인이 받는 급여가 본인이 속한 부서의 평균 급여보다 적은 급여를
받는 직원에 대하여 이름, 급여, 부서번호를 출력하시오.
select e1.ename, e1.sal, e1.deptno from
emp e1 where sal < (
select avg(e2.sal) from emp e2
where e1.deptno =e2.deptno
);
==>출력
김철수 200 20
이찬수 260 30
박종수 325 30
나대호 325 30
구자철 445 10
황인태 210 20
박민성 295 30
최철호 230 10
2) 서브쿼리가 위치하는 곳에 따라 분류
가. 일반 서브쿼리
나. 인라인 뷰(inline view): from 절에 위치
** from 절에는 테이블이나 뷰가 위치하는데 이것과 비교해서 서브쿼리에 의해
결과값들에 대해서 뷰르는 명칭. 인라인 뷰와 비교해서 정식으로 만들어진 뷰를 out of line view 라고도함.
(문제) 사원들의 평균 급여보다는 높고 최대 급여보다는 낮은 월급을 받는 사원들을 조회하시오.
-- 여기서는 where 절에서 max 값과 min 값을 필요로 하기 때문에 from 절 에 서브쿼리를 두었다.
select e.empno, e.ename, e.sal, round(e2.avgs), e2.maxs from emp e,
(select avg(sal) avgs, max(sal) maxs from emp) e2
where e.sal > e2.avgs and e.sal < e2.maxs order by e.sal desc;
>>>출력>
7902 박진성 600 394 800
7788 송기성 500 394 800
7566 임채호 497 394 800
7698 박지성 485 394 800
7782 구자철 445 394 800
위 쿼리는 다음 쿼리와 같다.
select e.empno, e.ename, e.sal, round((select avg(sal) avgs from emp)), (select max(sal) maxs from emp) maxs from emp e
where e.sal >(select avg(sal) avgs from emp) and e.sal < (select max(sal) maxs from emp) order by e.sal desc;
(문제) 직책(job_id)이 "사원" 인 사람들이 어떤 부서에 근무하고 있는지 사원의 이름, 직책, 부서 이름을
나타내시오. 단 from 절에 서브쿼리문을 사용하여 문장을 완성하시오.
select e.ename, e.job, d.dname
from (select ename, job, deptno from emp where job='사원') e, dept d
where e.deptno =d.deptno;
>>출력
최철호 사원 경리팀
김철수 사원 연구팀
황인태 사원 연구팀
박민성 사원 총무팀
3) scalar 서브쿼리
서브쿼리에 의해 하나의 행, 하나의 컬럼값을 반환하는 서브쿼리.9i부터 지원
(문제) 각 사원의 이름, 급여, 전 사원의 평균급여를 출력하시오.
-- ex1 ) 스칼라 서브쿼리
select ename, sal, (select round(avg(sal)) from emp) avg_sal from emp;
==>출력
김철수 200 394
이찬수 260 394
박종수 325 394
임채호 497 394
나대호 325 394
박지성 485 394
구자철 445 394
송기성 500 394
-- ex2) 스칼라 서브쿼리
-- 부서별 평균급여
select ename, sal, (select round(avg(sal)) from emp) avg_sal from emp;
select ename, sal, (select round(avg(sal)) from emp where DEPTNO =e.DEPTNO ) avg_sal from emp e;
>>>출력
김철수 200 401
이찬수 260 340
박종수 325 340
임채호 497 401
나대호 325 340
박지성 485 340
구자철 445 492
송기성 500 401
김철호 800 492
성명준 350 340
황인태 210 401
박민성 295 340
박진성 600 401
최철호 230 492
<실습문제>
(문제) 송도권 교수보다 나중에 입사한 사람의 이름과 입사일, 학과명을 출력하시오
select p.name, HIREDATE , d.dname
from
PROFESSOR p
JOIN DEPARTMENT d on p.deptno=d.deptno
and p.HIREDATE >(select HIREDATE from PROFESSOR where name='조인형')
;
==>출력
박승곤 87/01/30 컴퓨터공학과
송도권 98/03/22 컴퓨터공학과
양선희 01/09/01 멀티미디어공학과
김영조 85/11/30 멀티미디어공학과
주승재 82/04/29 멀티미디어공학과
김도형 81/10/23 소프트웨어공학과
나한열 97/07/01 소프트웨어공학과
김현정 02/02/24 소프트웨어공학과
심슨 81/10/23 전자공학과
최슬기 09/08/30 전자공학과
박원범 99/12/01 기계공학과
차범철 09/01/28 기계공학과
바비 85/09/18 화학공학과
전민 10/06/28 문헌정보학과
허은 01/05/23 문헌정보학과
(문제) 심슨 교수와 같은 입사일에 입사한 교수 중에서 조인형 교수보다 월급을 적게
받는 교수의 이름과 급여, 입사일을 출력하시오.
select hiredate from PROFESSOR where name='심슨';
select pay from PROFESSOR where name ='조인형';
select name , pay , hiredate from PROFESSOR
where HIREDATE ='81/10/23'
and pay<'550';
select name , pay , hiredate from PROFESSOR
where HIREDATE =(select hiredate from PROFESSOR where name='심슨')
and pay < (select pay from PROFESSOR where name ='조인형') ;
==>출력
김도형 530 81/10/23
(문제) 각 학년별로 가장 키가 큰 학생들의 학년과 이름, 키를 출력하시오.
select grade, name , max(height)from student group by grade, name order by grade;
==>그룹바이로 할경우 원하는 결과값이 안나온다.
=>출력
1 김주현 179
1 안은수 175
1 이윤나 162
1 인영민 173
1 허우 163
2 김문호 166
select grade , name, height from student s
where height =(select max(HEIGHT) from student where grade =s.grade ) order by grade;
==>출력
1 김주현 179
2 노정호 184
3 오나라 177
4 박동호 182
*********** DB에서 쿼리로 한번에 데이터를 가져오면 효율이 높은 쿼리 예
ex) 아래 sql 쿼리는 두번에 걸쳐 데이터를 자바에서 돌려야 데이터가 출력이 가능하다.
select studno, st.name, d.dname, p.name pname
,( select nvl(sum(point), 0) from subject s, lecture l
where l.subject_code=s.subject_code
and l.studno=st.studno) point
from student st, department d, professor p
where st.deptno1=d.deptno and st.profno=p.profno;
==>출력
9411 서진수 컴퓨터공학과 조인형 9
9511 김신영 컴퓨터공학과 박승곤 0
9611 일지매 컴퓨터공학과 박승곤 0
9612 김진욱 멀티미디어공학과 양선희 0
9412 서재수 멀티미디어공학과 양선희 0
9512 신은경 멀티미디어공학과 김영조 0
9413 이미경 소프트웨어공학과 나한열 0
9515 임세현 전자공학과 심슨 0
9414 김재수 전자공학과 심슨 0
9613 안광훈 전자공학과 최슬기 0
9513 오나라 기계공학과 박원범 0
9415 박동호 기계공학과 박원범 0
9614 김문호 전자공학과 박원범 0
9514 구유미 문헌정보학과 허은 0
댓글 ( 4)
댓글 남기기