/*- 05_서브쿼리.sql -*/

/*
 * # 서브 쿼리 ( subquery )
 * - 하나의 SELECT 문장 안에 또 하나의 SELECT 문을 사용. 
 * - 비교 연산의 오른쪽에 작성해야 하고, 반드시 괄호()로 묶어줘야 함.
 * - 메인 쿼리 실행 전에 한번만 실행.
 * - 하나의 테이블에서 검색한 결과를 다른 테이블에 전달하여 새로운 결과를 검색.
 */

-- S1
SELECT DEPTNO FROM EMP WHERE ENAME LIKE ('%SMITH%');

-- S2
SELECT DNAME FROM DEPT WHERE DEPTNO LIKE (20);

-- 서브쿼리(S1) 의 실행 결과를 메인쿼리(S2)에게 반환.
-- 서브쿼리는 메인쿼리가 필요한 값을 제공.

-- SMITH 사원의 부서명
SELECT DNAME FROM DEPT WHERE DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME LIKE '%SMITH%');
SELECT (SELECT DNAME FROM DEPT WHERE DEPTNO LIKE (20)) "부서명", DEPTNO "부서번호" FROM EMP WHERE ENAME LIKE ('%SMITH%');

-- SMITH 와 같은 부서에 근무하는 사원의 이름과 부서번호
SELECT ENAME, DEPTNO FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME LIKE ('%SMITH%')) AND ENAME NOT LIKE ('%SMITH%');

-- 서브쿼리에서 그룹 함수.
-- 평균 급여보다 더 많은 급여를 받는 사원의 이름 조회.
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 LIKE ('%ALLEN%')) ORDER BY SAL ASC;

/* quiz */
-- DALLAS 에서 근무하는 사원의 이름, 부서번호를 출력하세요
SELECT ENAME, DEPTNO FROM EMP WHERE DEPTNO LIKE (SELECT DEPTNO FROM DEPT WHERE LOC LIKE ('%DALLAS%'));
-- SALES 부서에서 근무하는 모든 사원의 이름, 급여를 출력하세요
SELECT ENAME, SAL FROM EMP WHERE DEPTNO LIKE (SELECT DEPTNO FROM DEPT WHERE DNAME LIKE ('%SALES%'));
-- 직속 상관이 KING 인 사원의 이름, 급여를 출력하세요
SELECT ENAME, SAL FROM EMP WHERE MGR LIKE (SELECT EMPNO FROM EMP WHERE ENAME LIKE ('%KING%'));
-- 사원 테이블에서 최대 급여를 받는 사원을 출력하세요
SELECT ENAME FROM EMP WHERE SAL LIKE (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 LIKE 20);

/*
 * # 다중행 서브쿼리
 * - 서브쿼리에서 반환되는 결과가 하나 이상일 때 사용하는 서브쿼리.
 * - 다중행 서브쿼리는 반드시 다중행 연산자와 함께 사용.
 *   > IN, ANY, ALL, EXISTS
 */
 
 -- IN
 -- 메인 쿼리의 비교 조건이 서브쿼리 결과 중에서 하나라도 일치하면 참.
 
 -- 급여를 3000 이상 받는 사원이 소속된 부서와 동일한 부서에서 근무하는 사원.
SELECT ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO IN (SELECT DISTINCT DEPTNO FROM EMP WHERE SAL >= 3000);
 

/* 
 * # ANY
 * - 메인 쿼리의 비교 조건이 서브쿼리의 결과와 하나 이상 일치하면 참.
 *   > ANY : 검색 결과에 대해서 하나라도 크면 참.
 *   < ANY : 검색 결과에 대해서 하나라도 작으면 참.
 */
 
-- 30번 부서에서 가장 작은 급여를 받는 사원보다 많은 급여를 받는 사원의 이름, 급여 출력.
-- > : 서브쿼리에서 하나라도 참이면 참.
SELECT ENAME, SAL FROM EMP WHERE SAL > ANY(SELECT SAL FROM EMP WHERE DEPTNO LIKE 30);

/*
 * # ALL
 * - 메인쿼리의 비교 조건이 서브쿼리의 검색 결과와 모두 일치하면 참.
 */
 
-- 30번 부서에서 가장 많이 급여를 받는 사원보다 더 많은 급여를 받는 사원의 이름, 급여
SELECT ENAME, SAL FROM EMP WHERE SAL > ALL(SELECT SAL FROM EMP WHERE DEPTNO LIKE 30);

/*
 * # EXISTS
 * - 메인쿼리의 비교 조건이 서브쿼리의 결과 중에서 만족하는 값이 하나라도 있으면 참.
 * - IN 과의 차이점 : IN 연산자는 실제 존재하는 데이터들의 모든 값까지 확인하지만,
 *                 EXISTS 연산자는 해당 행이 존재하는지의 여부만 확인.
 */
 
-- EMP 테이블에 있는 DEPTNO와 서브쿼리에 있는 DEPT 테이블의 DEPTNO 를 조인해서
-- DEPTNO 가 10, 20 이 있으면 ENP 테이블의 이름, 부서번호, 급여 출력.
SELECT 1 FROM EMP E, DEPT D WHERE D.DEPTNO IN (10,20) AND E.DEPTNO=D.DEPTNO;

SELECT ENAME, DEPTNO, SAL FROM EMP E WHERE EXISTS(SELECT 1 FROM DEPT D WHERE D.DEPTNO IN (10,20) AND E.DEPTNO=D.DEPTNO);

/* QUIZ */
SELECT * FROM DEPT;
-- 부서별로 가장 급여를 많이 받는 사원의 정보(사원번호, 사원이름, 급여, 부서번호) 를 출력하세요
SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP WHERE SAL IN (SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);
-- job 이 MANAGER 인 사람이 속한 부서의 부서번호, 부서명, 지역을 출력하세요
SELECT DEPTNO, DNAME, LOC FROM DEPT WHERE EXISTS(SELECT 1 FROM EMP WHERE JOB LIKE ('%MANAGER%') AND EMP.DEPTNO=DEPT.DEPTNO);
-- SALESMAN 의 최소 급여보다 많이 받는 사원들의 이름, 급여, 직급을 출력하세요
SELECT ENAME, SAL, JOB FROM EMP WHERE SAL > ANY (SELECT SAL FROM EMP WHERE JOB LIKE ('%SALESMAN%')) AND JOB NOT LIKE ('%SALESMAN%');
-- SALESMAN 보다 급여를 많이 받는 사원들의 이름, 급여, 직급을 출력하세요
SELECT ENAME, SAL, JOB FROM EMP WHERE SAL > ALL (SELECT SAL FROM EMP WHERE JOB LIKE ('%SALESMAN%'));
-- emp 테이블에서 적어도 한명의 사원으로부터 보고를 받을 수 있는
-- 사원의 정보(사원번호, 이름, 업무, 입사일자, 급여)를 사원번호 순으로 내림차순 정렬해서 출력하세요
SELECT E1.EMPNO, E1.ENAME, E1.JOB, E1.HIREDATE, E1.SAL FROM EMP E1 WHERE EXISTS(SELECT 1 FROM EMP E2 WHERE E1.EMPNO = E2.MGR) ORDER BY EMPNO DESC;