8/9 DeadLock, 독립성, JOIN

8/8의 과제 내용 다시 복습하기

 

[DeadLock 함수]

 

 

도구 > 세션모니터를 확인해보면

동시에 두개가 접속돼 있는 것을 알 수 있음. 이 중 안중요하다 생각되는 것의 우클릭 > 세션종료 를 하면 kill이 되며 DeadLock이 풀림.

 

[Transaction Isolation의 단계]

- Read Uncommitted : 트랜잭션에서 처리중인, 아직 커밋되지 않은 데이터를 다른 트랜잭션이 읽는 것을 허용. Oracle은 미지원.

- Read Committed : 독립성. 트랜잭션이 커밋되어 확정된 데이터만 읽는 것을 허용. 이것이 가장 합리적인 버전. 대부분 DBMS가 지원

- Repeat Read : 선행 트랜잭션이 읽은 데이터는 트랜잭션이 종료될 때까지 후행 트랜잭션이 갱신, 삭제하는 것 불허 . 대기시간이 길어져서 사용하지 않음.

- Serializable : 선행 트랜잭션이 읽은 데이터를 후행 트랜잭션이 갱신하거나 삭제하지 못할 뿐 아니라, 중간에 새로운 레크드를 삽입하는 것도 막아줌. 완벽한 읽기 일관성 모드를 제공하지만 다른 사람도 같이 사용하기에 너무 느리다는 단점.

 

-- 9-0. DeadLock 함수
-- 1~4 순서대로 한 문단씩 Transaction A는 Oracle에서 실행, B는 cmd창에서 실행해보면 DeadLock이 걸림.

--Transaction A
--1)
UPDATE emp
SET sal = sal * 1.1
WHERE empno = 7369;

--3)
UPDATE emp
SET sal = sal * 1.1
WHERE empno = 7839;

--Transaction B
--2)
UPDATE emp
SET comm = 500
WHERE empno = 7839;

--4)
UPDATE emp
SET comm = 300
WHERE empno = 7369;

 

[조인]

--조인의 개념
--하나의 SQL 명령문에 의해 여러 테이블에 저장된 데이터를 한번에 조회할 수 있는 기능
-- ex1-1) 학번이 10101인 학생의 이름과 소속 학과 이름을 출력하여라
SELECT studno, name, deptno FROM student WHERE studno=10101;
-- ex1-2) 학과를 가지고 학과이름
SELECT dname FROM department WHERE deptno = 101;
-- ex1-3) ex1-1 + ex1-2를 한 번에 조회하는 것이 join
SELECT studno, name,
    student.deptno, department.dname
FROM student, department
WHERE student.deptno = department.deptno;

-- 애매모호성 (ambiguously)
-- 오류 발생 : "column ambiguously defined" 오류가 뜸
SELECT studno, name, deptno, dname
FROM student s, department d
WHERE s.deptno = d.deptno;
-- 오류 해결 : WHERE 조건의 테이블의 이름 중 하나를 select에서도 주어주어야 한다.
-- WHERE 절에 주는 조건은 데이터 사이즈와 데이터 칼럼명이 같아야 한다.
--(select에 테이블이름(별명, allas)을 전부 주어주는 것을 권장)
SELECT s.studno, s.name, d.deptno, d.dname
FROM student s, department d
WHERE s.deptno = d.deptno;

-- '전인하’ 학생의 학번, 이름, 학과 이름 그리고 학과 위치를 출력
SELECT s.studno, s.name, d.dname, d.loc
FROM student s, department d
WHERE s.deptno = d.deptno 
and s.name = '전인하';

-- 몸무게가 80kg이상인 학생의 학번, 이름, 체중, 학과 이름, 학과위치를 출력
SELECT s.studno, s.name, s.weight, d.dname, d.loc
FROM student s, department d
WHERE s.deptno= d.deptno 
and s.weight >= 80;

-- 카티션 곱 두 개 이상의 테이블에 대해 연결 가능한 행을 모두 결합
-- student 행 16 곱하기 department 행 7을 하여 전체 112개의 행이 나옴.
-- 쓰여지는 이유(면접에서 물어볼 수 있음) : 
-- 1) 개발자 실수(where절을 작성하는 것을 잊었을 때)
-- 2) 개발 초기에 많은 데이터를 생성하기 위해
SELECT s.studno, s.name, d.dname, d.loc, s.weight, d.deptno
FROM student s, department d;
-- ,를 제외하고 CROSS JOIN을 사용하기도 한다.
SELECT s.studno, s.name, d.dname, d.loc, s.weight, d.deptno
FROM student s CROSS JOIN department d;

--중요(별 3개)
--조인 대상 테이블에서 공통 칼럼을 ‘=‘(equal) 비교를 통해 같은 값을 가지는 행을 연결하여 결과를 생성하는 조인 방법
--SQL 명령문에서 가장 많이 사용하는 조인 방법
--자연조인을 이용한 EQUI JOIN
--오라클 9i 버전부터 EQUI JOIN을 자연조인이라 명명
--WHERE 절을 사용하지 않고  NATURAL JOIN 키워드 사용
--오라클에서 자동적으로 테이블의 모든 칼럼을 대상으로 공통 칼럼을 조사 후, 내부적으로 조인문 생성
SELECT s.studno, s.name, d.deptno, d.dname
FROM student s, department d
WHERE s.deptno = d.deptno;

--Natural Join Convert 
--오류 발생 : "column used in NATURAL join cannot have qualifier"
SELECT s.studno, s.name, s.weight, d.dname, d.loc, d.deptno
FROM student s NATURAL JOIN department d;
-- 오류 해결 : Natural Join할 때만 조건절의 테이블 이름은 select에 주지 않는다.
-- 조인 애트리뷰트에 테이블 별명을 사용하면 오류가 발생
SELECT s.studno, s.name, s.weight, d.dname, d.loc, deptno
FROM student s NATURAL JOIN department d;

-- NATURAL JOIN을 이용하여 교수 번호, 이름, 학과 번호, 학과 이름을 출력하여라
SELECT p.profno, p.name, deptno, d.dname
FROM professor p NATURAL JOIN department d;

-- NATURAL JOIN을 이용하여 4학년 학생의 이름, 학과 번호, 학과이름을 출력하여라
SELECT s.name, s.grade, deptno, d.dname
FROM student s NATURAL JOIN department d
WHERE grade = 4;

--JOIN ~ USING 절을 이용한 EQUI JOIN
--USING절에 조인 대상 칼럼을 지정
--칼럼 이름은 조인 대상 테이블에서 동일한 이름으로 정의되어 있어야함
-- 문 1) JOIN ~ USING 절을 이용하여 학번, 이름, 학과번호, 학과이름, 학과위치를 출력하여라
SELECT s.studno, s.name, deptno, dname
FROM student s JOIN department USING(deptno);

--EQUI JOIN의 3가지 방법을 이용하여 성이 ‘김’씨인 학생들의 이름, 학과번호, 학과이름, 학과위치를 출력
--1) WHERE 절을 사용한 방법
SELECT s.name, s.deptno, d.dname, d.loc
FROM student s, department d
WHERE s.deptno=d.deptno
and s.name LIKE '김%';
--2) NATURAL JOIN 절을 사용한 방법
SELECT s.name, deptno, d.dname, d.loc
FROM student s NATURAL JOIN department d
WHERE s.name LIKE '김%';
--3) JOIN~USING 절을 사용한 방법
SELECT s.name, deptno, d.dname, d.loc
FROM student s JOIN department d USING(deptno)
WHERE s.name LIKE '김%';
--4) ANSI JOIN(INNER JOIN ~ ON) 절을 사용한 방법
--모든 디비에서 지원해주기 때문에 알아두는 것이 좋음.(where절과 비슷)
SELECT s.name, s.deptno, d.dname, d.loc
FROM student s INNER JOIN department d
ON s.deptno = d.deptno;

 

 

[NON-EQUI JOIN]

-- 1. NON-EQUI JOIN (별 2개)
--‘<‘,BETWEEN a AND b 와 같이 ‘=‘ 조건이 아닌 연산자 사용
-- 교수 테이블과 급여 등급 테이블을 NON-EQUI JOIN하여 
-- 교수별로 급여 등급을 출력하여라
CREATE TABLE "SCOTT"."SALGRADE2" 
   (	"GRADE" NUMBER(2,0), 
     	"LOSAL" NUMBER(5,0), 
    	"HISAL" NUMBER(5,0)
  );
  
SELECT p.profno, p.name, p.sal, s.grade
FROM professor p, salgrade2 s
WHERE p.sal BETWEEN s.losal AND s.hisal;

출력 결과

 

 

 

[OUTER_JOIN] (별 3개 중요)

OUTER JOIN.sql

-- 2. OUTER JOIN (별 3개)
-- EQUI JOIN에서 양측 칼럼 값중의 하나가 NULL 이지만 조인 결과로 출력할 필요가 있는 경우 OUTER JOIN 사용
-- 학생 테이블과 교수 테이블을 조인하여 이름, 학년, 지도교수의 이름, 직급을 출력
-- 지도교수가 배정되지 않은 학생이름은 출력할 필요 없는 경우
SELECT s.name, s.grade, p.name, p.position
FROM student s, professor p
WHERE s.profno = p.profno;

-- 1) LEFT OUTER JOIN
-- 학생 테이블과 교수 테이블을 조인하여 이름, 학년, 지도교수의 이름, 직급을 출력
-- 단, 지도교수가 배정되지 않은 학생이름도 함께 출력하여라.
--s.profno가 null이더라도 데이터를 다 나오게 해 준다.
SELECT s.name, s.grade, p.name, p.position
FROM student s, professor p
WHERE s.profno = p.profno(+);

-- ANSI LEFT OUTER JOIN
SELECT s.studno, s.name, s.profno, p.name
FROM student s LEFT OUTER JOIN professor p
    ON s.profno = p.profno;
    
-- 2) RIGHT OUTER JOIN
--학생 테이블과 교수 테이블을 조인하여 이름, 학년, 지도교수 이름, 직급을 출력
-- 단, 지도학생을 배정받지 않은 교수 이름도 함께 출력하여라
SELECT s.name, s.grade, p.name, p.position
FROM student s, professor p
WHERE s.profno(+) = p.profno
ORDER BY p.profno;

-- ANSI RIGHT OUTER JOIN
SELECT s.studno, s.name, s.profno, p.name
FROM student s
    RIGHT OUTER JOIN professor p
    ON s.profno = p.profno;

 

FULL OUTER JOIN.sql

-- 3) (1)FULL OUTER JOIN
--학생 테이블과 교수 테이블을 조인하여 이름, 학년, 지도교수 이름, 직급을 출력
-- 단, 지도학생을 배정받지 않은 교수 이름 및
-- 지도교수가 배정되지 않은 학생 이름을 함께 출력하여라
-- 즉 둘 다 null이어도 출력되도록
-- 오류 : "a predicate may reference only one outer-joined table" : outer 조인을 걸려면 한 쪽만 걸어라
-- 오라클은 FULL OUTER JOIN 허용하지 않는다.
SELECT s.name, s.grade, p.name, p.position
FROM student s, professor p
WHERE s.profno(+) = p.profno(+)
ORDER BY p.profno;

-- (2)FULL OUTER 모방   --> Union
select s.name, s.grade, p.name, p.position
from student s , professor p
where s.profno = p.profno(+)
union
select s.name, s.grade, p.name, p.position
from student s , professor p
where s.profno(+) = p.profno;

-- (3)ANSI FULL OUTER JOIN
-- 오류 해결 : ANSI는 FULL OUTER JOIN이 된다.
SELECT s.studno, s.name, s.profno, p.name
FROM student s
    FULL OUTER JOIN professor p
    ON s.profno = p.profno;

 

 

*DB와 Excel 의 차이점

	DB	Excel
사용자	상호작용이 가능하다(Shared)	단독으로 사용한다
Government(정책)	회사 정책 반영이 가능하다. 암호화가 가능하다. Access Control이 가능하다.	신뢰성이 없다.

 

SELF JOIN.sql

---3. SELF JOIN(중요 별 2개) --- 
-- 하나의 테이블내에 있는 칼럼끼리 연결하는 조인이 필요한 경우 사용
-- 조인 대상 테이블이 자신 하나라는 것 외에는 EQUI JOIN과 동일
SELECT c.deptno, c.dname, c.college, d.dname college_name
    --학과            학부
FROM department c, department d
WHERE c.college = d.deptno;

-- 부서 번호가 201 이상인 부서 이름과 상위 부서의 이름을 출력
-- 결과 : XXX소속은 xxx학부
SELECT dept.dname|| '의 소속은 ' ||org.dname
FROM department dept, department org
WHERE dept.college = org.deptno
AND dept.deptno >= 201;

department 조직도

 

[과제]

-- HomeWork --
-- 1. 이름, 관리자명(emp TBL)   
SELECT e.ename, m.ename
FROM emp e, emp m
WHERE m.empno = e.mgr;

-- 2. 이름,급여,부서코드,부서명,근무지, 관리자 명, 전체직원(emp ,dept TBL)
SELECT e.ename, e.sal, e.deptno, d.dname, d.loc, m.ename 관리자
FROM emp e, emp m, dept d
WHERE e.mgr = m.empno(+) AND e.deptno = d.deptno;

-- 3. 이름, 급여, 등급, 부서명, 관리자명, 급여가 2000이상인 사람 (emp, dept, salgrade TBL)
SELECT e.ename, e.sal, s.grade, d.dname, m.ename
FROM emp e, emp m, dept d, salgrade s
WHERE e.mgr = m.empno 
AND e.deptno = d.deptno
AND e.sal BETWEEN s.losal AND s.hisal
AND e.sal>=2000;

-- 4. 보너스를 받는 사원에 대하여 이름,부서명,위치를 출력하는 SELECT 문장을 작성(emp, dept TBL)
SELECT e.ename, d.dname, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno
--and e.comm is not null
AND e.comm >0 ;

-- 5. 사번, 사원명, 부서코드, 부서명을 검색하라. 사원명기준으로 오름차순정열(emp, dept TBL)
SELECT e.empno, e.ename, e.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno
ORDER BY ename;