SQL 문법(Sub query)

 

1. 문법설명
가. 서브쿼리의 사용위치
select (select ...) <----- 스칼라 서브쿼리
from (select ...) <----- 인라인 뷰
where col_name > (select ...) <----- 서브쿼리(연산자 오른쪽에 사용, 서브쿼리 안쪽에 order by 절을 사용하지 않음

나. 서브 쿼리 종류
- 단일 행 서브쿼리 : 서브쿼리의 리턴값으로 하나의 행만 메인쿼리로 반환
- 다중 행 서브쿼리 : 하나 이상의 행을 메인 쿼리로 반환
- 다중 컬럼 서브쿼리 : 하나 이상의 컬럼을 메인 쿼리로 반환
- 상호 관련 서브쿼리 : 서브쿼리와 메인 쿼리간에 결과값을 서로 주고 받는 식으로 처리되는 서브쿼리

다. 다중 행 서브쿼리 에서 사용하는 비교 연산자
- in : 메인쿼리의 비교조건이 서브쿼리 결과중에서 하나라도 일치하면 참, '=' 비교만 가능
- any, some : 메인쿼리의 비교조건이 서브쿼리의 결과중에서 하나 이상 일치하면 참
'=, <, >' 사용 가능
- all : 메인쿼리의 비교조건이 서브쿼리의 결과중에서 모든값이 일치하면 참
- exists : 메인쿼리의 비교조건이 서브쿼리 결과중에서 만족하는 값이 하나라도 존재하면 참

참고 :
< any : 최대값
> any : 최소값
> all : 최대값
< all : 최소값

2. 연습문제

- 학생테이블에서 이광훈 학생과 같은 학과의 학생들의 이름과 학과이름을 출력

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

SQL> select s.name, d.dname 2 from student s, department d 3 where s.deptno = d.deptno and 4 s.deptno = (select deptno from student where name='이광훈'); NAME DNAME ---------- ---------------- 류민정 컴퓨터공학과 이광훈 컴퓨터공학과 서재진 컴퓨터공학과 임유진 컴퓨터공학과 지은경 컴퓨터공학과 김영균 컴퓨터공학과 박미경 컴퓨터공학과 전인하 컴퓨터공학과 8 rows selected.

- 이광훈 학생의 학과의 평균몸무게보다 작은 학생들의 학생이름과 학생의 몸무게, 각 학생들의 학과이름과
지도교수 이름을 출력

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45

select s.name, s.weight, d.dname, p.name from student s full outer join professor p on s.profno = p.profno join department d on s.deptno=d.deptno and weight < (select avg(weight) from student where deptno = (select deptno from student where name='이광훈')) order by 1; NAME WEIGHT DNAME NAME ---------- ---------- ---------------- ---------- 김진경 51 전자공학과 이재우 김진영 48 멀티미디어학과 권혁일 박미경 52 컴퓨터공학과 이동훈 64 전자공학과 임유진 54 컴퓨터공학과 전은지 조명훈 62 전자공학과 지은경 42 컴퓨터공학과 전은지 7 rows selected. 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='이광훈')) order by 1; NAME WEIGHT DNAME NAME ---------- ---------- ---------------- ---------- 김진경 51 전자공학과 이재우 김진영 48 멀티미디어학과 권혁일 박미경 52 컴퓨터공학과 이동훈 64 전자공학과 임유진 54 컴퓨터공학과 전은지 조명훈 62 전자공학과 지은경 42 컴퓨터공학과 전은지 7 rows selected.

- Temp, tdept 테이블을 사용하여 인천에 근무하는 직원의 사번과 성명을 출력

1 2 3 4 5 6 7 8 9 10 11 12 13 14

SQL> select emp_id, emp_name 2 from temp 3 where dept_code in (select dept_code from tdept where area='인천'); EMP_ID EMP_NAME ---------- ---------- 19930331 정도령 20000334 박지리 19950303 이순신 20000305 정북악 19966102 지문덕 20006106 유도봉 6 rows selected.

- Temp, tdept, tcom 테이블을 참고하여 부서별로 commission을 받는 인원수를 출력

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

select d.dept_name, count(c.dept_code) from tdept d, (select e.dept_code from temp e, tcom c where e.emp_id = c.emp_id) c where d.dept_code=c.dept_code group by d.dept_name; DEPT_NAME COUNT(C.DEPT_CODE) -------------------- ------------------ 영업기획 2 영업1 2 영업2 2 영업 2 select d.dept_name, count(e.emp_id) from tdept d, temp e where d.dept_code = e.dept_code and emp_id in (select e.emp_id from temp e, tcom c where e.emp_id=c.emp_id) group by d.dept_name; DEPT_NAME COUNT(E.EMP_ID) -------------------- --------------- 영업기획 2 영업1 2 영업2 2 영업 2

- Temp 테이블에서 과장 중 가장 급여를 작게 받는 사람보다 많이 받는 전체사원들의 사번, 이름, 급여를 출력

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

SQL> select emp_id, emp_name, salary 2 from temp 3 where salary > any (select salary from temp where lev='과장'); EMP_ID EMP_NAME SALARY ---------- ---------- ---------- 19970101 김길동 100000000 19960101 홍길동 72000000 19930331 정도령 70000000 19930402 강감찬 64000000 19950303 이순신 56000000 19970201 박문수 50000000 19966102 지문덕 45000000 19970112 연흥부 45000000 8 rows selected.

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

SQL> select dept_code, emp_id, emp_name, salary 2 from temp 3 where (dept_code, salary ) in (select dept_code, max(salary) 4 from temp 5 group by dept_code) 6 order by 1; DEPT_C EMP_ID EMP_NAME SALARY ------ ---------- ---------- ---------- AA0001 19970101 김길동 100000000 AB0001 19960101 홍길동 72000000 AC0001 19970201 박문수 50000000 BA0001 19930331 정도령 70000000 BB0001 19950303 이순신 56000000 BC0001 19966102 지문덕 45000000 CA0001 19930402 강감찬 64000000 CB0001 19960303 설까치 35000000 CC0001 19970112 연흥부 45000000 CD0001 19960212 배뱅이 39000000 10 rows selected.

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

1 2 3 4 5 6 7 8

select studno, name, birthdate from student where birthdate <= all (select birthdate from student) STUDNO NAME BIRTHDATE ---------- ---------- ------------ 10202 오유석 12-OCT-77

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

1 2 3 4 5 6 7 8 9

select grade, weight from (select grade, round(avg(nvl(weight,0))) weight from student group by grade) where weight = (select min(round(avg(nvl(weight,0)))) from student group by grade); G WEIGHT - ---------- 2 53

* 교수 테이블을 참조하여 다음 질문에 답하세요.

- 교수 테이블에서 평균 연봉보다 많이 받는 교수들의 교수 번호, 이름, 연봉을
연봉이 높은 순으로 정렬하여 출력하세요.(연봉은 sal*12+comm 으로 계산합니다)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

select profno, name, nvl(sal,0)*12+nvl(comm,0) sal from professor where nvl(sal,0)*12+nvl(comm,0) > (select avg(nvl(sal,0)*12+nvl(comm,0)) from professor) order by 3; PROFNO NAME SAL ---------- ---------- ---------- 9908 남은혁 4817 9906 이만식 5040 9905 권혁일 5425 9901 김도훈 6020 select profno, name, avg(nvl(sal,0)*12+nvl(comm,0)) sal from professor group by profno, name having avg(nvl(sal,0)*12+nvl(comm,0)) > (select avg(nvl(sal,0)*12+nvl(comm,0)) from professor) order by 3; PROFNO NAME SAL ---------- ---------- ---------- 9908 남은혁 4817 9906 이만식 5040 9905 권혁일 5425 9901 김도훈 6020

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

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; col 01 format 99 col 02 format 99 col 03 format 99 col 04 format 99 col 05 format 99 col 06 format 99 col 07 format 99 col 08 format 99 col 09 format 99 col 10 format 99 col 11 format 99 col 12 format 99 col "합계" format 999 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