[빅데이터분석] SQL_TIL 14 (LOCK, SELECT FOR UPDATE, 서브쿼리를 이용한 데이터입력/수정/삭제 )
chonny
2024. 5. 30. 17:08
★ 점심시간문제
우리반 테이블에서 학생이름, 나이, 우리반 나이의 최대나이, 우리반 나이의 최소나이, 우리반 나이의 평균나이를 출력하시오. 데이터 분석함수 사용하지 말고 어제 마지막 문제 SQLP 주관식 문제 형식으로 작성하세요.
select ename, age, substr(통합,1,10) 최대나이,substr(통합,11,10) 최소나이, substr(통합,21,10) 평균나이
from (select ename, age, (select rpad(max(age),10,' ')|| rpad(min(age),10,' ') || rpad(avg(age),10,' ')
from emp19 )통합
from emp19 );
+ 데이터분석가와 데이터 엔지니어 비교하기
데이터 분석가 : 데이터를 분석해서 인사이트를 찾아내는 역할
데이터 엔지니어: RAW데이터를 분석하기 좋은 테이블 형태의 데이터로 만들어 주기 위한 파이프 라인을 구성하는 역할
[TIL 14]240530
83. LOCK
예제 83. 락(LOCK) 이해하기 데이터 무결성을 보장하기 위하여 lock을 거는 것 update 하게 되면 기본적으로 update 하려는 행에 lock을
설명: 두 사람이 동시에 같은 행을 수정하지 못하게 하는 함수 (먼저 수행하는 사람(동근)이 lock을 거는 것)
문제 1. sqldvelopder으로 c##scott 유저로 접속하는데 접속창 두 개로 열고 첫 번째 창에 업데이트 문을 쓰시오.
update emp
set sal = 8000
where ename ='KING';
문제 2. 두 번째 창도 업데이트 해보시오 update emp set sal = 0 where ename ='KING';
첫 번째 창에서 commit을 하면 lock 이 풀리면서 두 번째 창이 수행이 됨
84. SELECT FOR UPDATE
예제 84번. SELECT FOR UPDATE절 이해하기 lock 은 update 문을 수행할 때 update 하려는 행들에 자동으로 lock 이 걸립니다. 그래서 lock 은 update 문장을 수행할 때 걸립니다. 그런데 select를 할 때도 lock 을 걸 수 있습니다. 지금 내가 분석하는 데이터가 지금 현시점에서 너무나도 중요한 데이터 분석이어서 그 누구도 내가 select 를 하는 행들을 갱신하지 못하게 막고 싶을 때 select for update 문을 써서 락을 겁니다.
문제 1. emp 테이블 처음상태로 되돌리기
alter session set nls_Date_format='RR/MM/DD';
drop table emp;
drop table dept;
CREATE TABLE DEPT
(DEPTNO number(10),
DNAME VARCHAR2(14),
LOC VARCHAR2(13) );
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');
CREATE TABLE EMP (
EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4) ,
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) );
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'81-11-17',5000,NULL,10);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'81-05-01',2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'81-05-09',2450,NULL,10);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'81-04-01',2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'81-09-10',1250,1400,30);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'81-02-11',1600,300,30);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'81-08-21',1500,0,30);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'81-12-11',950,NULL,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'81-02-23',1250,500,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'81-12-11',3000,NULL,20);
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'80-12-09',800,NULL,20);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'82-12-22',3000,NULL,20);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'83-01-15',1100,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'82-01-11',1300,NULL,10);
commit;
85. 서브쿼리를 사용하여 데이터 입력하기
예제 85. 서브쿼리를 사용하여 데이터 입력하기 Q1. 아래의 테이블을 생성하시오.
create table emp900
(empno number(10),
ename varchar2(10),
sal number(10));
Q2. 위 테이블에 아래의 데이터를 입력하시오.
insert into emp900 (empno, ename, sal)
values (9384,'jack',3000);
Q3. 위 테이블의 데이터를 delete 문으로 모두 지우시오
delete from emp900;
★ Q4. emp테이블에서 empno, ename, sal의 모든 데이터를 emp900으로 이행하시오.
insert into emp900 (empno, ename, sal)
select empno, ename, sal
from emp;
문제 1. emp테이블과 똑같은 테이블을 emp_to_be라는 이름으로 생성하는데 emp테이블과 같은 구조로 생성하고 데이터를 삽입하시오..
1) emp 테이블의 테이블 구조만 가져옴
create table emp_to_be
as
select *
from emp
where 1=2;
2) emp_to_be 테이블에 emp 데이터를 삽입
insert into emp_to_be
select *
from emp;
문제 2. emp19 테이블과 똑같은 구조 테이블을 emp19_ to_be라는 이름으로 생성하는데 emp19테이블과 같은 구조로 생성하시오
1) emp19 테이블의 테이블 구조만 가져옴
create table emp19_to_be
as
select *
from emp19
where 1=2;
2) emp19_to_be 테이블에 emp19 데이터를 삽입
insert into emp19_to_be
select *
from emp19;
문제 3. 위 구조에서 이름을 가명처리해서 삽입하시오 (박○우)
insert into emp19_to_be(empno,ename,age,birth,telecom,major,email,address,gender)
SELECT empno, replace(ename, substr(ename, trunc(length(ename)/2)+1 , 1) , '○'),
age, birth, telecom, major, email, address, gender
FROM emp19;
select * from emp19_to_be;
commit;
문제 4. (데이터 분석팀 요청) emp19_to_be의 t_price에 emp19의 t_price로 값을 경신하시오
merge into emp19_to_be t
using emp19 e
on(t.empno = e.empno)
when matched then
update set t.t_price =e.t_price;
86. 서브쿼리를 사용하여 데이터 수정하기
예제 86. 서브쿼리를 사용하여 데이터 수정하기
update 문에서 subquery 쓸 수 있는 절
update
서브쿼리 가능
set
서브쿼리 가능
where
서브쿼리 가능
Q1. 사원 테이블의 ALLEN의 월급을 7800으로 수정하시오
update emp
set sal = 7800
where ename='ALLEN'
;
Q2. allwn 보다 늦게 입사한 사원들의 월급을 9000으로 변경하시오.
update emp
set sal = 9000
where hiredate > (select hiredate
from emp where ename ='ALLEN')
;
문제 1. scott과 같은 부서번호에서 일하는 사원들의 월급을 8000으로 수정하시오
update emp
set sal = 8000
where deptno = (select deptno
from emp where ename ='SCOTT');
문제 2. DALLAS에 있는 부서번호에서 근무하는 사원들의 커미션을 3700으로 수정하시오.
update emp
set comm=3700
where deptno = (select deptno from dept
where loc = 'DALLAS')
문제 3. king의 월급을 allen의 월급으로 변경하시오
update emp
set sal = (select sal from emp where ename ='ALLEN')
where ename = 'KING';
문제 4. KING의 부서번호를 JAMES의 부서번호로 변경하시오
update emp
set deptno = (select deptno from emp where ename ='JAMES')
where ename ='KING';
문제 5. EMP테이블에 LOC칼럼을 추가하시오
alter table emp
add loc varchar2(10);
문제 6. 지금 추가한 loc칼럼에 해당 사원의 부서위치로 값을 갱신하시오(merge)
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;
문제 7. 지금 추가한 loc컬럼에 값을 경신하는 해당사원의 부서위치로 값을 경신하시오 (상호 관련 서브쿼리 update문으로 수행하시오)
update emp e
set loc = (select loc
from dept d
where e.deptno = d.deptno);
설명 : emp테이블의 부서번호가 set절의 서브쿼리에 하나씩 들어가면서 하나씩 갱신되기 때문에 시간이 많이 걸림(merge가 더 좋음)
문제 8. emp 테이블에 추가한 loc 칼럼을 삭제하시오
alter table emp
drop column loc;
문제 9. 부서번호, 부서번호별 평균 월급을 출력하시오.
select deptno, avg(sal)
from emp
group by deptno;
문제 10. 사원 테이블에 부서평균이라는 컬럼을 추가하시오.
alter table emp
add deptavg number(10,2);
문제 11. emp 테이블에 deptavg 칼럼에 해당 사원이 속한 부서번호의 평균월급으로 값을 경신하시오. ( 튜닝 전 )
update emp e
set deptavg = (select round(avg(sal))
from emp s
where s.deptno =e.deptno);
설명 : 맨 위의 KING부터 하나씩 deptavg를 갱신하는데 KING 의 부서번호 10번을 서브쿼리에 넣고 10번 부서번호의 평균월급을 구해서 KING의 deptavg 를 갱신하고 그 다음행도 같은 방식으로 해당 사원의 부서번호를 서브쿼리에 넣어서 실행해서 평균월급을 구해서 갱신하는 것을 14번 반복합니다.
( 튜닝 후)
merge into emp e
using (select deptno, round(avg(sal)) as 부서평균
from emp
group by deptno) s
on (e.deptno=s.deptno)
when matched then
update set e.deptavg=s.부서평균;
문제 12. 직업, 직업별 인원수를 출력하시오 (세로)
select job, count(*)
from emp
group by job;
문제 13. emp 테이블에 job_cnt라는 칼럼을 추가하시오
alter table emp
add job_cnt number(10);
문제 14. emp 테이블에 job_cnt라는 칼럼의 값을 경신하는데 해당 직업의 인원 수로 값을 경신하시오 (튜닝 전)
update emp e
set job_cnt =(select count(*)
from emp s
where s.job =e.job);
(튜닝 후)
merge into emp e
using (select job , count(*) cnt
from emp
group by job) s
on (e.job=s.job)
when matched then
update set e.job_cnt=s.cnt;
문제 15. emp 테이블에 sal2라는 칼럼을 추가하시오!
alter table emp
add sal2 number(10);
select ename, sal, sal2
from emp;
문제 16. emp 테이블의 sal 값으로 sal2를 update 하시오!
update emp
set sal2 = sal;
select ename, sal, sal2
from emp;
문제 17. emp테이블에 loc 테이블을 출력하시오
alter table emp add loc varchar2(10);
select ename, loc from emp;
문제 18. emp 테이블의 loc 칼럼 데이터를 갱신하는데 해당사원의 부서위치로 값을 경신하시오.
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;
문제 19. emp테이블에 dname 칼럼을 추가하시오
alter table emp add dname varchar2(10);
select ename, dname from emp;
문제 20. emp 테이블에 dname 칼럼에 값을 경신하는데 해당 사원의 부서명으로 값을 경신하시오 (update)
update (select e.dname as emp_dname ,d.dname as dept_dname
from emp e, dept d
where e.deptno =d.deptno)
set emp_dname = dept_dname;
문제 21. comm 이 null 인 사원들의 부서버호를 20번으로 변경하시오
update emp
set deptno =20
where comm is null;
문제 22. 부서번호가 10,20 인사원들의 월급을 5000으로 변경하시오
update emp
set sal =5000
where deptno in(10,20);
문제 23. emp와 dept 테이블을 다시 재생성하시오. 1. 명령 프롬프트창을 열기 2. sqlplus c##scott/tiger
3. ed demo.sql작성 (이후 '예' 클릭)
4. 저장하고자 하는 sql을 기입
5. 명령프롬프트에 '@demo.sql'작성
6. 바로 실행 및 저장됨
서브쿼리를 사용하여 데이터 삭제하기
예제 87. 서브쿼리를 사용하여 데이터 삭제하기 Q1. 사원 테이블에서 직업이 SALESMAN인사원들을 지우시오.
delete from emp
where job= 'SALESMAN';
Q2. SCOTT보다 월급을 더 많이 받는 사람을 지우시오.
delete from EMP
where SAL > (SELECT SAL FROM EMP WHERE ENAME = 'SCOTT');
문제 1. ALLEN 보다 늦게 입사한 사원들을 지우시오
delete from emp
where hiredate > (Select hiredate from emp
where ename = 'ALLEN');
서브쿼리를 사용하여 데이터 합치기
예제 88. 서브쿼리를 사용하여 데이터 합치기 Q. EMP테이블과 똑같은 구조로 EMP_TEST라는 테이블을 생성하시오.
create table emp_test
as
select * from emp;
select * from emp_test;
Q2. EMP_TEST테이블은 개인정보를 수정합니다. 해당 테이블의 월급을 모두 0으로 변경하시오.
update emp_test
set sal = 0;
문제 1. 위 테이블 데이터를 지우는데 직업이 salesman과 analyst 인사원들을 지우시오
delete from emp_test
where job in ('SALESMAN','ANALYST');
문제 2. 운영 데이터베이스에 있는 EMP 테이블의 데이터와 테스트 데이터베이스에 있는 EMP_TEST테이블의 데이터와 데이터 싱크가 맞게 하시오.
merge into emp_test t
using emp e
on (e.empno =t.empno)
when matched then
update set t.ename =e.ename,
t.sal=e.sal,
t.job=e.job,
t.mgr=e.mgr,
t.hiredate=e.hiredate,
t.comm=e.comm,
t.deptno=e.deptno
when not matched then
insert( t.empno, t.ename, t.sal , t.job,t.mgr,t.hiredate, t.comm, t.deptno)
values(e.empno, e.ename, e.sal ,e.job,e.mgr,e.hiredate, e.comm, e.deptno);
문제 3. emp와 emp_test 테이블의 데이터의 차이가 있는지 확인하시오!
select * from emp
minus
select * from emp_test;
select * from emp_test
minus
select * from emp;
문제 4. 우리 반 테이블을 가지고 emp19_test 테이블을 생성하시오!
create table emp19_test
as
select *
from emp19;
select * from emp19_test;
문제 5. emp19_test 테이블의 age 모두 null로 변경하시오.
update emp19_test
set age = null;
문제 6. emp19_test 테이블에서 서울에서 살지 않는 학생들을 모두 지우시오,
delete from emp19_test
where address not like '%서울%';
.
문제 7. emp19_test의 데이터를 emp와 똑같이 merge 하시오.
merge into emp19_test t
using emp19 e
on (t.empno = e.empno)
when matched then
update set
t.ename =e.ename,
t.age=e.age,
t.birth=e.birth,
t.telecom=e.telecom,
t.major=e.major,
t.email=e.email,
t.address=e.address,
t.gender=e.gender,
t.t_price=e.t_price
when not matched then
insert(t.empno, t.ename, t.age, t.birth, t.telecom, t.major, t.email, t.address, t.gender, t.t_price)
values(e.empno, e.ename, e.age, e.birth, e.telecom, e.major, e.email, e.address, e.gender, e.t_price)
문제 8. 칼럼이 많은경우 컬럼 뽑아내기
select 't.' || lower(column_name) || '=' || 'e.' || lower(column_name) || ','
from user_tab_columns
where table_name='EMP19';
문제 9. emp와 salgrade 테이블을 조인해서 이름, 월급, grade를 출력하시오.
select e.ename, e.sal, s.grade
from emp e, salgrade s
where e.sal between s.losal and s.hisal;
문제 10. emp 테이블에 grade라는 테이블을 출력하시오.
alter table emp
add grade number(10);
select ename, sal, grade
from emp;
문제 11. 위테이블에서 grade에 데이터를 삽입하시오
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;
문제 12. 우리 반 테이블에서 이름, 생일 태어난 요일을 출력하시오.
select ename, birth, to_char(birth,'day') 요일
from emp19;
문제 13. 우리 반 테이블의 birth_day 칼럼을 추가하시오.
alter table emp19
add birth_day varchar2(20);
★문제 14. 위 새로 만든 birth_day 칼럼에 해당학생이 태어난 요일로 값을 경신하시오.
update emp19
set birth_day = to_char(birth,'day');
merge into emp19 e
using emp19 s
on (e.empno = s.empno)
when matched then
update set birth_day = to_char(birth,'day');
★문제 15. 우리 반 테이블에서 태어난 요일, 태어난 요일별 인원수, 순위(태어난 요일의 인원수가 높은 순으로)를 수행하시오.
select to_char(birth,'day') 요일, count(*) 요일별인원수, dense_rank () over(order by count(*)desc) 순위
from emp19
group by to_char(birth,'day');
