오라클[sql] 서브쿼리 / 연습문제

서브쿼리

▶ 메인쿼리 : 쿼리문 전체

▶ 서브쿼리 : 메인쿼리 안에 있는 또 하나의 SELECT 문장

-- 쿼리(SQL)에서 나오는 결과를 이용하여 메인쿼리에 적용을 하고자 할 때 사용하는 것이 "서브쿼리"

-- SQL 문장 내에 포함된 또 하나의 SELECT 문장

   * SQL 문장 : INSERT UPDATE DELETE 무관하게 사용가능

-- 쿼리문 전체 > 메인쿼리, 메인쿼리안에 들어있는 SELECT > 서브쿼리

  * 메인쿼리 : DML 모두 사용 가능

  * INSERT INTO > SELECT / UPDATE > SELECT / DELETE > SELECT / SELECT > SELECT
-- 실행순서 > 서브쿼리 > 메인쿼리

  * 서브쿼리 무조건 ( ) 묶어서 작성

  * 연산자의 오른쪽에 작성

 

▶ SELECT 안에 SELECT 일떄

-- SELECT 안에 서브쿼리 : 스칼라 서브쿼리

-- FROM 안에 서브쿼리 : 인라인 뷰

-- WHERE 안에 서브쿼리 : 서브쿼리

 

  SELECT () FROM WHERE

  SELECT FROM () WHERE

  SELECT FROM WHERE ()

  SELECT () FROM () WHERE()
 

▶ 서브쿼리는 결국 SELECT 문 > 조회 > 결과 1행 / 여러행 / 컬럼여러개

-- 단일행 서브쿼리 : 결과가 1행일 때  > 스칼라 서브쿼리에서 자주 사용

-- 다중행 서브쿼리 : 결과가 다중행일 때

-- 다중컬럼 서브쿼리 : 결과가 여러 컬림일 때 > FROM 안에서 자주 쓰는 서브쿼리형태

  

▶ FROM 안에 서브쿼리 > 별칭이 필수

-- 원래 테이블 > 테이블명, 컬럼명이 존재하기 때문에 값으로 선택

-- 서브쿼리의 경우는 테이블 명이 존재하지 않기 때문에 "별칭" 부여가 필수 >> 별칭.컬럼명으로 검색
    >> 컬럼명이 메인쿼리, 테이블과 중복이거나 그룹함수를 사용한 컬럼인 경우 별칭을 꼭 필수로 작성해야 합니다.

 

▶ 서브쿼리 결과를 이용하여 메인쿼리내에서 활용할때!

-- WHERE 단일행결과 : < > = !=

-- WHERE 다중행결과 : IN ALL ANY EXISTS

-- 서브쿼리의 결과의 갯수에 따라서 사용하는 연산자의 종류가 달라집니다.

 

 

예제

▶ 전체 사원의 평균보다 본인 급여가 높은 사원 조회 >> 평균의 결과를 나타내는 쿼리를 이용

SELECT ename, sal

FROM emp

WHERE sal > (SELECT ROUND(avg(sal),0) FROM emp);

 

▶ 월급이 가장 많은 사원의 정보를 조회

SELECT max(sal) FROM emp;

 

SELECT ename, sal

FROM emp

WHERE sal = (SELECT max(sal) FROM emp);

 

▶ 월급이 평균급여 보다 높고, 최대급여보다 낮은 월급을 받는 사원의 이름 및 월급을 출력

풀이 1)

SELECT ename, sal

FROM emp

WHERE sal > (SELECT avg(sal) FROM emp)

AND sal < (SELECT max(sal) FROM emp);

 

풀이 2)

SELECT e.ename, e.sal

FROM emp e, (SELECT AVG(sal) avg, MAX(sal) max FROM emp) ext

WHERE e.sal > ext.avg

AND e.sal < ext.max;

  

▶ SMITH 와 같은 부서 사람

SELECT deptno FROM emp WHERE ename = 'SMITH';

 

SELECT *

FROM emp

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

 

▶ JONES 의 급여보다 더 많은 급여를 받는 사람

SELECT sal FROM emp WHERE ename = 'JONES';

 

SELECT *

FROM emp

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

 

▶ 월급의 상위 10명을 출력, 정렬

-- ORDER BY 를 하기 전에 데이터에 대한 행 번호를 가진게 rownum

SELECT rownum, e.*

FROM ( SELECT ename, sal FROM emp WHERE sal IS NOT NULL ORDER BY sal DESC ) e
WHERE ROWNUM < 11 ;

 

다중행 서브쿼리

▶ 서브쿼리의 결과가 여러행이 나올 때

▶ IN

     ( = ), 메인쿼리 비교 조건 중에서 서브쿼리의 결과 중에 하나라도 일치하면 출력

▶ ANY

     ( < > >= <= !=) ANY (서브쿼리)  //  sal > ANY(서브쿼리)
     메인쿼리 비교조건 중에 서브쿼리의 결과 중에 하나라도 참이라면 출력

▶ ALL

     ( < > >= <= !=) ANY (서브쿼리)  //  sal > ALL(서브쿼리)

     메인쿼리 비교조건 중에서 서브쿼리의 결과 중 모두 참이라면 출력

▶ EXISTS

     메인쿼리 비교조건 중에서 서브쿼리의 결과 중 하나라도 일치하면 출력 = IN 동일한 기능

     컬럼단위에서 확인 속도적 이점이 있는 연산자

 

다중행 서브쿼리 예제

▶업무별로 최소급여를 받는 사원의 정보(이름, 업무, 급여)를 출력

SELECT *

FROM emp

WHERE sal IN (SELECT min(sal) FROM emp GROUP BY job);

 

 

ANY 예제

▶ 부서번호가 30인 사원중에서 급여가 가장 낮은 값보다 높은 급여를 받는 사원의 이름 SELECT * FROM emp WHERE sal > ANY (SELECT sal FROM emp WHERE deptno = 30);

-- sal > 1600,1250,1250,2850,1500,950 한번이라도 참이면 참인것

SELECT * FROM emp WHERE sal > ANY (SELECT sal FROM emp WHERE deptno = 30) AND deptno = 30;
-- 부서 30인 사람들만 출력

ALL 예제

SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno = 30);

-- sal > 1600,1250,1250,2850,1500,950 모든 연산이 참이면 참인것

 

EXISTS

SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM dept d WHERE d.deptno IN (10,20));

SELECT * FROM emp e WHERE EXISTS ( SELECT 1 FROM dept d WHERE d.deptno IN(10,20) AND e.deptno = d.deptno);

SELECT 1 FROM dept d WHERE d.deptno IN(10,20);

연습문제

▶ CREATE TABLE emp AS SELECT * FROM SCOTT.emp;

 

▶ Blake와 같은 부서에 있는 모든 사원의 이름과 입사일자를 출력

SELECT deptno FROM emp WHERE ename = 'BLAKE';

 

SELECT ename, hiredate

FROM emp

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

 

▶ 평균 급여 이상을 받는 모든 사원에 대해 사번과 이름을 출력 -- 단, 급여가 많은 순으로 정렬

SELECT empno, ename

FROM emp

WHERE sal >= (SELECT avg(sal) FROM emp)

ORDER BY sal desc;

 

▶ 이름에 ‘T’가 있는 사원이 근무하는 부서에서 근무하는 모든 사원에 대해 사번, 이름, 급여를 출력 -- 단, 사번순으로 출력

SELECT empno, ename, sal

FROM emp

WHERE deptno IN ( SELECT deptno FROM emp WHERE ename like '%T%')

ORDER BY empno;

 

▶ 부서위치가 Dallas인 모든 사원에 대해 이름, 업무, 급여를 출력​

SELECT ename, job, sal

FROM emp

WHERE deptno = ( SELECT deptno FROM dept WHERE loc = 'DALLAS');

 

▶ 부서위치가 Dallas와 NEW YORK인 모든 사원에 대해 이름, 업무, 급여를 출력

SELECT ename, job, sal

FROM emp

WHERE deptno IN ( SELECT deptno FROM dept WHERE loc IN( 'DALLAS', 'NEW YORK'));

 

▶ King에게 보고하는 모든 사원의 이름과 급여를 출력

▶ KING의 EMPNO 를 찾아서, MGR의 값과 일치하는 사원을 말함

SELECT deptno FROM emp WHERE ename = 'KING';

SELECT * FROM emp;

 

SELECT ename, sal

FROM emp

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

 

▶ SALES 부서의 사원명과 업무를 출력

SELECT ename, job

FROM emp

WHERE deptno IN ( SELECT deptno FROM dept WHERE dname = 'SALES');

▶ 월급이 30번 부서의 최저 월급보다 많은 사원들을 출력

SELECT min(sal) FROM emp e WHERE e.deptno = 30;

SELECT * FROM emp

WHERE sal > ANY ( SELECT min(sal) FROM emp WHERE deptno = 30);

 

▶ 10번 부서의 직원들 중 30번 부서의 사원과 같은 업무를 맡고 있는 사원의 이름과 업무를 출력​

SELECT job FROM emp WHERE deptno=30;

SELECT ename, job

FROM emp

WHERE job IN ( SELECT job FROM emp WHERE deptno=30)

AND deptno = 10;

 

▶ 업무가 jones와 같거나 월급이 ford의 월급 이상인 사원의 정보를 이름, 업무, 부서번호, 급여를 출력

-- 단, 업무별, 월급이 많은 순으로

SELECT ename, job, deptno, sal

FROM emp

WHERE job IN (SELECT job FROM emp WHERE ename='JONES')

OR sal > (SELECT sal FROM emp WHERE ename='FORD')

ORDER BY job, sal DESC;

▶ 업무별로 월급이 평균 월급보다 낮은 사원을 부서번호, 이름, 급여를 출력

SELECT job, avg(sal) FROM emp GROUP BY job;

 

SELECT deptno, ename, sal

FROM emp e1,( SELECT job, avg(sal) avg_sal FROM emp GROUP BY job) e2

WHERE e1.job = e2.job

AND e1.SAL < e2.avg_sal;

▶ 말단 사원의 사번, 이름, 업무, 부서번호를 출력

SELECT empno, ename, job, deptno

FROM emp

WHERE hiredate = (SELECT MAX(hiredate) FROM emp);

▶ SCOTT의 급여보다 많은 사원의 정보를 사원번호, 이름, 담당업무, 급여를 출력

SELECT empno, ename, job, sal

FROM emp

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

 

▶ 30번 부서의 최소 급여보다 각부서의 최소 급여가 높은 부서를 출력

SELECT min(sal) FROM emp WHERE deptno=30;

SELECT min(sal) FROM emp GROUP BY deptno;

SELECT deptno

FROM emp

GROUP BY deptno

HAVING min(sal)>(SELECT min(sal) FROM emp WHERE deptno=30);

 

▶ 업무별로 평균 급여 중에서 가장 적은 급여를 가진 직업을 출력​

SELECT min(avg(sal)) FROM emp GROUP BY job;

SELECT avg(sal), job FROM emp GROUP BY job;

 

SELECT job

FROM emp

GROUP BY job

HAVING avg(sal) = (SELECT min(avg(sal)) FROM emp GROUP BY job);

▶ 사원번호가 7521의 업무와 같고 사번 7934인 직원보다 급여를 많이 받는 사원의 정보를 출력

SELECT job FROM emp WHERE empno = 7521;

SELECT sal FROM emp WHERE empno = 7934;

SELECT *

FROM emp

WHERE job = (SELECT job FROM emp WHERE empno = 7521)

AND sal > (SELECT sal FROM emp WHERE empno = 7934);

 

▶ 'WARD'와 부서와 업무가 같은 사원 명단 출력

SELECT deptno FROM emp WHERE ename='WARD';

SELECT job FROM emp WHERE ename='WARD';

풀이 1)

SELECT *

FROM emp

WHERE deptno = (SELECT deptno FROM emp WHERE ename='WARD')

AND job IN (SELECT job FROM emp WHERE ename='WARD');

풀이 2)

SELECT *

FROM emp e, (SELECT deptno d, job j FROM emp WHERE ename = 'WARD')

WHERE job = j AND dentno = d;