11/26 복습복습 금토일..[3]

/*

#서브 쿼리

 

 

-서브 쿼리의 사용 방법은 ()안에 명시합니다.

-서브쿼리절의 리턴행이 1줄 이하여야 합니다.

-서브쿼리 절에는 비교할 대상이 반드시 한 개 들어가야 합니다.

-해석할 때는 서브쿼리 절을 먼저 해석하면 됩니다.

*/

 

--'Nancy'의 급여보다 급여가 많은 사람을 검색하는 문장

SELECT salary FROM employees WHERE first_name = 'Nancy';

SELECT first_name FROM employees WHERE salary>12008;

SELECT * FROM employees

WHERE salary > (SELECT salary

FROM employees

WHERE first_name = 'Nancy');

 

--다음 문장은 서브쿼리의 리턴 행이 여러 개라서 사용할 수 없습니다.

--이런 경우에는 다중행 연산자를 사용합니다.

SELECT * FROM employees

WHERE job_id = (SELECT job_id

FROM employees

WHERE job_id = 'IT_PROG'); --에러!

 

--다중행 연산자

--IN: 목록의 어떤 값과 같은지 확인합니다.

SELECT * FROM employees

WHERE job_id IN (SELECT job_id

FROM employees

WHERE job_id = 'IT_PROG');

 

--first_name이 David인 사람 중 가장 작은 값보다 급여가 큰 사람을 조회합니다.

SELECT salary FROM employees WHERE first_name = 'David';

--ANY: 값을 서브쿼리에 의해 리턴된 각각의 값과 비교합니다. 하나라도 만족하면 됩니다.

SELECT *

FROM employees

WHERE salary > ANY (SELECT salary

FROM employees

WHERE first_name = 'David');

--ALL: 값을 서브쿼리에 의해 리턴된 모든 값과 비교해서 모두 만족해야 합니다.

SELECT *

FROM employees

WHERE salary > ALL (SELECT salary

FROM employees

WHERE first_name = 'David');

--스칼라 서브쿼리

--SELECT 구문에 서브쿼리가 오는 것, LEFT OUTER JOIN과 같은 결과입니다.

SELECT e.first_name, d.department_name

FROM employees e

LEFT JOIN departments d

ON e.department_id = d.department_id

ORDER BY first_name ASC;

SELECT

first_name,

(SELECT department_name

FROM departmentS d

WHERE d.department_id = e.department_id) AS department_name

FROM employees e

ORDER BY first_name ASC;

/*

-스칼라 서브쿼리가 조인보다 좋은 경우!

:함수처럼 한 레코드당 정확히 하나의 값만을 리턴할 때

 

-조인이 서브쿼리보다 좋은 경우!

: 조회할 데이터가 대용량인 경우, 해당 데이터가 수정이 빈번한 경우

*/

--각 부서의 매니저장의 읆

--LEFT JOIN

SELECT

d.*, e.first_name

FROM departments d

LEFT JOIN employees e

ON d.manager_id = e.employee_id

ORDER BY d.manager_id ASC;

--스칼라

SELECT

d.*,

(SELECT first_name

FROM employees e

WHERE e.employee_id = d.manager_id) AS manager_name

FROM departments d

ORDER BY d.manager_id ASC;

--각 부서별 사원수 뽑기(departments의 모든 것, 사원수를 별칭 지어서 출력하세요.)

SELECT

d.department_name,

(SELECT COUNT(*) FROM employees e

WHERE e.department_id = d.department_id

GROUP BY department_id) AS 사원수

FROM departments d;

 

 

-- 인라인 뷰 (FROM 구문에 서브쿼리가 오는 것)

-- 순번을 정해놓은 조회 자료를 범위를 지정해서 가지고 오는 경우.

-- salary로 정렬을 진행하면서 ROWNUM을 붙이면

-- ROWNUM이 정렬이 되지 않는 사태가 발생합니다.

-- 이유: (ROWNUM을 붙이고 정렬이 진행되기 때문. ORDER BY는 항상 마지막에 진행.)

-- 해결: 정렬을 미리 해 놓은 자료에 ROWNUM을 붙여야 할 것 같아요.

SELECT ROWNUM as rn, employee_id, first_name, salary

FROM employees

ORDER BY salary DESC;

-- ROWNUM을 붙이고 나서 범위를 지정해서 조회하려고 하는데,

-- 범위 지정도 불가능하고, 지목할 수 없다는 문제가 발생합니다.

-- 이유: WHERE절부터 먼저 실행하고 나서 ROWNUM이 SELECT 되기 때문에

-- 해결: ROWNUM까지 붙여 놓고 다시 한 번 자료를 SELECT해서 범위를 지정해야 합니다.

-- 가장 안쪽 SELECT절에서 필요한 테이블 형식을 생성합니다.

-- 바깥쪽 SELECT 절에서 ROWNUM을 붙여서 다시 조회합니다.

-- 가장 바깥쪽 SELECT절에서 이미 붙어있는 ROWNUM의 범위를 지정해서 조회합니다.

-- SQL 실행 순서

-- FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

SELECT * FROM

(

SELECT ROWNUM as rn, tbl.*

FROM

(

SELECT first_name, salary

FROM employees

ORDER BY salary DESC

) tbl

)

WHERE rn <= 20 AND rn > 10;

 

--INSERT

--테이블 구조 확인 DESCRIBE

DESC departments;

--INSERT의 첫번째 방법 (모든 컬럼 데이터를 한번에 지정합니다.)

--타입에 맞는 값을 지정해서 삽입해야 합니다.

--문자나 숫자 타입으로 지정된 곳에 타입이 맞지 않는 데이터를 집어 넣을 때는

--자동 형 변환을 시도합니다.

INSERT INTO departments

VALUES ('300', null , null, 1700);

SELECT * FROM departments;

ROLLBACK; --실행 시점을 다시 뒤로 되돌리는 키워드입니다.

--INSERT의 두 번째 방법 (직접 컬럼을 지정하고 저장합니다.)

INSERT INTO departments

(department_id, department_name, location_id)

VALUES (280, '개발자', 1700);

INSERT INTO departments

(department_id, department_name, location_id)

VALUES (290, '디자이너', 1700);

INSERT INTO departments

(department_id, department_name, location_id)

VALUES (300, 'DB관리자', 1800);

INSERT INTO departments

(department_id, department_name, location_id)

VALUES (310, '데이터분석가', 1800);

INSERT INTO departments

VALUES (320, '퍼블리셔', 200, 1800);

INSERT INTO departments

VALUES (330, '서버관리자', 200, 1800);

SELECT * FROM departments;

ROLLBACK;

--사본 테이블 생성

CREATE TABLE managers AS

(

SELECT employee_id, first_name, job_id, salary, hire_date

FROM employees

WHERE 1 = 2 --FALSE

);

SELECT * FROM managers;

--INSERT(서브쿼리)

INSERT INTO managers

(

SELECT employee_id, first_name, job_id, salary, hire_date

FROM employees

);

--UPDATE

CREATE TABLE emps AS (SELECT * FROM employees WHERE 1=1); --WHERE 절 안 쓰면 자동 TRUE

SELECT * FROM emps;

/*CTAS를 사용하면 제약 조건은 NOT NULL을 제외하고는 복사되지 않습니다.

제약조건은 업무 규칙을 지키는 데이터만 저장하고, 그렇지 않은 것들이

DB에 저장되는 것을 방지하는 목적으로 사용합니다.*/

ALTER TABLE emps

ADD (CONSTRAINT emps_emp_id_pk PRIMARY KEY (employee_id),

CONSTRAINT emps_manager_fk FOREIGN KEY (manager_id)

REFERENCES emps(employee_id));

-- 제약조건 삭제

ALTER TABLE emps DROP CONSTRAINT emps_manager_fk;

ALTER TABLE emps DROP CONSTRAINT emps_emp_id_pk;

--UPDATE를 진행할 때는 누구를 수정할지 지목해야 합니다.

--그렇지 않으면 수정 대상이 테이블 전체로 지목됩니다.

UPDATE emps SET salary = 30000;

SELECT * FROM emps;

ROLLBACK;

UPDATE emps SET salary = 30000

WHERE employee_id = 100;

UPDATE emps SET salary = salary + (salary * 0.1)

WHERE employee_id = 100;

SELECT * FROM emps;

UPDATE emps SET manager_id = 100

WHERE employee_id = 100;

SELECT * FROM emps;

UPDATE emps SET phone_number = '515.123.4566', manager_id = 102

WHERE employee_id = 100;

SELECT * FROM emps;

--UPDATE(서브쿼리)

--employee_id 100인 사람의 select을 employee_id가 101에 집어넣겠다

UPDATE emps

SET(job_id, salary, manager_id) =

(SELECT job_id, salary, manager_id

FROM emps

WHERE employee_id = 100)

WHERE employee_id = 101;

SELECT * FROM emps;

ROLLBACK;

--DELETE

DELETE FROM emps

WHERE employee_id = 103;

SELECT * FROM emps

WHERE employee_id = 103;

--DELETE 서브쿼리

DELETE FROM emps

WHERE department_id = (SELECT department_id FROM departments

WHERE department_id = 100);

 

SELECT * FROM emps;

DELETE FROM emps

WHERE department_id = (SELECT department_id FROM departments

WHERE department_name = 'IT');

 

DELETE FROM departments WHERE department_id = 50;

 

추후에는 UPDATE를 사용해야 할 것을 자꾸 INSERT로 사용해서 오류나는 경우가 많다고도 알려주셨다.

처음에는 엥? UPDATE와 INSERT가 이렇게 다른데? 라고 생각했지만,

문제를 볼수록 UPDATE보다 INSERT가 더 접근성이 좋아 보여서 그런지 나 역시도 헷갈렸다.

 

--MERGE: 테이블 병합

/*

UPDATE와 INSERT를 한번에 처리합니다.

 

한 테이블에 해당하는 데이터가 있다면 UPDATE, 없다면 INSERT로 처리합니다.

만약 MERGE가 없었다면 해당 데이터의 존재 유무를 일일히 확인하고,

IF문을 사용해서 데이터가 있다면 UPDATE, 없다면 ELSE문을 사용해서

INSERT를 하라고 일일히 얘기해야 하는데, MERGE를 통해 쉽게 처리가 가능합니다.*/

CREATE TABLE emps_it AS(SELECT * FROM employees WHERE 1=2);

SELECT * FROM emps_it;

INSERT INTO emps_it

(employee_id, first_name, last_name, email, hire_date, job_id)

VALUES(105, '데이비드', '김', 'DAVIDKIM', '06/03/04', 'IT_PROG');

MERGE INTO emps_it a -- (MERGE를 할 타겟 테이블)

USING -- 병합시킬 데이터

(SELECT * FROM employees WHERE job_id = 'IT_PROG') b --조인구문

ON --병합시킬 데이터의 연결 조건

(a.employee_id = b.employee_id) --조인조건

WHEN MATCHED THEN --조건에 일치할 경우 타겟 테이블에 실행합니다.

UPDATE SET

a.phone_number = b.phone_number,

a.hire_date = b.hire_date,

a.salary = b.salary,

a.commission_pct = b.commission_pct,

a.manager_id = b.manager_id,

a.department_id = b.department_id

WHEN NOT MATCHED THEN --조건에 일치하지 않는 경우 타겟테이블에 실행합니다.

INSERT /*속성(컬럼)*/VALUES

(b.employee_id, b.first_name, b.last_name,

b.email, b.phone_number, b.hire_date, b.job_id,

b.salary, b.commission_pct, b.manager_id, b.department_id)

--------------------------------------------------------------------------

INSERT INTO emps_it

(employee_id, first_name, last_name, email, hire_date, job_id)

VALUES(102, '렉스', '박', 'LEXPARK', '01/04/06', 'AD_VP');

INSERT INTO emps_it

(employee_id, first_name, last_name, email, hire_date, job_id)

VALUES(101, '니나', '최', 'NINA', '20/04/06', 'AD_VP');

INSERT INTO emps_it

(employee_id, first_name, last_name, email, hire_date, job_id)

VALUES(103, '흥민', '손', 'HMSON', '20/04/06', 'AD_VP');

/*employees 테이블을 매번 수정되는 테이블이라고 가정합니다.

기존의 데이터는 email, phone, salary, commission, man_id, dept_id는

업데이트 하도록 처리합니다.

새로 유입된 데이터는 그대로 추가.*/

MERGE INTO emps_it a

USING

(SELECT * FROM employees) b

ON

(a.employee_id = b.employee_id)

WHEN MATCHED THEN

UPDATE SET

a.email = b.email,

a.phone_number = b.phone_number,

a.salary = b.salary,

a.commission_pct = b.commission_pct,

a.manager_id = b.manager_id,

a.department_id = b.department_id

WHEN NOT MATCHED THEN

INSERT VALUES

(b.employee_id, b.first_name, b.last_name,

b.email, b.phone_number, b.hire_date, b.job_id,

b.salary, b.commission_pct, b.manager_id, b.department_id);

SELECT * FROM emps_it

ORDER BY employee_id ASC;

 

 

여기까지 배우고 수업 종료! merge에 대해서는 월요일에 더 수업하신다고 했다. 남은 주말에는 자바 복습과 오라클 연습 문제를 풀어야겠다.