서브쿼리

     select      (select ...)   ← 스칼라 서브쿼리

     from        (select ...)    ← 인라인 뷰

     where sal > (select ...)   ← 서브쿼리


※ 주의 ! 서브쿼리 사용시!!

연산자 기준 오른쪽에 위치시켜 , ( ) 묶어주고, ORDER BY 쓰지말자.




예제 : 서브쿼리를 이용하여 '전은지' 교수와 동일한 직급의 교수 이름을 검색하세요.

SQL> SELECT name, position

     FROM professor

     WHERE position = (SELECT position

                          FROM professor

                          WHERE name = '전은지');




단일 행 서브쿼리 : 서브쿼리의 리턴값으로 하나의 행만 메인쿼리로 반환


예제 : 사용자 아이디가 'jun123'인 학생과 같은 학년인 학생의 학번, 이름, 학년을 출력하세요.

SQL> SELECT studno, name, grade

     FROM student

     WHERE grade = (SELECT grade

                      FROM student

                      WHERE userid = 'jun123');



문제 : 학생테이블에서 이광훈 학생과 같은 학과의 학생들의 이름과 학과이름을 출력하세요.

SQL> select s.name, d.dname

     from student s,department d

     where s.deptno = d.deptno

     and  s.deptno = (select deptno

     from student

     where name='이광훈');


※ 풀이 중 참고

SQL> select name, deptno

     from student

     where deptno = (select deptno

     from student

     where name='이광훈');



문제 : 101번 학과 학생들의 평균몸무게보다 적은 학생의 이름,학과번호,몸무게를 출력하세요

SQL> select name,deptno,weight from student

     where weight < (select avg(weight) from student where deptno=101);



예제 : 101번 학과 학생들의 평균 몸무게보다 몸무게가 적은 학생의

       이름, 학과 번호, 몸무게를 출력하여라.

SQL> SELECT name, deptno, weight

     FROM student

     WHERE   weight < ( SELECT AVG(weight)

                             FROM student

                             WHERE deptno = 101)

     ORDER BY deptno;



문제 : 이광훈 학생의 학과의 평균몸무게보다 작은 학생들의 학생이름과

       학생의 몸무게, 각 학생들의 학과이름과 지도교수 이름을 출력하세요.

SQL> select s.name 학생이름, s.weight 몸무게,

     d.dname 학과이름, p.name ||'교수' 교수이름

     from student s, department d, professor p

     where s.deptno=d.deptno

     and s.profno=p.profno(+)

     and weight < (select avg(weight)

     from student

     where deptno=(select deptno

     from student

     where name='이광훈'));


SQL> select s.name, s.weight, d.deptno, p.name

     from student s, department d, professor p

     where s.deptno=d.deptno

     and s.profno = p.profno(+)

     and s.weight <

     (select avg(weight) from student

     where deptno=

     (select deptno from student

     where name='이광훈'));



예제 : 20101번 학생과 학년이 같고, 키는 20101번 학생보다 큰 학생의 이름, 학년, 키를 출력하여라.

SQL> SELECT name, grade, height

     FROM student

     WHERE grade = ( SELECT grade

                       FROM    student

                       WHERE  studno = 20101)

     AND height > ( SELECT   height

                     FROM student

                     WHERE studno = 20101);




다중 행 서브쿼리 : 하나 이상의 행을 메인 쿼리로 반환

    - in : 메인쿼리의 비교조건이 서브쿼리 결과중에서 하나라도 일치하면 참, '=' 비교만 가능

    - any, some : 메인쿼리의 비교조건이 서브쿼리의 결과중에서 하나 이상 일치하면 참

                  '=, <, >' 사용 가능

    - all :  메인쿼리의 비교조건이 서브쿼리의 결과중에서 모든값이 일치하면 참

    - exists :  메인쿼리의 비교조건이 서브쿼리 결과중에서 만족하는 값이 하나라도 존재하면 참



예제 : 정보 미디어학부(부서번호:100)에 소속된 모든 학생의 학번, 이름, 학과번호를 출력하여라.

SQL> SELECT name, grade, deptno

     FROM student

     WHERE deptno IN ( SELECT deptno

                        FROM department

                        WHERE  college = 100);

※ 해설 : 서브쿼리를 실행하면 2개의 결과가 나오며 이 두 개의 결과값이 IN 연산자를 통해 메인 쿼리에게 전달된다.



문제 : Temp , tdept 테이블을 사용하여 인천에 근무하는 직원의 사번과 성명을 구하세요

SQL> select emp_id, emp_name

     from temp

     where dept_code in (select dept_code from tdept where area = '인천');



문제 : Temp , tdept , tcom 테이블을 참고하여

       부서별로 commission 을 받는 인원수를 세는 쿼리를 작성하세요.


    DEPT_NAME              COUNT(*)

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

  영업기획                     2

  영업1                        2

  영업2                        2

  영업                         2


SQL> select b.dept_name, count(*)

     from temp a, tdept b

     where b.dept_code = a.dept_code

     and a.emp_id in (select emp_id from tcom)

     group by b.dept_name;



※ 참고 : ANY 연산자

<any : 최대값

>any : 최소값

>all : 최대값

<all : 최소값



예제 : 모든 학생 중에서 4학년 학생 중에서 키가 제일 작은 학생보다 키가 큰 학생의

       학번, 이름, 키를 출력하여라.


최소 SQL> select studno,name,height from student

         where height > any (select height from student where grade='4');


최대 SQL> select studno,name,height from student

          where height < any (select height from student where grade='4');


※ 풀이 때 참고 - 서브쿼리 결과값

SQL> select height

     from student

     where grade='4';

HEIGHT

----------

176

177

175

예제 : 모든 학생중에서 4한년 학생중에서 키가 가장 큰 학생보다 키가 큰 학생의

       학번, 이름, 키를 출력하여라.

최대 SQL> SELECT studno, name, height

          FROM student

          WHERE height > ALL ( SELECT height

                               FROM student

                               WHERE grade = '4');

※ 참고 : 서브쿼리의 결과값이 3개가 나오는데 최대키는 177이다

 

최소 SQL> select studno,name,height

          from student

          where height < ALL (select height from student where grade='4');



문제 : Temp 테이블에서 과장 중 가장 급여를 작게 받는 사람보다

       많이 받는 사원들의 사번, 이름, 급여 를 출력하세요.

SQL> select emp_id 사번,

     emp_name 이름,

     salary 연봉

     from temp

     where salary > any (select salary

     from temp

     where lev='과장');




EXISTS 연산자

서브쿼리에서 검색된 결과가 하나라도 존재하면 메인쿼리 조건절이 참이 되는 연산자



예제 : 보직수당을 받는 교수가 한명이라도 있으면 모든 교수의

       교수 번호, 이름, 급여, 보직수당 그리고 급여와 보직수당의 합을 출력하세요.

SQL> SELECT profno, name, sal, comm, SAL+NVL(COMM,0)

     FROM  professor

     WHERE EXISTS ( SELECT profno

                    FROM    professor

                    WHERE comm IS NOT NULL);


출력결과)

PROFNO  NAME    SAL    COMM     SAL+NVL(COMM,0)

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

9901    김도훈  500    20       520

............(이하중략)

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


※ 해설 : 서브쿼리의 출력결과가 보직수당을 받는 교수가 4명이 있으므로

          메인 쿼리 조건절이 참이되어 교수테이블의 모든 행이 출력된다.



다중 컬럼 서브쿼리 : 하나 이상의 컬럼을 메인 쿼리로 반환. 서브쿼리에서 여러 개의 칼럼 값을 검색하여 메인쿼리의 조건절과 비교하는 서브 쿼리.



문제 : TEMP 에서 부서별 최고 연봉을 확인하고 사원중에 해당 부서와 최고 연봉금액이 일치하는 사원의 사번 , 성명, 연봉을 출력하세요.

SQL> select emp_id 사번,

     emp_name 이름,

     salary 급여

     from temp

     where (dept_code,salary) in (select dept_code, max(salary) from temp group by dept_code);





연습문제

* 학생테이블

1. 학생 중에서 생년월일이 가장 빠른 학생의 학번, 이름, 생년월일을 출력하세요

SQL> select studno,name,birthdate

     from student

     where birthdate IN (select min(birthdate) from student );



2. 학년별로 평균 체중이 가장 적은 학년과 평균 몸무게를 출력하세요.

SQL> select grade,avg(weight)

     from student

     group by grade

     having avg(weight) <= (select min(avg(weight)) from student group by grade);



* 교수 테이블

3. 교수 테이블에서 평균 연봉보다 많이 받는 교수들의 교수 번호, 이름, 연봉을

   연봉이 높은 순으로 정렬하여 출력하세요.(연봉은 sal*12+comm 으로 계산합니다)

SQL> select profno,name,sal*12+nvl(comm,0)

     from professor

     where sal*12+nvl(comm,0) >any (select avg(sal*12+nvl(comm,0)) from professor);


4. student 테이블을 조회하여 각 월별 생일자가 몇명인지 아래와 같이 출력하세요.


01  02  03  04  05  06  07  08  09  10  11  12   합

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

2   1   0   2   1   1   1   1   1   2   2   2    16


SQL> select count(decode(to_char(birthdate,'mm'),'01',1)) "01",

     count(decode(to_char(birthdate,'mm'),'02',1)) "02",

     count(decode(to_char(birthdate,'mm'),'03',1)) "03",

     count(decode(to_char(birthdate,'mm'),'04',1)) "04",

     count(decode(to_char(birthdate,'mm'),'05',1)) "05",

     count(decode(to_char(birthdate,'mm'),'06',1)) "06",

     count(decode(to_char(birthdate,'mm'),'07',1)) "07",

     count(decode(to_char(birthdate,'mm'),'08',1)) "08",

     count(decode(to_char(birthdate,'mm'),'09',1)) "09",

     count(decode(to_char(birthdate,'mm'),'10',1)) "10",

     count(decode(to_char(birthdate,'mm'),'11',1)) "11",

     count(decode(to_char(birthdate,'mm'),'12',1)) "12",

     count(*) "합"

     from student;

'Oracle > Oracle - SQL' 카테고리의 다른 글

SQL정리 9장 DDL  (0) 2012.01.10
SQL정리 8장 DML  (0) 2012.01.10
SQL정리 6장 join  (0) 2012.01.07
SQL정리 5장 그룹함수  (0) 2012.01.05
SQL정리 4장 함수  (0) 2012.01.04
Posted by 딩구르
,