오라클(SQL)다중행 서브쿼리

다중행 서브쿼리

테이블 다운로드

 

    -복수개의 결과를 반환하는 서브쿼리
    -IN, ANY, EXISTS 등의 연산자의 결합
사용예)사원테이블에서 2007년 이후에 입사한 사원중 2005년도에 입사한 사원들의 
    최저 급여보다 더 많은 급여를 받는 사원을 조회하시오
    ALIAS는 사원번호, 사원명, 입사일, 급여, 직책명

SELECT  A.EMPLOYEE_ID AS 사원번호,  
        A.EMP_NAME AS 사원명,  
        A.HIRE_DATE AS 입사일,  
        A.SALARY AS 급여,  
        B.JOB_TITLE AS 직책명 
FROM    EMPLOYEES A, JOBS B 
WHERE   A.HIRE_DATE >= TO_DATE('20070101') 
AND     A.SALARY >= ANY (SELECT SALARY  
                         FROM   EMPLOYEES 
                         WHERE  EXTRACT(YEAR FROM HIRE_DATE)=2004) 
AND     A.JOB_ID=B.JOB_ID; 

(EXISTS 연산자 사용)

SELECT  A.EMPLOYEE_ID AS 사원번호,  
        A.EMP_NAME AS 사원명,  
        A.HIRE_DATE AS 입사일,  
        A.SALARY AS 급여,  
        B.JOB_TITLE AS 직책명 
FROM    EMPLOYEES A, JOBS B 
WHERE   A.HIRE_DATE >= TO_DATE('20070101') 
AND     EXISTS (SELECT 1 
                FROM   EMPLOYEES B 
                WHERE  EXTRACT(YEAR FROM HIRE_DATE)=2004 
                AND    A.SALARY >= B.SALARY) 
AND     A.JOB_ID=B.JOB_ID; 

문제)부서테이블과 사원테이블을 이용하여 다음문제를 해결하시오
    상위부서(PARENT_ID)가 90번 부서에 속한 하위부서 속한 직원중 본인이 속한
    부서의 평균급여보다 많으 급여를 지급 받는 사원정보 조회
    ALIAS는 사원번호, 사원명, 부서번호, 부서명, 평균급여
    단, INLINE SUBQUERY로 작성하시오

(별도의 테이블)

SELECT  A.EMPLOYEE_ID AS 사원번호,  
        A.EMP_NAME AS 사원명,  
        D.DID AS 부서번호,  
        D.BU AS 부서명,  
        D.AV AS 평균급여 
FROM    EMPLOYEES A,(SELECT  B.DEPARTMENT_ID AS DID, 
                            B.DEPARTMENT_NAME AS BU, 
                            ROUND(AVG(A.SALARY))AS AV 
                    FROM    EMPLOYEES A, DEPARTMENTS B 
                    WHERE   B.PARENT_ID = 90 
                    AND     A.DEPARTMENT_ID = B.DEPARTMENT_ID 
                    GROUP   BY B.DEPARTMENT_NAME,B.DEPARTMENT_ID)D 
WHERE   D.AV <= SALARY 
AND     A.DEPARTMENT_ID = DID;

SELECT  B.DEPARTMENT_NAME AS DID, 
        ROUND(AVG(A.SALARY)) 
FROM    EMPLOYEES A, DEPARTMENTS B 
WHERE   B.PARENT_ID = 90 
AND     A.DEPARTMENT_ID = B.DEPARTMENT_ID 
GROUP   BY B.DEPARTMENT_NAME;