귤귤의 대모험

8. 부서번호가 50인 사원들 중 이 부서의 평균 급여액보다 낮은 월급을 받는 사원 명단을 추출하시오

SELECT a.employee_id, a.last_name, a.salary

FROM ( SELECT employee_id, manager_id, salary, last_name

FROM employees WHERE department_id = 50 ) a，

(SELECT AVG (salary) avg_salary

FROM employees WHERE department_id = 50 ) b

WHERE a.salary < b.avg_salary

10. 9번의 출력문을 다시 with을 사용하여 다시 작성하시오

WITH a AS  (SELECT employee_id, manager_id, salary, last_name

                         FROM employees

                         WHERE department id = 50 )，

             b AS (SELECT AVG(salary) avg_salary

                         FROM employees

                         WHERE department id = 50)

SELECT a.employee id, a.last_name, a.salary

FROM a， b

WHERE a. salary < b.avg_salary

11. 전체 부서별 평균 급여액보다 부서별 급여 합계액이 큰 부서의 명단을 추출해 보자 단 서브쿼리

SELECT d.d_dep, d.SUM_sal

FROM

(SELECT a.department_name d_dep, ROUND(SUM(b.salary)) SUM_sal

FROM departments a

, employees b

WHERE a.department_id = b.department_id

GROUP BY a.department_name

) d,

(SELECT ROUND(SUM(sum(b.salary)) / COUNT(*)) AVG_sal

FROM departments a

, employees b

WHERE a.department_id = b.department_id

GROUP BY a.department_name

) c

WHERE d.SUM_sal > c.AVG_sal;

12 위의 방법을 다시 WITH를 이용하여 다시 작성하시오

WITH dept_costs AS (SELECT department_name, SUM(salary) dept_total

                                            FROM employees e, departments d

                                            WHERE e.department_id = d.department_id

                                            GROUP BY department_name ),

            avg_cost AS (SELECT SUM(dept_total) /COUNT (*) avg

                                       FROM dept_costs)

SELECT dept_costs.*

FROM dept_costs, avg_cost

WHERE dept_costs.dept_total > avg_cost.avg

13. WITH은 SELECT 절과 같이 쓰여 아주 유용한 기능을 제공한다.

10장에서 계층형 쿼리에 대해 학습했는데, 계층형 쿼리를 이용하여 EMPLOYEES 테이블에 있는 사원들을 계층적으로 조회하였다

SELECT LEVEL, LPAD(' ', 4* (LEVEL -1)) || first_name || ' ' || last_name "성명"

FROM employees

START WITH MANAGER_ID IS NULL

CONNECT BY manager_id = PRIOR employee_id;

계층형 정보를 꼭 CONNECT BY 절을 사용해서만 구현할 수 있는 것은 아니다. WITH 구문을 사용하여 위의 결과와 동일한 데이터를 추출하는 쿼리를 작성해 보자.

(힌트: 실제 EMPLOYEES 테이블의 사원 계층은 총 4레벨까지 존재하므로， 레벨 별로 인라인 뷰를 4개 생성해서 WITH을 사용해 생성한 인라인 뷰에서 다른 인라인 뷰를 참조하는 형태로 작성하자. 문제는 난 이도가 꽤 높다. 따라서 이 문제를 풀 정도먼 WITH 구문을 완전히 이해했다고 볼 수 있다 )

15. 0RDERS 테이블을 참조하여 연도별로 주문건수와 총 주문금액을 구하는 쿼리를 작성해 보자.

16. 1번 쿼리를 기준으로 해서 다음과 같은 형태의 정보를 조회하는 쿼리를 작성해 보자.

기준연도 주문건수 주문총액 이전연도_주문총액 이후연도_주문총액

xxxx 00 0000 0000000 000000

SELECT order_date, order_status, order_total,

SUM(order_total) OVER ( ORDER BY order_date

                                                    RANGE interval '1' year preceding ) AS 이전연도_총합계,

SUM(order_total) OVER ( ORDER BY order_date

                                                    RANGE BETWEEN CURRENT row AND interval '1' year following) AS 이후년도_총합계

FROM ORDERS

단 이전연도와 이후연도는 기준연도에서 1년을 더하거나 뺀 연도가 아니라

실제 집계된 데이터가 존재하는 연도를 말한다.

17. 1999년도 전체 영업실적 중 각 개인별 실적 비율을 구해보자

전체 실적금액으로 각 개인별 실적금액을 니누면 된다 윈도우 함수를 중 분석함수를 사용해서 풀어보자

SELECT emp.last_name 이름, SUM (ord.ORDER_total) 개인별실적,

ROUND(SUM(ord.order_total) / SUM(SUM(ord.order_total)) OVER( PARTITION BY TO_CHAR(ord.order_date, 'YYYY' ) ) ,2) 개인별비율

FROM orders ord, employees emp

WHERE TO_CHAR(ord.ORDER_date， 'YYYY') = '1999'

AND ord. sales_rep_id = emp. employee_id

GROUP BY emp.last_name, TO_CHAR(ord.order_date, 'YYYY')

ORDER BY emp.last_name;

18. 위의 결과물과 같은 출력값을 가질 수 있게 RATIO_TO_REPORT 함수를 써서 출력하시오

SELECT emp.last_name 이름, SUM(ord.order_total) 개인별실적,

ROUND (RATIO_TO_REPORT(SUM(ord.order_total)) OVER (PARTITION BY TO_chAR(ord.order_date, 'YYYY' )),2) RATIO

FROM orders ord, employees emp

WHERE TO_CHAR(ORD.ORDER_DATE, 'YYYY') = '1999'

AND ord.sales_rep_id = emp.employee_id

GROUP BY emp.last_NAME, TO_CHAR(ord.ORDER_DATE, 'YYYY')

ORDER BY emp.last_name;

WITH

WITH 구문의 사용 (ORACLE9i R2이후 사용 가능)

WITH 문장은 인라인 뷰에 별칭(alias)을 부여하는데， 별칭을 부여히는 것으로 끝나지 않고

SELECT 문장에서 별칭이 부여된 인라인 뷰를 시용 기능하게 한다

특징

자주사용되는 쿼리를 사용하기전에 WITH절로 미리 쿼리 블록으로 정의한 후 사용한다

서브쿼리문에서 서브쿼리에 의해 메인 쿼리가 실행되기 때문에 서브쿼리문은 성능이 저하된다.

with절은 여러 개의 서브쿼리가 하나의 메인 쿼리에서 사용될 때 생기는 복잡성을 보다 간결하게 정의하여 사용함으로써 서브쿼리에서 발생할 수 있는 성능저하 현상을 방지할 수 있다

WITH <별칭1> AS ( SELECT 문장1 ),

             <별칭2> AS ( SELECT 문장2 FROM 별칭1 )，

SELECT 컬럼1， 걸림2，

FROM 별칭1, 별칭2

WITH 구문은 별칭2의 인라인 뷰를 FROM 절에서 바로 이전에 시용한 별칭1을 직접 사용할 수 있는 점이

WITH 구문만이 가진 고유한 특징이다.