7. SUBQUERY

© qimono, 출처 Pixabay

Subquery

개념 : 하나의 SQL 쿼리문 속에 다른 SQL 쿼리문이 포함되어 있는 형태

 

실습1. 'SCOTT'보다 월급이 많은 사람의 이름은?

1. SCOTT의 월급 찾기

2. SCOTT의 월급보다 월급이 많은 사람 찾기

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

 

Single-Row Subquery

개념 : Subquery의 결과가 한 행인 경우

 

실습2.

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

 

실습 3.

SELECT ename, deptno FROM emp 
WHERE deptno = (SELECT deptno FROM dept WHERE dname = 'SALES');

 

Multi-Row Subquery

개념 : Subquery의 결과가 둘 이상의 행인 경우

 

(주의) Multo-Row에 대한 연산을 사용해야함(ANY, ALL, IN, EXIST...)

 

실습 4.

SELECT ename, sal, deptno FROM emp 
WHERE ename IN (SELECT MIN(ename) 
FROM emp GROUP BY deptno);

 

실습 5. 각 부서별로 최고급여를 받는 사원을 출력하시오

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

 

SELECT e.deptno , e.empno , e.ename , e.sal FROM emp e 
,(SELECT s.deptno, MAX(s.sal) msal FROM emp s GROUP BY deptno ) m 
WHERE e.deptno = m.deptno AND e.sal = m.msal;

 

SELECT deptno , empno , ename , sal FROM emp e 
WHERE e.sal = (SELECT max(sal) 
FROM emp WHERE deptno = e.deptno);

 

Top-K Query

개념 : 조건을 만족하는 상위 K개의 결과를 빨리 얻을 수 있는 방법

 

실습 6. 85년도에 입사한 사람들 중 월급이 가장 많은 3명은 누구인가?

SELECT rownum , ename , sal 
FROM (SELECT * FROM emp 
WHERE hiredate like '85%' 
ORDER BY sal DESC) 
WHERE rownum < 4;

 

(WHERE rownum < 4) = (FETCH FIRST 3 ROW ONLY) / OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY)

 

SET Operator(집합 연산)

실습 6.

SELECT ename FROM emp 
UNION 
SELECT dname FROM dept;

 

RANK 관련 함수

· 종류

- RANK

- DENSE_RANK

- ROW_NUMBER

SELECT sal, ename, 
RANK() OVER (ORDER BY sal DESC) AS rank, 
DENSE_RANK() OVER (ORDER BY sal DESC) AS dense_rank, 
ROW_NUMBER() OVER (ORDER BY sal DESC) AS row_number, 
rownum AS "rownum" FROM emp;