SQL 문법(Sub query)

1. 문법설명 가. 서브쿼리의 사용위치 select (select …) <—– 스칼라 서브쿼리 from (select …) <—– 인라인 뷰 where col_name > (select …) <—– 서브쿼리(연산자 오른쪽에 사용, 서브쿼리 안쪽에 order by 절을 사용하지 않음 나. 서브 쿼리 종류 – 단일 행 서브쿼리 : 서브쿼리의 리턴값으로 하나의 행만 메인쿼리로 반환 – 다중 행 서브쿼리 : 하나 이상의 행을 메인 쿼리로 반환 – 다중 컬럼 서브쿼리 : 하나 이상의 컬럼을 메인 쿼리로 반환 – 상호 관련 서브쿼리 : 서브쿼리와 메인 쿼리간에 결과값을 서로 주고 받는 식으로 처리되는 서브쿼리 다. 다중 행 서브쿼리 에서 사용하는 비교 연산자 – in : 메인쿼리의 비교조건이 서브쿼리 결과중에서 하나라도 일치하면 참, ‘=’ 비교만 가능 – any, some : 메인쿼리의 비교조건이 서브쿼리의 결과중에서 하나 이상 일치하면 참 ‘=, <, >’ 사용 가능 – all : 메인쿼리의 비교조건이 서브쿼리의 결과중에서 모든값이 일치하면 참 – exists : 메인쿼리의 비교조건이 서브쿼리 결과중에서 만족하는 값이 하나라도 존재하면 참
참고 : < any : 최대값 > any : 최소값 > all : 최대값 < all : 최소값
2. 연습문제 – 학생테이블에서 이광훈 학생과 같은 학과의 학생들의 이름과 학과이름을 출력
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.
– 이광훈 학생의 학과의 평균몸무게보다 작은 학생들의 학생이름과 학생의 몸무게, 각 학생들의 학과이름과 지도교수 이름을 출력
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 테이블을 사용하여 인천에 근무하는 직원의 사번과 성명을 출력
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을 받는 인원수를 출력
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 테이블에서 과장 중 가장 급여를 작게 받는 사람보다 많이 받는 전체사원들의 사번, 이름, 급여를 출력
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에서 부서별 최고 연봉을 확인하고, 사원중에 해당 부서와 최고 연봉금액이 일치하는 사원의 사번 , 성명, 연봉을 출력
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.
– 학생 중에서 생년월일이 가장 빠른 학생의 학번, 이름, 생년월일을 출력하세요
select studno, name, birthdate
from student
where birthdate <= all (select birthdate from student)


    STUDNO NAME       BIRTHDATE
---------- ---------- ------------
     10202 오유석     12-OCT-77
– 학년별로 평균 체중이 가장 적은 학년과 평균 몸무게를 출력하세요.
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 으로 계산합니다)
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 테이블을 조회하여 각 월별 생일자가 몇명인지 아래와 같이 출력하세요.
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

You may also like...

답글 남기기

이메일 주소는 공개되지 않습니다. 필수 항목은 *(으)로 표시합니다