Posted 2022-05-13Updated 2022-05-17 minkuen SQL / oracle9 minutes read (About 1371 words)

Oracle_practice6_3

6장 조인과 서브쿼리/후반

1	%load_ext sql

1	%sql oracle://ora_user:evan@127.0.0.1:1521/myoracle

p.198
기획부 산하에 있는 부서에 속한 사원의 평균급여보다 많은 급여를 받는 사원- 서브쿼리
오전 : WHERE, SELECT
오후 : FROM
기획부 : 부서 테이블
급여 : 사원 테이블

SELECT
a.employee_id
, a.emp_name
, b.department_id
, b.department_name
FROM
employees a
, departments b
(서브쿼리) d – 기획부 평균급여
WHERE a.deartment_id = b.department_id
AND a.salary > d.avg_salary

%%sql

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
       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

 * oracle://ora_user:***@127.0.0.1:1521/myoracle
0 rows affected.

employee_id	emp_name	department_id	department_name
201	Michael Hartstein	20	마케팅
114	Den Raphaely	30	구매/생산부
120	Matthew Weiss	50	배송부
121	Adam Fripp	50	배송부
103	Alexander Hunold	60	IT
204	Hermann Baer	70	홍보부
145	John Russell	80	영업부
146	Karen Partners	80	영업부
147	Alberto Errazuriz	80	영업부
148	Gerald Cambrault	80	영업부
149	Eleni Zlotkey	80	영업부
150	Peter Tucker	80	영업부
151	David Bernstein	80	영업부
152	Peter Hall	80	영업부
153	Christopher Olsen	80	영업부
156	Janette King	80	영업부
157	Patrick Sully	80	영업부
158	Allan McEwen	80	영업부
159	Lindsey Smith	80	영업부
162	Clara Vishney	80	영업부
163	Danielle Greene	80	영업부
168	Lisa Ozer	80	영업부
169	Harrison Bloom	80	영업부
170	Tayler Fox	80	영업부
174	Ellen Abel	80	영업부
175	Alyssa Hutton	80	영업부
176	Jonathon Taylor	80	영업부
177	Jack Livingston	80	영업부
100	Steven King	90	기획부
101	Neena Kochhar	90	기획부
102	Lex De Haan	90	기획부
108	Nancy Greenberg	100	자금부
109	Daniel Faviet	100	자금부
110	John Chen	100	자금부
205	Shelley Higgins	110	경리부
206	William Gietz	110	경리부

%%sql

SELECT AVG(c.salary) AS avg_salary
FROM
    departments b
    , employees c
WHERE b.parent_id = 90
    AND b.department_id = c.department_id

 * oracle://ora_user:***@127.0.0.1:1521/myoracle
0 rows affected.

avg_salary
7908.285714285714285714285714285714285714

p.200
2000년 이탈리아 평균 매출액(연평균)보다 큰 월의 평균 매출액은 구함
첫 번째 서브쿼리 : 월 평균 매출 구하는 것
두 번째 서브쿼리 : 연평균 매출액을 구하는 것%%sql

SELECT a.*
FROM
(서브쿼리) a – 월 평균 매출액
, (서브쿼리) b – 연 평균 매출액
WHERE a.month_avg>b.year_avg – 연 평균 이상의 월 평균을 구한다.- 이 쿼리는 두 개의 서브 쿼리를 FROM 절에 위치시켰다.

2000년 이탈리아 평균 매출액(연평균)보다 큰 달의 평균 매출액을 구해야 한다.
첫 번째 서브 쿼리에서는 월별 평균 매출액을, 두 번째 서브 쿼리에서는 연평균 매출액을 구한다.
그 다음에 ‘월 평균 매출액 > 연 평균 매출액’ 조건을 만족하는 월 평균매출액을 출력한다.

%%sql

SELECT a.*
    FROM 
        (SELECT a.sales_month, ROUND(AVG(a.amount_sold)) 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 
               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

 * oracle://ora_user:***@127.0.0.1:1521/myoracle
0 rows affected.

sales_month	month_avg
200002	137
200007	122
200009	110
200012	184

ERD 참고
p.200
복잡한 쿼리 작성법 예시
(1), (2) –> 메인쿼리 작성
(3), (4) –> 서브쿼리 작성 후 합치기
연도별로 이탈리아 매출 데이터를 살펴
매출실적이 가장 많은 사원의 목록과 매출액을 구하라
연도, 최대매출사원, 최대매출액
이탈리아 찾기 : countries
이탈리아 고객 : customers
매출 : sales
사원정보 : employees
(1) 연도, 사원별 이탈리아 매출액 구하기
이탈리아 고객 찾기 : customers, countries country_id로 조인
이탈리아 매출 찾기 : 위 결과와 sales 테이블을 cust_id로 조인
최대 매출액 구하려면 MAX 함수 쓰고, 연도별로 GROUP BY

%%sql

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

 * oracle://ora_user:***@127.0.0.1:1521/myoracle
0 rows affected.

years	employee_id	amount_sold
1998	1	5404.82
2001	170	10822.62
1999	150	132543.37
1999	175	126982.7
1999	160	72663.95
2000	171	62765.84
2000	176	69330.91
2000	153	142987.82
2001	167	171591.21
2001	153	2443.72
1998	156	121910.05
1999	168	100289.54
2000	172	96872.2
2000	169	102573.99
1999	145	4646.74
2000	163	92604.53
2001	179	123200.62
2001	149	138663.65
2001	154	83216.54
2001	176	9718.24
1998	157	203236.79
1999	162	94310.98
2000	175	24371.95
2000	177	90685.56
2001	173	426018.7
1998	174	258841.2
1998	158	224240.07
1999	159	163403.76
2000	170	79950.04
2000	150	76638.75
2000	148	64039.34
2000	155	32005
2000	161	53927.44
2001	166	141683.55
1998	145	311761.02
1999	147	193319.44
1999	146	89197.23
1999	151	39957.53
2000	162	16259.5
2000	154	116769.25
2001	171	14145.59
2001	148	1557.54
1999	152	97859.24
2000	160	77893.94
2001	165	145408.5
2001	164	145790.31

(2) (1) 결과에서 연도별 최대, 최소 매출액 구하기

%%sql

SELECT 
    years
    , MAX(amount_sold) AS max_sold
    , MIN(amount_sold) AS min_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

 * oracle://ora_user:***@127.0.0.1:1521/myoracle
0 rows affected.

years	max_sold	min_sold
1998	311761.02	5404.82
1999	193319.44	4646.74
2000	142987.82	16259.5
2001	426018.7	1557.54

(3) (1) 결과와 (2) 결과를 조인해서
최대매출, 최소매출액을 일으킨 사원을 찾는다.SELECT
emp.years
, emp.employee_id
, emp.amount_sold
FROM
() emp – (1) 결과를 대입
, () sale – (2) 결과를 대입
WHERE emp.years = sales.years
AND emp.amount_sold = sale.max_sold
ORDER BY years;

%%sql


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
         , MIN(amount_sold) AS min_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

 * oracle://ora_user:***@127.0.0.1:1521/myoracle
0 rows affected.

years	employee_id	emp_name	amount_sold
1998	145	John Russell	311761.02
1999	147	Alberto Errazuriz	193319.44
2000	153	Christopher Olsen	142987.82
2001	173	Sundita Kumar	426018.7

Reference : 오라클 SQL과 PL/SQL을 다루는 기술

Oracle_practice6_3

https://kmk3593.github.io/2022/05/13/Oracle_ch06_0428_2/

Author

minkuen

Posted on

2022-05-13

Updated on

2022-05-17

Licensed under

#oracle

You need to set install_url to use ShareThis. Please set it in _config.yml.

Oracle_practice6_3

Author

Posted on

Updated on

Licensed under

Like this article? Support the author with

Comments

Links

Categories

Recents

Archives

Tags

Subscribe for updates

follow.it