subquery.sql

​

서브 쿼리 ( subquery )

- 서브쿼리는 하나의 select 문장 안에 또 하나의 select 문을 사용한는 것입니다

- 서브쿼리는 비교 연산의 오른쪽에 작성해야 하고, 반드시 괄호() 로 묶어줘야 합니다.

- 서브쿼리는 메인 쿼리가 실행되기 전에 한번만 실행됩니다.

- 하나의 테이블에서 검색한 결과를 다른 테이블에 전달하여 새로운 결과를 검색합니다.

​

SMITH 사원 부서명

> 서브쿼리의 실행 결과를 메인쿼리에게 반환합니다

서브쿼리는 메인쿼리가 필요한 값을 제공합니다.

SELECT dname

FROM dept

WHERE deptno=(SELECT deptno
              FROM emp
              WHERE ename='SMITH');

          ![](https://velog.velcdn.com/images/smsee3/post/15cee682-b458-4583-a9e5-c8bdf7dde826/image.png)
          

평균 급여보다 더 많은 급여를 받은 사원 조회

SELECT ename, sal
FROM emp
WHERE sal > (SELECT AVG(sal)
            FROM emp);

--특정 사원의 급여와 동일하거나 더 많이 받는 사원의 이름, 급여 조회

SELECT ename, sal
FROM emp
WHERE sal >=(SELECT sal
             FROM emp
             WHERE ename='ALLEN')
AND ename!= 'ALLEN';        

quiz

-- DALLAS 에서 근무하는 사원의 이름, 부서번호를 출력하세요

   SELECT E.ename,E.deptno "부서번호"
   FROM emp E
   WHERE E.deptno=(SELECT D.deptno
                 FROM dept D
                 WHERE D.loc='DALLAS');

-- SALES 부서에서 근무하는 모든 사원의 이름, 급여를 출력하세요

   SELECT E.ename,E.sal
   FROM emp E
   WHERE e.deptno=(SELECT D.deptno
                 FROM dept D
                 WHERE DNAME='SALES');

-- 직속 상관이 KING 인 사원의 이름, 급여를 출력하세요

SELECT E.ename, E.sal
FROM emp E
WHERE MGR=(SELECT E.empno
           FROM emp E 
           WHERE ename='KING');

-- 사원 테이블에서 최대 급여를 받는 사원을 출력하세요

SELECT E.ename
FROM emp E
WHERE E.sal =(SElECT MAX(sal)
              FROM emp);

-- 사원 테이블에서 20번 부서의 최소 급여보다 많은 부서를 출력하세요

SELECT deptno, MIN(sal)
FROM emp 
GROUP BY deptno
HAVING MIN(sal) > (SELECT MIN(sal)
                FROM emp
                WHERE deptno=20);

            ​

다중해 서브쿼리

• 서브쿼리에서 반환되는 결과가 하나 이상일 때 사용하는 서브쿼리 입니다

• 다중행 서브쿼리는 반드시 다중행 연산자와 함께 사용해야 합니다.

  IN, ANY, ALL, EXISTS

  IN

• 메인 쿼리의 비교 조건이 서브쿼리 결과 중에서 하나라도 일치하면 참 입니다

급여를 3000이상 받는 사원이 소속된 부서와 동일한 부서에서 근무하는 사원

--IN 사용해서

  SELECT ename, sal, deptno
FROM emp
WHERE deptno IN (SELECT DISTINCT deptno
                 FROM emp
                 WHERE sal>=3000); 

# ANY

- 메인쿼리의 비교 조건이 서브쿼리의 결과와 하나 이상이 일치하면 참 입니다

ANY : 검색 결과에 대해서 하나라도 크면 참
< ANY : 검색 결과에 대해서 하나라도 작으면 참

--30번 부서에서 가장 작은 급여를 받는 사원보다 많은 급여를 받는 사원의 이름, 급여를 출력

   SELECT empno,ename sal,deptno
FROM emp
WHERE sal > ANY(SELECT sal
                FROM emp
                WHERE deptno=30);

            ![](https://velog.velcdn.com/images/smsee3/post/1379b890-e300-445a-b2b7-5b2ba5a343a1/image.png)

ALL

- 메인쿼리의 비교 조건이 서브쿼리의 검색 결과와 모두 일치하면 참입니다.

 SELECT ename, sal
FROM emp
WHERE sal > All(SELECT sal
                FROM emp
                WHERE deptno=30);

​

EXISTS

- 메인쿼리의 비교 조건이 서브쿼리의 결과 중에서 만족하는 값이 하나라도 있으면 참입니다

- IN 과의 차이점 : IN 연산자는 실제 존재하는 데이터들의 모든 값까지 확인하지만,

EXISTS 연산자는 해당 행이 존재하는지의 여부만 확인합니다

​

emp 테이블에 있는 deptno 와 서브쿼리에 있는 dept 테이블의 deptno 를 조인해서

deptno 10, 20 이 있으면 emp 테이블의 이름, 부서번호, 급여 출력

SELECT ename, deptno, sal
FROM emp E
WHERE EXISTS (SELECT 1
FROM dept D
WHERE D.deptno=D.
deptno
AND E.deptno IN (10,20));

quiz

​

부서별로 가장 급여를 많이 받는 사원의 정보(사원번호, 사원이름, 급여, 부서번호) 를 출력하세요

SELECT empno,ename, sal,deptno

FROM emp

WHERE sal IN (SELECT MAX(sal)
              FROM emp
              GROUP BY deptno);

job 이 MANAGER 인 사람이 속한 부서의 부서번호, 부서명, 지역을 출력하세요

SELECT E.deptno, D.DNAME, D.loc

FROM emp E, dept D

WHERE D.deptno IN(SELECT E.deptno
                FROM emp E
                WHERE job = 'MANAGER');

​

SALESMAN 의 최소 급여보다 많이 받는 사원들의 이름, 급여, 직급을 출력하세요

SELECT ename, sal, job

FROM emp 

WHERE sal >  ANY(SELECT MIN(sal)
                FROM emp
                WHERE job = 'SALESMAN');

​

SALESMAN 보다 급여를 많이 받는 사원들의 이름, 급여, 직급을 출력하세요

SELECT ename, sal, job

FROM emp

WHERE sal > ALL(SELECT MAX(sal)
                FROM emp
                WHERE job = 'SALESMAN');

​

emp 테이블에서 적어도 한명의 사원으로부터 보고를 받을 수 있는 사원의 정보(사원번호, 이름, 업무, 입사일자, 급여)를 사원번호 순으로 내림차순 정렬해서 출력하세요

SELECT empno, ename, job, hiredate, sal

FROM emp E

WHERE EXISTS(SElECT *
             FROM emp 
             WHERE E.empno=mgr)
ORDER BY empno ;