SUB QUERY (서브쿼리)

SUB QUERY(서브쿼리)

 

 

기본 문법	SELECT SELECT_LIST FROM TABLE 또는 VIEW WHERE 조건 연산자 (SELECT SELECT_LIST FROM TABLE WHERE 조건);
유형	SELECT ENAME, SAL FROM EMP WHERE SAL>(SELECT SAL FROM EMP WHERE ENAME='SCOTT');

 

 

■ 단일행 서브쿼리

--단일행 SUB QUERY 연습문제1

--STUDENT 테이블과 DEPARTMENT 테이블을 사용하여 이윤나 학생과 1전공(DEPTNO1)이 동일한 학생들의 이름과

--1전공 이름을 출력하세요

 

SELECT S.NAME, D.DNAME

FROM STUDENT S, DEPARTMENT D

WHERE S.DEPTNO1 = (SELECT DEPTNO1

FROM STUDENT

WHERE NAME = '이윤나')

AND S.DEPTNO1 = D.DEPTNO;

 

 

--단일행 SUB QUERY 연습문제2

--PROFESSOR 테이블에서 입사일이 송도권 교수보다 나중에 입사한 사람의 이름과 입사일, 학과명을 출력하세요

 

SELECT P.NAME, P.HIREDATE, D.DNAME

FROM PROFESSOR P, DEPARTMENT D

WHERE HIREDATE > (SELECT HIREDATE

FROM PROFESSOR

WHERE NAME = '송도권')

AND P.DEPTNO = D.DEPTNO;

 

--단일행 SUB QUERY 연습문제3

--STUDENT 테이블에서 1전공(DEPTNO1)이 101번 인 학과의 평균 몸무게보다 몸무게가 많은 학생들의 이름과 몸무게를

--출력하세요.

 

SELECT NAME,WEIGHT

FROM STUDENT

WHERE WEIGHT > (SELECT AVG(WEIGHT)

FROM STUDENT

WHERE DEPTNO1 = 101);

 

--단일행 SUB QUERY 연습문제4

--PROFESSOR 테이블에서 심슨 교수와 같은 입사일에 입사한 교수 중에서 조인형 교수보다 월급을 적게 받는 교수의

--이름과 급여, 입사일을 출력하세요

 

SELECT NAME,PAY,HIREDATE

FROM PROFESSOR

WHERE PAY < (SELECT PAY

FROM PROFESSOR

WHERE NAME = '조인형')

AND HIREDATE = (SELECT HIREDATE

FROM PROFESSOR

WHERE NAME = '심슨');

 

■  다중 행 SUB QUERY 

SUB QUERY의 결과과 2건 이상 출력되는 것을 말한다. 다중 행 SUB QUERY는 SUB QUERY의 결과가 여러건 출력되기 때문에 단일 행 연산자를 사용할 수 없다. 그래서 다중 행 SUB QUERY의 경우 아래와 같이 별도의 연산자가 존재함

 

연산자	의미
IN	같은 값을 찾음
>ANY	최소 값을 반환함
<ANY	최대 값을 반환함
<ALL	최소 값을 반환함
>ALL	최대 값을 반환함
EXISTS	SUB QUERY의 값이 있을 경우 반환함

 

SAL > ANY (100,200,300) 으로 되면 ANY 자리에 최소값인 100이 반환

SAL > 100이라는 식으로 표현

 

SAL < ALL (100,200,300) 이 되면 SUB QUERY의 최소 값인 100이 반환되긴 하지만 식은

SAL < 100이 되 버림

 

 

--EMP 테이블의 부서 별로 가장 급여를 많이 받는 사원의 사원번호 , 이름, 급여 , 부서번호를 조회하여라

 

SELECT EMPNO,ENAME,SAL,DEPTNO

FROM EMP

WHERE SAL IN (SELECT MAX(SAL)

FROM EMP

GROUP BY DEPTNO);

 

--다중 행 SUB QUERY

--EMP2 테이블과 DEPT2 테이블을 참조하여 근무지역(DEPT2 테이블의 AREA 컬럼)이 서울 지사인 모든 사원들의

--사번과 이름, 부서번호를 출력

 

SELECT EMPNO, NAME, DEPTNO

FROM EMP2

WHERE DEPTNO IN (SELECT DCODE

FROM DEPT2

WHERE AREA = '서울지사');

 

-- 업무가 SALESMAN 인 최소 한 명 이상의 사원보다 급여를 많이 받는 사원의 이름, 업무를 조회하여라

 

SELECT ENAME, JOB, SAL

FROM EMP

WHERE SAL > ANY( SELECT SAL

FROM EMP

WHERE JOB = 'SALESMAN')

AND JOB !='SALESMAN';

 

-- 다중 행 SUB QUERY 예 2

-- EMP2 테이블을 사용하여 전체 직원 중 과장 직급의 최소 연봉자보다 연봉이 높은 사람의 이름과 직급,

-- 연봉을 출력하세요. 단 연봉 출력 형식은 아래와 같이 천 단위 구분기호와 원 표시를 하세요

 

SELECT NAME, POSITION, PAY

FROM EMP2

WHERE 1=1

AND POSITION != '과장'

AND PAY > ANY(SELECT PAY

FROM EMP2

WHERE POSITION = '과장');

 

 

-- 다중 행 SUB QUERY 예 3

-- STUDENT 테이블을 조회하여 전체 학생 중에서 체중이 4학년 학생들의 체중이 4학년 학생들의 체중에서 가장 적게

-- 나가는 학생보다 몸무게가 적은 학생의 이름과 학년과 몸무게를 출력하세요.

 

SELECT NAME, GRADE, WEIGHT

FROM STUDENT

WHERE WEIGHT < ALL(SELECT WEIGHT

FROM STUDENT

WHERE GRADE =4)

 

   

■  다중 컬럼 SUB QUERY

서브쿼리로 출력되는 컬럼수가 2개 이상 출력되는 서브 쿼리

 

SELECT EMPNO, ENAME, SAL, DEPTNO

FROM EMP

WHERE (DEPTNO,SAL) IN (SELECT DEPTNO, MAX(SAL)

FROM EMP

GROUP BY DEPTNO);

 

-- 다중 컬럼 SUB QUERY 1

-- STUDENT 테이블을 조회하여 각 학년별로 최대키를 가진 학생들의 학년과 이름과 키를 출력하세요

 

SELECT GRADE,NAME,HEIGHT

FROM STUDENT

WHERE (GRADE,HEIGHT) IN (SELECT GRADE,MAX(HEIGHT)

FROM STUDENT

GROUP BY GRADE)

ORDER BY 1;

 

-- 다중 컬럼sub query 연습문제 1:

-- PROFESSOR 테이블을 조회하여 각 학과 별로 입사일이 가장 오래된 교수의 교수번호와 이름, 학과명을 출력하세요

-- (학과이름순으로 오름차순 정렬)

 

SELECT P.NAME, D.DNAME

FROM PROFESSOR P , DEPARTMENT D

WHERE (P.DEPTNO,P.HIREDATE) IN ( SELECT DEPTNO,MIN(HIREDATE)

FROM PROFESSOR

GROUP BY DEPTNO )

AND P.DEPTNO = D.DEPTNO

ORDER BY D.DNAME ;

 

 

-- 다중 컬럼 SUB QUERY 연습문제 2

-- EMP2 테이블을 조회하여 직급별로 해당 직급에서 최대 연봉을 받는 직원의 이름이 직급, 연봉을 출력하세요

-- 연봉순으로 오름차순 정렬하세요

 

SELECT NAME, PAY

FROM EMP2

WHERE (POSITION,PAY) IN ( SELECT POSITION,MAX(PAY)

FROM EMP2

GROUP BY POSITION)

ORDER BY PAY;

 

-- 다중 컬럼 SUB QUERY 연습문제 3 -- 다중행 컬럼 서브쿼리

-- EMP2 테이블을 조회하여 각 부서별 평균 연봉을 구하고 그 중에서 평균 연봉이 가장 적은 부서의

-- 평균 연봉보다 적게 받는 직원들의 부서명, 직원명, 연봉을 출력하세요.

-- ( 단, 연봉이 적은 순으로 나열하세요)

 

SELECT D.DNAME,E.NAME,E.PAY

FROM EMP2 E, DEPT2 D

WHERE PAY < ALL (SELECT AVG(PAY)

FROM EMP2

GROUP BY DEPTNO)

AND D.DCODE = E.DEPTNO

ORDER BY PAY;

 

 

--STUDENT 테이블을 이용하여 사용자 아이디가 BINGO인 학생과 같은 학년인 학생의

--학번, 이름, 학년을 출력하여라

 

SELECT STUDNO, NAME, GRADE

FROM STUDENT

WHERE GRADE = (SELECT GRADE

FROM STUDENT

WHERE ID = 'bingo');

 

 

 

--STUDENT 테이블을 이용하여 101번 학과 학생들의 평균 몸무게보다 적은 학생의 이름, 학과번호,몸무게를 출력하여라.

 

SELECT NAME, DEPTNO1, WEIGHT

FROM STUDENT

WHERE WEIGHT < ( SELECT AVG(WEIGHT)

FROM STUDENT

WHERE DEPTNO1 = 101 );

 

 

 

--STUDENT,DEPARTMENT 테이블을 이용하여 정보(PART:100)에 소속된 모든 학생의 학번, 이름, 학과번호를 출력하여라

 

SELECT STUDNO, NAME, DEPTNO1

FROM STUDENT

WHERE DEPTNO1 IN (SELECT DEPTNO

FROM DEPARTMENT

WHERE PART = 100);

 

 

--STUDENT 테이블을 이용하여 모든 학생 중에서 4학년 학생 중 키가 제일 작은 학생보다 키가 큰 학생의

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

 

SELECT STUDNO, NAME, HEIGHT

FROM STUDENT

WHERE HEIGHT > ANY( SELECT HEIGHT

FROM STUDENT

WHERE GRADE = 4);

 

-- STUDENT 테이블을 이용하여 학년별로 몸무게가 최소인 학생의 이름, 학년, 몸무게를 출력하여라

 

SELECT NAME, GRADE, WEIGHT

FROM STUDENT

WHERE (GRADE,WEIGHT) IN (SELECT GRADE,MIN(WEIGHT)

FROM STUDENT

GROUP BY GRADE);

 

 

■  상호 연관 SUB QUERY 

- 메인쿼리절과 서브쿼리간에 검색 결과를 교환하는 서브쿼리

- 메인쿼리와 서브쿼리간의 결과를 교환하기 위하여 서브쿼리의 WHERE 조건절에서 메인쿼리의 테이블과 연결

*주의*

   행을 비교할 때마다 결과를 메인으로 반환하는 관계로 처리 성능이 저하될 수 있음

 

--상호 연관 SUB QUERY

-- EMP2 테이블을 조회해서 직원 들 중에서 자신의 직급의 평균연봉과 같거나 많이 받는 사람들의

--이름과 직급, 현재 연봉을 출력하세요

 

SELECT NAME, POSITION, PAY

FROM EMP2 A

WHERE PAY >= (SELECT AVG(PAY)

FROM EMP2 B

GROUP BY A.POSITION = B.POSITION);

 

■ 스칼라서브쿼리 : SELECT절에서 함수처럼 사용되는 쿼리문

- 하나의 레코드만 리턴이 가능( 반환값은 한 개) 두 개이상의 레코드는 리턴 X

- 일치하는 데이터가 없더라고 NULL 값을 리턴 할 수 있다.

 

SELECT D.DEPTNO, (SELECT MIN(EMPNO) FROM EMP WHERE DEPTNO = D.DEPTNO)

FROM DEPT D

ORDER BY D.DEPTNO;

 

■ 인라인뷰(상호연관 SUB QUERY 보다 성능이 좋음)

- FROM 절에서 임시 공간에 테이블을 생성하여 사용하는 뷰와 비슷한 저장 형태

- FROM 절에 있는 서브쿼리가 인라인 뷰를 생성

- 효율적인 검색 가능

- FROM 절에 있는 서브쿼리에는 자주 별칭 사용

 

--STUDENT 테이블을 이용하여 각 학년 별로 평균키보다 큰 학생들의 이름, 학년, 키를 출력하여라

 

SELECT NAME, A.GRADE, HEIGHT

FROM STUDENT A, (SELECT GRADE,AVG(HEIGHT) AS "B_HEIGHT"

FROM STUDENT

GROUP BY GRADE) B

WHERE A.HEIGHT > B_HEIGHT

AND A.GRADE = B.GRADE

ORDER BY GRADE,HEIGHT;