서브쿼리
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 |
