일 | 월 | 화 | 수 | 목 | 금 | 토 |
---|---|---|---|---|---|---|
1 | 2 | 3 | 4 | 5 | 6 | 7 |
8 | 9 | 10 | 11 | 12 | 13 | 14 |
15 | 16 | 17 | 18 | 19 | 20 | 21 |
22 | 23 | 24 | 25 | 26 | 27 | 28 |
29 | 30 | 31 |
- difftime
- count
- %in%
- 총과 카드만들기
- 히스토그램 그리기
- loop 문
- 팀스파르타
- merge
- if문 작성법
- 회귀분석
- 데이터분석가
- max
- 불순도제거
- 순위출력
- 정보획득량
- 상관관계
- 여러 데이터 검색
- Dense_Rank
- sql
- 그래프 생성 문법
- 빅데이터
- sqld
- 빅데이터분석
- 단순회귀 분석
- 막대그래프
- 데이터분석
- 그래프시각화
- Intersect
- 회귀분석 알고리즘
- Sum
- Today
- Total
ch0nny_log
[빅데이터분석] SQL_TIL 13 (INSERT, UPDATE, DELETE, TRUNCATE, DROP, 백업테이블,COMMIT, ROLLBACK,MERGE) 본문
[빅데이터분석] SQL_TIL 13 (INSERT, UPDATE, DELETE, TRUNCATE, DROP, 백업테이블,COMMIT, ROLLBACK,MERGE)
chonny 2024. 5. 29. 17:20★ 점심시간 문제
우리반 테이블에서 우리반의 평균나이보다 더 나이가 많은 학생들의 이름과 나이를 출력하시오
select ename, age
from emp19
where age > ( select avg(age) over()평균나이
from emp19
where age is not null
order by age desc fetch first 1 rows only);
[TIL 13]240529
* 복습
문제 1. (포스코 &삼성 디스플레이) SQL 성능이 좋아졌다는 걸 어떻게 평가하는가?
- 초록 라인 사이에 분석하고자 하는 SQL을 기입한다.
select /*+ gather_plan_statistics */ ename, sal, job, ( 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 where job='SALESMAN'; SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
실행계획과 버퍼의 갯수를 확인 할 수있다.
버퍼의 갯수가 작으면 작을 수록 튜닝이 된 SQL.
문제 2. (SQLP 주관식 정답) 토탈월급과 최대월급과 최소월급의 길이가 달라져도 정확하게 잘라내질 수 있도록 SQL을 작성해야합니다. 아래의 업데이트 문장을 수행해도 결과가 잘 출력되어지는지 확인하시오 !
update emp set sal = 90000 where ename='MARTIN'; COMMIT; select /*+ gather_plan_statistics */ ename, sal, job, substr(통합, 1,4) 토탈, substr(통합,5,4) 최대, substr(통합, 9,4) 최소 from ( select ename, sal, job, ( select sum(sal) || max(sal) || min(sal) from emp where job = 'SALESMAN') as 통합 from emp where job ='SALESMAN');
MARTIN 의 월급을 90000 으로 변경했더니 결과가 잘못나오고 있습니다.
데이터가 어떻게 변경이 되든 결과가 잘 나오게 하는게 정답입니다.
문제 3. 토탈월급을 출력하는데 전체 10자리를 잡아서 출력하고 나머지 오른쪽 공간에 공백을 채워넣어라
select rpad(sum(sal),10,'') from emp;
select ename, sal, job,
substr(통합,1,instr(통합,'A')-1) 토탈,
substr(통합,instr(통합,'A')+1,instr(통합,'B')-instr(통합,'A')-1) 최대,
substr(통합,instr(통합,'B')+1,length(통합)-instr(통합,'B')) 최소
from (select ename, sal, job,
(select sum(sal)||'A'||max(sal)||'B'||min(sal)
from emp
where job = 'SALESMAN') 통합
from emp)
where job = 'SALESMAN';
78. INSERT
예제 78/ 데이터 입력하기 (INSERT)
: insert문을 이용하면 데이터를 테이블에 입력할 수 있음.
Q1. 사원 테이블에 아래의 데이터를 입력하시오.
사원번호 :9321
사원이름: JANE
월급: 4000
입사일: 오늘날
insert into emp(empno, ename, sal, hiredate) values (9321, 'JANE', 4000, SYSDATE);
select ename, hiredate from emp where hiredate = to_Date('24/05/29', 'RR/MM/DD')
- SYSDATE 를 테이블에 기입해서 시분초까지 정확하게 작성해야 확인 가능
- TO_DATE('24/05/29', 'RR/MM/DD') 는 24/05/29:00:00:00 임
insert into emp(empno, ename, sal, hiredate) values(9322, 'JACK', 5600, to_Date('24/05/29', 'RR/MM/DD')) ; select ename, hiredate from emp where hiredate = to_Date('24/05/29', 'RR/MM/DD');
문제1. 아래의 데이터를 EMP 테이블에 입력하시오.
사원번호: 9577
사원이름: TONY
월급: 6700
부서번호: 20
입사일: 240520
insert into emp(empno, ename, sal, deptno, hiredate ) values( 9577, 'TONY', 6700, 20, to_date('2024/05/20', 'RRRR/MM/DD') );
문제2. 아래의 데이터를 DEPT 테이블에 입력하시오.
부서번호: 50
부서명: HR
부서위치: SEOUL
insert into dept(deptno, dname, loc ) values( 50, 'HR', 'SEOUL' );
문제3. 아래의 데이터를 EMP19테이블에 입력하시오.
학생번호: 33
이름: 김인호
나이: 33
생일: 1991/11/04
주소: 서울시 강남구
통신사: sk
성별: 남
※ null값을 입력하는 방법 2가지
1. 명시적 입력
insert into emp(empno, ename, sal)
values (7234, null, 4000);
insert into emp(empno,ename,sal)
values (7456, '', 5000);
# 싱글퀘테이션 마크 (') 붙여서 기입
2. 암시적 입력
insert into emp(empno, ename, sal)
values (3933,'aaa',5000);
# 입력하는 컬럼외에 나머지 컬럼은 다 null이 입력됨
문제 4. 이름이 null 인사원을 출력하시오.
select ename, sal from emp where ename is null;
문제5. 아래의 데이터를 사원테이블에 입력하시오.
insert into emp(empno, ename, sal) values (4956,' ',6000);
문제6. 이름이 null이 아닌사원들의 이름과 월급을 출력하시오.
select ename, sal from emp where ename is not null;
문제 7. 위의 결과에서 이름이 공백인 사원도 제외하시오.
select ename, sal from emp where TRIM(ename) is not null;
79. UPDATE
예제 79. 데이터 수정하기 (update)
※ update 문장 작성시 주의사항 !
조심히 update 해야합니다. 잘못해서 where 절 없이 update 를 하면 전부다 같은 값으로 변경됩니다.
Q. 이름이 SCOTT이 사원의 월급을 5600으로 수정하시오.
update emp set sal=5600 where ename= 'SCOTT'; select ename, sal from emp;
문제1. 직업이 salesman인 사원들의 커미션을 9000으로 수정하시오.
update emp set sal=9000 where job = 'SALESMAN';
문제 2. 월급이 2000이상이 사원들의 부서번호를 10번으로 수정하시오.
update emp set deptno = 10 where sal >= 2000;
문제 3. 사원테이블의 월급을 모두 0으로 변경하시오.
update emp set sal = 0;
문제 4. 이름이 king인사원의 월급을 8700으로 수정하고 커미션을 5600으로 수정하시오.
update emp set sal = 8700, comm = 5600 where ename = 'KING';
문제 5. SMITH 의 데이터를 다음과 같이 변경하시오 !
SMITH 의 월급은 5700
SMITH 의 커미션은 800
SMITH 의 직업은 SALESMAN
update emp set sal = 5700, comm = 800, job ='SALESMAN' where ename='SMITH'; commit; select * from emp; rollback; select * from emp;
80. DELETE, TRUNCATE, DROP
예제80번. 데이터 삭제하기(DELETE, TRUNCATE, DROP)
1) DELETE 2) TRUNCATE 3) DROP data 삭제 삭제 삭제 저장공간 유지 삭제 삭제 저장구조 유지 유지 삭제 flashback 가능여부 가능 불가능 가능 rollback 가능여부 가능 불가능 불가능 실행속도 느림 빠름
Q . 사원 테이블에서 이름이 KING인 사원의 데이터를 삭제하시오
delete from emp where ename='KING'; select * from emp; rollback;
문제 1. 사원 테이블에서 월급이 3000이상인 사원들을 지우시오
delete from emp where sal >=3000;
문제 2. 사원 테이블에서 직업이 salesman 인사원들을 지우시오
delete from emp where job ='SALESMAN';
문제 3. 우리반 테이블에서 생일이 null 인 학생들을 지우시오 !
delete from emp19 where birth is null; selet * from emp19; commit;
문제 4. 사원테이블에서 사원번호가 7788번인 사원을 지우시오
delete from emp where empno=7788; select * from emp; rollback;
문제 5. emp 테이블의 모든 데이터를 delete 하시오.
delete from emp ; select * from emp; rollback;
★ 문제 6. emp 테이블을 어떻게 복구하는지 다음과 같이 복구하시오 (실수로 데이터 수정 후 commit 한 경우)
(오라클의 타임머신 기능- 15분내 복구)
select table_name, row_movement from user_tables where table_name='EMP'; ## EMP 를 꼭 대문자로 작성해야함
alter table emp enable row movement; select table_name, row_movement from user_tables where table_name='EMP';
--현재시간 확인 select systimestamp from dual;
flashback table emp to timestamp to_timestamp('24/05/29:13:30:00','RR/MM/DD:HH24:MI:SS');
+ 골든타임 확인하는 방법 (900초)select name, value from v$parameter where name='undo_retention';
문제7. 사원테이블의 월급을 모두 0으로 변경하고 복구하시오.
update emp set sal = 0; select * from emp; select table_name, row_movement from user_tables where table_name = 'EMP'; select systimestamp from dual; flashback table emp to timestamp to_timestamp('24/05/29:14:02:00','RR/MM/DD:HH24:MI:SS');
문제 8. emp 테이블을 drop 하시오.
drop table emp;
문제 9. 위 결과를 flashback 하시오.
1) 휴지통에 emp 테이블이 있는지 확인하기.
show recyclebin;
2) 휴지통에서 emp 테이블을 복원하기.
flashback table emp to before drop;
문제10. dept 테이블을 drop 하고 복구하기.
drop table dept; select * from dept; flashback table dept to before drop;
설명: 테이블 drop 하고 컴퓨터를 껐어도 다시 켜면 복구가 가능 (휴지통에서언제든 복구 가능)
delete 후 commit은 컴퓨터를 끄면 복구 불가
문제 11. emp 테이블을 backup 하시오
create table emp_backup as select * from emp; select * from emp_backup;
문제 12. emp 테이블을 truncate 하시오
truncate table emp;
rollback 이 안됨
문제 13. emp 테이블을 10분전으로 flashback 하시오.
flashback table emp to timestamp to_timestamp('24/05/29:14:30:00','RR/MM/DD:HH24:MI:SS'); select * from emp; # flashback 안됨
문제 14. emp_backup 테이블의 모든 데이터를 emp 테이블에 입력하시오.
insert into emp select * from emp_backup; select * from emp;
문제 15. emp19테이블을 emp19_backup 이라는 테이블 이름으로 백업하시오.
create table emp19_backup as select * from emp19; select * from emp19_backup;
문제 16. emp19 테이블을 truncate하고 복구하시오.
truncate table emp19; insert into emp19 select *from emp19_backup; select * from emp19;
문제 17.
문제
81. COMMIT, ROLLBACK
예제 81. 데이터 저장 및 취소하기 (COMMIT, ROLLBACK)
1. COMMIT: 앞에 작업한 데이터를 database에 영원히 저장
1) 명시적 commit : SQL사용자가 직접 COMMIT 명령어를 수행한 것
2) 암시적 commit
- Data Definition Language 명령어(create, drop, truncate, rename)를 수행했을 때
- Data Control Language 명령어(grant, revoke)를 수행했을 때
- 정상 종료했을 때(commit , exit)
2. ROLLBACK: 앞에서 작업한 데이터의 입력, 수정, 삭제한 내용을 취소하는 명령어
문제 1. emp 테이블의 데이터를 모두 delete 하고 테이블을 생성후 rollback 하시오
create table dept2 ( deptno number(10), dname varchar2(10) ); rollback; select * from emp;
= create 라는 암시적 commit이 일어났기 때문에 rollback 이 안됨.
문제 2. 직업이 analyst인 사원들의 커미션을 6000으로 변경하시오.
update EMP SET COMM =6000 where job =='ANALYST';
문제 3. 직업이 analyst인 사원들을 지우시오,
DELETE FROM EMP WHERE JOB ='ANALYST'
문제 4. 월급이ㅣ 1200이상이고 직업이 SALESMAN인 사원들의 커미션을 5800으로 수정하시오.
update emp set comm = 5800 where job ='SALESMAN' and sal >=1200;
★ 문제 5. auto commit 기능이 활성화 되었는지 비활성화 되었는지 확인하기
show autocommit;
set autonommit on; show autocommit;
문제 6. emp 테이블의 데이터를 모두 delete 하시오.
** 바로 commit 되어서 rollback 이 안됨
DELETE FROM EMP; select * from emp; rollback;
※ 오라클은 기본적으로 자동 커밋이 비활성화 되어져 있지만mySQL 이라는 데이터베이스는 자동 커밋이 기본적으로 활성화 되어져 있습니다.
82. MERGE
예제 81. 데이터 입력, 수정, 삭제 한번에 하기(MERGE)
insert , update, delete 를 하나의 문장으로 한번에 수행하는 sql (성능이 빨라서 SQL튜닝할 때 튜너들이 주로사용)
Q1(복수) EMP 와 dept를 조인해서 이름과 부서위치를 출력하시오.
select e.ename, d.loc from emp e, dept d where e.deptno = d.deptno;
※ 데이터 모델링중에 반정규화란 ?
위와 같이 조인을 자주 해야하는 상황인데 조인을 할 때 성능이 너무 느리다면
그냥 loc 컬럼을 emp 테이블에 추가 시키고 조인을 하지 않고 그냥 다음과 같이
검색을 하는것을 반정규화라고 합니다.select ename, loc from emp;
문제1. emp 테이블에 loc컬럼을 추가하시오,
alter table emp add loc varchar2(10); select ename, loc from emp;
★ 문제2. emp 테이블에 추가한 loc 컬럼값을 해당 사원의 부서위치로 데이터 갱신하시오.
1) 튜닝전
update emp e set loc =(select loc from dept d where d.deptno = e.deptno);
설명: emp 테이블 데이터를 한행씩 갱신하는데 맨 첫번째 행의 데이터가 king이면 king의 부서번호 10번을 서브쿼리 안으로 넣습니다. 그러면 부서번호 10번의 부서위치가 출력되면서 new york 으로 king의 부서위치가 갱신된다.(그다음행 반복과정)
2) 튜닝후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;
설명: 튜닝전 update문은 emp테이블의 건수만큼 update를 반복하지만 merge는 한번에 update 함(속도가 빠름)
문제 3. 우리반테이블의 t_price 컬럼을 추가하시오.
alter table emp19 add t_price number(10);
문제4. telecom_table 의 t_price의 값으로 emp19의 t_price를 갱신하시오.
merge into emp19 e using telecom_table t on ( lower(e.telecom) = t.telecom ) when matched then update set e.t_price = t.t_price ; select ename, t_price from emp19;
문제 5. emp와 salgrade를 조인해서 이름, 월급과 grade를 출력하시오.
select e.ename, e.sal, s.grade from emp e, salgrade s where e.sal between s.losal and s.hisal;
문제 6. emp 테이블에 grade 라는 컬럼을 추가하시오 !
alter table emp add grade number(10); select ename, grade from emp;
문제7. emp 테이블에 grade 컬럼에 값을 갱신하 는데 해당 사원의 급여등급으로
값을 갱신하시오 salgrade 테이블을 사용해서 merge 하시오
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;
문제 8.아래의 데이터를 사원테이블에 입력하시오.
사원번호 : 2345
사원이름 : JANE
직업 : ANALYST
월급 : 3500
입사일: 오늘날짜
커미션 : NULL
부서번호: 20
insert into emp(empno, ename, job, sal, hiredate, comm, deptno) values(2345, 'JANE','ANALYST', 3500, to_date('2024/05/29','RRRR/MM/DD'), NULL, 20);
문제 9. 이름이 king 사원의 데이터를 다음과 같이 변경하시오.
월급 : 9800
커미션 : 2450
부서번호: 20
update emp set sal = 9800 , deptno = 20 , comm= 2450 where ename ='KING';
★ 문제 10. 사원테이블에 DNAME컬럼을 추가하고 해당사원의 부서명(DNAME)으로 값을 갱신하시오
튜닝전(UPDATE)
alter table emp add dname varchar2(10); update emp e set e.dname= (select dname from dept d where e.deptno = d.deptno);
튜닝후(MERGE)
merge into emp e using dept d on(e.deptno=d.deptno) when matched then update set e.dname = d.dname;
★ 문제 11 SQLP주관식 정답
select ename, sal, job, substr(통합,1,10) 토탈, substr(통합,11,10) 최대, substr(통합,21,10) 최소 from (select ename, sal, job, (select rpad(sum(sal), 10, ' ') || rpad(max(sal),10,' ') || rpad(min(sal),10,' ') from emp where job = 'SALESMAN') 통합 from emp) where job = 'SALESMAN';