[SQLD] 2과목 2장 SQL 활용 정리

1. 표준 조인

[1] 일반 집합 연산자

1. UNION : 합집합(중복 행은 1개로 처리)

2. UNION ALL : 합집합(중복 행도 표시)

3. INTERSECT : 교집합(INTERSECTION)

4. MINUS/EXCEPT : 차집합(DIFFERENCE)

5. CROSS JOIN : 곱집합(PRODUCT)

 

[2] 순수 관계 연산자 : 관계형 DB를 새롭게 구현

1. SELECT -> WHERE

2. PROJECT -> SELECT

3. NATRUAL JOIN -> 다양한 JOIN

4. DIVIDE -> 현재 사용 x

{a, x}{a, y}{a, z} divdie {x, z} = {a}

 

[3] FROM 절 JOIN 형태

1. INNER JOIN

2. NATURAL JOIN

3. USING 조건절

4. ON 조건절

5. CROSS JOIN

6. OUTER JOIN

 

[4] INNER JOIN (= EQUAL JOIN)

- JOIN 조건에서 동일한 값이 있는 행만 반환, USING이나 ON 절을 필수적으로 사용.

- WHERE절에서 사용하던 JOIN의 DEFAULT 옵션으로 JOIN 조건에서 동일 값이 있는 칼럼은 행을 반환한다.

SELECT 테이블1.칼럼1, 테이블2.칼럼1 FROM 테이블1, 테이블2
WHERE 테이블1.컬럼1 = 테이블2.컬럼1;

SELECT 테이블1.칼럼1, 테이블2.칼럼1 FROM 테이블1 
INNER JOIN 테이블2 ON 테이블1.컬럼1 = 테이블2.컬럼1;

 

[5] NATURAL JOIN 

- 두 테이블 간의 동일한 이름을 갖는 모든 칼럼들에 대해 동등조인(EQUI JOIN) 수행한다.

- NATURAL JOIN이 명시되면 추가로 USING, ON, WHERE 절에서 JOIN 조건을 정의할 수 없다, SQL Sever는 지원 안 함.

- 칼럼에 별칭 (Alias)과 테이블명을 붙일 수 없다.

SELECT * FROM 테이블1 NATURAL JOIN 테이블2;
SELECT * FROM 테이블1 INNER JOIN 테이블2 ON 테이블1.칼럼1 = 테이블2.칼럼1;

 

[6] USING 조건절

- 같은 이름을 가진 칼럼들 중에서 원하는 칼럼에 대해서만 선택적으로 EQUI JOIN을 할 수 있다.

- JOIN 칼럼에 대해서 ALIAS나 테이블 이름과 같은 접두사를 붙일 수 없다, SQL Server 지원 안 함.

 

[7] ON 조건절

- ON 조건절과 WHERE 조건절을 분리하여 이해가 쉬우며, 칼럼명이 다르더라도 JOIN 조건을 사용할 수 있는 장점.

- ALIAS나 테이블명 반드시 사용.

SELECT 테이블1.C1, 테이블1.C2, 테이블2.C1, 테이블2.C2 FROM 테이블1
JOIN 테이블2 ON (테이블1.C1 = 테이블2.C2) WHERE 테이블1.C1 = 100;

SELECT 테이블1.C1, 테이블1.C2, 테이블2.C1, 테이블2.C2 FROM 테이블1
JOIN 테이블2 ON (테이블1.C1 = 테이블2.C2) AND 테이블1.C1 = 100;

- USING 조건절을 이용한 JOIN에서는 JOIN 칼럼에 대하여 별칭(Alias)과 테이블 명 설정 시 오류 발생한다.

- ON 조건절을 이용한 JOIN에서는 JOIN 칼럼에 대하여 별칭(Alias)과 테이블 명을 설정하여 명확하게 지정해줘야 한다.

 

[8] CROSS JOIN

- 테이블 간 JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합으로 CARTESIAN PRODUCT 또는 CROSS PRODUCT와 같이 불린다.

- 양쪽 집합의 M*N건의 데이터 조합이 발생한다.

 

[9] OUTER JOIN

- JOIN 조건에서 동일한 값이 없는 행도 반환 가능.

- USING이나 ON 조건절 반드시 사용해야 함.

- 칼럼 뒤에 (+) 표시.

 

[10] LEFT OUTER JOIN

- 조인 수행 시 먼저 표기된 좌측 테이블에 해당하는 데이터를 읽은 후, 나중 표기된 우측 테이블에서 JOIN 대상 데이터를 읽어 온다.

- 우측 값에서 같은 값이 없는 경우 NULL 값으로 채운다.

 

[11] RIGHT OUTER JOIN

- LEFT OUTER JOIN의 반대.

 

[12] FULL OUTER JOIN

- 조인 수행 시 좌측, 우측 테이블의 모든 데이터를 읽어 JOIN 하여 결과를 생성한다.

- UNION ALL이 아닌 UNION 기능과 동일하여 중복되는 데이터는 삭제한다.

 

2. 집합 연산자(SET OPERATION)

[1] 집합 연산자 

- 두 개 이상의 테이블에서 조인을 사용하지 않고 연관된 데이터를 조회할 때 사용한다.

- SELECT 절의 칼럼 수가 동일하고 SELECT 절의 동일 위치에 존재하는 칼럼의 데이터 타입이 상호 호환할 때 사용 가능하다.

- 2개 이상의 질의 결과를 하나로 만들어 주는 역할을 수행한다.

 

[2] UNION

- 여러 개의 SQL문의 결과에 대한 합집합으로 결과에서 모든 중복된 행은 하나의 행으로 만든다.

- 작성된 순서대로 칼럼 값이 출력된다.

SELECT PLAYER_NAME 선수명, BACK_NO 백넘버
FROM PLAYER
WHERE TEAM_ID = 'K02'
UNION
SELECT PLAYER_NAME 선수명, BACK_NO 백넘버
FROM PLAYER
WHERE TEAM_ID = 'K07'
ORDER BY;

[3] UNION ALL

- 여러 개의 SQL문의 결과에 대한 합집합으로 중복된 행도 그대로 결과로 표시된다. 단순히 결과만 합쳐놓은 것.

- 일반적으로 여러 질의 결과가 상호 배타적일 때 많이 사용한다.

- 개별 SQL문의 결과가 서로 중복되지 않은 경우, UNION의 결과와 동일하다.(정렬 순서는 차이 있을 수도 있음)

 

[4] INTERSECT

- 여러 개의 SQL문의 결과에 대한 교집합이다.

- 중복된 행은 하나의 행으로 만든다.

- 교집합은 EXIST 또는 IN 서브 쿼리를 이용한 SQL 문을 사용하여 출력할 수 있다.

 

[5] EXCEPT

- 앞의 SQL문의 결과에서 뒤의 SQL문의 결과에 대한 차집합이다.

- 중복된 행은 하나의 행으로 만든다.(일부 DBMS는 MINUS를 사용)

- 차집합은 <> 연산자, NOT EXISTS, NOT IN을 사용하여 출력할 수 있다.

 

3. 계층형 질의와 셀프 조인

[1] 계층형 질의

- 테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해 사용

 

[2] 계층형 데이터

- 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 말함

 

- SATRT WITH : 계층 구조 전개의 시작 위치 지정

- CONNECT BY : 다음에 전개될 자식 데이터 지정

- PRIOR : CONNECT BY 절에 사용되며, 현재 읽은 칼럼을 지정한다.

- PRIOR 자식 = 부모 형태를 사용하면 계층구조에서 부모 데이터에서 자식 데이터(부모->자식) 방향으로 전개하는 순방향 전개를 한다.

- 반대는 역방향 전개

- NOCYCLE : 동일한 데이터가 전개되지 않음

- ORDER SIBLINGS BY : 형제 노드 간의 정렬 수행

- WHERE : 모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출한다.(필터링)

 

- LEVEL : 루트 데이터이면 1, 그 하위 데이터면 2, 리프 데이터까지 1씩 증가

- CONNECT_BY_ISLEAF : 해당 데이터가 리프 데이터면 1, 그렇지 않으면 0

- CONNECT_BY_ISCYCLE : 해당 데이터가 조상이면 1, 아니면 0 (CYCLE 옵션 사용했을 시만 사용 가능)

 

- SYS_CONNECT_BY_PATH : 루트 데이터부터 현재 전개할 데이터까지의 경로를 표시한다.

- CONNECT_BY_ROOT : 현재 전개할 데이터의 루트 데이터를 표시한다. 단항 연산자이다.

 

[3] 셀프 조인

- 동일 테이블 사이의 조인, FROM 절에 동일 테이블이 2번 이상 나타난다.

- 반드시 테이블 별칭을 사용해야 함.

 

4. 서브쿼리

[1] 서브 쿼리

- 하나의 SQL문 안에 포함되어 있는 또 다른 SQL 문을 의미한다.

- 메인쿼리가 서브쿼리를 포함하고 있는 종속적인 관계되어 있다.

 

[2] 서브 쿼리 사용 시 주의 사항

1. 서브쿼리를 괄호로 감싸서 사용한다.

2. 서브쿼리는 단일 행 또는 복수 행 비교 연산자와 함께 사용 가능하다.

3. 단일 행 비교 연산자는 서브쿼리의 결과가 반드시 1건 이하여야 하고 복수 행 비교 연산자는 결과 건수와 상관없다.

4. 서브쿼리에서는 ORDER BY를 사용하지 못한다.

5. SELECT, FROM, WHERE, HAVING, ORDER BY, INSERT-VALUES, UPDATE-SET 절에 사용 가능

 

[3] 반환되는 데이터의 형태에 따른 서브쿼리 분류

- 단일 행 비교 연산자 : =,<,>, <> 등

- 다중 행 비교 연산자 : IN, ALL, ANY, SOME 등

 

[4] 스칼라 서브쿼리

- 한 행, 한 칼럼만을 반환하는 서브쿼리

- 메인쿼리의 결과 건 수만큼 반복 수행된다.

 

[5] 인라인 뷰

- FROM절에 사용되는 서브쿼리로 SQL문이 실행될 때만 임시적으로 생성되는 동적인 뷰이다.

- 테이블 명이 올 수 있는 곳에 사용가능. ORDER BY 사용 가능.

- FROM절에는 반드시 테이블명이 오게 되어있어 FROM절에 위치한 서브쿼리의 결과는 마치 실행 시에 동적으로 생성된 테이블인 것처럼 사용할 수 있다.

- 임시적인 뷰이므로 데이터베이스에 해당 정보가 저장되지 않는다.

 

[6] 뷰

- 테이블은 실제로 데이터를 가지고 있는 반면, 뷰는 실제 데이터를 가지고 있지 않다.

- 가상 테이블이라고도 함

 

[7] 뷰 사용 장점

1. 독립성 : 테이블 구조가 변경되어도 뷰를 사용하는 응용 플그램은 변경하지 않아도 된다.

2. 편리성 : 복잡한 질의를 뷰로 생성함으로써 관련 질의를 단순하게 작성할 수 있다.

3. 보안성 : 직원의 급여정보와 같이 숨기고 싶은 정보가 존재할 때 사용

CREATE VIEW V_PLAYER_TEAM AS
DROP VIEW V_PLAYER_TEAM;

 

5. 그룹 함수

[1] ROLLUP

- Subtotal을 생성하기 위해 사용, Grouping Columns의 수를 N이라고 했을 때 N+1 Level의 Subtotal이 생성된다.

- ROLLUP의 인수는 계층 구조이므로 인수 순서가 바뀌면 수행 결과도 바꾸는 특징을 가진다. 인수 순서에 주의.

- 칼럼이 하나씩 붙으면서 생성되는 조합을 생성한다.

 

[2] GROUPING

- Subtotal의 total을 생성

 

[3] CUBE

- 결합 가능한 모든 값에 대하여 다차원 집계를 생성, ROLLUP에 비해 시스템에 부하 심함

- 가능한 모든 조합이므로 2n 개의 소계 조합을 생성한다.

 

[4] GROUPING SETS

- 인수들에 대한 개별 집계를 구할 수 있다, 다양한 소계 집합 생성 가능

- GROUPING SETS() 괄호 내부 안에 사용자가 원하는 집계 칼럼 조합을 직접 작성하는 방식으로 훨씬 직관적으로 원하는 소계를 구할 수 있다.

- ROULLUP함수와 달리 괄호 안의 입력하는 순서가 중요하지 않다.

- 칼럼이 나열된 것만 출력된다.

 

6. 윈도우 함수

[1] 윈도우 함수

- 행과 행간의 관계를 정의하거나 행과 행간을 비교, 연산하는 함수

- 다른 함수와 달리 중첩하여 사용할 수 없지만 서브 쿼리에서는 사용할 수 있다.

 

[2] 순위 함수

- RANK : 특정 항목에 대한 순위를 구하는 함수, 동일한 값에 대해서는 동일한 순위를 부여(1,2,2,4)

- DENSE_RANK : 동일한 순위를 하나의 등수로 간주(1,2,2,3)

- ROW_NUMBER : 동일한 값이라도 고유한 순위 부여

 

[3] 윈도우 집계 함수

- SUM : 파티션별 윈도우의 합 구할 수 있다.

ex) 같은 매니저를 두고 있는 사원들의 월급 합

- MAX, MIN : 파티션별 윈도우의 최대, 최소 값을 구할 수 있다.

ex) 같은 매니저를 두고 있는 사원들 중 최대 값

- AVG : 원하는 조건에 맞는 데이터에 대한 통계 값

ex) 같은 매니저 내에서 앞의 사번과 뒤의 사번의 평균

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

(현재 행을 기준으로 파티션 내에서 앞의 1건, 현재행, 뒤의 1건을 범위로 지정)

- COUNT : 조건에 맞는 데이터에 대한 통계 값

ex) 본인의 급여보다 50 이하가 적거나 150 이하로 많은 급여를 받는 인원수

 

[4] 행 순서 함수(SQL Server는 지원 x)

- FIRST_VALUE : 파티션별 윈도우에서 가장 먼저 나온 값을 구한다.

- LAST_VALUE : 파티션별 윈도우에서 가장 나중에 나온 값을 구한다.

- LAG : 파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있다.

- LEAD : 파티션별 윈도우에서 이후 몇 번째 행의 값을 가져올 수 있다.

 

[5] 비율 함수

- RATIO_TO_REPORT : 파티션 내 전체 SUM값에 대한 행별 칼럼 값의 백분율을 소수점으로 구할 수 있다. 결과 값은 0보다 크고 1보다 작거나 같다.

- PERCENT_RANK : 파티션별 윈도우에서 제일 먼저 나오는 것을 0, 제일 늦게 나오는 것을 1로 하여 행의 순서별 백분율을 구한다. 0>=,<=1

- CUME_DIST : 현재 행보다 작거나 같은 건수에 대한 누적백분율을 구한다. >0, <=1

- NTILE : 파티션별 전체 건수를 인수 값으로 N등분한 결과를 구할 수 있다.

 

[6] ROW, RANGE

- ROWS : 행의 수를 선택할 때 사용한다.

- RANGE : 값의 범위를 선택할 때 사용한다.

 

7. DCL

[1] DCL

- 유저 생성하고 권한을 제어할 수 있는 명령어

 

[2] Oracle과 SQL Server의 사용자 아키텍처 차이

- Oracle : 유저를 통해 DB에 접속을 하는 형태, ID와 PW 방식으로 인스턴스에 접속을 하고 그에 해당하는 스키마에 오브젝트 생성 등의 권한을 부여받게 됨

- SQL Server : 인스턴스에 접속하기 위해 로그인이라는 것을 생성하게 되며, 인스턴스 내에 존재하는 다수의 DB에 연결하여 작업하기 위해 유저를 생성한 후 로그인과 유저를 매핑해 주어야 한다. Windows 인증 방식과 혼합 모드 방식이 존재함

 

[3] 시스템 권한

- 사용자가 SQL 문을 실행하기 위해 필요한 적절한 권한

 

[4] 유저 생성과 시스템 권한 부여

- GRANT : 권한 부여

- REVOKE : 권한 취소

GRANT CREATE USER TO SCOTT;
CONN SCOTT/TIGER(ID/PW)
CREATE USER TOM IDENTIFIED BY KOREA7;
GRANT CREATE SESSION TO TOM;
GRANT CREATE TABLE TO TOM;
REVOKE CREATE TABLE FROM TOM;

- 모든 유저는 각각 자신이 생성한 테이블 외에 다른 유저의 테이블에 접근하려면 해당 테이블에 대한 오브젝트 권한을 소유자로부터 부여받아야 한다.

 

[5] ROLE

- 유저에게 알맞은 권한들을 한 번에 부여하기 위해 사용하는 것

CREATE ROLE LOGIN_TABLE;
GRANT CREATE TABLE TO LOGIN_TABLE;

 

[6] CASCADE

- 하위 오브젝트까지 삭제

DROP USER TOM CASCADE;

8. 절차형 SQL

[1] 절차형 SQL

- SQL문의 연속적인 실행이나 조건에 따른 분기처리를 이용하여 특정 기능을 수행하는 저장 모듈을 생성할 수 있다.

- Procedure, User Defined Function, Trigger 등이 있다.

 

[2] 저장 모듈

- PL/SQL 문장을 DB 서버에 저장하여 사용자와 애플리케이션 사이에서 공유할 수 있도록 만든 일종의 SQL 컴포넌트 프로그램.

- 독립적으로 실행되거나 다른 프로그램으로부터 실행될 수 있는 완전한 실행 프로그램이다.

 

[3] PL/SQL 특징

1. Block 구조로 되어있어 각 기능별로 모듈화 가능하다.

2. 변수, 상수 등을 선언하여 SQL 문장 간 값을 교환가능 하다.

3. IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 한다.

4. DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있다.

5. PL/SQL은 Oracle에 내장되어 있으므로 호환성이 좋다.

6. 응용 프로그램의 성능을 향상한다.

7. Block 단위로 처리 -> 통신량을 줄일 수 있다.

 

- DECLARE : BEGIN~END 절에서 사용될 변수와 인수에 대한 정의 및 데이터 타입 선언부

- BEGIN~END : 개발자가 처리하고자 하는 SQL문과 여러 가지 비교문, 제어문을 이용 필요한 로직 처리

- EXCEPTION : BEGIN~END 절에서 실행되는 SQL문이 실행될 때 에러가 발생하면 그 에러를 어떻게 처리할지 정의하는 예외 처리부

CREATE Procedure Procedure_name
REPLACE Procedure Procedure_name
DROP Procedure Procedure_name
/ <- 컴파일하라는 명령어

- T-SQL : 근본적으로 SQL Server를 제어하는 언어

CREATE Procedure schema_NAME.Procedure_name

- Trigger : 특정한 테이블에 INSERT, UPDATE, DELETE와 같은 DML문이 수행되었을 때, DB에서 자동으로 동작하도록 작성된 프로그램, 사용자 호출이 아닌 DB 자동 수행.

CREATE Trigger Trigger_name

[4] 프로시저와 트리거의 차이점

- 프로시저는 BEGIN~END 절 내에 COMMIT, ROLLBACK과 같은 트랜잭션 종료 명령어 사용가능하다.

- DB 트리거는 BEGIN~END 절 내에 사용 불가