2. SQL 기초 실습 코드

https://eglife.tistory.com/59

1. Over View of DB Systems

 

SQL기초는 어차피 코드를 가지고 놀 거라서 굳이 데이터는 여러개가 필요 없다.

 

Live SQL에서 EMP TABLE을 갖고 놀기로 한다.

간단히 연습하기엔 요기가 짱!

https://livesql.oracle.com

https://livesql.oracle.com/apex/f?p=590%3A1%3A16081309759585%3A%3A%3ARP%3A%3A

DROP TABLE DEPT;
CREATE TABLE DEPT (
    DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
 DNAME VARCHAR2(14) ,
 LOC VARCHAR2(13) ) ;

DROP TABLE EMP;
CREATE TABLE EMP(
    EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
 ENAME VARCHAR2(10),
 JOB VARCHAR2(9),
 MGR NUMBER(4),
 HIREDATE DATE,
 SAL NUMBER(7,2),
 COMM NUMBER(7,2),
 DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
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');
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20);
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);

DROP TABLE BONUS;
CREATE TABLE BONUS (
 ENAME VARCHAR2(10) ,
 JOB VARCHAR2(9)  ,
 SAL NUMBER,
 COMM NUMBER
) ;

DROP TABLE SALGRADE;
CREATE TABLE SALGRADE (
    GRADE NUMBER,
 LOSAL NUMBER,
 HISAL NUMBER
);
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);

select empno, ename from emp;
select * from emp where deptno = 20;  
select * from emp where deptno = 20 and sal >= 2000; 
select deptno, count(*) from emp group by deptno;
select deptno, avg(sal) from emp group by deptno;
select ename, dname, loc from emp e, dept d where e.deptno = d.deptno;

 

Data Independence

 

- Logical ( Conceptual ) data independence.

alter table emp rename to emp2; -> emp table 이름을 emp2로 바꾼다.

 

이렇게 되면 기존 emp table을 껴서 만들었던 view는 사라지게 됨 -> 다시 만들어줘야 한다.

 

- Redefine the external schema

create or replace view dept_sal as select deptno,avg(sal) AVG_SAL from emp2 group by deptno;

External Schema(=View)도 마찬가지로, 사용 안 하면 drop view dept_sal; 해준다.

 

- Physical Data Independence

( External schema : create view.../Logical schema : create table../Physical schema: create index.. )

select * from emp where deptno = 30;
create index emp_deptno on emp(deptno); -- Check what step appears in Oracle execution plan.
select * from emp where deptno = 30;
drop index emp_deptno;
select * from emp where deptno = 30;

 

뭐.. index가 생성되었다는데 뭐가 달라진 지 모르겠다.

 

어쨌든 index를 만들어주면서 physical schema가 바뀌었다는데( ex) creation / deletion of index , 우리의 select문은 문제없이 작동한다. 즉, physical schema가 바뀌어도 select query를 바꿔줄 필요가 없다는 것 = physical independence!

 

- Transaction example : Money transfer

drop table account;
create table account (id number, balance number, primary key(id));

insert into account values (1, 100); -- 100$ in account 1
insert into account values (2, 200); -- 200$ in account 2
commit;

-- An example of "money transfer" transfer
-- TX1: Move 10$ from account 1 to account 2
-- BEGIN transaction; (implicit in SQL*Plus)
update account set balance = balance - 10 where id = 1;
update account set balance = balance + 10 where id = 2;
COMMIT;

table을 만들고 Data를 집어넣고 Data를 Update 하는 방법:

 

1) Create table "T_name" (col1 number, col2 number, primary key(col1));

 

데이터 넣어 주기

2) insert into "T_name" values (1 , 100); co1 -->1 , col2 --> 100;

insert into "T_name" values(2, 200);

 

데이터업데이트

3) update T_name set col2 = col2 -1 where co1 = 1;

update account set balance = balance - 50 where id = 2;

 

Data ACID check : Atomicity, Consistency, Isolation, Durability

 

1. Atomicity and Durability

-- begin transaction; (implicit in SQL*Plus)
update account set balance = balance - 10 where id = 1;
-- 1. When system crashes while tx is in progress: (e.g. shutdown your computer at this moment.)
update account set balance = balance + 10 where id = 2;
commit; /* THINK ABOUT WHAT SHOULD HAPPEN for durability inside computer

 

위부터 차례대로 T1 / T2라고 할 때,

For Durability : T1은 중간에 crash 발생해도 유지되어야 한다. 왜냐면 T1은 commit되고 crash 난 거니까

For Atomicity : T2는 발생하면 안 된다. 왜냐하면 T2 commit 전에 crash 났으니까.(Transaction은 commit이 있기 전까진 하나의 원자단위처럼 쪼개서 발동되지 않고 묶어서 발동한다.)

 

------------------------------------------
-- 1.7.1 Concurrency Control: Isolation  -
------------------------------------------

-- Unfortunately, Oracle LiveSQL do not support multiple sessions. 

-- LOCKING for concurrency control
select sal from emp where ename = 'SMITH';
update emp set sal = sal + 100 where ename = 'SMITH';
select sal from emp where ename = 'SMITH';

-- Open another SQLPlus session S2
select sal from emp where ename = 'SMITH';
-- sal?
update emp set sal = sal * 1.1 where ename = 'SMITH';
-- What happens? why?

-- execute commit in session S1
commit;
-- Then, what happens in S2?

 

SQL Plus에서 실험해보니까,

SQL Plus #1에서 update을 하고(commit 수행 전) #2에서 data를 조회하면 update 전 정보가 저장된다.

그리고 #2에서 똑같은 data를 update 시도하면 SQL이 멈춘다. 뭔가 대기상태에 빠진듯한 모습.

 

대기상태는 풀리지 않다가, #1에서 commit;을 해줘야 비로소 #2에도 대기상태가 풀린다.

 

그 뒤에 Data를 조회해보면 #1 update내용은 물론, #2 내용까지 update가 된 것을 볼 수 있었다. 

 

근데 이것도 #2에서 조회하면 #2내용까지 update 된 건데, #1에선 #1 내용만 update된 것이 보인다..

 

#2에서 추가로 commit을 하니까 #1에서도 비로소 #2 내용까지 완성된 수정본이 조회가 된다. 결론! commit! 기준으로 연산은 작동한다.