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;
'IT_Web > Oracle' 카테고리의 다른 글
| Oracle 컬럼 합계와 행 합계 동시에 만들어서 출력, 윈도우함수 (0) | 2020.03.09 |
|---|---|
| Oracle 특정 컬럼의 제품을 제품별, 등급별, 특정 등급으로 관리, 차액의 비율, CASE, ROLLUP, GROUP BY 활용 (0) | 2020.03.09 |
| Oracle ROLLUP, GROUPING SETS활용 TYPE 별 가격 합계, 소계 출력하기 (0) | 2020.03.09 |
| Oracle WITH 절 특징 및 개념 (0) | 2020.03.08 |
| Oracle RATIO_TO_REPORT WINDOW 함수 파티션 그룹에 대한 비율 반환함수 (0) | 2020.03.08 |