[Oracle] SQL 서브 쿼리_02 : 연습

서브쿼리 _02 연습

사용예) HR계정의 사원테이블에서 부서의 위치가 미국에 위치한 부서의 속한 사원의 
       평균 급여보다 더 많은 급여를 받는 미국 이외의 부서에 근무하는 사원정보를 조회
       Alias 사원번호, 사웝명, 부서명, 급여
       
서브 쿼리 : 미국에 위치한 부서에 속한 사원의 평균 급여

	SELECT AVG(A.SALARY)
         FROM HR.EMPLOYEES A, HR.DEPARTMENTS B,HR.LOCATIONS C
        WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
          AND B.LOCATION_ID = C.LOCATION_ID
          AND C.COUNTRY_ID = 'US'

메인 쿼리 : 급여가 미국에 있는 사원의 평균급여 보다 많은 미국 외 근무하는 사원정보

      SELECT A1.EMPLOYEE AS 사원번호, 
             A1.EMP_NAME AS 사웝명, 
             B1.DEPARTMENT_NAME AS 부서명, 
             ROUND(D1.ASLA) AS 평균급여,
             A1.SALARY AS 급여
        FROM HR.EMPLOYEES A1, HR.DEPARTMENTS B1,HR.LOCATIONS C1
             (SELECT AVG(A.SALARY)
                FROM HR.EMPLOYEES A, HR.DEPARTMENTS B,HR.LOCATIONS C
               WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
                 AND B.LOCATION_ID = C.LOCATION_ID
                 AND C.COUNTRY_ID = 'US')D1
        WHERE A1.DEPARTMENT_ID = B1.DEPARTMENT_ID
          AND B1.LOCATION_ID = C1.LOCATION_ID
          AND C1.COUNTRY_ID!='US'
          and A1.ASLARY <>D1.ASAL
        ORDER AY A.DEPARTMENT_ID, A.SALARY;

 

 

 

      
사용예) 회원테이블에서 마일리지가 많은 5명의 회원이 2020년 4월 구매한 정보를 조회
       Alias는 회원번호, 회원명, 구매금액합계 MEM,PROD,CART
       
1. 서브쿼리 : 마일리지가 많은 회원번호(마일리지값 으로 ORDER BY, 

  SELECT MEM_ID , MEM_NAME, MEM_MILEAGE
          FROM MEMBER
          WHERE MEM_MILEAGE IS NOT NULL -- != NULL은 안됨
          ORDER BY MEM_MILEAGE DESC

2. 서브쿼리 : 정렬된 회원 5명 (ROWNUM)

     	 SELECT A.MEM_ID AS AID, A.MEM_NAME AS ANAME
                 FROM (SELECT MEM_ID , MEM_NAME, MEM_MILEAGE
                         FROM MEMBER
                        WHERE MEM_MILEAGE IS NOT NULL -- != NULL은 안됨
                        ORDER BY MEM_MILEAGE DESC) A
          	WHERE ROWNUM<=5

1. 메인쿼리 : 5명의 회원이 2020년 4월 구매한 정보
       

       SELECT C.CART_MEMBER AS 회원번호,
              M.MEM_NAME AS 회원명,
              SUM(C.CART_QTY*P.PROD_PRICE) AS 구매금액합계
         FROM CART C, MEMBER M, PROD P
        WHERE C.CART_MEMBER = M.MEM_ID
          AND C.CART_PROD = P.PROD_ID
          AND C.CART_NO LIKE '202004%'
          AND C.CART_MEMBER IN(SELECT A.MEM_ID
                                 FROM (SELECT MEM_ID , MEM_NAME, MEM_MILEAGE
                                         FROM MEMBER
                                        WHERE MEM_MILEAGE IS NOT NULL -- != NULL은 안됨
                                        ORDER BY MEM_MILEAGE DESC) A
                                WHERE ROWNUM<=5)
        GROUP BY C.CART_MEMBER, M.MEM_NAME
        ORDER BY 3 DESC;

 

 

 

        
사용예) 부서테이블에서 부서관리사원의 사원번호가 100에 속한 사원정보를 조회
1. 서브쿼리 : 부서관리사원의 사원번호가 100인 부서의 부서번호

            SELECT DEPARTMENT_ID 
              FROM HR.DEPARTMENTS
             WHERE MANAGER_ID =100;

2. 메인쿼리 : 100인 부서에 속한 사원번호

            SELECT EMPLOYEE_ID AS 사원번호,
                   EMP_NAME AS 사원명, 
                   DEPARTMENT_ID AS 부서번호
              FROM HR.EMPLOYEES
             WHERE DEPARTMENT_ID IN(SELECT DEPARTMENT_ID -- IN대신 =도 가능 대신 한개만있을때!
                                       FROM HR.DEPARTMENTS
                                      WHERE MANAGER_ID =100);
       
        -- EXISTS는무조건 서브쿼리가 나오고 
        -- 서브쿼리다은 안에 서브쿼리에 결과가 하나라도 있으면 참 없으면 거짓
        -- EXISTS에는 컬럼이 안옴
        -- IN ALL ANY SUM EXISTS

        
(EXISTS 연산자 사용)

            SELECT A.EMPLOYEE_ID AS 사원번호,
                   A.EMP_NAME AS 사원명.
                   A.DEPARTMENT_ID AS 부서번호
              FROM HR.EMPLOYEES A
                                -- WHERE 절이 참이면 1을 찍어라? 1을 찍으면 행이 생김
             WHERE EXISTS ( SELECT 1
                              FROM HR.DEPARTMENTS B
                             WHERE B.MANAGER_ID =100
                               AND A.DEPARTMENT_ID = B.DEPARTMENT_ID);

                            

 

 

       
사용예) 사원테이블에서 각 부서별 평균급여보다 더 많은 급여를 받는 사원정보를 조회
       Alias 사원번호, 사원명, 부서번호, 부서평균급여, 급여
       
1. 서브쿼리 : 각 부서별 평균급여

            SELECT DEPARTMENT_ID,
                   ROUND(AVG(SALARY))
              FROM HR.EMPLOYEES
             GROUP BY DEPARTMENT_ID

2. 메인쿼리 : 평균보다 더 많은 급여를 받는 사원정보      

            SELECT A.EMPLOYEE_ID AS 사원번호, 
                   A.EMP_NAME AS 사원명, 
                   A.DEPARTMENT_ID AS 부서번호, 
                   (SELECT ROUND(AVG(C.SALARY))
                      FROM HR.EMPLOYEES C 
                     WHERE C.DEPARTMENT_ID = A.DEPARTMENT_ID) AS 부서평균급여, 
                   A.SALARY AS 급여
              FROM HR.EMPLOYEES A
             WHERE A.SALARY > ( SELECT AVG(B.SALARY)
                                  FROM HR.EMPLOYEES B
                                 WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID)
            ORDER BY 3, 5 DESC;

(EXISTS 연산자 사용)

            SELECT A.EMPLOYEE_ID AS 사원번호, 
                   A.EMP_NAME AS 사원명, 
                   A.DEPARTMENT_ID AS 부서번호, 
                   (SELECT ROUND(AVG(C.SALARY))
                      FROM HR.EMPLOYEES C 
                     WHERE C.DEPARTMENT_ID = A.DEPARTMENT_ID) AS 부서평균급여, 
                   A.SALARY AS 급여
              FROM HR.EMPLOYEES A
             WHERE EXISTS( SELECT 1
                             FROM HR.EMPLOYEES B
                            WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
                              AND A.SALARY >(SELECT AVG(C.SALARY )
                                               FROM HR.EMPLOYEES C
                                              WHERE A.DEPARTMENT_ID=C.DEPARTMENT_ID))
            ORDER BY 3, 5 DESC;