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 입사일: 오늘날
문제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 하시오
문제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
