sql 중급 / DML(commit, rollback, merge, lock, select for update)

문제395. 부서번호, 부서번호별 평균월급을 출력하시오

select deptno, avg(sal)
 from emp
 group by deptno;

문제396. 위의 SQL의 결과를 view로 생성하시오

create view emp_deptno_avg
as
select deptno, avg(sal) as avg
 from emp
 group by deptno;
 
 -- view 생성할 때 쿼리문에 그룹함수가 있으면 컬럼별칭을 반드시 사용해야함.

문제397. emp테이블과 emp_dept_avg view를 조인해서 이름,월급, 자기가 속한 부서의 평균월급을 출력하시오

select e.ename, e.sal, a.평균
 from emp e, emp_deptno_avg a
 where e.deptno = a.deptno;

문데398. 어제 마지막 문제의 결과처럼 자기의 월급이 자기가 속한 부서번호의 평균월급보다 더 큰 사원들만 나오게 위의 SQL을 이용해서 완성하시오

select e.ename, e.sal, a.평균
 from emp e, emp_deptno_avg a
 where e.deptno = a.deptno 
    and e.sal > a.평균;
    
/* view를 만들 수 없는 상황이라면 
 아래처럼 바로 from 절의 서브쿼리로 작성하면 됨 */
select e.ename, e.sal, a.평균
 from emp e, ( select deptno, avg(sal) 평균
                 from emp
                 group by deptno ) a
 where e.deptno = a.deptno and e.sal > a.평균;
 -- 하지만, emp테이블을 2번이나 select했기 때문에 악성 sql임

o over() 의 괄호 안에 partition by를 넣느냐 안 넣느냐의 차이

--사원테이블의 전체 평균월급 출력
select deptno, ename, sal, avg(sal) over ()
 from emp;
 
--partition by 때문에 부서번호별 평균월급이 출력 
select deptno, ename, sal, avg(sal) over (partition by deptno)
 from emp;

문제399. 통신사, 이름, 나이, 통신사별 평균나이를 출력하는데 자신의 나이가 통신사별 평균나이보다 더 많은 나이인 학생들만 출력하시오

select *
 from (
        select telecom, ename, age, avg(age) over (partition by telecom) as 평균
         from emp14
      )
 where age > 평균;

 

ㅁ오라클에서 데이터를 삭제하는 방법 3가지

1. delete

2. truncate

3. drop

 

ㅁdrop 명령어로 emp테이블 삭제하기

drop table emp;

DDL 명령어(create, alter, drop, truncate, rename)는 수행되면서 commit이 되어버림. 그래서 rollback할 수 없음

-- 휴지통 보는 방법
show recyclebin;

select *
 from user_recyclebin;
 
-- 휴지통에서 emp테이블 복구하기
flashback table emp to before drop;

select *
 from emp;
 
-- 휴지통 비우기
purge recyclebin;

delete는 휴지통이 아닌 undo_segment라는 공간에 들어가기 때문에 flashback table로 복구해야함

 

 

081 데이터 저장 및 취소하기(COMMIT, ROLLBACK)

commit : 지금까지 작업한 DML작업(insert, update, delete, merge)를 데이터베이스에 영구 저장

rollback : 지금까지 작업한 DML작업(insert, update, delete, merge)를 모두 취소

문제400. 사원테이블의 월급을 전부 0으로 변경하고 commit하시오

update emp
set sal = 0;

commit;

select *
 from emp;

문제401. 사원테이블의 데이터를 전부 delete로 지우시오

delete from emp;
 
select *
 from emp;

이 상태에서 rollback하게 되면 delete만 rollback됨. 그 전에 commit한거는 rollback되지 않음.

ㅇ 암시적으로 commit이 수행되는 경우

1. 정상종료 했을 때(exit)

2. DDL문을 실행했을 때 (create, alter, drop, truncate, rename)

3. DCL문을 실행했을 때 (grant, revoke)

ㅇ 암시적 rollback이 수행되는 경우

1. 컴퓨터가 비정상적으로 종료되었을 때

 

 

082 데이터 입력, 수정, 삭제 한번에 하기(MERGE)

merge문은 insert, update, delete 명령어를 한번에 수행하는 SQL, 주로 대용량 데이터를 한번에 update하는 SQL의 성능을 개선하기 위해서 사용

-- emp와 dept를 조인해서 사원이름, 부서위치를 출력하시오
select e.ename, d.loc
 from emp e, dept d
 where e.deptno = d.deptno;
 
-- emp와 dept를 조인하지 않고 emp테이블에서 부서위치에 대한 데이터도 보기위해 emp테이블에 loc컬럼을 추가하기
alter table emp
  add loc varchar2(10);   
  /* 컬럼추가 명령어
      alter table 테이블명
         add  컬럼명   데이터타입; */

-- merge문을 이용해서 loc컬럼에 값을 갱신하기(해당 사원의 부서위치로 값 갱신)
merge into emp e             -- emp테이블을 merge하는데
using dept d                 -- dept테이블을 사용해라
on (e.deptno = d.deptno )    -- 두 테이블의 연결고리는 deptno
when matched then            -- on절의 연결고리가 match된다면
update set e.loc = d.loc;    -- emp테이블의 loc를 dept테이블의 loc로 갱신해라~

문제402. emp테이블에 dname(부서명) 컬럼을 추가하시오!

alter table emp
  add dname varchar2(20);
  
merge into emp e
using dept d
on (e.deptno = d.deptno)
when matched then
update set e.dname = d.dname;

다시 emp테이블을 재생성하겠습니다.

만약 merge문을 이용하지 않고 emp테이블에 loc 값을 갱신한다면,

alter table emp
   add loc varchar2(10);   -- 컬럼추가
   
update emp e
set loc = (select loc
            from dept d
            where d.deptno = e.deptno);

위와 같이 update문으로 loc컬럼을 갱신하는 방법은 먼저 emp테이블에서 첫번째 행인 KING의 loc를 갱신하기 위해서 KING의 deptno 10을 서브쿼리로 전달 > dept테이블에서 deptno가 10인 loc를 출력해서 emp테이블의 KING의 loc로 갱신 > 그리고 두번째행도 같은 방법으로 갱신 , emp테이블의 데이터가 14건이니까 총 14번 수행함. 대용량 데이터를 다룰시 성능이 느려짐

> 이런 SQL을 merge문으로 튜닝하면 금방 수행됨.

 

문제404. emp14테이블 telecom_price 컬럼을 추가하시오

alter table emp14
  add telecom_price number(10);

문제405. 위의 update문을 이용해서 emp14의 telecom_price의 값을 갱신하는데, telecom_service 테이블의 price 컬럼의 값으로 갱신하시오.

update emp14 e
set telecom_price = (select price
                      from telecom_service s
                      where s.telecom = e.telecom);

문제406. merge문으로 튜닝하시오

merge into emp14 e
using telecom_service s
on (e.telecom = s.telecom)
when matched then
update set e.telecom_price = s.price;

 

문제407. (점심시간 문제) emp테이블에 grade라는 컬럼을 추가하고 salgrade테이블의 해당 사원의 월급의 범위로 grade컬럼을 갱신하시오.

alter table emp
   add grade number(10);
   
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;

 

문제408. 부서번호, 부서번호별 인원수를 출력하시오.

select deptno, count(*)
 from emp
 group by deptno;

문제409. 부서테이블에 cnt라는 컬럼을 숫자형으로 추가하시오

alter table dept
  add cnt   number(10);

문제410. 지금 추가한 cnt컬럼에 해당 부서번호의 인원수로 값을 갱신하시오

-- view 생성
create view deptno_cnt
as
 select deptno, count(*) cnt
  from emp
  group by deptno;

merge into dept d
using deptno_cnt c
on (d.deptno = c.deptno )
when matched then
update set d.cnt = c.cnt;


-- view 생성하지 않고 하는 법
merge into dept d
using (select deptno, count(*) as 인원수
         from emp
         group by deptno) c
on (d.deptno = c.deptno)
when matched then
update set d.cnt = c.인원수;

문제411. 부서번호, 부서번호별 토탈월급을 출력하시오

select deptno, sum(sal)
 from emp
 group by deptno;

문제412. 부서테이블에 sum(sal)이라는 컬럼을 추가하시오

alter table dept
   add sumsal  number(10);

문제413. 지금 추가한 sumsal컬럼에 값을 갱신하시오

merge into dept d
using ( select deptno, sum(sal) as 토탈
          from emp
          group by deptno )  s
on (d.deptno = s.deptno)
when matched then
update set d.sumsal = s.토탈;

문제414. (DML문 복습) 직업이 ANALYST인 사원들의 커미션을 9000으로 수정하시오

update emp
set comm = 9000
where job = 'ANALYST';

문제415. 자동커밋 기능이 활성화 되어있는지 확인하시오

show autocommit;

문제416. 자동커밋 기능을 활성화 시키시오

set autocommit on;
-- autocommit immediate 출력됨 자동커밋기능 활성화

set autocommit off;
-- 비활성화

자동커밋기능을 활성화시키면 dml문장이 실행될때마다 자동 커밋됨

 

문제417. (DML문 복습) 커미션이 null인 사원들의 데이터를 삭제하시오

delete from emp
where comm is null;

문제418. (DML문 복습) 1981년도에 입사한 사원들을 삭제하시오

delete from emp
where hiredate between to_date('1981/01/01','RRRR/MM/DD') 
                and to_date('1981/12/31','RRRR/MM/DD')+1;

문제419. (DML문 복습) 성이 김씨인 학생들의 통신사를 kt로 변경하시오

update emp14
set telecom = 'kt'
where ename like '김%';

 

083 락(LOCK) 이해하기

특정 행을 갱신하는 update 문장이 이싿면 그 update문장을 수행하는 세션은 update하는 행에 lock을 검. 그 lock은 commit이나 rollback을 할 때가지 유지됨.

lock을 거는 이유?

내가 변경하고 있는 데이터를 다른 사람이 변경하지 못하도록 하기 위해서.

ex)내가 KING의 월급을 9000으로 변경하고 잘 변경됐는지 확인해보니 0이라면, 황당쓰

 

update 하는 행 전체에 lock > 그래서 KING의 월급을 내가 변경했으면 다른 세션들은 KING의 월급외에도 다른 어떤 컬럼의 데이터도 변경할 수 없음.

 

문제420. sqldeveloper로 창을 두 개 열고 똑같이 scott으로 접속하고 아래의 update문을 각각 수행하고 lock이 걸리는지 확인하세요

 

 

084 SELECT FOR UPDATE절 이해하기

lock은 update문을 수행할 때 필요한 것 = 내가 변경하고자 하는 데이터를 다른 세션이 변경하지 못하도록 막을 때

그런데 select를 할 때도 lock을 걸 수 있음. 그게 바로 select ~ for update문

> select 할 때 lock을 거는 이유 

ex) 편의점에서 밤12시 오늘의 총 매출을 집계하고 싶고, 지금 select하는 그 시점의 데이터로 집계하고 싶을 때 데이터가 변경되면 안됨. 내가 집계하는 동안 그 누구도 데이터를 갱신시키지 못하게 막음

commit이나 rollback 하면 풀림