본문 바로가기
수업내용

[Day25] [Oracle] SET Operator / UNION / UNION ALL / INTERSECT/ MINUS / JOIN

clapdev 2019. 9. 25. 17:32

Ⅰ. SET Operator(SET 연산자)

A = { a, x, b, e, g }

B = { c, d, a, b, y, k, m}

 

1. UNION       

-- 합집합

-- A ∪ B = { a, b, c, d, e, g, k, m, x, y }

 

2. UNION ALL

-- { a, x, b, e, g, c, d, a, b, y, k, m}

-- 속도는 빠르지만 중복이 있고 정렬되지 않는다.


3. INTERSECT    

-- 교집합

-- A ∩ B = { a, b }

 

4. MINUS        

-- 차집합

-- A - B { x, e, g }
-- B - A { c, d, y, k, m}

 

 

▷ UNION  

-- UNION은 서로 다른 테이블(뷰)의 행(ROW)과 행(ROW)을 합칠 때 사용하는 연산자이다.

 

HR

insert into tbl_panmae(panmaedate, jepumname, panmaesu)
values( sysdate, '새우깡', 10);

insert into tbl_panmae(panmaedate, jepumname, panmaesu)
values( sysdate, '감자깡', 20);

insert into tbl_panmae(panmaedate, jepumname, panmaesu)
values( sysdate, '새우깡', 15);

commit;

select *
from tbl_panmae;

 

서브쿼리를 사용하여 2개월 전의 달 (2019-07)에 속한 행들을 가지고 tbl_panmae_201907 이라는 테이블을 생성

 

HR

create table tbl_panmae_201907
as
select *
from tbl_panmae
where substr(panmaedate, 5, 1) = 7;

 

 

1개월 전의 달 (2019-08)에 속한 행들을 가지고 tbl_panmae_201908 이라는 테이블을 생성

 

HR

create table tbl_panmae_201908
as
select *
from tbl_panmae
where substr(panmaedate, 5, 1) = 8;

 

 

tbl_panmae 테이블에서 이번달(2019-09)에 속한 행들을 제외한 나머지 행들은 삭제

 

HR

delete from tbl_panmae where to_char(panmaedate, 'yyyy-mm') != to_char(sysdate, 'yyyy-mm');

select *
from tbl_panmae;

 

 

-- 월만 지정해서 지우면 2018년 9월달 데이터 값이 있을 경우에도 남아있게 되므로 년도까지 같이 묶어줘야 한다.

 

 

2019년에 발생한 판매에 있어서 제품별, 월별 판매량의 합계를 나타내세요.

 

HR

select jepumname
     , to_char(panmaedate, 'yyyy-mm') AS 판매월
     , sum(panmaesu) AS 판매량
from
(
select *
from tbl_panmae_201907
UNION
select *
from tbl_panmae_201908
UNION
select *
from tbl_panmae
) V
group by jepumname, to_char(panmaedate, 'yyyy-mm')
order by 1, 2;

 

 

 

▷ UNION ALL

 

HR

insert into tbl_panmae_201907(panmaedate, jepumname, panmaesu)
values(to_date('2019-09-01 10:30:50', 'yyyy-mm-dd hh24:mi:ss'), '초코파이', 20);

insert into tbl_panmae_201908(panmaedate, jepumname, panmaesu)
values(to_date('2019-09-01 10:30:50', 'yyyy-mm-dd hh24:mi:ss'), '초코파이', 20);

insert into tbl_panmae(panmaedate, jepumname, panmaesu)
values(to_date('2019-09-01 10:30:50', 'yyyy-mm-dd hh24:mi:ss'), '초코파이', 20);

commit;

 

HR

select *
from tbl_panmae_201907
UNION
select *
from tbl_panmae_201908
UNION
select *
from tbl_panmae;

 

 

-- UNION 은 첫번째 SELECT 문의 결과와 두번째 SELECT 문의 결과인 행들을 합치는 것인데, 출력의 결과물은 첫번째 SELECT 문의 결과와 두번째 SELECT 문의 결과에서 중복된 행들이 있으면 제거하고 1번만 보여준다.
-- 또한 SELECT 되어져 나오는 첫번째 컬럼값을 기준으로 자동으로 오름차순 되어서 나온다.

 

HR

select *
from tbl_panmae_201907
UNION ALL
select *
from tbl_panmae_201908
UNION ALL
select *
from tbl_panmae;

 

 

-- UNION ALL 은 첫번째 SELECT 문의 결과와 두번째 SELECT 문의 결과인 행들을 합치는 것인데, 출력의 결과물은 첫번째 SELECT 문의 결과와 두번째 SELECT 문의 결과에서 중복된 행들이 있으면 제거하지 않고 그대로 중복되어 보인다.
-- 또한 정렬은 없다.

 

▷INTERSECT

 

HR

select *
from tbl_panmae_201907
INTERSECT
select *
from tbl_panmae_201908;

 

 

 

▷MINUS

 

HR

select *
from tbl_panmae_201907
MINUS
select *
from tbl_panmae_201908;

 

 

 

HR

select *
from tbl_panmae_201908
MINUS
select *
from tbl_panmae_201907;

 

 

Ⅱ. JOIN

-- JOIN(조인)은 테이블(뷰)과 테이블(뷰)을 합치는 것을 말하는데 행(ROW)과 행(ROW)을 합치는 것이 아니라, 컬럼(COLUMN)과 컬럼(COLUMN)을 합치는 것을 말한다.

 

▷ A = { 1, 2, 3 } 원소가 3개
    B = { a, b }    원소가 2개
    
    A ⊙ B = { (1,a), (1,b)
                  ,(2,a), (2,b)
                  ,(3,a), (3,b)  }
              
-- 데카르트곱(수학) ==> 원소의 곱 : 3개 * 2개 = 6개(모든 경우의 수)

-- 수학에서 말하는 데카르트곱을 데이터베이스에서는 "Catersian Product"라고 부른다.

 

 

1) SQL 1992 CODE 방식


: 데이터베이스 벤더(회사) 제품마다 약간씩 문법이 다르다.
: 테이블(뷰)과 테이블(뷰) 사이에 콤마(,)를 찍어주는 것

 

HR

select count(*)
from employees; -- 107개 행

select count(*)
from departments; -- 27개 행

select count(*)
from employees , departments; --2889개 행

 

-- 107개 행 × 27개 행 = 2889개 행

 

 

 

2) SQL 1999 CODE 방식(ANSI SQL)
: 표준화된것(ANSI SQL)
: 테이블(뷰)과 테이블(뷰) 사이에 JOIN이라는 단어를 넣어주는 것

 

HR

select *
from employees;

select count(*)
from employees; -- 107개 행

select count(*)
from departments; -- 27개 행

select *
from employees CROSS JOIN departments;

select count(*)
from employees CROSS JOIN departments; -- 2889개 행

 

-- 결과는 SQL 1992 CODE 방식과 동일하다.

 

 

▷ Catersian Product 가 사용되어지는 예

1. 프로야구 경기일정(1팀당 다른 모든 팀끼리 경기)
2. 그룹함수로 나온 1개의 행을 가지고 어떤 데이터 값을 얻으려고 할 때

 

 

사원번호, 사원명, 월급, 평균월급, 평균월급차액, 비율을 구해 보세요.

 

▷ SQL 1992 CODE 방식

 

HR

select employee_id
      ,first_name || ' ' || last_name AS ENAME
      ,nvl(salary+salary*commission_pct, salary) AS MONTHSAL
from employees; -- 107개 행

select trunc(avg(nvl(salary+salary*commission_pct, salary))) AS AVGSAL
from employees; -- 1개 행

 

HR

select V1.employee_id
      ,V1.ENAME
      ,V1.MONTHSAL
      ,V2.AVGSAL
      ,V1.MONTHSAL - V2.AVGSAL
from
(
select employee_id
      ,first_name || ' ' || last_name AS ENAME
      ,nvl(salary+salary*commission_pct, salary) AS MONTHSAL
from employees
) V1 
,
(
select trunc(avg(nvl(salary+salary*commission_pct, salary))) AS AVGSAL
from employees
) V2;

 

 

 

비율을 구해 보세요.

 

-- V2.AVGSAL : V1. MONTHSAL = 1 : X

 

HR

select V1.employee_id
      ,V1.ENAME
      ,V1.MONTHSAL
      ,V2.AVGSAL
      ,V1.MONTHSAL - V2.AVGSAL
      ,round(V1.MONTHSAL/V2.AVGSAL,2)
from
(
select employee_id
      ,first_name || ' ' || last_name AS ENAME
      ,nvl(salary+salary*commission_pct, salary) AS MONTHSAL
from employees
) V1 
,
(
select trunc(avg(nvl(salary+salary*commission_pct, salary))) AS AVGSAL
from employees
) V2;

 

 

 

HR

select V1.employee_id   AS 사원번호
      ,V1.ENAME         AS 사원명
      ,to_char(V1.MONTHSAL, '99,999')      AS 월급
      ,to_char(V1.MONTHSAL - V2.AVGSAL, '99,999')  AS 평균월급차액
      ,round(V1.MONTHSAL/V2.AVGSAL,2)   AS 비율
from
(
select employee_id
      ,first_name || ' ' || last_name AS ENAME
      ,nvl(salary+salary*commission_pct, salary) AS MONTHSAL
from employees
) V1 
,
(
select trunc(avg(nvl(salary+salary*commission_pct, salary))) AS AVGSAL
from employees
) V2;

 

 

▷ SQL 1999 CODE 방식

 

HR

select V1.employee_id   AS 사원번호
      ,V1.ENAME         AS 사원명
      ,to_char(V1.MONTHSAL, '99,999')      AS 월급
      ,to_char(V1.MONTHSAL - V2.AVGSAL, '99,999')  AS 평균월급차액
      ,round(V1.MONTHSAL/V2.AVGSAL,2)   AS 비율
from
(
select employee_id
      ,first_name || ' ' || last_name AS ENAME
      ,nvl(salary+salary*commission_pct, salary) AS MONTHSAL
from employees
) V1 
CROSS JOIN
(
select trunc(avg(nvl(salary+salary*commission_pct, salary))) AS AVGSAL
from employees
) V2;

 

-- 위의 SQL 1992 CODE 방식 결과와 동일하게 나온다.

 

▷ SQL 1992 CODE 방식 중 EQUI JOIN

 

부서번호, 부서명, 사원번호, 사원명, 월급을 나타내세요.

-- 부서번호는 employees, departments / 부서명은 departments / 사원번호, 사원명, 월급은 employees

 

HR

select D.department_id AS 부서번호
      ,D.department_name AS 부서명
      ,E.employee_id AS 사원번호
      ,E.first_name || ' ' || last_name AS 사원명
      ,nvl(E.salary+E.salary*E.commission_pct, E.salary) AS 월급
from employees E, departments D
where E.department_id = D.department_id;

 

 

-- 부서번호(필수)만 적어도 되지만 가독성을 위해 모두 적어 두는 게 좋다.

 

 

 

부서번호가 null인 Kimberely Grant를 나타내 보세요.

 

 

HR

select distinct department_id
from employees;

 

 

-- 중복된 값을 제거하고 나타내 준다.

 

HR

select D.department_id AS 부서번호
      ,D.department_name AS 부서명
      ,E.employee_id AS 사원번호
      ,E.first_name || ' ' || last_name AS 사원명
      ,nvl(E.salary+(E.salary*E.commission_pct), E.salary) AS 월급
from employees E, departments D
where E.department_id = D.department_id(+);

 

 

 

▷ SQL 1999 CODE 방식 중 INNER JOIN

 

HR

select D.department_id AS 부서번호
      ,D.department_name AS 부서명
      ,E.employee_id AS 사원번호
      ,E.first_name || ' ' || last_name AS 사원명
      ,nvl(E.salary+E.salary*E.commission_pct, E.salary) AS 월급
from employees E INNER JOIN departments D
ON E.department_id = D.department_id;

 

-- INNER JOIN에서 INNER는 생략가능하다.

 


▷ SQL 1999 CODE 방식 중 OUTER JOIN

 

HR

select D.department_id AS 부서번호
      ,D.department_name AS 부서명
      ,E.employee_id AS 사원번호
      ,E.first_name || ' ' || last_name AS 사원명
      ,nvl(E.salary+E.salary*E.commission_pct, E.salary) AS 월급
from employees E LEFT OUTER JOIN departments D
ON E.department_id = D.department_id;

 

-- LEFT OUTER JOIN은 LEFT(왼쪽) 테이블(지금은 employees E)에 있는 모든 행들을 출력해준 다음에 E.department_id = D.department_id와 같은 조건에 만족하는 행들과 짝을 지어 보여준다.

 

HR

select D.department_id AS 부서번호
      ,D.department_name AS 부서명
      ,E.employee_id AS 사원번호
      ,E.first_name || ' ' || last_name AS 사원명
      ,nvl(E.salary+E.salary*E.commission_pct, E.salary) AS 월급
from employees E RIGHT OUTER JOIN departments D
ON E.department_id = D.department_id;

 

-- 사원이 존재하지 않는 부서번호도 나타내 준다.

 


▷ SQL 1999 CODE 방식 중 FULL JOIN

 

HR

select D.department_id AS 부서번호
      ,D.department_name AS 부서명
      ,E.employee_id AS 사원번호
      ,E.first_name || ' ' || last_name AS 사원명
      ,nvl(E.salary+E.salary*E.commission_pct, E.salary) AS 월급
from employees E FULL OUTER JOIN departments D
ON E.department_id = D.department_id;

 

-- OUTER는 생략이 가능하다.

 

부서번호 30번과 60번에 근무하는 사원들에 대해서 부서번호, 부서명, 사원번호, 사원명, 월급을 나타내세요.

 

 

▷ SQL 1992 CODE 방식

 

HR

select D.department_id AS 부서번호
      ,D.department_name AS 부서명
      ,E.employee_id AS 사원번호
      ,E.first_name || ' ' || last_name AS 사원명
      ,nvl(E.salary+E.salary*E.commission_pct, E.salary) AS 월급
from employees E, departments D
where E.department_id in(30, 60) AND
      E.department_id = D.department_id;

 

▷ SQL 1999 CODE 방식

 

HR

select D.department_id AS 부서번호
      ,D.department_name AS 부서명
      ,E.employee_id AS 사원번호
      ,E.first_name || ' ' || last_name AS 사원명
      ,nvl(E.salary+E.salary*E.commission_pct, E.salary) AS 월급
from employees E JOIN departments D
ON E.department_id = D.department_id
where E.department_id in(30, 60);

 

-- SQL 1992 CODE 방식은 where 절에 순서가 바뀌어도 상관없지만, SQL 1999 CODE 방식은 where 절 전에 on을 먼저 써 주어야 한다.

 

 

부서번호, 사원번호, 사원명, 기본급, 부서별평균기본급여, 부서별평균급여차액, 비율을 나타내세요.

HR

select E.department_id
      ,E.employee_id AS 사원번호
      ,first_name || ' ' || last_name AS 사원명
      ,to_char(E.salary, '99,999') AS 기본급여
      ,V.AVGDEPTSAL, E.salary AS 부서별평균기본급여
      ,to_char(E.salary - V.AVGDEPTSAL, '99,999') AS 부서별평균월급차액
      ,round(E.salary/V.AVGDEPTSAL,2) AS 비율
from employees E LEFT JOIN (select department_id
                             ,round(avg(salary),2) AS AVGDEPTSAL
                       from employees
                       group by department_id) V
ON nvl(E.department_id, -9999) = nvl(V.department_id, -9999)
order by 1, 4;

 

문제 ▷▷ employees 테이블을 사용하여 사원번호, 사원명, 기본급여, 나이, 연령대별 평균 급여, 평균급여와의 차액, 비율을 나타내세요.

 

HR

select V1.employee_id AS 사원번호
      ,V1.ENAME AS 사원명
      ,V1.SAL AS 기본급여
      ,V1.AGE AS 나이
      ,trunc(V2.AVGSAL, 2) AS 연령대별평균급여
      ,V1.SAL - V2.AVGSAL AS 평균차액
      ,trunc(V1.SAL/V2.AVGSAL, 2) AS 비율
from
(
select employee_id
      ,first_name || ' ' || last_name AS ENAME
      ,salary AS SAL
      ,extract(year from sysdate) - (substr(jubun, 1, 2) + case when substr(jubun, 7, 1) in('1','2') then 1900 else 2000 end) +1 AS AGE
      ,trunc(extract(year from sysdate) - (substr(jubun, 1, 2) + case when substr(jubun, 7, 1) in('1','2') then 1900 else 2000 end) +1, -1) AS AGELINE
      
from employees) V1 JOIN (select trunc(extract(year from sysdate) - (substr(jubun, 1, 2) + case when substr(jubun, 7, 1) in('1','2') then 1900 else 2000 end) +1, -1) AS AGELINE
                             ,trunc(avg(salary),2) AS AVGSAL
                       from employees
                       group by trunc(extract(year from sysdate) - (substr(jubun, 1, 2) + case when substr(jubun, 7, 1) in('1','2') then 1900 else 2000 end) +1, -1)) V2
ON V1.AGELINE = V2.AGELINE;

 

 

'수업내용' 카테고리의 다른 글

[Day27][Java] Collection / Generic / auto Boxing / auto unBoxing / ArrayList / LinkedList / HashMap  (0) 2019.09.30
[Day26][Oracle] Multi Table JOIN / NON-EQUI JOIN / SELF JOIN / Stored VIEW  (0) 2019.09.26
[Day24][Oracle] cube / grouping sets / truncate / 누적 / SUB Query / Sub Query 를 사용하여 테이블 복사 / Pairwise Query(쌍 서브쿼리)  (0) 2019.09.24
[Day23][Oracle] 그룹함수(집계함수) / group by / having 절 / rollup / grouping  (0) 2019.09.23
[Day22][Oracle] 날짜함수 / 변환함수 / 기타함수  (0) 2019.09.20

'수업내용' Related Articles

티스토리툴바