076 서브 쿼리 사용하기 6 (FROM절의 서브 쿼리)

절	서브쿼리 사용 가능	서브쿼리 이름
SELECT	O	Scalar Subquery
FROM	O	In line view
WHERE	O	Subquery
GROUP BY	X	X
HAVING	O	Scalar Subquery
ORDER BY	O	Scalar Subquery

 

---

문제 407. (복습문제) 이름, 월급, 월급에 대한 순위를 출력하시오. 

SELECT ename, sal, DENSE_RANK() OVER (ORDER BY sal DESC) AS 순위
FROM emp;

---

문제 408. 위의 결과에서 순위가 3등과 5등을 출력하시오. 

SELECT ename, sal
FROM (SELECT ename, sal, 
            DENSE_RANK() OVER (ORDER BY sal DESC) AS 순위 
            FROM emp)
WHERE 순위 IN (3,5);

* FROM절의 서브쿼리를 써야 위의 결과를 볼 수 있음. 

---

SELECT ename, sal, DENSE_RANK() OVER (ORDER BY sal DESC) AS 순위 
FROM emp
WHERE 순위 IN (3,5);

위와 같은 경우는 오류 발생. 

수행 순서를 생각해보면 where가 먼저 실행되므로, 순위가 아직 설정되지 않은 상태에서 filter할 수 없기 때문임. 

 

---

 문제 409. 직업, 이름, 월급, 순위를 출력하는데 순위가 직업별로 각각 월급이 높은 순서대로 순위를 부여하시오.

SELECT job, ename, sal, DENSE_RANK() OVER (PARTITION BY job ORDER BY sal DESC) AS 순위
FROM emp;

---

문제 410. 위의 결과를 다시 출력하는데 순위가 1등인 사원들만 출력하시오. 

SELECT *
FROM (SELECT job, ename, sal, 
            DENSE_RANK() OVER (PARTITION BY job
            ORDER BY sal DESC)AS 순위
        FROM emp)
WHERE 순위 = 1;

---

문제 411. (복습문제) 사원 테이블의 평균 월급을 출력하시오.

SELECT avg(sal)
FROM emp;

---

문제 412. 사원테이블에서 이름, 월급, 사원 테이블의 평균월급을 출력하시오.

SELECT ename, sal, avg(sal) over () 평균월급
from emp;

---

문제 413. 위의 결과를 다시 출력하는데 자기의 월급이 사원 테이블의 평균월급보다 더 큰 사원들만 출력하시오.

SELECT *
FROM (SELECT ename, sal, AVG(sal) OVER () 평균월급
        FROM emp)
WHERE sal > 평균월급;

---

문제 414. (복습문제) 부서번호, 부서번호별 평균 월급을 출력하시오.

SELECT deptno, AVG(sal)
FROM emp
GROUP BY deptno;

---

문제 415. 부서번호, 이름, 월급, 자기가 속한 부서번호의 평균월급을 출력하시오.

SELECT deptno, ename, sal, AVG(sal) OVER (PARTITION BY deptno) AS 부서평균
FROM emp;

---

문제 416. 위의 결과를 다시 출력하는데, 자기의 월급이 자기 부서번호의 평균월급보다 더 큰 사원들만 출력하시오. 

SELECT *
FROM (SELECT deptno, ename, sal,
            AVG(sal) OVER (PARTITION BY deptno) AS 부서평균
        FROM emp)
WHERE sal > 부서평균;

---

문제 417. 위의 결과를 보다 느린 SQL로 작성하시오. (튜닝 전 버전)

SELECT E.deptno, E.ename, E.sal, V.부서평균
FROM emp E , (SELECT deptno, AVG(sal) 부서평균
		FROM emp
		GROUP BY deptno) V
WHERE E.deptno = V.deptno AND E.sal > V.부서평균
ORDER BY 1;

emp 테이블을 2번 스캔하기 때문에 416과 같은 결과를 반환하지만 더 느리다.