[09단원] 서브쿼리

 

교재명 : Do it! 오라클로 배우는 데이터베이스 입문 (이지스퍼블리싱 / 이지훈 지음)

 

[ Q1 ]

[ A1 ]

SELECT E.JOB, E.EMPNO, E.ENAME, E.SAL, E.DEPTNO, D.DNAME
  FROM EMP E, DEPT D
 WHERE E.DEPTNO = D.DEPTNO
   AND JOB = (SELECT JOB
                FROM EMP
               WHERE ENAME = 'ALLEN');

[ Q2 ]

[ A2 ]

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

오답: SALGRADE TABLE을 사용안함

SELECT ...
  FROM EMP E JOIN DEPT D ON E.DEPTNO = D.DEPTNO
 WHERE E.SAL > (SELECT AVG(SAL) FROM EMP)
 ORDER BY E.SAL DESC, E.EMPNO ASC

[ Q3 ]

[ A3 ]

*서브쿼리 안에 JOB 조회할 때, DISTINCT 를 사용하여 중복 제거를 해주어야 메인쿼리의 JOB와 비교할 수 있음 (데이터 갯수가 일치해야 함)

SELECT E.EMPNO, E.ENAME, E.JOB, E.DEPTNO, D.DNAME, D.LOC
  FROM EMP E, DEPT D
 WHERE E.DEPTNO = D.DEPTNO
   AND E.DEPTNO = 10
   AND JOB NOT IN (SELECT DISTINCT JOB
                     FROM EMP
                    WHERE DEPTNO = 30);

[ Q4 ]

[ A4 ]

-- 다중행 함수 사용하지 않는 방법
SELECT E.EMPNO, E.ENAME, E.SAL, S.GRADE
  FROM EMP E, SALGRADE S
 WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL
   AND SAL > (SELECT MAX(SAL)
                FROM EMP
               WHERE JOB = 'SALESMAN')
ORDER BY E.EMPNO; 

--다중행 함수 사용하는 방법
SELECT E.EMPNO, E.ENAME, E.SAL, S.GRADE
  FROM EMP E, SALGRADE S
 WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL
   AND SAL > ALL (SELECT DISTINCT SAL
                    FROM EMP
                   WHERE JOB = 'SALESMAN')
ORDER BY E.EMPNO;

 [ 참고자료 ] 

• https://github.com/GroovySunday/doit-oracle