071 서브 쿼리 사용하기 1(단일행 서브쿼리)

예제. JONES보다 더 많은 월급을 받는 사원들의 이름과 월급을 출력하시오.

 

기존의 방법

SELECT ename, sal
FROM emp
WHERE ename = 'JONES';

1. JONES의 월급 확인

SELECT ename, sal
FROM emp
WHERE sal > 2975;

2. 월급 값을 WHERE절에 명시해 최종 확인

 

서브쿼리를 이용해 쿼리문 두 번 작성하는 대신 한 번에 해결하는 방법

SELECT ename, sal -- 메인 쿼리, OUTER 쿼리
FROM emp
WHERE sal > (SELECT sal -- 서브쿼리
            FROM emp
            WHERE ename = 'JONES');

---

문제 362. SCOTT과 같은 월급을 받는 사원들의 이름과 월급을 출력하시오.

SELECT ename, sal
FROM emp 
WHERE sal = (SELECT sal
            FROM emp
            WHERE ename = 'SCOTT');

---

문제 363. 위의 결과를 다시 출력하는데 SCOTT은 제외하고 출력하시오. 

SELECT ename, sal -- 메인 쿼리
FROM emp 
WHERE sal = (SELECT sal -- 서브 쿼리 
            FROM emp
            WHERE ename = 'SCOTT') 
    AND ename != 'SCOTT'; -- 메인 쿼리의 검색조건

---

문제 364. ALLEN보다 늦게 입사한 사원들의 이름과 입사일을 출력하시오. 

SELECT ename, hiredate
FROM emp
WHERE hiredate > (SELECT hiredate
                  FROM emp 
                  WHERE ename = 'ALLEN')
ORDER BY 2;

---

문제 368. 서울시 물가 데이터 중에 가장 가격(a_price)이 비싼 생필품명(a_name)과 그 가격(a_price)을 출력하시오. 

SELECT a_name, a_price
FROM price
WHERE a_price = (SELECT MAX(a_price)
		FROM price);

---

문제 369. 직업이 SALESMAN인 사원들 중에 최대월급을 받는 사원의 이름과 월급을 출력하시오.

SELECT ename, sal
FROM emp
WHERE sal = (SELECT MAX(sal)
            FROM emp
            WHERE JOB = 'SALESMAN')
AND job = 'SALESMAN'; -- 이 부분은 좀 더 확실하게 하기 위함. 같은 월급의 타 직업 직원이 있을 수 있기 때문에

 

 

오라클은 서브쿼리 먼저 수행함. 수행한 결과를 바탕으로 메인쿼리 수행.

---

문제 370. 위의 결과를 서브쿼리 이용하지 말고 수행하시오. 

SELECT ename, sal
FROM emp 
WHERE JOB = 'SALESMAN'
ORDER BY sal DESC FETCH FIRST 1 ROWS ONLY;

 

---

문제 371. 위 두 문제 SQL의 버퍼 갯수를 확인 하시오. 

369번 SQL문

SELECT /*+ gather_plan_statistics */ ename, sal
FROM emp
WHERE sal = (SELECT MAX(sal)
            FROM emp
            WHERE JOB = 'SALESMAN');

SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

버퍼의 갯수 : 12개

---

370번 SQL문

SELECT /*+ gather_plan_statistics */ ename, sal
FROM emp 
WHERE JOB = 'SALESMAN'
ORDER BY sal DESC FETCH FIRST 1 ROWS ONLY;

SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

버퍼의 갯수 : 6개

---

=> 370번의 SQL문은 emp테이블 전체 스캔을 1번만 하므로, 버퍼의 갯수가 적다. 

---

 문제 372. DALLAS의 부서번호를 출력하시오.

SELECT deptno
FROM dept
WHERE loc = 'DALLAS';

---

문제 373. DALLAS의 부서번호에서 근무하는 사원들의 이름과 월급을 출력하시오.

(JOIN 말고 서브쿼리로)

SELECT ename, sal
FROM emp
WHERE deptno = (SELECT deptno
		FROM dept
		WHERE loc = 'DALLAS');

---

문제 374. 위의 결과를 서브쿼리 쓰지 말고 조인으로 수행하시오.

SELECT ename, sal
FROM emp e, dept d
WHERE e.deptno = d.deptno 
    AND d.loc = 'DALLAS';

---

 문제 375.  KING에게 보고하는 사원들의 이름을 출력하시오.

SELECT ename
FROM emp
WHERE mgr = (SELECT empno 
		FROM emp 
        	WHERE ename = 'KING');