[SQLD] 과목 2 | 제 2장 SQL 활용

1절 표준 조인

STANDARD SQL 개요

국내뿐만 아니라 전 세계적으로 많이 사용되고 있는 관계형 데이터베이스의 경우 오브젝트 개념을 포함한 여러 새로운 기능들이 꾸준히 개발되고 있으며, 현재 기업형 DBMS는 순수 관계형 데이터베이스가 아닌 객체 지원 기능이 포함된 객체관계형(Object Relational) 데이터베이스를 대부분 사용하고 있다.

 

1.   SQL에서의 연산

	일반 집합 연산자	현재 SQL
집합 연산	UNION	UNION	합집합 (공통 교집합 중복 없애기) 1. UNION ALL 기능이 추가되었는데, 특별한 요구 사항이 없다면 공통집합을 중복해서 그대로 보여 주기 때문에 정렬 작업이 일어나지 않는 장점을 가짐 2. 만일 UNION과 UNION ALL의 출력 결과가 같다면, 응답 속도 향상이나 자원 효율화 측면에서 데이터 정렬 작업이 발생하지 않는 UNION ALL을 사용하는 것을 권고
	INTERSECTION	INTERSECTION	교집합 (공통집합 추출)
	DIFFERENCE	MINUS (오라클) EXCEPT (SQL Server)	차집합 (공통집합 제외)
	PRODUCT	CROSS JOIN	곱집합 (JOIN 조건이 없는 경우 생길 수 있는 모든 데이터 조합)
관계 연산	SELECT	WHERE절	조건에 맞는 행 조회
	PROJECT	SELECT절	조건에 맞는 칼럼 조회
	JOIN	여러 JOIN
	DIVIDE	없음	공통요소를 추출하고 분모 릴레이션의 속성을 삭제한 후 중복된 행 제거

2.   ANSI/ISO SQL에서 표시하는 FROM 절의 JOIN 형태

- INNER JOIN

• WHERE 절에서부터 사용하던 JOIN의 DEFAULT 옵션으로 JOIN 조건에서 동일한 값이 있는 행만 반환
• DEFAULT 옵션이므로 생략이 가능하지만, CROSS JOIN, OUTER JOIN과는 같이 사용 불가
• USING 조건절이나 ON 조건절을 필수적으로 사용
• SQL>> SELECT 칼럼s FROM 테이블1 A, 테이블2 B WHERE A.칼럼=B.칼럼
• SQL>> SELECT 칼럼s FROM 테이블1 A INNER JOIN 테이블2 B ON A.칼럼=B.칼럼; (ANSI/ISO 표준)
• SQL>> SELECT A.칼럼=B.칼럼 FROM 테이블1 A JOIN 테이블2 B ON EMP.DEPTNO = DEPT.DEPTNO; (INNER 생략 가능)

- NATURAL JOIN

• INNER JOIN의 하위 개념으로 NATURAL JOIN은 두 테이블 간의 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI(=) JOIN을 수행
• NATURAL JOIN이 명시되면, 추가로 USING 조건절, ON 조건절, WHERE 절에서 JOIN 조건을 정의 불가
• SQL Server에서는 지원하지 않는 기능
• JOIN에 사용된 칼럼들은 같은 데이터 유형이어야 하며, ALIAS나 테이블 명과 같은 접두사를 붙일 수 없음
• SQL>> SELECT 칼럼s FROM 테이블1 NATURAL JOIN 테이블2;

- USING 조건절

• FROM 절의 USING 조건절을 이용하면 같은 이름을 가진 칼럼들 중에서 원하는 칼럼에 대해서만 선택적으로 EQUI JOIN 가능
• SQL Server에서는 지원하지 않는 기능
• USING 조건절을 이용한 EQUI JOIN에서도 NATURAL JOIN과 마찬가지로 JOIN 칼럼에 대해서는 ALIAS나 테이블 이름과 같은 접두사를 붙일 수 없음
• SQL>> SELECT 칼럼s FROM 테이블1 A JOIN 테이블2 B USING (칼럼명);

- ON 조건절

• JOIN 서술부(ON 조건절)와 비 JOIN 서술부(WHERE 조건절)를 분리하여 이해가 쉬우며, 칼럼명이 다르더라도 JOIN 조건을 사용할 수 있는 장점
• 이름이 다른 칼럼명을 JOIN 조건으로 사용하거나, JOIN 칼럼을 명시하기 위해 사용
•  ALIAS나 테이블 명과 같은 접두사를 사용하여 SELECT에 사용되는 칼럼을 논리적으로 명확하게 지정해주어야 함
• SQL>> SELECT 칼럼s FROM 테이블1 A JOIN 테이블2 B ON (A.칼럼=B.칼럼);

- CROSS JOIN

• 테이블 간 JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합
• 결과는 양쪽 집합의 M*N 건의 데이터 조합이 발생
• SQL>> SELECT 칼럼 FROM 테이블1, 테이블2; (조인 조건이 없을 때 발생 ↔ NATURAL JOIN은 명시해야 됨)

- OUTER JOIN

• 조건에서 동일한 값이 없는 행도 반환할 때 사용
• LEFT OUTER JOIN : 조인 수행시 먼저 표기된 좌측 테이블에 해당하는 데이터를 먼저 읽은 후, 나중 표기된 우측 테이블에서 JOIN 대상 데이터를 읽어 온다. Table A와 B가 있을 때(Table 'A'가 기준이 됨)
• SELECT 칼럼s FROM 테이블1 A LEFT OUTER JOIN 테이블2 B ON (A.칼럼=B.칼럼);
• RIGHT OUTER JOIN : 조인 수행시 LEFT JOIN과 반대로 우측 테이블이 기준이 되어 결과를 생성한다. TABLE A와 B가 있을 때(TABLE 'B'가 기준이 됨)
• FULL OUTER JOIN : 조인 수행시 좌측, 우측 테이블의 모든 데이터를 읽어 JOIN하여 결과를 생성한다. 즉, TABLE A와 B가 있을 때(TABLE 'A', 'B' 모두 기준이 됨), RIGHT OUTER JOIN과 LEFT OUTER JOIN의 결과를 합집합으로 처리한 결과와 동일, 단, UNION ALL이 아닌 UNION 기능과 같으므로 중복되는 데이터는 삭제
• SQL>> SELECT 칼럼s FROM 테이블1 A FULL OUTER JOIN 테이블2 B ON (A.칼럼=B.칼럼);
• OUTER JOIN 시 활용되는 '(+)' :  '(+)' 기호의 위치의 반대쪽 테이블이 OUTER JOIN의 기준이 되는 테이블
  

- LEFT JOIN, RIGHT JOIN은 칼럼에서 같은 값이 없는 경우에는 테이블에서 가져오는 칼럼들은 NULL 값으로 채운다.

- LEFT JOIN, RIGHT JOIN, FULL JOIN으로 OUTER 키워드를 생략해서 사용 가능

 

INNER vs OUTER vs CROSS JOIN 비교

 

2절 집합 연산자

 

1.   집합 연산자

• 두 개 이상의 테이블에서 조인을 사용하지 않고 연관된 데이터를 조회하는 방법
• 여러 개의 질의의 결과를 연결하여 하나로 결합하는 방식

집합 연산자	연산자의 의미
UNION	여러 개의 SQL문의 결과에 대한 합집합으로 결과에서 모든 중복된 행은 하나의 행으로 만든다.
UNION ALL	여러 개의 SQL문의 결과에 대한 합집합으로 중복된 행도 그대로 결과로 표시된다. 즉, 단순히 결과만 합쳐놓은 것이다. 일반적으로 여러 질의 결과가 상호 배타적인 일때 많이 사용한다. 개별 SQL문의 결과가 서로 중복되지 않는 경우, UNION과 결과가 동일하다. (결과의 정렬 순서에는 차이가 있을 수 있음)
INTERSECT	여러 개의 SQL문의 결과에 대한 교집합이다. 중복된 행은 하나의 행으로 만든다.
EXCEPT	앞의 SQL문의 결과에서 뒤의 SQL문의 결과에 대한 차집합이다. 중복된 하나의 행으로 만든다. (일부 데이터베이스는 MINUS를 사용함)

1.   UNION: 합집합, 칼럼 수와 데이터 타입이 모두 동일한 테이블 간 연산만 가능

SQL>> SELECT 칼럼명 FROM 테이블명 A WHERE 조건절 UNION SELECT 테이블명 WHERE 조건절;

▶ UNION ALL: 중복된 행도 전부 출력하는 합집합, 정렬 안함 (↔ UNION은 정렬을 유발함), 집합 연산자에 속함

SQL>> SELECT 칼럼명 FROM 테이블명 A WHERE 조건절 UNION ALL SELECT 테이블명 WHERE 조건절;

2.   INTERSECT: 교집합

SQL>> SELECT 칼럼명 FROM 테이블명 A WHERE 조건절MS INTERSECT SELECT 테이블명 WHERE 조건절;

3.   MINUS, EXCEPT: 차집합

SQL>> SELECT 칼럼명 FROM 테이블명 A WHERE 조건절 MINUS SELECT 테이블명 WHERE 조건절;

▶ 집합 연산자를 사용하여 만들어지는 SQL문의 형태

SELECT 칼럼명1, 칼럼명2, ... FROM 테이블명1 [WHERE 조건식 ] [[GROUP BY 칼럼(Column)이나 표현식 [HAVING 그룹조건식 ] ] 집합 연산자 SELECT 칼럼명1, 칼럼명2, ... FROM 테이블명2 [WHERE 조건식 ] [[GROUP BY 칼럼(Column)이나 표현식 [HAVING 그룹조건식 ] ] [ORDER BY 1, 2 [ASC또는 DESC ] ;

 

 

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

 

1.   Oracle 계층형 질의(Hierarchical Query): 계층형 데이터를 조회하기 위해 사용함, Oracle에서 지원함

▶ 계층형 데이터: 엔터티를 순환관계 데이터 모델로 설계할 때 발생함

	-- 계층형 질의 구문
    SELECT ...
    FROM 테이블
    WHERE condition AND condition ...
    START WITH condition
    CONNECT BY [NOCYCLE] condidion AND condition ...
    [ORDER SIBLINGS BY column, cloumn, ...]

▶ CONNECT BY : 트리 형태의 구조로 쿼리 수행 (루트 노드부터 하위 노드의 쿼리를 실행함) 상사 이름과 사람 이름을 조인하여 상사 밑에 넣기

- START WITH절은 계층 구조 전개의 시작 위치를 지정하는 구문이다. 즉, 루트 데이터를 지정한다.(액세스)

- CONNECT BY절은 다음에 전개될 자식 데이터를 지정하는 구문이다. 자식 데이터는 CONNECT BY절에 주어진 조건을 만족해야 한다.(조인)

- PRIOR : CONNECT BY절에 사용되며, 현재 읽은 칼럼을 지정한다. PRIOR 자식 = 부모 형태를 사용하면 계층구조에서  자식 데이터(부모 → 자식) 방향으로 전개하는 순방향 전개를 한다. 그리고 PRIOR 부모 = 자식 형태를 사용하면 반대로 부모 데이터에서 자식 데이터에서 부모 데이터(자식 → 부모) 방향으로 전개하는 역방향 전개를 한다. (PRIOR이 부모쪽에 붙으면 역방향, 자식쪽에 붙으면 순방향)

PRIOR 키워드는 CONNECT BY에서 뿐만 아니라 SELECT, WHERE절에서 사용할 수 있다.

- NOCYCLE : 데이터를 전개하면서 이미 나타났던 동일한 데이터가 전개 중에 다시 나타난다면 이것을 가리켜 사이클(Cycle)이 형성되었다라고 말한다. 사이클이 발생한 데이터는 런타임 오류가 발생한다. 그렇지만 NOCYCLE를 추가하면 사이클이 발생한 이후의 데이터는 전개하지 않는다.

- ORDER SIBLINGS BY : 형제 노드(동일 LEVEL) 사이에서 정렬을 수행한다.

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

￘ CONNECT_BY_ISCYCLE : 순환구조 발생지점 표시 (부모 노드와 자식 노드가 같을 때 1 아니면 0 출력)

가상 칼럼	설명
LEVEL	루트 데이터이면 1, 그 하위 데이터이면 2이다. 리프 데이터까지 1씩 증가한다.
CONNECT_BY_ISLEAF	전개 과정에서 해당 데이터가 리프 데이터이면 1, 그렇지 않으면 0이다.
CONNECT_BY_ISCYCLE	전개 과정에서 자식을 갖는데, 해당 데이터가 조상으로서 존재하면 1, 그렇지 않으면 0이다. 여기서 조상이란 자신으로부터 루트까지의 경로에 존재하는 데이터를 말한다. CYCLE 옵션을 사용했을 때만 사용할 수 있다.

함수	설명
SYS_CONNECT_BY_PATH	루트 데이터부터 현재 전개할 데이터까지의 경로를 표시한다.
CONNECT_BY_ROOT	현재 전개할 데이터의 루트 데이터를 표시한다. 단항 연산자이다. 사용법 : CONNECT_BY_ROOT 칼럼

▶ LPAD : 계층형 조회 결과를 명확히 하기 위해 사용 (LEVEL 값을 이용하여 결과 데이터 정렬)

 

2.   SQL Server 계층형 질의: CTE(Common Table Expression)로 재귀 호출

WITH EMPLOYEES_ANCHOR AS (
	SELECT EMPLOYEEID, LASTNAME, FIRSTNAME, REPORTSTO, 0 AS LEVEL
		FROM EMPLOYEES
		WHERE REPORTSTO IS NULL / * 재귀 호출의 시작점 */
		UNION ALL
		SELECT R.EMPLOYEEID, R.LASTNAME, R.FIRSTNAME, R.REPORTSTO, A.LEVEL + 1
		FROM EMPLOYEES_ANCHOR A, EMPLOYEES R
		WHERE A.EMPLOYEEID = R.REPORTSTO )
	SELECT LEVEL, EMPLOYEEID, LASTNAME, FIRSTNAME, REPORTSTO
FROM EMPLOYEES_ANCHOR
GO

UNION ALL 연산자로 두개의 쿼리를 결합

위에 있는 쿼리를 '앵커 멤버'(Anchor Member), 아래에 있는 쿼리를 '재귀 멤버' (Recursive Member)

앵커멤버를 실행하여 기본 결과 집합을 만들고 이후 재귀 멤버를 지속적으로 실행한다.

 

3.   셀프 조인: 동일 테이블 사이의 조인, 한 테이블 내에서 두 칼럼이 연관 관계가 있는 경우, 반드시 테이블 별칭(Alias)를 사용

SQL>> SELECT ALIAS명1.칼럼명, ALIAS명2.칼럼명, ... FROM 테이블1 ALIAS명1, 테이블2 ALIAS명2 WHERE ALIAS명1.칼럼명2 = ALIAS명2.칼럼명1;

 

 

4절 서브쿼리

1.   서브쿼리: 하나의 SQL문 안의 SQL문

• 메인쿼리가 서브쿼리를 포함하는 종속적인 관계
• 서브쿼리는 메인쿼리의 칼럼을 모두 사용할 수 있지만 메인쿼리는 서브쿼리의 칼럼을 사용할 수 없다
• 서브쿼리 레벨과는 상관없이 항상 메인쿼리 레벨로 결과 집합이 생성

▶ 서브쿼리를 사용할 때 주의사항

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

② 서브쿼리는 단일 행(Single Row) 또는 복수 행(Multiple Row) 비교 연산자와 함께 사용 가능하다. 단일 행 비교 연산자는 서브쿼리의 결과가 반드시 1건 이하이어야 하고 복수 행 비교 연산자는 서브쿼리의 결과 건수와 상관 없다.

③ 서브쿼리에서는 ORDER BY를 사용하지 못한다. ORDER BY절은 SELECT절에서 오직 한 개만 올 수 있기 때문에 ORDER BY절은 메인쿼리의 마지막 문장에 위치해야 한다.

▶ 서브쿼리가 SQL문에서 사용이 가능한 곳

- SELECT 절

- FROM 절

- WHERE 절

- HAVING 절

- ORDER BY 절

- INSERT문의 VALUES 절

- UPDATE문의 SET 절

 

2.   종류

▶ 동작 방식에 따른 분류

• 비연관 서브쿼리: 메인쿼리 칼럼을 가지고 있지 않는 서브쿼리, 메인쿼리에 값을 제공하기 위한 목적으로 주로 사용함

￘ Access Subquery: 제공자 역할

￘ Filter Subquery: 확인자 역할

￘ Early Filter Subquery: 데이터 필터링 역할

• 연관 서브쿼리(Associative Subquery): 서브쿼리 내에 메인쿼리 칼럼이 사용된 서브쿼리, 메인쿼리의 결과를 조건이 맞는지 확인하기 위한 목적으로 주로 사용함

▶ 반환 데이터 형태에 따른 분류

• 단일 행 서브쿼리: 실행 결과가 1건 이하인 서브쿼리, 단일 행 비교 연산자(=, <, <=, >, >=, <>)와 함께 사용
• 다중 행 서브쿼리: 실행 결과가 여러 건인 서브쿼리, 다중 행 비교 연산자(IN, ALL, ANY, SOME)와 함께 사용 

※ 다중 행 비교 연산자

- IN : 서브쿼리의 결과 중 하나의 값이라도 동일하다는 조건

- ANY : 서브쿼리의 결과 중 하나의 값이라도 만족한다는 조건

- ALL : 서브쿼리의 모든 결과값을 만족한다는 조건

- EXISTS : 서브쿼리의 결과를 만족하는 값이 존재하는지 여부를 확인하는 조건, ‘WHERE EXISTS (SELECT ~)’ (항상 연관 서브쿼리로 사용)

• 다중 칼럼 서브쿼리: 실행 결과로 여러 칼럼 반환, 주로 메인쿼리의 조건과 비교하기 위해 사용 (비교하고자 하는 칼럼의 개수와 위치가 동일해야 함), SQL Server에서는 지원되지 않는 기능

3.   스칼라 서브쿼리

• 값 하나를 반환하는 서브쿼리, SELECT, FROM, HAVING, UPDATE의 SET, INSERT의 VALUES절에서 사용 가능
• SELECT절에 사용하는 서브쿼리
• HAVING 절은 그룹함수와 함께 사용될 때 그룹핑된 결과에 대해 부가적인 조건을 주기 위해서 사용
• SQL >> SELECT PLAYER_NAME 선수명, HEIGHT 키, (SELECT AVG(HEIGHT) FROM PLAYER X WHERE X.TEAM_ID = P.TEAM_ID) 팀평균키 FROM PLAYER P

 

4.   뷰: 가상의 테이블, FROM절에 사용하는 뷰는 인라인 뷰(Inline View) 또는 Dynamic View라고 함

▶ 장점

• 독립성: 테이블 구조 변경 자동 반영
• 편리성: 쿼리를 단순하게 작성할 수 있음, 자주 사용하는 SQL문의 형태를 뷰로 생성하여 사용할 수 있음
• 보안성: 뷰를 생성할 때 칼럼을 제외할 수 있음

5.   WITH: 서브쿼리를 이용하여 뷰로 사용할 수 있는 구문

SQL>> WITH 뷰명 AS (SELECT ~)

 

 

5절 그룹 함수

1.   ANSI/ISO SQL 표준은 데이터 분석 함수

집계 함수 : COUNT, SUM, AVG, MAX, MIN 등

그룹 함수 : 소그룹 간의 소계를 계산하는 ROLLUP, GROUP BY 항목들 간 다차원적인 소계를 계산 할 수 있는 CUBE, 특정 항목에 대한 소계를 계산하는 GROUPING SETS 

윈도우 함수 : 분석 함수(ANALYTIC FUNCTION)나 순위 함수(RANK FUNCTION)로도 알려져 있음

 

2. ROLLUP 함수

▶ GROUP BY로 묶인 칼럼의 소계 계산, 계층 구조로 GROUP BY의 칼럼 순서가 바뀌면 결과 값 바뀜

GROUP BY ROLLUP(A, B, C)

1. A로 묶고, B로 묶고, C로 다 분류 되었을 때 C는 null로 말아(말아올려서 집계)!

2. A로 묶고, B로 다 분류 되었을 때 B와 C는 null로 말아!

3. A로 다 묶었을 때 A, B, C 모두 null로 말아!

ROLLUP (a, b, c) : (a, b, c) / (a, b) / (a) / ()

 

3. CUBE 함수

▶ 조합 가능한 모든 값에 대해 다차원 집계

Grouping Columns이 가질 수 있는 모든 경우에 대하여 Subtotal을 생성해야 하는 경우에는 CUBE를 사용하는 것이 바람직하나, ROLLUP에 비해 시스템에 많은 부담을 주므로 사용에 주의해야 한다.

CUBE (a, b, c) : (a, b, c) / (a, b) / (a, c) / (b, c) / (a) / (b) / (c) / ()

 

4. GROUPING SETS 함수

▶ 특정 항목에 대한 소계 계산, GROUP BY의 칼럼 순서와 무관하게 개별적으로 처리함

GROUPING SETS(a, b, c) : (a) / (b) / (c)

• UNION ALL을 사용한 일반 그룹함수를 사용한 SQL과 같은 결과를 얻을 수 있으며, 괄호로 묶은 집합 별로(괄호 내는 계층 구조가 아닌 하나의 데이터로 간주함) 집계를 구할 수 있다.
• GROUPING SETS의 경우 일반 그룹함수를 이용한 SQL과 결과 데이터는 같으나 행들의 정렬 순서는 다를 수 있다.
• GROUPING SETS 인수들은 평등한 관계이므로 인수의 순서가 바뀌어도 결과는 같다.

표현식	출력값
GROUP BY ROLLUP (E1,E2)	E1과 E2별 소계 / E1별 모든 E2의 SUBTOTAL / 총합계
GROUP BY CUBE (E1,E2)	E1과 E2별 소계 / E1별 소계 / E2별 소계 / 총합계
GROUP BY GROUPING SETS (E1,E2)	E1별 소계 / E2별 소계

※ ‘GROUP BY CUBE (E1,E2)’와 ‘GROUP BY GROUPING SETS (E1,E2,(E1,E2),())’는 동일한 결과 출력

 

5. GROUPING 함수

▶ 그룹 함수에서 생성되는 합계를 구분해주는 함수, 소계나 합계가 계산되면 GROUPING(EXPR) = 1(결과가 NULL일 경우, 합계 소계의 칼럼이 NULL로 표기됨) 아니면GROUPING(EXPR) = 0 반환

반드시 사용할 칼럼이 GROUP BY절에 명시되어야 함

 

 

6절 윈도우 함수

 

1.  윈도우 함수(Window Function)

여러 행 간의 관계 정의 함수, 중첩 불가, 서브쿼리에서는 사용 가능

- WINDOW 함수에는 OVER 문구가 키워드로 필수 포함된다.

- GROUP BY 구문과 병행하여 쓸 수는 없다.(GROUP BY 절의 집합을 원본으로 하는 데이터를 WINDOW FUNCTION과 함께 사용한다면 같이 사용한다고 해도 오류가 발생하지 않고, SELECT구문 작동 순서에 따라 GROUP BY로 먼저 그룹핑된 후에 SELECT구문이 작동하게 된다.)

SQL >> SELECT WINDOW_FUNCTION (ARGUMENTS) OVER ( [PARTITION BY 칼럼] [ORDER BY 절] [WINDOWING 절] ) FROM 테이블 명;

• ARGUMENTS (인수) : 함수에 따라 0 ~ N개의 인수가 지정될 수 있다.
• PARTITION BY 절 : 전체 집합을 기준에 의해 소그룹으로 나눌 수 있다.
• ORDER BY 절 : 어떤 항목에 대해 순위를 지정할 지 ORDER BY 절을 기술한다.
• WINDOWING 절 : WINDOWING 절은 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다. ROWS는 물리적인 결과 행의 수를, RANGE는 논리적인 값에 의한 범위를 나타내는데, 둘 중의 하나를 선택해서 사용할 수 있다. 다만, WINDOWING 절은 SQL Server에서는 지원하지 않는다.
• N PRECEDING, N FOLLOWING : N번째 앞 행, N번째 뒤 행
• UNBOUNDED PRECEDING, UNBOUNDED FOLLOWING : 첫 행, 끝 행

▶ 순위(RANK) 관련 함수

• RANK : 중복 순위 포함
• DENSE_RANK : 중복 순위 무시 (중간 순위를 비우지 않음)
• ROW_NUMBER : 동일한 값에 무관하게 고유한 순위 부여, (Oracle의 경우 rowid가 적은 행이 먼저 나온다)

▶ 집계(AGGREGATE) 관련 함수

SUM, MAX, MIN, AVG, COUNT

▶ 행 순서 관련 함수

• FIRST_VALUE, LAST_VALUE : 윈도우에서 가장 먼저 나온 값, 가장 나중에 나온 값(공동 등수를 인정하지 않고 가장 나중에 나온 행만을 처리), SQL Server에서는 지원하지 않음
• LAG, LEAD : 몇 번째 이전(DEFAULT 1) 행, 몇 번째 이후(DEFAULT 1) 행 (Oracle) 랙릿, SQL Server에서는 지원하지 않음 
• SQL Server의 경우 집계 함수 뒤에는 OVER 절 내의 ORDER BY 구문을 지원하지 않는다.

※ LAG 예제

SQL >> SELECT ENAME, HIREDATE, SAL, LAG(SAL, 2, 0) OVER (ORDER BY HIREDATE) as PREV_SAL FROM EMP WHERE JOB = 'SALESMAN' LAG(SAL, 2, 0)의 기능은 두 행 앞의 SALARY를 가져오고, 가져올 값이 없는 경우는 0으로 처리한다.

※ ‘LEAD(E,A)’는 E에서 A번째 행의 값을 호출하는 형태로도 쓰임 (A의 기본값은 1)

▶ 그룹 내 비율 관련 함수

• PERCENT_RANK() : 윈도우에서 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 하여, 값이 아닌 행의 순서별 백분율을 구한다.(결과값 >= 0 & <= 1) SQL Server에서는 지원하지 않음
• CUME_DIST() : 현재 행보다 작거나 같은 값을 포함한 누적 백분율(결과값 >0 & <=1), SQL Server에서 지원하지 않음
• NTILE(A) : 전체 데이터 A등분
• RATIO_TO_REPORT : SUM(칼럼) 값에 대한 행별 칼럼 값의 백분율 소수점으로 구하기(결과값 > 0 & <= 1), SQL Server에서 지원하지 않음

▶ 선형 분석을 포함한 통계 분석 관련 함수 (통계에 특화된 기능이므로 설명 생략)

 

 

7절 DCL

 

1.  DCL

유저를 생성하거나 권한을 제어하는 명령어, 보안을 위해 필요함

▶ GRANT: 권한 부여

SQL>> GRANT [객체권한명] (컬럼) ON [객체명] TO { 유저명 | 롤명 | PUBLC} [WITH GRANT OPTION]

▶ REVOKE: 권한 제거

SQL>> REVOKE { 권한명 [, 권한명...] ALL} ON 객체명 FROM {유저명 [, 유저명...] | 롤명(ROLE) | PUBLIC} [CASCADE CONSTRAINTS]

- 객체 권한의 철회는 그 권한을 부여한 유저야만이 수행할 수 있다.

- CASCADE CONSTRAINT : 명령어의 사용으로 참조 객체 권한에서 사용된 참조 무결성 제한을 같이 삭제 가능

- WITH GRANT OPTION 으로 객체 권한을 부여한 사용자의 객체 권한을 철회하면 권한을 부여받은 사용자가 부여한 객체 권한 또한 같이 철회되는 종속철회가 발생한다.

 

2.   권한(Privileges)

▶ SELECT, INSERT, UPDATE, DELETE, ALTER, ALL : DML 관련 권한

▶ REFERENCES : 지정된 테이블을 참조하는 제약조건을 생성하는 권한

▶ INDEX : 지정된 테이블에서 인덱스를 생성하는 권한

 

3.   Oracle의 유저

▶ SCOTT: 테스트용 샘플 유저, Default 패스워드:TIGER

▶ SYS: DBA 권한이 부여된 최상위 유저

▶ SYSTEM: DB의 모든 시스템 권한이 부여된 DBA 유저, Oracle 설치 완료 시에 패스워드 설정

 

4.   ROLE: 권한의 집합, 권한을 일일이 부여하지 않고 ROLE로 편리하게 여러 권한을 부여할 수 있음

▶ Oracle의 ROLE

ROLE	권한
CONNECT	CREATE SESSION
RESOURCE	CREATE CLUSTER CREATE PROCEDURE CREATE TYPE CREATE SEQUENCE	CREATE TRIGGER CREATE OPERATOR CREATE TABLE CREATE INDEXTYPE

5. Oracle과 SQL Server의 사용자에 대한 아키텍처 비교

▶ Oracle

유저를 통해 데이터베이스에 접속을 하는 형태

SQL >> Oracle GRANT CREATE USER TO SCOTT;

▶ SQL Server

인스턴스에 접속하기 위해 로그인이라는 것을 생성

1. Windows 인증 방식

2. 혼합 모드(Windows 인증 또는 SQL 인증) 방식

 

오브젝트 권한과 오브젝트와의 관계 (Oracle 사례)

객체권한	테이블	VIEWS	SEQUENCE	PROCEDURE
ALTER	O		O
DELETE	O	O
EXECUTE				O
INDEX	O
INSERT	O	O
REFERENCES	O
SELECT	O	O	O
UPDATE	O	O

오브젝트 권한과 오브젝트와의 관계 (SQL Server 사례)

객체권한	테이블	VIEWS	FUNCTION	PROCEDURE
ALTER	O		O
DELETE	O	O	O
EXECUTE				O
INDEX	O
INSERT	O	O
REFERENCES	O
SELECT	O	O	O
UPDATE	O	O

 

 

8절 절차형 SQL

 

1.  절차형 SQL

일반적인 개발언어처럼 절차지향적인 프로그램을 작성할 수 있도록 제공하는 기능

▶ SQL문의 연속적인 실행 및 조건에 따른 분기처리를 이용하여 특정 기능을 수행하는 저장 모듈 생성 가능

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

- Oracle의 저장 모듈에는 Procedure, User Defined Function, Trigger

▶ PL/SQL (Oracle)

• PL/SQL은 Block 구조로 되어있어 각 기능별로 모듈화가 가능하다.
• 변수, 상수 등을 선언하여 SQL 문장 간 값을 교환한다.
• IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 한다.
• DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있다.
• PL/SQL은 Oracle에 내장되어 있으므로 Oracle과 PL/SQL을 지원하는 어떤 서버로도 프로그램을 옮길 수 있다.
• PL/SQL은 응용 프로그램의 성능을 향상시킨다.
• PL/SQL은 여러 SQL 문장을 Block으로 묶고 한 번에 Block 전부를 서버로 보내기 때문에 통신량을 줄일 수 있다.

▶ PL/SQL 구조

• DECLARE : BEGIN ~ END 절에서 사용될 변수와 인수에 대한 정의 및 데이터 타입을 선언하는 선언부이다.
• BEGIN ~ END : 개발자가 처리하고자 하는 SQL문과 여러 가지 비교문, 제어문을 이용하여 필요한 로직을 처리하는 실행부이다. 
• EXCEPTION : BEGIN ~ END 절에서 실행되는 SQL문이 실행될 때 에러가 발생하면 그 에러를 어떻게 처리할 것이지를 정의하는 예외 처리부이다.

▶ PL/SQL 기본 문법(Syntax)

프로시저를 삭제하는 명령어

DROP Procedure [Procedure_name];

PL/SQL에서는 동적 SQL 또는 DDL 문장을 실행할 때 EXECUTE IMMEDIATE를 사용하여야 한다.

▶ T-SQL

SQL Server를 제어하기 위한 언어

• 변수 선언 기능 @@이라는 전역변수(시스템 함수)와 @이라는 지역변수가 있다.
• 지역변수는 사용자가 자신의 연결 시간 동안만 사용하기 위해 만들어지는 변수이며 전역변수는 이미 SQL서버에 내장된 값이다.
• 데이터 유형(Data Type)을 제공한다. 즉 int, float, varchar 등의 자료형을 의미한다.
• 연산자(Operator) 산술연산자( +, -, *, /)와 비교연산자(=, <, >, <>) 논리연산자(and, or, not) 사용이 가능하다.
• 흐름 제어 기능 IF-ELSE와 WHILE, CASE-THEN 사용이 가능하다.
• 주석 기능한줄 주석 : -- 뒤의 내용은 주석범위 주석 : /* 내용 */ 형태를 사용하며, 여러 줄도 가능함

▶ T-SQL 구조

• DECLARE : BEGIN ~ END 절에서 사용될 변수와 인수에 대한 정의 및 데이터 타입을 선언하는 선언부이다.
• BEGIN ~ END : 개발자가 처리하고자 하는 SQL문과 여러 가지 비교문, 제어문을 이용하여 필요한 로직을 처리하는 실행부이다. T-SQL에서는 BEGIN, END 문을 반드시 사용해야하는 것은 아니지만 블록 단위로 처리하고자 할 때는 반드시 작성해야 한다.
• ERROR 처리 : BEGIN ~ END 절에서 실행되는 SQL문이 실행될 때 에러가 발생하면 그 에러를 어떻게 처리할 것이지를 정의하는 예외 처리부이다.

▶ T-SQL 기본 문법(Syntax)

프로시저를 삭제하는 명령어

DROP Procedure [schema_name.]Procedure_name;

 

2.  프로시저

 

3. User Defined Function

절차형 SQL을 로직과 함께 DB 내에 저장해 놓은 명령문 집합, RETURN을 통해 반드시 하나의 값 반환 (↔ 프로시저)

 

4. 트리거(Trigger)

• DML문이 수행되었을 때 자동으로 동작하는 프로그램 (↔ 프로시저는 EXECUTE로 실행함)
• DCL와 TCL(Commit, Rollback) 실행 불가 (↔ 프로시저는 사용 가능함)
• 데이터베이스에서 자동적으로 수행
• 데이터베이스에 의해 자동 호출되지만 결국 INSERT, UPDATE, DELETE 문과 하나의 트랜잭션 안에서 일어나는 일련의 작업들
• 데이터베이스 보안의 적용, 유효하지 않은 트랜잭션의 예방, 업무 규칙 자동 적용 제공 등에 사용
• 트리거의 용도 : 데이터의 무결성과 일관성을 위해서 사용자 정의 함수를 사용

 

5. 프로시저와 트리거의 차이점

프로시저는 BEGIN ~ END 절 내에 COMMIT, ROLLBACK과 같은 트랜잭션 종료 명령어를 사용할 수 있지만, 데이터베이스 트리거는 BEGIN ~ END 절 내에 사용할 수 없다.

프로시저	트리거
CREATE Procedure 문법사용	CREATE Trigger 문법사용
EXECUTE 명령어로 실행	생성 후 자동으로 실행
COMMIT, ROLLBACK 실행 가능	COMMIT, ROLLBACK 실행 안됨