9. 서브 쿼리 : 쿼리 안에 작성하는 쿼리

Q. 사원 중에서 월급을 가장 많이 받는 사원의 이름, 업무, 입사일, 급여 정보를 출력하는 쿼리를 작성하세요

SELECT ENAME, JOB, HIREDATE, SAL
FROM EMP
WHERE SAL = (SELECT MAX(SAL) FROM EMP);

 

9.1 서브쿼리의 종류

1. 서브 쿼리 실행 결과의 형태
   • 단일 행 서브쿼리
   • 다중 행 서브쿼리
   • 다중 열 서브쿼리
2. 서브 쿼리 동작 방식
   • 일반 서브쿼리
   • 상관 서브쿼리

Q1. EMP 테이블에서 각 업무별 평균 급여가 전체 사원의 평균 급여 이상인 경우, 그 결과를 출력하는 쿼리를 작성하세요.

SELECT JOB, AVG(SAL) 
FROM EMP
WHERE AVG(SAL) > (SELECT AVG(SAL) FROM EMP)
GROUP BY JOB;

라고 썼는데 안 된다.

WHERE 안에서 AVG()를 쓸 수 없기 때문

SELECT JOB, AVG(SAL) 
FROM EMP
GROUP BY JOB
HAVING AVG(SAL) >= (SELECT AVG(SAL) FROM EMP);

HAVING절을 사용해야 한다

Q2. 시카고에 근무하는 사원의 이름, 업무, 입사일, 부서 번호 정보를 출력하는 쿼리를 작성하세요

SELECT ENAME, JOB, HIREDATE, DEPTNO
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE LOC = 'CHICAGO');

그러나 이 쿼리는 잘못된 쿼리라고 한다.

만약 시카고에 있는 부서가 2개 이상이라면, 문제가 발생한다.

즉, 다중행 서브쿼리는 비교연산자와 함께 사용될 수 없다.

 

다중행 연산자

Q3. MARTIN 사원과 동일한 급여를 받는 사원의 이름, 업무, 입사일, 부서번호를 출력하세요

SELECT ENAME, JOB, HIREDATE, DEPTNO
FROM EMP
WHERE SAL = (SELECT SAL FROM EMP ENAME = 'MARTIN');

이 서브쿼리는 현재는 단일행을 반환하지만 동명이인이 생길 경우 얼마든지 다중행이 될 수 있다.

 

SELECT ENAME, JOB, HIREDATE, DEPTNO
FROM EMP
WHERE SAL IN (SELECT SAL FROM EMP ENAME = 'MARTIN');

 

Q4. EMP 테이블에서 한 명 이상의 부하직원을 가지는 관리자의 이름, 업무, 입사일, 급여, 부서번호를 출력하세요.

SELECT ENAME, JOB, HIREDATE, SAL, DEPTNO
FROM EMP
WHERE EMPNO IN (SELECT MGR FROM EMP);

Q5. EMP 테이블에서 부하 직원이 한 명도 없는 평사원의 이름, 업무, 입사일, 급여, 부서번호를 출력하세요.

SELECT ENAME, JOB, HIREDATE, SAL, DEPTNO
FROM EMP
WHERE EMPNO IN (SELECT MGR FROM EMP);

놓쳤다.

 

Q6. EMP 테이블에서 30번 부서에 속한 어떤 사원보다 더 많은 급여를 받는 사원들의 이름, 업무, 급여, 부서번호 정보를 출력하세요. 단, 30번 부서원은 제외합니다.

SELECT ENAME, JOB, SAL, DEPTNO
FROM EMP
WHERE SAL > ANY (SELECT SAL FROM EMP WHERE DEPTNO = 30) AND DEPTNO != 30;

 

Q7. EMP 테이블에서 30번 부서에 속한 모든 사원보다 더 많은 급여를 받는 사원들의 이름, 업무, 급여, 부서번호 정보를 출력하세요. 단, 30번 부서원은 제외합니다.

SELECT ENAME, JOB, SAL, DEPTNO
FROM EMP
WHERE SAL > ALL (SELECT SAL FROM EMP WHERE DEPTNO = 30) AND DEPTNO != 30;

ALL : 서브쿼리에서 반환하는 모든 값과 한번에 비교한다.

Q8. EMP 테이블에서 한 명 이상의 부하직원을 가지는 관리자의 이름, 업무, 입사일, 급여, 부서번호를 출력하세요.(Q4와 동일)

SELECT ENAME, JOB, HIREDATE, SAL, DEPTNO
FROM EMP
WHERE EMPNO = ANY (SELECT MGR FROM EMP);

 

Q9. EMP 테이블에서 한 명 이상의 부하직원을 가지는 관리자의 이름, 업무, 입사일, 급여, 부서번호를 출력하세요.(Q4와 동일)

SELECT ENAME, JOB, HIREDATE, SAL, DEPTNO
FROM EMP E
WHERE EXISTS (SELECT * FROM EMP WHERE MGR = E.EMPNO);

 

 

Q10. SCOTT 사원과 동일한 급여와 상여금을 받는 모든 사원의 정보를 출력하세요

SELECT *
FROM EMP
WHERE (SAL, NVL(COMM, -1)) IN (SELECT SAL, NVL(COMM, -1) FROM EMP WHERE ENAME = 'SCOTT');

다중열 서브쿼리

 

 

다음은 교재 문제 푼 흔적

SELECT JOB, EMPNO, ENAME, SAL, EMP.DEPTNO, DNAME
FROM EMP INNER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO
WHERE JOB IN (SELECT JOB FROM EMP WHERE ENAME = 'ALLEN')
ORDER BY SAL DESC;

SELECT EMPNO, ENAME, DNAME, HIREDATE, LOC, EMP.SAL, GRADE
FROM EMP INNER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO
		 INNER JOIN SALGRADE ON SAL BETWEEN LOSAL AND HISAL
WHERE SAL > ANY(SELECT AVG(SAL) FROM EMP)
ORDER BY SAL DESC, EMPNO;

SELECT EMPNO, ENAME, JOB, EMP.DEPTNO, DNAME, LOC
FROM EMP INNER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO
WHERE JOB NOT IN (SELECT JOB FROM EMP WHERE DEPTNO = 30) AND EMP.DEPTNO = 10;

SELECT EMPNO, ENAME, SAL, GRADE
FROM EMP INNER JOIN SALGRADE ON SAL BETWEEN LOSAL AND HISAL
WHERE SAL > ALL(SELECT SAL FROM EMP WHERE JOB = 'SALESMAN')
ORDER BY EMPNO;

 

 

FROM절에서 서브 쿼리 작성하기

SELECT *
FROM (SELECT ENAME, JOB, SAL, HIREDATE, DEPTNO
	  FROM EMP
      WHERE EMPNO IN (SELECT MGR FROM EMP)) MANAGER INNER JOIN DEPT
        ON MANAGER.DEPTNO = DEPT.DEPTNO;

SELECT ENAME, JOB, SAL, HIREDATE, DNAME, LOC
FROM (SELECT ENAME, JOB, SAL, HIREDATE, DEPTNO
	  FROM EMP
      WHERE EMPNO IN (SELECT MGR FROM EMP)) MANAGER INNER JOIN DEPT
        ON MANAGER.DEPTNO = DEPT.DEPTNO;

SELECT ENAME, JOB, SAL, HIREDATE, DNAME, LOC
FROM (SELECT ENAME, JOB, SAL, HIREDATE, DEPTNO
	  FROM EMP
      WHERE EMPNO IN (SELECT MGR FROM EMP ORDER BY MGR)) MANAGER INNER JOIN DEPT
        ON MANAGER.DEPTNO = DEPT.DEPTNO;

서브 쿼리에서는 ORDER BY를 쓸 수 없는 게 기본. ORDER BY는 최종 결과물에 적용하는 것이기 때문이다.

SELECT *
FROM (SELECT ENAME, JOB, SAL, HIREDATE, DEPTNO
	  FROM EMP
      WHERE EMPNO IN (SELECT MGR FROM EMP)
      ORDER BY ENAME);

FROM절에서 사용하는 서브쿼리에서는 ORDER BY를 사용할 수 있다. FROM절은 테이블을 바탕으로 하기 때문이다.

SELECT ENAME, JOB, SAL, HIREDATE, DNAME, LOC
FROM (SELECT ENAME, JOB, SAL, HIREDATE, DEPTNO
	  FROM EMP
      WHERE EMPNO IN (SELECT MGR FROM EMP)
      ORDER BY SAL DESC) MANAGER INNER JOIN DEPT
      	ON MANAGER.DEPTNO = DEPT.DEPTNO;

FROM절 서브쿼리에서 ORDER BY쓰기 + JOIN하기

 

SELECT절에서 서브 쿼리 사용하기

SELECT (SELECT COUNT(*) FROM EMP) TOTAL,
		(SELECT COUNT(*) FROM EMP WHERE TO_CHAR(HIREDATE, 'YYYY') = '1980') "1980",
		(SELECT COUNT(*) FROM EMP WHERE TO_CHAR(HIREDATE, 'YYYY') = '1981') "1981",
(SELECT COUNT(*) FROM EMP WHERE TO_CHAR(HIREDATE, 'YYYY') = '1982') "1982",
(SELECT COUNT(*) FROM EMP WHERE TO_CHAR(HIREDATE, 'YYYY') = '1983') "1983"
FROM DUAL;

 

WITH로 이름과 함께 서브쿼리를 먼저 쓰기

WITH
E10 AS (SELECT * FROM EMP WHERE DEPTNO = 10),
D AS (SELECT * FROM DEPT)
SELECT E10.EMPNO, E10.ENAME, E10.DEPTNO, D.DNAME, D.LOC
FROM E10 INNER JOIN D
	ON E10.DEPTNO = D.DEPTNO;

 

강사 기출 문제

1. EMP 테이블에서 BLAKE와 같은 부서에 있는 모든 사원의 이름과 입사일자를 출력하세요.

SELECT ENAME, HIREDATE
FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO FROM EMP WHERE ENAME = 'BLAKE');

 

2. EMP 테이블에서 평균 급여 이상을 받는 모든 사원에 대해, 사원번호와 이름을 출력하세요. 단, 급여가 많은 순.

SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL >= ANY(SELECT AVG(SAL) FROM EMP)
ORDER BY SAL DESC;

 

3. EMP 테이블에서 이름에 "T"가 있는 사원과 같은 부서에서 근무하는 모든 사원에 대해 사원번호, 이름, 급여를 출력하세요. 단, 사원번호 순.

SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO FROM EMP WHERE ENAME LIKE '%T%')
ORDER BY EMPNO;

 

4. EMP 테이블에서 부서 위치가 DALLAS인 모든 사원에 대해 이름, 업무, 급여를 출력하세요

SELECT ENAME, JOB, SAL
FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = 'DALLAS');

 

5. EMP 테이블에서 KING에게 보고하는 모든 사원의 이름과 급여를 출력하세요.

SELECT ENAME, SAL, MGR
FROM EMP
WHERE EMPNO IN (SELECT EMPNO FROM EMP WHERE MGR = 7839);

 

6. EMP 테이블에서 SALES부서 사원의 이름, 업무를 출력하세요.

SELECT ENAME, JOB, EMP.DEPTNO, DNAME
FROM EMP INNER JOIN DEPT
	 ON EMP.DEPTNO = DEPT.DEPTNO
WHERE DNAME IN (SELECT DNAME FROM DEPT WHERE DNAME = 'SALES');

 

7. EMP 테이블에서 월급이 30번 부서의 최고 급여보다 높은 급여를 받는 사원의 모든 정보를 출력하세요.

SELECT *
FROM EMP
WHERE SAL > (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 30);

8. EMP 테이블에서 부서 10번의 사원과 같은 업무를 맡고 있는 사원의 이름과 업무를 출력하세요. 단 10번 부서의 사원은 제외하세요.

SELECT ENAME, JOB
FROM EMP
WHERE JOB = ANY(SELECT JOB FROM EMP WHERE DEPTNO = '10')
	  AND DEPTNO != 10;

9. EMP 테이블에서 FORD와 업무도 월급도 같은 사원의 모든 정보를 출력하세요.

SELECT *
FROM EMP
WHERE (JOB, SAL) = (SELECT JOB, SAL FROM EMP WHERE ENAME = 'FORD');

10. EMP 테이블에서 업무가 JONES와 같거나 월급이 FORD 이상인 사원의 이름, 업무, 부서번호, 급여를 출력하세요.

SELECT ENAME, JOB, DEPTNO, SAL
FROM EMP
WHERE JOB IN (SELECT JOB FROM EMP WHERE ENAME = 'JONES')
	  OR SAL > ALL(SELECT SAL FROM EMP WHERE ENAME = 'FORD');

11. EMP 테이블에서 SCOTT 또는 WARD와 월급이 같은 사원의 이름, 업무, 급여를 출력하세요.

SELECT ENAME, JOB, SAL
FROM EMP
WHERE SAL = ANY(SELECT SAL FROM EMP WHERE ENAME = 'SCOTT' OR ENAME = 'WARD');

12. EMP 테이블에서 CHICAGO에서 근무하는 사원의 업무별 평균 급여와 최대, 최소 급여를 출력하세요.

SELECT DEPTNO, AVG(SAL), MAX(SAL), MIN(SAL)
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE LOC = 'CHICAGO')
GROUP BY DEPTNO;

13. EMP 테이블에서 부서별로 월급이 자신의 부서 평균 월급보다 높은 사원의 부서번호, 이름, 급여를 출력하세요.

 

14. 사원의 이름, 업무, 입사일, 부서번호, 부서명을 출력하세요. 단 조인은 사용하지 않습니다.