💡 merge란?
insert, update, delete작업을 하나의 명령어로 한번에 수행하는 DML(Data Manipulation Language)문
💡 DML문 (Data Manipulation Language)
insert, update, delete, merge, select
문제1. demo 스크립트를 돌려서 emp와 dept를 다시 생성하시오
@demo
문제2. emp 와 dept 를 조인해서 이름과 부서위치를 출력하시오
select ename, loc
from emp e, dept d
where e.deptno = d.deptno;문제3. emp 테이블에 loc 컬럼을 추가하시오
alter table emp
add loc varchar2(20);문제4. emp 테이블의 loc 컬럼의 데이터를 dept 테이블의 loc 를 이용해서 해당 사원의 부서위치로 값을 갱신하시오
merge into emp e
using dept d
on (e.deptno = d.deptno)
when matched then
update set e.loc = d.loc;ㄴ 💡 역정규화
문제5. 사원 테이블에 dname 컬럼을 추가하시오
alter table emp
add dname varchar2(20);문제6. 사원 테이블의 dname 컬럼의 데이터를 해당 사원의 부서명으로 갱신하시오
merge into emp e
using dept d
on (e.deptno = d.deptno)
when matched then
update set e.dname = d.dname;복습문제1. 사원이름, 월급, 직업이 SALESMAN인 사원들의 토탈월급, 직업이 SALESMAN 인 사원들의 최대월급, 직업이 SALESMAN인 사원들의 최소월급을 출력하시오
select ename, sal, ( select sum(sal) from emp where job='SALESMAN' ) 토탈월급,
( select max(sal) from emp where job='SALESMAN' ) 최대월급,
( select min(sal) from emp where job='SALESMAN' ) 최소월급
from emp;마지막 문제1. 아래 SQL을 튜닝하시오
- 튜닝 전
select ename, sal,
( select sum(sal) from emp where job='SALESMAN') as 토탈월급,
( select max(sal) from emp where job='SALESMAN') as 최대월급,
( select min(sal) from emp where job='SALESMAN') as 최소월급
from emp;- 튜닝 후
select ename, sal, substr(total, 1, 10) as 토탈월급,
substr(total, 11, 10) as 최대월급,
substr(total, 21, 10) as 최소월급
from (
select ename, sal,
( select rpad(sum(sal),10,' ') || rpad(max(sal),10,' ') || rpad(min(sal),10,' ')
from emp
where job='SALESMAN') total
from emp
);마지막 문제2. emp21 테이블에 t_price 라는 컬럼을 추가하고 telecom_table의 t_price 로 값을 갱신하시오
alter table emp21
add t_price number(10);
merge into emp21 e
using telecom_table t
on (e.telecom = t.telecom)
when matched then
update set e.t_price = t.t_price;
select telecom, t_price from emp21;문제7. emp 테이블과 salgrade 테이블을 서로 조인해서 이름과 월급과 급여등급을 출력하시오
select e.ename, e.sal, s.grade
from emp e, salgrade s
where e.sal between s.losal and s.hisal;문제8. 사원 테이블에 grade 라는 컬럼을 추가하시오
-- grade 컬럼의 유형 조회
desc salgrade;
-- 컬럼유형을 똑같이 맞춰서 add 해줌
alter table emp
add grade number(10);
select * from emp;문제9. merge 문을 이용해서 emp 테이블의 grade 컬럼의 데이터를 salgrade 를 이용해서 해당 사원의 급여등급으로 값을 갱신하시오
merge into emp e
using salgrade s
on (e.sal between s.losal and s.hisal)
when matched then
update set e.grade = s.grade;
select ename, grade
from emp;
rollback;
-- 또는
select ename, grade
from emp;
update emp
set grade = null;문제10. 삼성디스플레이에서 위(문제9) SQL을 어떻게 작성했는지 확인하고 수행하시오
update emp e
set grade = ( select grade
from salgrade s
where e.sal between s.losal and s.hisal );
select ename, grade
from emp;💡 위의 update 문장은 emp테이블의 갯수만큼 update가 수행됨. 그래서 emp 테이블의 갯수가 많으면 더 오래걸림 -->
튜닝 전 SQL
--> merge문은 1번 갱신하고 끝남
문제11. emp 테이블에 loc 컬럼을 추가하시오
-- loc 컬럼 유형 조회
desc dept;
alter table emp
add loc varchar2(13);
select ename, loc
from emp;문제12. merge 문을 작성하는데 emp 테이블의 loc를 dept를 이용해서 해당 사원의 부서위치로 값을 갱신하게 작성하시오
merge into emp e
using dept d
on (e.deptno = d.deptno)
when matched then
update set e.loc = d.loc;
-- 잘 갱신되었는지 확인
select ename, loc from emp;문제13. 다시 rollback 하고 위의 merge문의 튜닝 전 SQL인 update 문을 작성하시오 (문제10 방법으로)
-- 튜닝 전 SQL
update emp e
set loc = ( select loc
from dept d
where e.deptno = d.deptno );문제14. 아래의 SQL을 튜닝하시오
alter table emp
add dname varchar2(20);
select ename, dname from emp; 튜닝 전:
update emp e
set dname = ( select dname
from dept d
where d.deptno = e.deptno );튜닝 후:
merge into emp e
using dept d
on (d.deptno = e.deptno)
when matched then
update set e.dname = d.dname;문제15. emp 테이블과 똑같은 구조와 데이터를 갖는 emp_test 테이블을 생성하시오
create table emp_test
as
select *
from emp;
select * from emp_test;문제16. emp21 테이블을 백업하시오. 백업 테이블명은 emp21_backup 으로 하시오
create table emp21_backup
as
select *
from emp21;
select * from emp21_backup;문제17. emp_test 테이블의 월급과 커미션을 모두 0 으로 변경하시오
update emp_test
set sal = 0, comm = 0;
commit;
select * from emp_test;문제18. emp_test 테이블에서 직업이 ANALYST, SALESMAN 인 사원들을 삭제하시오
delete from emp_test
where job in ('ANALYST', 'SALESMAN');문제19. emp 테이블의 데이터를 emp_test 테이블에 똑같이 입력 또는 수정하시오
OLTP 서버 —————————> OLAP 서버
(emp 테이블) -------- (emp_test 테이블)
merge와 insert를 한번에
merge into emp_test t
using emp e
on (e.empno = t.empno)
when matched then
update set t.ename = e.ename,
t.job = e.job,
t.mgr = e.mgr,
t.hiredate = e.hiredate,
t.sal = e.sal,
t.comm = e.comm,
t.deptno = e.deptno
when not matched then
insert ( t.empno, t.ename, t.job, t.mgr, t.hiredate, t.sal, t.comm, t.deptno )
values ( e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm, e.deptno );문제20. emp21 과 똑같은 구조와 데이터르 emp21_test 를 생성하시오
create table emp21_test
as
select *
from emp21;문제21. emp21_test 테이블의 age와 email을 null 로 변경하시오
update emp21_test
set age= null, email=null;문제22. emp21_test 테이블의 empno 11번과 12번을 지우시오
delete from emp21_test
where empno in ( 11, 12);
commit; 문제. merge 문을 이용해서 emp21_test 테이블의 내용을 emp21과 동기화 시키시오
merge into emp21_test t
using emp21 e
on (e.empno = t.empno)
when matched then
update set t.ename = e.ename,
t.age = e.age,
t.birth = e.birth,
t.telecom = e.telecom,
t.major = e.major,
t.email = e.email,
t.address = e.address,
t.t_price = e.t_price
when not matched then
insert ( t.empno, t.ename, t.age, t.birth, t.telecom, t.major, t.email, t.address, t.t_price )
values ( e.empno, e.ename, e.age, e.birth, e.telecom, e.major, e.email, e.address, e.t_price );
select * from emp21_test;