4장 대형 데이터 집합 조작
SQL 심화2017. 9. 7. 03:17
4장 목차
- 서브쿼리를 사용한 데이터 조작
- INSERT, UPDATE 문에서 DEFAULT 사용법
- 다중 테이블 INSERT
- MERGE 문
- FLASHBACK VERSION QUERY
■ 4.1. 서브쿼리를 사용한 데이터 조작
- SELECT 문의6가지 절에서 SUBQUERY 를 쓸 수 있는 절
SELECT ---- subquery
FROM ---- subquery
WHERE ---- subquery
GORUP BY
HAVING ---- subquery
ORDER BY ---- subquery
- insert 문에서 서브쿼리를 쓸 수 있는 절
inser ---- subquery
values ---- subquery
문제433. 우리반에서 sk 텔레콤을 이용하는 학생중 가장 나이가 어린 학생의 이름과 나이를 출력하시오
- 텔레콤, 이름, 나이 , 순위를 출력하느넫 순위가
텔레콤별로 각 나이가 어린 학생순으로 순위를 부여하시오
select telecom,ename, age , dense_rank() over (partition by telecom order by age asc) a
from emp2
- 위의 결과를 다시 출력하는데 순위가 1등인 학생들만 출력하시오
SELECT telecom, ename , age
FROM
(SELECT telecom,
ename,
age ,
dense_rank() over (partition BY telecom ORDER BY age ASC) a
FROM emp2
)
WHERE a= 1 ;
문제434. 이름, 월급, 사원 테이블 전체의 평균 월급을 출력하시오
select ename, sal, round((select avg(sal) from EMP)) 평균월급
from EMP;
문제435. 이름, 월급 , 사원테이블 전체의 평균월급,
사원테이블 전체의 토탈 월급,
사원 테이블 전체의 최대 월급,
사원 테이블 전체의, 최소월급을 출력하시오
--튜닝전
select ename, sal , (select avg(sal) from EMP) 평균,
(select sum(sal) from EMP) 토탈,
(select max(sal) from EMP) 최대,
(select min(sal) from EMP) 최소
from EMP;
--튜닝후
SELECT ename,sal,round(평균월급) 평균월급,
토탈월급,
최대월급,
최소월급
FROM EMP,
(SELECT avg(sal) 평균월급,
sum(sal) 토탈월급,
max(sal) 최대월급,
min(sal) 최소월급
FROM EMP
) ;
select ename, sal , avg(sal) over () 평균,
sum(sal) over () 토탈,
max(sal) over () 최대,
min(sal) over () 최소
from emp;
select ename, sal , ( select avg(sal), sum(sal), max(sal), min(sal) from emp) from emp;
설명 : 슼라라 서브쿼리는 값을 딱 하나만 리턴한다 여러개를 리턴할 수 없다.
SELECT ename, sal , (
SELECT round(avg(sal))||' , '|| sum(sal)||' , '|| max(sal)||' , '|| min(sal) FROM emp
)
FROM emp;
문제436. 부서번호, 이름, 월급, 자기가 속한 부서번호의 평균월급을
출력하시오 !
select deptno, ename, sal , round(avg(sal)) over (partiton by deptno)
from emp;
문제436. 부서번호 , 이름, 월급, 자기가 속한 부서번호의 평균월급이
출력되는데 자기의 월급이 자기가 속한 부서번호의 평균월급 보다 더 큰 사원들만 출력되게 하시오
SELECT *
FROM
(SELECT deptno,
ename,
sal ,
round(avg(sal) over (partition BY deptno)) 부서평균
FROM emp
)
WHERE sal > 부서평균;
문제438 아래와 같이 열과를 출력하시오
select telecom, listagg(ename,',') within group (order by age desc)
from EMP2
where telecom ='sk'
group by telecom;
■ 데이터 분석함수
- rank
- dense_rank
- ntile
- listagg
- 누적치 sum(sal) over ( .. )
- lag 와 lead
문제439.
이름, 나이, 그 전행의 이름을 출력하시오 !
( 나이가 높은 순서대로 정렬해서 출력 )
SELECT ename,
age,
lag(ename,1) over (ORDER BY age desc) 그전행,
lead(ename,1) over (ORDER BY age desc) 다음행 from emp2;
문제440. telecom , 텔레콤별로 각각 그 전 행의 이름,
텔레콤별로 각각 그 다음 행의
이름, 나이, 그 전행의 이름을 출력하시오 !
( 나이가 높은 순서대로 정렬해서 출력 )
SELECT telecom,
ename,
age,
lag(ename,1) over (partition BY telecom ORDER BY age desc) 그전행,
lead(ename,1) over (partition BY telecom ORDER BY age desc) 다음행 from emp2;
문제441. 위의 결과를 다시 출력하는데 나이가 같다면 생일이 먼저인 학생이 먼저 출력되게 해보자
SELECT telecom,
ename,
age,
lag(ename,1) over (partition BY telecom ORDER BY age desc, birth asc) 그전행,
lead(ename,1) over (partition BY telecom ORDER BY age desc,birth asc) 다음행 from emp2;
문제442. (오늘의 마지막 문제)
아래와 같이 결과를 출력하시오 !
(보험회사에서 보험금액을 책정할 때 도움이 되도록 데이터를 분석해서
아래와 같이 결과를 출력하시오 )
SELECT 성별,
listagg (암종||'('||rnk||'위)',', ') within
GROUP (
ORDER BY 환자수 desc ) 암종순위
FROM
(SELECT 성별,
암종,
환자수,
rank() over (partition BY 성별 ORDER BY 환자수 desc) rnk
FROM C2012_A
WHERE 환자수 IS NOT NULL
AND 암종 <>'모든암'
)
GROUP BY 성별 ;
■ 어제 배운 내용을 복습
- external table 생성하는 실습
왜 external table을 사용해야 하는가?
오라클 DB insert 작업하는데 시간이 많이 걸린다(인건비, 시간, 오류, 디스크용량)
-> 링크만 걸어서 select한다 insert하려면 시간과 비용이 든다.
- 데이터 딕셔너리를 활용하는 방법
왜 data dictionary를 알아야 하는가?
1 내가 분석할 데이터가 들어있는 테이블을 알기위해
( 주석 처리가 다 되어있다.)
2 테이블 건수를 미리 확인해볼 수 있기 때문에 SQL을 어떻게 작성해야 할지 계획을 세울 수 있음
- 서브쿼리 사용법
- select 절의 서브 쿼리 --> 스칼라 서브 쿼리
- from 절의 서브 쿼리 --> 인라인 뷰
4장 목차
- 서브쿼리를 사용한 데이터 조작
- INSERT, UPDATE 문에서 DEFAULT 사용법
- 다중 테이블 INSERT
- MERGE 문
- FLASHBACK VERSION QUERY
문제443. 30번 부서번호에서 월급을 제일 많이 받는 사원의
이름과 월급과 순위를 출력하시오
select *
from (select ename, sal, rank() over (order by sal desc ) rnk
from emp
where deptno =30)
where rnk = 1;
- select 문에서 서브쿼리를 쓸 수 있는 절
- select
- from ------ 인 라인 뷰
- where
- having
- order by
문제444.직업, 직업별 토탈월급을 출력하시오
SELECT job ,
sum(sal)
FROM emp
GROUP BY job;
문제445. 위에서 출력된 직업별 토탈월급들의 평균값을 구하시오 !
SELECT avg(a)
FROM
(SELECT job ,
sum(sal) a
FROM emp
GROUP BY job
);
SELECT sum(a)/5
FROM
(SELECT job ,
sum(sal) a
FROM emp
GROUP BY job
);
SELECT avg(sum(sal))
FROM EMP
GROUP BY job;
문제446. 직업, 직업별 토탈월급을 출력하는데 직업별 토탈월급들의 평균값(문제445) 보다 더 큰 값만 출력되게 하시오
SELECT job,
sum(sal)
FROM emp
GROUP BY job
HAVING sum(sal) >
(SELECT avg(sum(sal))
FROM emp
GROUP BY job) ;
문제447. 텔레콤 , 텔레콤별 토탈나이를 출력하는 텔레콤별 토탈나이의 평균값보다 더 큰것만 출력하시오 !
select telecom , sum(age)
from emp2
group by telecom
having sum(age) > (select avg(sum(age))
from emp2
group by telecom);
4.2 insert 문에 서브쿼리를 쓸 수 있는 절 (p 4-7)
insert into (서브쿼리)
(서브쿼리)
insert into ( select empno, ename , sal , deptno
from emp
where deptno = 20 )
values ( 1234, 'JACK', 3500, 20);
select * from emp;
설명: 아래와 같이 with check option 을 써서 data 입력하면 where절에서 기술한 조건에 위배되게 data를 입력할 수 없다.
insert into ( select empno, ename , sal , deptno
from emp
where deptno = 20
with check option )
values ( 1234, 'JACK', 3500, 20);
select * from emp;
문제448. 사원번호, 이름, 월급, 부서번호를 물어보게해서 data가 입력될 수 있도록 SQL 을 작성하는데 월급 9000이상으로
입력하려 하면 data가 입력안되게 막으시오
insert into (select empno, ename, sal, deptno
from EMP
where sal < 9000
with check option)
values(&empno,'&ename',&sal,&deptno);
4.2 insert, update 문에서 default 사용법
create table emp448
( empno number(10),
ename varchar2(20),
sal number(10) default 3000,
job varchar2(20) default 'salesman');
insert into emp448
values(2912,'jone',default,default);
문제448. 아래의 DATA를 입력하고 UPDATE를 수행하는데
이름이 JANE 인 사원의 월급을 DEFAULT로 UPDATE하시오
INSERT INTO EMP448
VALUES(2013,'JANE',4500,'ANALYST);
UPDATE EMP448
SET SAL = DEFAULT ,JOB= DEFAULT
WHERE ENAME = 'JANE';
4장 목차
- 서브쿼리를 사용한 데이터 조작
- INSERT, UPDATE 문에서 DEFAULT 사용법
- 다중 테이블 INSERT
- MERGE 문
- FLASHBACK VERSION QUERY
4.3 다중 INSERT 문
원래는 우리가 DATA를 INSERT 할 때 한번에 한나의 테이블에만 INSERT 할 수 있는데
다중 INSERT 문은 한번에 여러개의 테이블에 동신에 INSERT 할 수 있다.
예:
CREATE TABEL EMP2_SK
AS
SELECT * FROM EMP WHERE 1=2;
문제450. 우리반 테이블에 텔레콤이 SK 인 학생들의 DATA를 EMP2_SK 테이블에 입력하셍
INSERT INTO EMP2_SK
SELECT * FROM EMP2 WHERE TELECOM='sk';
문제451. 다중insert 문을 이용해서 동시에 3개의 테이블에 data가 입력되게 하시오
sk ------------------> emp2_sk
emp lg ------------------> emp2_lg
kt ------------------> emp2_kt
insert all
when telecom='sk' then into emp2_sk
when telecom-='lg' then into emp2_lg
when telecom-='kt' then into emp2_kt
select * from emp2;
**다중 insert 문의 종류 4가지
1. 무조건 all insert 문
insert all
into emp2_sk
into emp2_sk
into emp2_sk
select * from EMP2;
truncate table emp2_sk;
truncate table emp2_kt;
truncate table emp2_lg;
2. 조건부 all insert 문
insert all
when telecom='sk' then into emp2_sk
when telecom-='lg' then into emp2_lg
when telecom-='kt' then into emp2_kt
select * from emp2;
3. 조건부 first insert 문
4. pivoting insert 문
문제452. 아래의 3개의 테이블을 생성하고 데이터를 한번에 입력하세오!
emp2_30 (30대 이상 다)
emp2_27 (27살 ~ 29살)
emp2_24 (24살 ~ 26살)
create table emp2_20
as select * from emp2;
- 조건부 first insert 문
insert first
when age > 30 then into emp2_sk
when telecom='lg' then into emp2_lg
when telecom='kt' tehn into emp2_kt
selet * from emp2;
4.4 pivoting insert 문
"컬럼 ----> 데이터가 되어서 insert가 되는 구문"
문제453. 부서번호, 부서번호별 토탈월급을 출력하시오 !
- 가로
select
sum(decode(deptno,10,sal,0)) 10,
sum(decode(deptno,20,sal,0)) 20,
sum(decode(deptno,30,sal,0)) 30
from emp;
문제454. ctas를 이용해서 테이블을 생성하시오 emp454
create table emp454
as (
select *
from (select deptno,sal
from emp)
pivot (sum(sal) for deptno in (10,20,30)));
문제455. pivot 문으로 아래의 결과를 출력하시오
select *
from (select deptno,sal
from emp)
pivot (sum(sal) for deptno in (10,20,30));
문제456. unpivot 문을 이용해서 emp454 테이블의 data를 세로로 출력하시오
select *
from emp454
unpivot ( sumsal for deptno in ("10","20","30")); ---> 숫자여서 더블 쿼테이션 마크를 사용한 것이고
문자는 그냥 쓰면 된다. ( 예: in (SMITH, ALLEN, CLARK))
문제457. 아래와 같이 결과를 출력하시오
SELECT *
FROM CRIME_AGE
UNPIVOT (CNT FOR AGE IN (UNDER_6,UNDER_12,UNDER_15,UNDER_20,UNDER_30,UNDER_40,UNDER_50,UNDER_60,OVER_60));
문제458. 서울시에서 2014 년에 살인 사건이 가장 많이 일어난 지역과 그 순위를 아래와 같이 출력하시오 !
select local, sum(sum_tot),
dense_rank()over(order by sum(sum_tot) desc)
from (
select local, sum_tot
from crime_age
where type = '살인' and year=2014 )
group by local;
문제459. unpivot 문을 이용해서 crime_age 를 아래와 같이 출력하시오 !
select *
from crime_age
unpivot( cnt for age in (under_6, UNDER_12, UNDER_15, UNDER_20,
UNDER_30, UNDER_40, UNDER_50, UNDER_60, OVER_60));
문제460. 2014년도에 살인을 일으킨 나이대, 나이대별 살인건수를 출력하시오 !
SELECT age, SUM(cnt)
FROM (SELECT *FROM crime_age
unpivot (cnt FOR age IN (UNDER_6,
UNDER_12,UNDER_15,UNDER_20,UNDER_30,UNDER_40,UNDER_50,UNDER_60,OVER_60) ) )
WHERE TYPE = '살인' AND year = 2014
GROUP BY age
ORDER BY SUM(cnt) DESC;
4.4 MERGE 문
"INSERT , UPDATE, DELETE 를 한번에 수행하는 명령어"
문제461. 사원테이블에 loc컬럼을 추가하시오
alter table emp
add loc varchar2(10);
문제462. 사원이름, 사원 테이블의 부서위치, 부서 테이블의 부서위치를 출력하시오
select e.name, e.loc, d.loc
from emp e, dept d
where e.deptno = d.deptno;
문제463. 위의 결과를 view로 생성하시오 ! (view 이름 : view463)
create view emp463
as
select ename, e.loc as emp_loc, d.loc as dept_loc
from emp e, dept d
where e.deptno = d.deptno;
문제464. 위의 view결과를 이용해서 update하는데emp_loc를 dept_loc컬럼의 값으로 갱신하시오
update emp463
set emp_loc = dept_loc;
update
(select 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;
- dept 테이블에 primary key 생성
alter tabele dept
add constraint dept_deptno_pk primary key(deptno);
- emp 테이블에 foreign key 생성하면서 dept 테이블에 deptno 참조
alter table emp
add constraint emp_deptno_fk foreign key(deptno)
references dept(deptno);
emp 테이블 loc 컬럼을 아래와 같이 update 하는 구문을 "updatable join view" 라고 한다.
문제465. 카페에서 데이터를 내려받아서 cafe 테이블을 생성하고 아래와 같이 전국에서
카페가 가장 많이 생긴 지역을 구 단위까지 출력하시오!
SELECT SUBSTR(OLD_ADD,1,INSTR(OLD_ADD,' ',2,1)) FROM CAFE;
**다중 insert 문의 종류 4가지
1. 무조건 all insert 문
2. 조건부 all insert 문
3. 조건부 first insert 문
4. pivoting insert 문
■ 어제 배웠던 내용 복습
1.다중 insert 문
-무조건 all insert문
-조건부 all insert문
-조건부 first insert문
-pivoting insert
2.pivot과 unpivot
-pivot :data를 column으로
-unpivot: column을 data로
3.updatable join view ---->merge 문
updatable join view? 수정가능한 조인뷰 /복합뷰
create view emp_dept
as
select ename, e.loc emp_loc,
d.loc dept_loc
from emp e, dept d
where e.deptno = d.deptno;
update emp_dept
set emp_loc = dept_loc;
dept ------------------------------emp
1쪽 집합 m(다) 쪽 집합
↑
primary key
위와 같이 성능이 좋은 같은 SQL? merge문
MERGE 문?
- INSERT , UPDATE, DELETE를 한번에 수행하는 명령어.
CREATE TABLE EMP90
AS
SELECT *
FROM EMP
WHERE JOB='SALESMAN';
UPDATE EMP
SET SAL = SAL *2;
문제 466. EMP90 테이블을 MERGE하는데
EMP 테이블에는 존재하고 EMP90테이블에는
없는 데이터는 새로 INSERT 하시오!
SELECT EMPNO FROM EMP
MINUS
SELECT EMPNO FROM EMP90;
SELECT *
FROM EMP
WHERE EMPNO IN (
SELECT EMPNO FROM EMP
MINUS
SELECT EMPNO FROM EMP90);
SELECT * FROM EMP
MINUS
SELECT * FROM EMP90;
SELECT *
FROM EMP
WHERE EMPNO NOT IN (
SELECT EMPNO FROM EMP90);
문제467. EMP90 테이블의 SAL을EMP 테이블의 SAL 로 값을 갱신하시오
UPDATE EMP90
SET SAL = (SELECT SAL
FROM EMP);
설명 : 위의 UPDATE 문이 가능하게 되려면 상호관련 서브쿼리(6장)를 써야한다
UPDATE EMP90 E9
SET SAL = (SELECT SAL
FROM EMP E
WHERE E.EMPNO = E9.EMPNO);
메인쿼리의 테이블을 먼저 읽고
문제468. emp 테이블에 loc 컬럼을 추가하시오
alter table emp
add loc varchar2(20);
문제.469. 상호관련 서브쿼리를 이용해서 emp 테이블의 loc컬럼의 data를 dept 테이블의 해당 부서번호의 loc 데이터로
값을 갱신하시오
update emp
set loc = (select loc
from DEPT
where emp.deptno = dept.deptno);
select * from EMP;
but 상호관련 서브쿼리는 악성 SQL이다
이것을 튜닝하는 것이 merge문이다.
문제470. 위의 작업을 merge문으로 수행하시오
MERGE INTO EMP E
USING DEPT D
ON (D.DEPTNO = e.DEPTNO)
WHEN MATCHED THEN
UPDATE SET E.LOC = D.LOC;
문제471. merge문을 위해서 아래의 환경을 구성하시오 !!
create table sales100
as
select * from sh.sales;
create table sales200
as
select rownum rn, s.*
from sh.sales s;
create table sales300
as
select *
from sales200;
select * from seles200;
select * from seles300;
drop table sales100;
drop table sales200;
drop table sales300;
select 'ALTER TABLE SALES300 '||
'DROP CONSTRAINT '||constraint_name||';'
from user_constraints
where table_name='SALES300';
ALTER TABLE SALES300 DROP CONSTRAINT SYS_C0011137;
ALTER TABLE SALES300 DROP CONSTRAINT SYS_C0011138;
ALTER TABLE SALES300 DROP CONSTRAINT SYS_C0011139;
ALTER TABLE SALES300 DROP CONSTRAINT SYS_C0011140;
ALTER TABLE SALES300 DROP CONSTRAINT SYS_C0011141;
ALTER TABLE SALES300 DROP CONSTRAINT SYS_C0011142;
ALTER TABLE SALES300 DROP CONSTRAINT SYS_C0011143;
--제약조건 삭제 업데이트 가능
UPDATE SALES300
SET PROD_ID = NULL;
SELECT * FROM SALES100;
SELECT * FROM SALES200;
SELECT * FROM SALES300;
문제472. (점심시간 문제)
SALES200테이블을 이용해서 SALES300 테이블의 PROD_ID
의 값을 갱신하시오 (연결고리는 RN컬럼)
<튜닝후>
MERGE INTO SALES300 S3
USING SALES200 S2
ON(S3.RN = S2.RN)
WHEN MATCHED THEN
UPDATE SET S3.PROD_ID = S2.PROD_ID;
SQLPLUS > SET TIMING ON
문제473. SALES200과 SALES300을 조인해서
rn, sales200 의 prod_id, sales300의 prod_id를 출력하시오!
select s2.rn, s2.prod_id s2prod, s3.prod_id s3prod
from sales200 s2, sales300 s3
where s2.rn = s3.rn;
문제474. updatable join view 를 이용해서
sales300의 prod_id를 sales200의 prod_id의 값으로 갱신하시오
create view views
as
s2.rn, s2.prod_id s2prod, s3.prod_id s3prod
from sales200 s2, sales300 s3
where s2.rn = s3.rn;
문제475. 아래의 2개의 테이블을 생성하고 값을 입력하시오 !
create table chulsuk_date create table chulsuk_cnt 문제476. 이름, 지각건수, 결석건수를 출력하시오 ! |
select ename, count(late_date), count(absent_date)
from chulsuk_date
group by ename;
문제477. 위의 결과를 다시 출력하는데 emp2 와 조인을 해서 지각과 결석을 한번도 하지 않은 학생들은
0으로 출력되게 하시오
SELECT e.ename,
sum(decode(late_date,null,0,emp1)) as 지각,
sum(decode(absent_date,null,0,1)) as 결석
FROM emp2 e,
chulsuk_date c
WHERE e.empno = c.empno(+)
GROUP BY e.ename;
-----------------------------------
--SQLGATE에서 안됨
select ename,count(l), count(c) from (SELECT e.ename,
late_date l,
absent_date c
FROM emp2 e,
chulsuk_date c
WHERE e.empno = c.empno(+)
)
GROUP BY ename;
-------------------------------------
--SQLGATE에서 안됨
SELECT e.ename, count(late_date),
count(absent_date)
FROM emp2 e,
chulsuk_date c
WHERE e.empno = c.empno(+)
GROUP BY e.ename;
문제468. 아래 쿼리의 결과를 chulsuk_cnt 테이블에 입력하시오
insert into chulsuk_cnt
select empno,ename, count(late_date), count(absent_date)
from chulsuk_date
group by ename,empno;
문제479. chulsuk_cnt 테이블의 컬럼 2개 late_cnt와 absent_cnt 컬럼을 0으로 만드세요
update chulsuk_cnt
set late_cnt = null , absent_cnt = null;
문제480. 아래의 쿼리의 결과를 이용해서 chulsuk_cnt 테이블의 late_cnt와 absent_cnt 컬럼을 갱신하는 merge문을 작성하시오
merge into chulsuk_cnt c using
(SELECT empno d_empno ,
ename d_name,
count(late_date) lc,
count(absent_date) ac
FROM chulsuk_date
GROUP BY ename,
empno
) on (d_empno=c.empno )
WHEN MATCHED THEN
UPDATE
SET c.late_cnt= lc,
c.absent_cnt =ac ;
commit;
문제481. 이름, 지각건수, 결석건수를 출력하시오
select empno,ename, count(late_date), count(absent_date)
from chulsuk_date
group by ename,empno;
문제483. 업데이트와 오늘 결석을 해서 새롭게 데이터가 생긴 혜승양의 DATA를
위의 MERGE 문을 이용해서 INSERT하시오
MERGE
INTO CHULSUK_CNT C USING (select empno,
ename,
count(late_date) LATE_CNT,
count(absent_date) ABSENT_CNT
from chulsuk_date
group by ename,
empno) V ON (C.EMPNO = V.EMPNO)
WHEN MATCHED THEN
UPDATE
SET C.LATE_CNT = V.LATE_CNT ,
C.ABSENT_CNT = V.ABSENT_CNT
WHEN NOT MATCHED THEN
INSERT VALUES(V.empno,V.ename,V.LATE_CNT,V.ABSENT_CNT);
설명: MERGE문으로 INSERT, UPDATE, DELETE 를 한번에 수행할 수 잇어서 다음과 같은 장점있다.
- 코드가 심플해진다.
- 성능이 좋아진다.
문제484. DEPT 테이블에 DEPTNO_CNT 라는 컬럼을 추가하고
해당 부서번호의 인원수로 값을 갱신하시오
ALTER TABLE DEPT
ADD DPETNO_CNT NUMBER(10);
MERGE INTO DEPT D
USING (SELECT DEPTNO, COUNT(*) DEPTNO_CNT
FROM EMP
GROUP BY DEPTNO) E
ON(D.DEPTNO = E.DEPTNO)
WHEN MATCHED THEN
UPDATE SET D.DEPTNO_CNT = E.DEPTNO_CNT;
문제485. 사원테이블에 아래의 데이터를 입력하고 다시 DEPT TABLE에 merge 하시오 !
INSERT INTO EMP ( EMPNO , ENAME, SAL , DEPTNO)
VALUES ( 1256, 'jack', 3500, 60);
INSERT INTO EMP ( EMPNO , ENAME, SAL , DEPTNO)
VALUES ( 1257, 'jane', 4900, 60);
alter table emp
drop constraint emp_deptno_fk cascade;
MERGE INTO DEPT D
USING (SELECT DEPTNO, COUNT(*) DEPTNO_CNT
FROM EMP
GROUP BY DEPTNO) E
ON(D.DEPTNO = E.DEPTNO)
WHEN MATCHED THEN
UPDATE SET D.DEPTNO_CNT = E.DEPTNO_CNT
WHEN NOT MATCHED THEN
INSERT VALUES (E.DEPTNO,NULL,NULL,E.DEPTNO_CNT);
4.5 FLASHBACK VERSION QUERY
"테이블이 그 동안 어떻게 변경해 왔는지 그 이력을 확인하는 쿼리"
- FLASHBACK 기술
- flashback table ----> 테이블을 과거로 되돌리는 기능
- flashback Query ----> 과거에 데이터를 확인하는 쿼리
- flashback drop ----> 휴지통 속에서 복원하는 기능
- flashback version Query ----> 그 동안 데이터가 어떻게 변경되어 왔는지 이력정보 확인
문제486. 아래의 작업을 순서대로 진행하시오
- 현재시간과 scn (system change number) 확인
select current_scn from v$database;
select systimestamp from dual;
※ scn ?database에 변경이 일어날때마다 1씩 증가하는 번호
- 아래의 update 문은 순서대로 실행하면서 commit하시오
Update emp
set sal = 9000;
where ename = 'KING';
COMMIT;
UPDATE EMP SET DEPTNO = 30
WHERE ENAME = 'KING';
COMMIT;
DELETE FROM EMP WHERE ENAME ='KING';
COMMIT;
- KING의 데이터가 그 동안 어덯게 변경이 되어왔는지 알아내시오
SELECT VERSIONS_STARTSCN, VERSIONS_ENDSCN, VERSIONS_OPERATION,
ENAME, SAL, DEPTNO
FROM EMP
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
WHERE ENAME = 'KING';
- 위에서 확인한 SCN 번호를 이용해서 그때 당시 KING 의 DATA가 무엇이었는지 확인한다.
(FLASH BACK 쿼리)
SELECT *
FROM EMP AS OF SCN 1481089
WHERE ENAME='KING';
- SCN 번호로 시간을 알아내는 함수
SELECT SCN_TO_TIMESTAMP('1481089')
FROM DUAL;
11시 8 분대의 CSN 번호 확인
SELECT * FROM EMP AS OF SCN 1481037
WHERE ENAME = 'KING';
- FLASHBACK TABLE 로 EMP 테이블을 SCN 7241771 번호대로 되돌리시오!
SE
ALTER TABLE EMP ENABLE ROW MOVEMENT; -- EMP 테이블을 FLASHBACK 가능한 상태로 설정한다.
FLAShback TABLE EMP TO SCN 1481037;
OR
FLASHBACK TABLE EMP TO TIMESTAMP TO_TIMESTAMP('2017/03/20 11:08:06','RR/MM/DD HH24:MI:SS');
문제487. 짝꿍과 자리를 바꿔서 우리반 테이블을 마음대로 갱신하고 삭제를 일부하고 자리로 돌아온다.
SELECT VERSIONS_STARTSCN, VERSIONS_1482815ENDSCN, VERSIONS_OPERATION,
ENAME, empno
FROM EMP2
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;
ALTER TABLE EMP2 ENABLE ROW MOVEMENT; -- EMP 테이블을 FLASHBACK 가능한 상태로 설정한다..
FLASHback TABLE EMP2 TO SCN 1482815;
문제488. (점심시간문제)
DEPT 테이블에 SUMSAL 과 AVGSAL 컬럼을 추가하고 MERGE문을 이용해서 해당 부서번호의 토탈월급과 평균월급으로 값을
갱싱하시오
ALTER TABLE DEPT ADD SUMSAL NUMBER(10);
ALTER TABLE DEPT ADD AVGS NUMBER(10);
MERGE
INTO DEPT D USING (SELECT DEPTNO,
COUNT(*) DEPTNO_CNT,
SUM(SAL) SUMSAL,
ROUND(AVG(SAL)) AVGS
FROM EMP
GROUP BY DEPTNO) E ON(D.DEPTNO = E.DEPTNO)
WHEN MATCHED THEN
UPDATE
SET D.DEPTNO_CNT = E.DEPTNO_CNT,
D.SUMSAL = E.SUMSAL,
D.AVGS = E.AVGS;
'SQL 심화' 카테고리의 다른 글
| 6장. 서브쿼리에서의 데이터 검색 (0) | 2017.09.07 |
|---|---|
| 5장. 다른 시간대에서의 데이터 관리 (0) | 2017.09.07 |
| 3장 데이터 딕셔너리를 통해서 객체 관리 (0) | 2017.09.07 |
| 2장 스키마 객체 관리 (0) | 2017.09.07 |
| 1장 유저 엑세스 제어 (0) | 2017.09.07 |
