[SQL]서브쿼리(IN,ANY,SOME ALL,EXISTS )

/* 제임스의 급여보다 큰 사원 출력*/
SELECT *FROM EMP
WHERE SAL>(SELECT SAL FROM EMP WHERE ENAME = 'JONES');

SELECT * FROM EMP
WHERE COMM>(SELECT COMM FROM EMP WHERE ENAME='ALLEN');

 

/*단일 행 서브 쿼리*/

SELECT E.EMPNO,E.ENAME,E.JOB,E.SAL,D.DEPTNO,D.DNAME,D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO
AND E.DEPTNO = 20
AND SAL>(SELECT AVG(SAL) FROM EMP);

 

/*238P 1분 복습*/
SELECT E.EMPNO,E.ENAME,E.JOB,E.SAL,D.DEPTNO,D.DNAME,D.LOC
FROM EMP E,DEPT D
WHERE E.DEPTNO = 20
AND E.SAL<=(SELECT AVG(SAL) FROM EMP);

 

/*다중행 서브쿼리(실행결과가 여러개) */
/* IN ([1],[2]) 연산자 (1이거나 2면 출력)*/
SELECT * FROM EMP
WHERE DEPTNO IN(20,30);

SELECT * FROM EMP
WHERE SAL IN(SELECT MAX(SAL) FROM EMP
GROUP BY DEPTNO);

SELECT MAX(SAL) FROM EMP
GROUP BY DEPTNO;

 

/* ANY,SOME : 조건식을 만족하는 결과가 하나이상이면 TRUE*/

/* ANY */
SELECT * FROM EMP77777777777
WHERE SAL= ANY(SELECT MAX(SAL)
FROM EMP
GROUP BY DEPTNO);

/*SOME*/
SELECT * FROM EMP
WHERE SAL= SOME(SELECT MAX(SAL)
FROM EMP
GROUP BY DEPTNO);

 

/*30번 부서들의 최소급여보다 많은 급여를 받는 사원정보 출력*/
SELECT * FROM EMP
WHERE SAL> ANY(SELECT SAL
FROM EMP
WHERE DEPTNO=30);

/* ALL (최소급여보다 더 작은 급여받는 사람)*/
SELECT * FROM EMP
WHERE SAL > ALL(SELECT SAL
FROM EMP
WHERE DEPTNO=30);

/*EXISTS (결과가 하나이상 존재하면 모두 true)*/

SELECT *
FROM EMP
WHERE EXISTS (SELECT DNAME FROM dept
WHERE DEPTNO =10);

/*257P 1분 복습*/

SELECT *
FROM EMP
WHERE HIREDATE < ALL (SELECT HIREDATE
FROM EMP WHERE DEPTNO =10);

 

/*연습 문제 262P 1번*/
SELECT E.JOB,E.EMPNO,E.ENAME,E.SAL,D.DEPTNO,D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO
AND JOB = (SELECT JOB FROM EMP WHERE ENAME='ALLEN');

SELECT * FROM salgrade;
SELECT * FROM EMP;
SELECT * FROM DEPT;

/*2번*/
SELECT E.EMPNO,E.ENAME,D.DNAME,HIREDATE,D.LOC,SAL,GRADE
FROM EMP E,SALGRADE S,DEPT D
WHERE E.DEPTNO=D.DEPTNO
AND E.SAL BETWEEN S.LOSAL AND S.HISAL
AND SAL>(SELECT AVG(SAL) FROM EMP)
ORDER BY SAL DESC, EMPNO ASC;