3.Oracle SQL 서브쿼리/DML/트랜잭션

2022.07.22.금

---

가.서브쿼리

=>하나의 SELECT 만으로 원하는 데이터를 조회할 수 없을 때 사용하는 방법

=>여러 개의 SELECT문장을 하나로 합쳐서 하나의 실행 가능한

     SQL문장으로 만들어 원하는 데이터를 조회

/*
문법
(main 쿼리)
SELECT select_list
FROM 테이블         (서브쿼리) 
WHERE 컬럼명 연산자 (SELECT select_list
 FROM 테이블);
*/

--월급이 Whalen보다 많은 사원 이름 출력
SELECT last_name,salary
FROM employees
WHERE salary >= (SELECT salary
                 FROM employees
                 WHERE last_name = 'Whalen');

 -서브쿼리 종류

→단일행 서브쿼리 : 서브쿼리 실행 결과가 한 개의 행을 반환(=, >, >=, <, <= ,!= 와 같은 비교 연산자)

--사원들의 평균 월급보다 더 많은 월급을 받는 사원을 조회
SELECT last_name,salary
FROM employees
WHERE salary >= (SELECT AVG(salary)
                 FROM employees);
                 
--부서번호가 100인 사원들 중에서 최대 월급을 받는 
--사원과 동일한 월급을 받는 사원을 조회
SELECT last_name,salary
FROM employees
WHERE salary = (SELECT MAX(salary)
                FROM employees
                WHERE department_id=100);

→복수행 서브쿼리 : 서브쿼리 실행 결과가 복수 개의 행을 반환(IN, ANY, ALL, EXIST 연산자)

/*
복수행 연산자
IN : 메인 쿼리와 서브 쿼리가 IN 연산자로 비교
ALL : 복수행 서브쿼리에서 > 또는 < 같은 비교 연산자를 사용하고자 할 때 사용
      검색 조건의 모든 값이 일치하면 참
ANY : 복수행 서브쿼리에서 > 또는 < 같은 비교 연산자를 사용하고자 할 때 사용 
      검색 조건이 하나라도 일치하면 참
EXIST : 서브 쿼리의 반환값이 존재하면 메인 쿼리를 실행하고 
        반환값이 없으면 메인 쿼리를 실행하지 않음 
*/

--IN 연산자
--이름인 Whalen 또는 Fay 사원과 같은 월급을 받는 모든 사원정보 출력
SELECT last_name, salary
FROM employees
WHERE salary IN ( SELECT salary
                  FROM employees
                  WHERE last_name IN ('Whalen','Fay') );
                  
 --ALL 연산자
 --직업이 IT_PROG인 사원의 최소 월급보다 적은 월급을 가진 사원정보 출력
SELECT last_name, department_id, salary
FROM employees
WHERE salary < ALL (SELECT salary
                    FROM employees
                    WHERE job_id = 'IT_PROG');
 
 --직업이 IT_PROG인 사원의 최대 월급보다 많은 월급을 받는 사원정보 출력
SELECT last_name, department_id, salary
FROM employees
WHERE salary > ALL (SELECT salary
                    FROM employees
                    WHERE job_id = 'IT_PROG');
 
 --ANY 연산자
 --직업이 IT_PROG인 사원의 최소 월급보다 많은 월급을 받는 사원정보 출력
SELECT last_name, department_id, salary
FROM employees
WHERE salary > ANY (SELECT salary
                    FROM employees
                    WHERE job_id = 'IT_PROG');
 
 --직업이 IT_PROG인 사원의 최대 월급보다 작은 월급을 받는 사원정보 출력
SELECT last_name, department_id, salary
FROM employees
WHERE salary < ANY (SELECT salary
                    FROM employees
                    WHERE job_id = 'IT_PROG');
 
 --EXIST 연산자
 --사원들 중에서 커미션을 받는 사원이 한 명이라도 있으면 모든 사원 정보를 출력
SELECT last_name, department_id, salary
FROM employees
WHERE EXISTS (SELECT employee_id
              FROM employees
              WHERE commission_pct IS NOT NULL);

 -다중 컬럼 서브쿼리

--부서별로 가장 많은 월급을 받는 사원정보를 출력
--pairwise 다중 컬럼 서브쿼리
SELECT last_name, department_id, salary
FROM employees
WHERE (department_id, salary) IN ( SELECT department_id, MAX(salary)
                                   FROM employees
                                   GROUP BY department_id )
ORDER BY 2;

 -인라인 뷰(in-line view)

=>FROM 절에서 사용된 서브쿼리

=>FROM절에서 참조하는 테이블의 크기가 클 경우에 필요한 행과 컬럼만으로

    구성된 집합을 재정의하여 쿼리문을 효율적으로 사용할 수 있는 장점이 있음(성능↑)

/*
문법
SELECT select_list
FROM ( 서브쿼리 ) alias
WHERE 조건식;
*/

--사원 테이블과 부서 테이블에서 부서별 월급 총합과 평균 
--그리고 부서별 인원수를 출력

--107개의 데이터를 가진 EMPLOYEES 테이블과 
--27개의 데이터를 가진 DEPARTMENTS 테이블 데이터가 조인에 참여
SELECT e.department_id , SUM(salary) 총합, AVG(salary) 평균, COUNT(*) 인원수
FROM employees e , departments d
WHERE e.department_id = d.department_id
GROUP BY e.department_id
ORDER BY 1;

--inline view 사용
--서브쿼리를 사용하지 않은 경우보다 적은 수의 데이터로 조인이 처리되어 효율적임
--서브쿼리를 사용한 경우에는 12개의 데이터를 가진 가상 테이블(별칭 e로 된 인라인 뷰)과 
--27개의 데이터를 가진 부서 테이블이 조인에 참여
SELECT e.department_id, 총합, 평균, 인원수
FROM ( SELECT department_id, SUM(salary) 합계, AVG(salary) 평균 , COUNT(*) 인원수
       FROM employees
       GROUP BY department_id ) e, 
     departments d
WHERE e.department_id = d.department_id
ORDER By 1;

---

나.DML(Data Manipulation Language)

=>데이터베이스의 테이블에 새로운 데이터를 저장(INSERT)하거나 삭제(DELETE) 또는

    수정 (UPDATAE) 및 병합(MERGE)할 때 사용하는 데이터 조작어

+TCL(Transaction Control Language) : 트랜잭션 처리어

 

 -단일행 INSERT문

/*
문법
INSERT INTO 테이블명 [(컬럼명,컬럼명2,...)]
VALUES ( 값, 값2, ... );
*/

--dept테이블에 새로운 부서정보 저장
--권장 : 컬럼 지정하여 저장
INSERT INTO dept( deptno, dname, loc )
VALUES ( 50 ,'개발','서울');

--컬럼명을 생략한 dept테이블 삽입
INSERT INTO dept
VALUES ( 60 ,'인사','경기');

--묵시적 null 저장
--NOT NULL 제약 조건이 설정된 컬럼인 경우 사용 불가
INSERT INTO dept(deptno, dname )
VALUES ( 70 ,'인사');

--명시적 null 저장
INSERT INTO dept(deptno, dname , loc )
VALUES ( 80 ,'인사', NULL );

--TCL
--DML 사용시 메모리에만 올라감. 다른 사람에게 적용하려면 TCL(commit)해야 함
commit;

 -복수행 INSERT 문

/*
문법
INSERT INTO 테이블명 [(컬럼명,컬럼명2,...)]
Subquery;
*/

--dept 테이블의 구조만 복사하여 mydept 생성
--NOT NULL 제외한 제약조건 복사X
--기본키 제약 조건X
CREATE TABLE mydept
AS
SELECT * FROM dept
WHERE 1=2;

INSERT INTO mydept
SELECT deptno,dname,loc
FROM dept;

--기본키 제약 조건 없기 때문에 중복 추가가능
INSERT INTO mydept
SELECT deptno,dname,loc
FROM dept;

 -다중테이블 다중행 INSERT 문

/*
문법
INSERT ALL
 [WHEN 조건식 THEN]
 INTO 테이블1 VALUES ( 컬럼명,컬럼명2,...,컬럼명n)
 [WHEN 조건식2 THEN]
 INTO 테이블2 VALUES ( 컬럼명,컬럼명2,...,컬럼명n)
Subquery;
*/

--사원테이블 emp에서 구조만 복사한 myemp_hire테이블 생성
CREATE TABLE myemp_hire
AS
SELECT empno,ename,hiredate,sal
FROM emp
WHERE 1=2;

--사원테이블 emp에서 구조만 복사한 myemp_mgr 생성
CREATE TABLE myemp_mgr
AS
SELECT empno,ename,mgr
FROM emp
WHERE 1=2;

--서브쿼리를 사용
--myemp_hire 테이블에는 사원번호,이름,입사일,월급 데이터를 저장
--myemp_mgr 테이블에는 사원번호,이름,관리자번호 데이터를 저장
INSERT ALL
INTO myemp_hire VALUES ( empno,ename,hiredate,sal )
INTO myemp_mgr VALUES ( empno,ename,mgr )
SELECT empno,ename,hiredate,sal,mgr
FROM emp;

--조건 INSERT ALL
--테이블 2개 생성
CREATE TABLE myemp_hire2
AS
SELECT empno,ename,hiredate,sal
FROM emp
WHERE 1=2;

CREATE TABLE myemp_mgr2
AS
SELECT empno,ename,mgr
FROM emp
WHERE 1=2;

--myemp_hire2 : 월급이 3000 보다 큰 사원만 저장 
--myemp_mgr2 : 관리자 번호가 7698인 사원만 저장
INSERT ALL
WHEN sal > 3000 THEN
 INTO myemp_hire2 VALUES ( empno,ename,hiredate,sal )
WHEN mgr = 7698 THEN
 INTO myemp_mgr2 VALUES ( empno,ename,mgr )
SELECT empno,ename,hiredate,sal,mgr
FROM emp;

--조건 INSERT FIRST 문
--WHEN절에 지정된 조건이 중복되는 경우에 처음 조건에 일치하는
--테이블에만 저장되고 이후에는 조건이 일치해도 테이블에 저장되지 않는 방법
--INSERT ALL -> INSERT FIRST로 변경

INSERT FIRST
WHEN sal = 800 THEN
 INTO table1 VALUES ( empno,ename,hiredate,sal )
WHEN sal < 2500 THEN
 INTO table2 VALUES ( empno,ename,mgr )
SELECT empno,ename,hiredate,sal,mgr
FROM emp;

 -UPDATE 문

=>테이블에 저장된 데이터를 수정하기 위해서 사용

/*
문법
UPDATE 테이블명
SET 컬럼명=변경할값[, 컬럼명1=변경할값]
[WHERE 조건식];
*/

--50부서의 부서명을 개발에서 영업으로 변경/지역을 서울에서 경기로 변경
UPDATE mydept
SET dname='영업',loc='경기' WHERE deptno = 50;

--서브쿼리 이용
--mydept테이블의 부서번호가 60인 사원의 부서명을 
--dept테이블의 부서번호가 10인 부서의 부서명으로 수정
--부서위치는 dept테이블의 부서번호가 20인 부서위치로 수정
UPDATE mydept
SET dname = ( SELECT dname
               FROM dept
               WHERE deptno = 10)
   ,loc = ( SELECT loc
            FROM dept
            WHERE deptno=20)
WHERE deptno = 60;

 -DELETE 문

/*
문법
DELETE FROM 테이블명
[WHERE 조건식];
*/

--mydept테이블에서 부서번호가 50인 행을 삭제
DELETE FROM mydept
WHERE deptno = 50;

--서브쿼리를 이용한 DELETE 문
--DEPT테이블에서 부서번호가 20인 부서위치와 
--동일한 위치에 해당하는 행을 mydept테이블에서 삭제
DELETE
FROM mydept
WHERE loc = (SELECT loc
             FROM dept
             WHERE deptno = 20);

---

다.트랜잭션

=>데이터베이스의 논리적인 작업 단위로서 분리될 수 없는 한 개 이상의 데이터베이스 조작

=>개별적인 DML작업을 하나의 작업처럼 처리하는 개념(무결성 보장)

 

 -TCL(Transaction Control Language)명령어

→COMMIT : DML문에 의해서 실행되었으나 실제로 저장되지 않은 모든 데이터를

                     데이터베이스에 저장 하고 현재의 트랜잭션을 종료하는 명령어

→ROLLBACK : 저장되지 않은 모든 데이터의 변경 사항을 취소하고

                          현재의 트랜잭션을 종료하는 명령어

 

※주의 : 트랜잭션을 발생시키고 명시적으로 종료시키지 않으면 다른 사용자 무한 대기하게 됨