Oracle sub-query에 관한 이야기

구조를 보시는 방법이 제일 좋습니다. 물론 직접 해보시는게 더 좋죠.

그리고 SQL의 경우는 무조건 FROM절부터 해석하시는게 빠릅니다. 왜냐면, SQL은 FROM이 가장 1순위로 처리 됩니다.

SQL이란 '어디에서 무엇을' Select/Insert/update/delete/join을 하는 option이기에 가장 중요한건 FROM절입니다.

--기획부 산하에 있는 부서에 속한 사원의 평균급여 보다 많은 급여를 받는 사원을 조회
SELECT a.employee_id
      ,a.emp_name
      ,b.department_id
      ,b.department_name
 FROM employees a
     ,departments b
     ,( SELECT AVG(c.salary) AS avg_salary --inline view 
         FROM departments b
             ,employees c
        WHERE b.parent_id = 90 --기획부
         AND b.department_id = c.department_id 
      ) d
 WHERE a.department_id = b.department_id
  AND a.salary > d.avg_salary;
--두 개의 서브쿼리를 FROM 절에 위치하였으며, 이탈리아 연평균 매출액 보다 큰 월의 평균 매출액을 조회
SELECT a.*
 FROM ( SELECT a.sales_month
              ,ROUND(AVG(a.amount_sold)) AS month_avg
         FROM sales a
             ,customers b
             ,countries c
        WHERE a.sales_month BETWEEN '200001' AND '200012'
          AND a.cust_id  = b.CUST_ID
          AND b.COUNTRY_ID = c.COUNTRY_ID
          AND C.COUNTRY_NAME = 'Italy'
        GROUP BY a.sales_month
      ) a,
      ( SELECT a.sales_month
              ,ROUND(AVG(a.amount_sold)) AS year_avg
         FROM sales a
             ,customers b
             ,countries c
        WHERE a.sales_month BETWEEN '200001' AND '200012'
          AND a.cust_id  = b.CUST_ID
          AND b.COUNTRY_ID = c.COUNTRY_ID
          AND C.COUNTRY_NAME = 'Italy'
       ) b
 WHERE a.month_avg > b.year_avg;       
 
 -- 결과 항목 정하기 
 -- 필요한 테이블/컬럼 파악
 -- 작은 단위로 분할
 -- 최종 산출
 -- 검증
 
 
 --연도 , 사원별 이탈리아 매출액
 SELECT SUBSTR(a.sales_month, 1, 4) AS years
        ,a.employee_id
        ,SUM(a.amount_sold) AS amonut_sold
  FROM sales a
      ,customers b
      ,countries c
 WHERE a.cust_id = b.CUST_ID
   AND b.country_id = c.COUNTRY_ID
   AND c.country_name = 'Italy'
GROUP BY SUBSTR(a.sales_month, 1, 4) , a.employee_id;

SELECT years
      ,MAX(amount_sold) AS max_sold
 FROM ( SELECT SUBSTR(a.sales_month, 1, 4) as years
              ,a.employee_id
              ,SUM(a.amount_sold) AS amount_sold
        FROM sales a
            ,customers b
            ,countries c
        WHERE a.cust_id = b.CUST_ID
         AND b.country_id = c.COUNTRY_ID
         AND c.country_name = 'Italy'
      GROUP BY SUBSTR(a.sales_month, 1,4), a.employee_id
      ) K
  GROUP BY years
  ORDER BY years;
  
SELECT emp.years
      ,emp.employee_id
      ,emp2.emp_name
      ,emp.amount_sold
 FROM ( 
        SELECT SUBSTR(a.sales_month, 1, 4) AS years
              ,a.employee_id
              ,SUM(a.amount_sold) AS amount_sold
          FROM sales a
              ,customers b
              ,countries c
         WHERE a.cust_id = b.CUST_ID
           AND b.country_id = c.COUNTRY_ID
           AND c.country_name = 'Italy'
        GROUP BY SUBSTR(a.sales_month, 1, 4) , a.employee_id
      )emp,
      (
       SELECT years
      ,MAX(amount_sold) AS max_sold
         FROM ( SELECT SUBSTR(a.sales_month, 1, 4) as years
                      ,a.employee_id
                      ,SUM(a.amount_sold) AS amount_sold
                  FROM sales a
                      ,customers b
                      ,countries c
                  WHERE a.cust_id = b.CUST_ID
                   AND b.country_id = c.COUNTRY_ID
                   AND c.country_name = 'Italy'
                GROUP BY SUBSTR(a.sales_month, 1,4), a.employee_id
              ) K
        GROUP BY years
      ) sale,
      employees emp2
WHERE emp.years = sale.years
 AND emp.amount_sold = sale.max_sold
 AND emp.employee_id = emp2.employee_id
ORDER BY years;

--계층형 쿼리 
SELECT department_id
      ,LPAD(' ', 3 * (LEVEL-1)) || department_name
      ,LEVEL
 FROM departments
START WITH parent_id IS NULL --최상위 계층의 row를 식별하는 조건
CONNECT BY PRIOR department_id =parent_id; --계층형 구조가 어떤 식으로 연결 되는지를 기술하는 부분