sql중급 / 서브쿼리를 사용한 update, delete, merge

ㅁ복습

SCOTT의 월급을 6700으로 수정하시오

update emp
set sal = 6700
where ename = 'SCOTT';

직업이 SALESMAN인 사원들의 커미션을 모두 5000으로 수정하시오

update emp
set comm = 5000
where job = 'SALESMAN';

ALLEN과 직업이 같은 사원들의 이름과 직업을 출력하는데 ALLEN은 제외하고 출력하시오

select ename, job
 from emp
 where job = (select job
               from emp
               where ename = 'ALLEN')
   and ename != 'ALLEN';

 

 

086 서브 쿼리를 사용하여 데이터 수정하기(서브쿼리를 사용한 update문)

update문에 서브쿼리를 쓸 수 있는 절 

 update 테이블명               -- 서브쿼리 가능

 set 변경할 컬럼과 값          -- 서브쿼리 가능

 where 조건                      -- 서브쿼리 가능

 

-- SMITH보다 더 많은 월급을 받는 사원들의 부서번호를 10번으로 수정하시오
update emp
set deptno = 10
where sal > (select sal
              from emp
              where ename = 'SMITH');

문제431. ALLEN보다 늦게 입사한 사원들의 커미션을 9000으로 수정하시오

update emp
set comm = 9000
where hiredate > (select hiredate
                   from emp
                   where ename = 'ALLEN');

문제432. ALLEN의 직업을 JONES의 직업으로 변경하시오

update emp
set job = (select job
            from emp
            where ename = 'JONES')
where ename = 'ALLEN';

문제433. SCOTT의 월급을 KING의 월급으로 변경하시오

update emp
set sal = (select sal
            from emp
            where ename = 'KING')
where ename = 'SCOTT';

문제434. ALLEN보다 늦게 입사한 사원들의 월급을 KING의 월급으로 변경하시오

update emp
set sal = (select sal
            from emp
            where ename = 'KING')
where hiredate > (select hiredate
                   from emp
                   where ename = 'ALLEN');

문제435. JONES보다 더 많은 월급을 받는 사원들의 부서번호를 ALLEN의 부서번호로 바꾸시오

update emp
set deptno = (select deptno
               from emp
               where ename = 'ALLEN')
where sal > (select sal
              from emp
               where ename = 'JONES');

update 절 테이블명에 서브쿼리를 사용하는 경우 > 주로 튜닝을 위해서 사용함.

문제436. 사원테이블에 SAL2라는 컬럼을 아래와 같이 수행해서 추가하시오

alter table emp
   add sal2 number(10);

문제437. 사원 테이블의 월급을 sal2에 그대로 업데이트 하세요

update emp
set sal2 = sal;

방금 추가한 sal2컬럼 삭제하기

alter table emp
  drop column sal2;
  
/*컬럼 추가/삭제 명령어

컬럼추가
alter table 테이블명
  add 컬럼명 테이터타입;
  
컬럼삭제
alter table 테이블명
 drop column 컬럼명;  */

문제438. emp와 dept를 조인해서 이름, 부서위치를 출력하시오

select e.ename, d.loc
 from emp e, dept d;

문제439. emp테이블에 loc컬럼을 추가하시오

alter table emp
  add loc varchar2(10);

문제440. (복습문제) merge문을 이용해서 emp테이블에 loc컬럼 값을 갱신하시오

merge into emp e
using dept d
on (e.deptno = d.deptno)
when matched then
update set e.loc = d.loc;

문제441. 이름, 사원 테이블의 부서위치(loc), 부서테이블의 부서위치(loc)를 출력하시오

select e.ename, e.loc, d.loc
 from emp e, dept d
 where e.deptno = d.deptno;

문제442. emp_loc컬럼의 데이터를 dept_loc컬럼의 데이터로 update하시오

update (select e.ename, e.loc as emp_loc, d.loc as dept_loc
         from emp e, dept d
         where e.deptno = d.deptno)
set emp_loc = dept_loc;
/* 에러 : ORA-01779: 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다
dept테이블의 primary key제약을 deptno에 걸면 됨
(primary key 제약은 뒤에서 배울 예정/ 21c primary key제약 없이 가능)
primary key 제약을 특정 컬럼에 걸면 중복된 데이터와 null값이 입력안됨

primary key 제약 거는 방법
alter table dept
add constraint dept_deptno_pk primary key(deptno);


-- 컬럼 별칭 반드시 사용해서 작성해야함

데이터 갱신 속도가 merge문 만큼이나 빨라서 튜닝 방법으로 많이 사용

 

문제443. 위의 update문을 rollback하고 다음의 악성 SQL로 loc컬럼을 갱신하시오

update emp e
set loc = (select loc
              from dept d
              where e.deptno = d.deptno);
              
/* emp테이블의 부서번호가 첫번재 행부터 서브쿼리로 들어와서 부서위치를 찾게 됨
> 모든 사원들에 대해 갱신해줘야 하므로 e.deptno=d.deptno 데이터의 갯수만큼 반복됨
> 따라서 성능을 위해 merge문을 사용하거나 update절의 서브쿼리를 사용해 튜닝하면 됨 */

문제444. 사원 테이블에 dname 컬럼을 추가하시오

alter table emp
add   dname  varchar2(10);

문제445. 사원 테이블의 dname을 부서테이블의 dname으로 변경하시오

update (select e.dname as emp_d, d.dname as dept_d
         from emp e, dept d
         where e.deptno = d.deptno)
set emp_d = dept_d;

문제446. telecom_service테이블의 telecom에 primary key제약을 거시오

alter table telecom_service
add  constraint telecom_service_pk primary key(telecom);
--                    제약이름     제약의 종류(제약이 걸리는 컬럼)

문제447. emp14테이블에 price라는 컬럼을 다음과 같이 추가하시오

alter table emp14
add price number(10);

문제448. (점심시간 문제) emp14테이블의 price컬럼에 값을 갱신하는데 telecom_service의 해당통신사의 price로 값을 갱신하시오

update (select e.price as emp_p, t.price as tel_p
          from emp14 e, telecom_service t
          where e.telecom = t.telecom)
set emp_p = tel_p;

 

ㅇ서브쿼리를 사용한 update문은 여러개의 데이터를 한번에 갱신할 수 있는 SQL인데 많은 양의 데이터를 갱신할 때는 튜닝된 SQL을 생각해야함.

그 튜닝된 SQL이 바로 merge문과 update절의 서브쿼리

 

 

087 서브 쿼리를 사용하여 데이터 삭제하기(서브쿼리를 사용한 delete문)

--복습예제. 월급이 3000 이상인 사원들을 삭제하시오
delete from emp
where sal >= 3000;

delete는 where절에만 서브쿼리 작성가능

--SCOTT보다 더 많은 월급을 받는 사원들을 삭제하시오
delete from emp
where sal > (select sal
              from emp
              where ename = 'SCOTT');

문제449. SCOTT과 같은 직업을 갖는 사원들을 삭제하는데 SCOTT은 제외하고 삭제하시오

delete from emp
where job = (select job
              from emp
              where ename = 'SCOTT')
   and ename <> 'SCOTT';

문제450. ALLEN보다 늦게 입사한 사원들을 모두 지우시오

delete from emp
where hiredate > (select hiredate
                   from emp
                   where ename = 'ALLEN');

 

문제451. (복습문제) 부서위치, 부서위치별 토탈월급을 출력하는데 부서위치별 토탈월급이 높은 것부터 출력하시오

select d.loc, sum(e.sal)
 from emp e, dept d
 where e.deptno = d.deptno
 group by d.loc;

문제452. (복습문제) 부서번호, 부서번호별 토탈월급을 출력하시오

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

 

 

088 서브 쿼리를 사용하여 데이터 합치기(서브쿼리를 사용한 merge문)

예제. 사원테이블의 loc컬럼을 추가하고 loc컬럼의 데이터를 해당 사원의 부서위치로 값을 갱신하시오

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

문제453. dept테이블에 sumsal이라는 컬럼을 number(10)으로 추가하시오

-- emp테이블에 sumsal컬럼 삭제하는 명령어
alter table emp
drop column sumsal;


-- dept테이블에 sumsal컬럼 추가하는 명령어
alter table dept
add sumsal number(10);

문제454. dept테이블에 추가한 sumsal컬럼에 값을 해당 부서번호의 토탈월급으로 갱신하시오

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

문제455. 위의 결과에서 deptno 40번의 sumsal은 숫자 0으로 갱신되게 하시오

merge into dept d
using (select deptno, sum(sal) as sumsal
        from emp
        group by deptno ) v
on (d.deptno = v.deptno(+))
when matched then
update set d.sumsal = nvl(v.sumsal,0) ;

 

문제456. dept테이블과 deptno_sumsal을 조인해서 부서위치를 출력하고 그 옆에 부서위치별 토탈월급이 출력되게 하세요

select d.loc, s.sumsal
 from dept d, deptno_sumsal s
 where d.deptno = s.deptno;

문제457. 위의 결과를 다시 출력하는데 outer join을 사용해서 다음과 같이 출력하세요

select d.loc, s.sumsal
 from dept d, deptno_sumsal s
 where d.deptno = s.deptno(+);

문제458. 위의 결과에서 sumsal이 null로 나오는 부분을 0으로 출력되게 하시오

select d.loc, nvl(s.sumsal,0)
 from dept d, deptno_sumsal s
 where d.deptno = s.deptno(+);

문제459. telecom_service테이블에 cnt라는 컬럼을 추가하시오

alter table telecom_service
 add cnt number(10);

문제460. 우리반테이블에서 통신사를 출력하고 통신사별 인원수를 출력하시오

select telecom, count(*)
 from emp14
 group by telecom;

문제461. telecom_service테이블에 cnt컬럼의 해당 통신사의 인원수로 값을 갱신하시오

merge into telecom_service t
using (select telecom, count(*) as cnt
        from emp14
        group by telecom ) v
on (t.telecom = v.telecom)
when matched then
update set t.cnt = v.cnt;

문제462. telecom_service테이블에 avg_age 컬럼을 추가하시오

alter table telecom_service
add  avg_age   number(10);

문제463. 우리반 테이블에서 통신사, 통신사별 평균나이를 출력하시오

select telecom, round(avg(age))
 from emp14
 group by telecom;

문제464. telecom_service테이블에 추가한 avg_age컬럼에 데이터를 통신사별 평균나이로 값을 갱신하시오

merge into telecom_service t
using (select telecom, round(avg(age)) as aa
        from emp14
        group by telecom) v
on (t.telecom = v.telecom)
when matched then
update set t.avg_age = v.aa;

문제465. 위의 결과를 악성 SQL로 작성하시오

update telecom_service t
set t.avg_age = (select round(avg(age))
                  from emp14 e
                  where e.telecom = t.telecom );