[2과목] Part2. SQL 활용

1) 표준 조인

1. STANDARD SQL 개요

• 대표적인 ANSI/ISO 표준 SQL의 기능

  • STANDARD JOIN 기능 추가 (CROSS, OUTER JOIN 등 새로운 FROM 절 JOIN 기능들)
  • 스칼라 서브 쿼리, TOP-N 쿼리 등 (서브 쿼리 기능들)
  • ROLLUP, CUBE, GROUPING SETS 등 (리포팅 기능)
  • 윈도우 함수 (분석 기능)

• 관계 대수

  • 일반 집합 연산자
    • UNION 연산
    • INTERSECTION 연산
    • DIFFERENCE 연산
    • PRODUCT 연산
  • 순수 관계 연산자 (관계형 데이터베이스에 적용할 수 있도록 특별히 개발된 연산자)
    • SELECT 연산
    • PROJECT 연산
    • (NATURAL) JOIN 연산
    • DIVIDE 연산

• 일반 집합 연산자

• 순수 관계 연산자

2. FROM 절 JOIN 형태

• ANSI/ISO SQL에서 표시하는 FROM절의 JOIN 형태
  • INNER JOIN
  • NATURAL JOIN
  • USING 조건절
  • ON 조건절
  • CROSS JOIN
  • OUTER JOIN

3. INNER JOIN (↔️ OUTER JOIN) ⭐️⭐️⭐️

• JOIN 조건에서 동일한 값이 있는 행만 반환한다. 그동안 WHERE절에 사용하던 JOIN 조건을 FROM절에서 정의하겠다는 표시이므로 USING 조건절이나 ON 조건절을 필수적으로 사용해야 한다.
• JOIN의 DEFAULT 옵션
• CROSS JOIN, OUTER JOIN과는 같이 사용할 수 없다.

4. NATURAL JOIN (SQL Server에서는 지원하지 않는 기능)

• INNER JOIN의 하위 개념으로 두 테이블 간의 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI(=) JOIN을 수행한다. NATURAL JOIN이 명시되면, 추가로 USING 조건절, ON 조건절, WHERE절에서 JOIN 조건을 정의할 수 없다.
• JOIN에 사용된 테이블의 데이터 성격(도메인)과 칼럼명 등이 동일해야 하며, ALIAS나 테이블명과 같은 접두사를 붙일 수 없다.
• * 와일드카드처럼 별도의 칼럼 순서를 지정하지 않으면 NATURAL JOIN의 기준이 되는 칼럼들이 다른 칼럼보다 먼저 출력된다.

5. USING 조건절

-- DEPTNO 컬럼을 이용한 INNER JOIN
-- 별도의 칼럼 순서를 지정하지 않으면 USING 조건절의 기준이 되는 칼럼이 다른 칼럼보다 먼저 출력된다.
SELECT * FROM DEPT
JOIN DEPT_TEMP USING(DEPTNO)

• NATURAL JOIN에서는 모든 일치되는 칼럼들에 대해 JOIN이 이루어지지만, FROM절의 USING 조건절을 이용하면 같은 이름을 가진 칼럼들 중에서 원하는 칼럼에 대해서만 EQUI JOIN을 할 수가 있다. 다만, 이 기능은 SQL Server에서는 지원하지 않는 기능이다.

6. ON 조건절

• JOIN 서술부(ON 조건절)와 비 JOIN 서술부(WHERE 조건절)를 분리하여 이해가 쉬우며, 칼럼명이 다르더라도 JOIN 조건을 사용할 수 있다.
• WHERE 절과 혼용 가능

-- 아우터 조인에서 조인 대상을 제한하기 위한 목적이 아니라면 WHERE 조건절 사용을 권고
SELECT E.ENAME, E.MGR, D.DEPTNO, D.DNAME
FROM EMP E
JOIN DEPT D
ON (E.DEPTNO = D.DEPTNO AND E.MGR = 7698);

7. CROSS JOIN

💡 CARTESIAN PRODUCT, CROSS PRODUCT. 결과는 양쪽 집합의 M*N건의 데이터 조합이 발생한다.

• 일반 집합 연산자의 PRODUCT의 개념
• 테이블 간 JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합

8. OUTER JOIN (외부 조인) ⭐️⭐️⭐️

💡 JOIN 조건에서 동일한 값이 없는 행도 반환할 때 사용할 수 있다. 기준 칼럼과 같은 값이 있을 때 해당 데이터를 가져오고, 없는 경우에는 NULL 값으로 채운다.

1. LEFT (OUTER) JOIN

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

2. RIGHT (OUTER) JOIN

   💡 조인 수행 시 LEFT JOIN과 반대로 우측 테이블이 기준이 되어 결과 생성한다.

3. FULL (OUTER) JOIN

   💡 조인 수행 시 좌측, 우측 테이블의 모든 데이터를 읽어 JOIN 하여 결과를 생성한다. LEFT JOIN, RIGHT JOIN 결과의 합집합. UNION 기능과 같으므로 중복되는 데이터는 삭제한다.

2) 집합 연산자(Set Operator)

💡 두 개 이상의 테이블에서 조인을 사용하지 않고 연관된 데이터를 조회하는 방법. 여러 개의 질의의 결과를 연결하여 하나로 결합하는 방식을 사용, 2개 이상의 질의 결과를 하나의 결과로 만들어 준다.

• 집합 연산자를 사용하는 상황

  • 서로 다른 테이블에서 유사한 형태의 결과를 반환하는 것을 하나의 결과로 합치고자 할 때
  • 서로 다른 질의를 수행하여 결과를 합치고자 할 때
  • 이외에도 튜닝 관점에서 실행 계획을 분리하고자 하는 목적으로도 사용

• 집합 연산자를 사용하기 위해 만족해야하는 제약조건

  • SELECT 절의 칼럼 수 동일
  • SELECT절의 동일 위치에 존재하는 칼럼의 데이터 타입이 상호 호환 가능해야 한다. (반드시 동일한 데이터 타입일 필요는 없음)

• 집합 연산자의 종류

  • UNION (∪)

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

  • UNION ALL (∪)

    💡 UNION ALL을 사용하는 경우 칼럼 ALIAS는 첫번째 SQL 모듈 기준, 정렬 기준은 마지막 SQL 모듈을 기준으로 한다.

    • 여러 개의 SQL문의 결과에 대한 합집합
    • 중복된 행도 그대로 결과로 표시된다. (단순히 결과만 합쳐놓은 것)
    • 일반적으로 여러 질의 결과가 상호 배타적일 때 많이 사용한다.
    • 개별 SQL문의 결과가 서로 중복되지 않는 경우, UNION 절과 결과가 동일하다. (결과의 정렬 순서에는 차이가 있을 수 있음)

  • INTERSECT (∩)

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

  • EXCEPT (일부 데이터베이스는 MINUS를 사용함)

    • 앞의 SQL문의 결과에서 뒤의 SQL문의 결과에 대한 차집합
    • 중복된 행은 하나의 행으로 만든다.

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

1. 계층형 질의(Hierarchical Query)

• 계층형 데이터

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

• Oracle 계층형 질의

  SELECT ...
  FROM 테이블
  WHERE condition AND condition ...
  START WITH condition
  CONNECT BY [NOCYCLE] condition AND condition ...
  [ORDER SIBLINGS BY column, column, ...]

  • START WITH절 : 계층 구조의 최상위 행을 지정

  • CONNECT BY절 : 부모 자식 관계를 지정 / 자식 데이터는 CONNECT BY절에 주어진 조건을 만족해야 한다. (조인)

  • PRIOR : CONNECT BY 절에 사용되며 PRIOR에 지정된 컬럼이 맞은편 컬럼을 찾아간다.

    • CONNECT BY PRIOR 자식 컬럼 = 부모 컬럼 → 자식 순방향 전개
    • CONNECT BY PRIOR 부모 컬럼 = 자식 컬럼 → 부모 역방향 전개

  • ORDER SIBLINGS : 계층형 쿼리에서 정렬을 수행한다.

  • Oracle 계층형 질의에 사용되는 가상 칼럼

• Oracle 계층형 질의에 사용되는 함수

• SQL Server 계층형 질의 : ~ SQL Server 2000 버전

2. 셀프 조인

💡 동일 테이블 사이의 조인으로 테이블과 칼럼 이름이 모두 동일하기 때문에 식별을 위해 반드시 테이블 별칭(Alias)를 사용해야 한다.

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

4) 서브 쿼리

💡 메인 쿼리가 서브 쿼리를 포함하는 종속적인 관계

• 서브 쿼리는 메인 쿼리의 칼럼을 모두 사용할 수 있지만, 메인 쿼리는 서브 쿼리의 칼럼을 사용할 수 없다. 질의 결과에 서브쿼리 칼럼을 표시해야 한다면 조인 방식으로 변환하거나 함수, 스칼라 서브쿼리(Scalar Subquery) 등을 사용해야 한다.

• 서브 쿼리 레벨과는 상관없이 항상 메인쿼리 레벨로 결과 집합이 생성된다.

• 알려지지 않은 기준을 이용한 검색을 위해 사용한다.

• 조인은 집합간의 곱(Product)의 관계이다.

• 서브 쿼리 사용 시 주의사항

  1. 서브쿼리를 괄호로 감싸서 사용한다.
  2. 서브쿼리는 단일 행(Single Row) 또는 복수 행(Multiple Row) 비교 연산자와 함께 사용 가능하다. 단일 행 비교 연산자는 서브쿼리가 반드시 1건 이하이어야 하고 복수 행 비교 연산자는 서브쿼리의 결과 건수와 상관없다.
  3. 서브쿼리에서는 ORDER BY를 사용하지 못한다. ORDER BY절은 SELECT절에서 오직 한 개만 올 수 있기 때문에 ORDER BY절은 메인 쿼리의 마지막 문장에 위치해야 한다.

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

  • SELECT절, FROM절, WHERE절, HAVING절, ORDER BY절
  • INSERT문의 VALUES절, UPDATE문의 SET절

• 동작 방식에 따른 서브쿼리 분류

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

1. 단일 행 서브 쿼리

💡 단일 행 비교 연산자(=, <, <=, >, >=, <>)와 함께 사용할 때는 서브 쿼리의 결과 건수가 반드시 1건 이하이어야 한다. (2건 이상 시 런타임 오류 발생)

2. 다중 행 서브 쿼리

💡 서브 쿼리 결과가 2건 이상 반환될 수 있다면 반드시 다중 행 비교 연산자(IN, ALL, ANY, SOME)와 함께 사용해야 한다. 그렇지 않으면 SQL문은 오류를 반환한다.

3. 다중 칼럼 서브 쿼리

4. 연관 서브 쿼리 (Correlated Subquery)

💡 서브 쿼리 내에 메인 쿼리 칼럼이 사용된 서브 쿼리

• EXISTS 서브쿼리 : 아무리 조건을 만족하는 건이 여러 건이더라도 조건을 만족하는 1건만 찾으면 추가적인 검색을 진행하지 않는다.

5. 그밖에 위치에서 사용하는 서브 쿼리

6. 뷰(View)

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

• 뷰는 단지 **뷰 정의(View Definition)**만을 가지고 있으며, 실행 시점에 질의를 재작성하여 수행한다. (질의에서 뷰가 사용되면 뷰 정의를 참조해서 DBMS 내부적으로 질의를 재작성)

• 뷰는 실제 데이터를 가지고 있지 않지만 테이블이 수행하는 역할을 하기 때문에 가상 테이블(Virtual Table)이라고도 한다.

• 실제 데이터를 저장하고 있는 뷰를 생성하는 기능을 지원하는 DBMS도 있다.

• 뷰 사용의 장점 ⭐️⭐️⭐️

5) 그룹 함수

💡 소계, 중계, 합계, 총 합계 등 레벨별 집계를 위해 하나의 테이블을 여러번 읽을 필요 없이 하나의 SQL로 테이블을 한 번만 읽어서 빠르게 원하는 리포트를 작성할 수 있다.

1. 데이터 분석 개요

• ANSI/ISO SQL 표준 정의 데이터 분석 함수
  • AGGREGATE FUNCTION (집계 함수)
    • GROUP AGGREGATE FUNCTION이라고도 부르며 GROUP FUNCTION의 한 부분으로 분류할 수 있다.
    • COUNT, SUM, AVG, MAX, MIN 외 집계 함수들이 포함되어 있다.
  • GROUP FUNCTION (그룹 함수)
    • ROLLUP 함수 : 소그룹 간의 소계를 계산하는 함수
    • CUBE 함수 : GROUP BY 항목들 간 다차원적인 소계를 계산할 수 있는 함수
    • GROUPING SETS 함수 : 특정 항목에 대한 소계를 계산하는 함수
  • WINDOW FUNCTION (윈도우 함수)
    • 분석 함수(ANALYTIC FUNCTION)나 순위 함수(RANK FUNCTION)로도 알려져있는 윈도우 함수는 데이터웨어하우스에서 발전한 기능이다.

2. ROLLUP 함수

💡 소그룹 간의 합계를 계산하는 함수. ROLLUP의 인수는 계층 구조이므로 인수 순서가 바뀌면 수행 결과도 바뀐다.

SELECT 칼럼명...
FROM 테이블명
GROUP BY ROLLUP(칼럼명...);

1. 일반적인 GROUP BY절 사용 → 집계 O, 정렬 X
2. GROUP BY절 + ORDER BY절 사용 → 집계 O, 정렬 O
3. ROLLUP 함수 사용 → 집계 O, 정렬 O, 추가 레벨 집계 생성 O
   • 명시한 칼럼에 대해서만 그룹 결과 뿐만 아니라 총 집계까지 생성
   • ROLLUP 집계 결과가 집계 컬럼이 아니면 NULL로 표기 → GROUPING 사용
   • GROUPING 함수 : ROLLUP, CUBE, GROUPING SETS 등의 그룹 함수에 의해 집계가 된 데이터가 아니면 0을 리턴하는 함수

3. CUBE 함수 (시스템 연산 부담을 주므로 사용에 주의)

💡 결합 가능한 모든 값에 대하여 다차원 집계를 계산하는 함수. ROLLUP과 달리 인수는 평등한 관계이므로 인수의 순서가 바뀌는 경우에 행간에 정렬 순서는 바뀔 수 있어도 수행 결과는 같다.

SELECT 칼럼명 ...
FROM 테이블명
GROUP BY CUBE(칼럼명);

• 결과에 대한 정렬이 필요한 경우 ORDER BY 절 수행 필요
• GROUPING COLUMN의 수가 N 개일 경우 → 2의 N 승 LEVEL의 소계를 생성
• UNION ALL 의 결과 데이터가 같음 (행들의 정렬은 다를 수 있다)

4. GROUPING SETS 함수

• GROUPING SETS 사용 → UNION ALL, 일반 그룹 함수를 사용과 같은 결과와 같음
• 인수의 순서가 바뀌어도 결과는 동일하다.

6) 윈도우 함수

1. WINDOW FUNCTION 개요

💡 기존 관계형 DB는 칼럼간 연산은 쉽지만, 행 간의 관계를 쉽게 정의하기 위해 만들어진 함수. 중첩(Nested) 사용 불가, 서브 쿼리에서도 사용 가능

• 윈도우 함수의 종류

  1. 그룹 내 순위(RANK) 함수
  2. 그룹 내 집계 관련 함수
  3. 그룹 내 행 순서 관련 함수
  4. 그룹 내 비율 관련 함수
  5. 선형 분석을 포함한 통계 분석 함수

• WINDOW FUNCTION SYNTAX ⭐️

  💡 윈도우 함수에는 OVER 문구가 키워드로 필수 포함된다.

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

  • WINDOW_FUNCTION: 기존 함수 or 윈도우 함수로 추가된 함수

  • ARGUMENTS (인수) : 함수에 따라 0~N개의 인수 지정

  • PARTITION BY절 : 전체 집합을 기준에 의해 소그룹으로 나눌 수 있음

  • ORDER BY절 : 순서를 지정할 기준 항목

  • WINDOWING절 : 함수의 대상이 되는 행 기준의 범위를 지정

    • ROWS/RANGE 중 하나를 선택하여 사용
      • ROWS : 행의 수를 기준으로 한 범위
      • RANGE : 값을 기준으로 한 범위

  • WINDOWING절의 사용 예

    -- 해당 파티션 내에서 앞의 한 행, 현재 행, 뒤의 한 행을 범위로 지정
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
     
    -- 해당 파티션 내에서 (현재 행의 값 - 50) ~ (현재 행의 값 + 150)을 범위로 지정
    RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING
     
    -- 현재 파티션의 첫 행부터 현재 행까지 지정
    RANGE UNBOUNDED PRECEDING

2. 그룹 내 순위 함수

💡 ORDER BY를 포함한 쿼리문에서 특정 항목에 대한 순위를 구하는 함수. 특정 파티션 내에서 순위를 구할 수도 있고 전체 데이터에 대한 순위를 구할 수도 있다.

-- RANK / DENSE_RANK / ROW_NUMBER 공통 형태
SELECT ...
RANK() OVER (
	[PARTITION BY partition_expression],
	ORDER BY sort_expression [ASC | DESC], ...
) ALIAS
FROM 테이블명

3. 일반 집계 함수 (집계 윈도우 함수)

• MAX / MIN 함수

  SELECT ...
  MAX(컬럼명) OVER (
      [PARTITION BY partition_expression],
      [ORDER BY sort_expression [ASC | DESC]]
  ) ALIAS
  FROM 테이블명;

• SUM / AVG 함수

  💡 PARTITION BY절만 명시한다면 그룹 영역별 최종 집계 값을 표기하지만, ORDER BY절과 함께 사용하면 누적 값을 표기한다. 또한 값이 동일한 경우 동시에 반영한다.

  SELECT ...
  SUM(컬럼명) OVER (
      [PARTITION BY partition_expression],
      [ORDER BY sort_expression [ASC | DESC]],
      [WINDOWING 절]
  ) ALIAS
  FROM 테이블명

• COUNT 함수

  💡 파티션이 지정되지 않고, WINDOWING 절의 범위가 지정되어 있다면 모든 건수를 대상으로 검사하게 된다.

4. 그룹 내 행 순서 함수 (SQL Server 미지원)

• FIRST_VALUE / LAST_VALUE 함수

  💡 각 파티션에서 가장 먼저(또는 가장 나중) 나온 값

• LAG / LEAD 함수

  💡 각 파티션에서 해당 행의 몇 번째 이전(또는 이후) 행의 값을 가져옴

  SELECT ...
  	LAG(컬럼, 1) OVER(ORDER BY sort_expression [ASC | DESC]) AS ALIAS1,
  	LEAD(컬럼, 1) OVER(ORDER BY sort_expression [ASC | DESC]) AS ALIAS2
  FROM 테이블명;

  • LAG(SAL, 1) = LAG(SAL) / LEAD(SAL, 1) = LEAD(SAL) → 디폴트 값 1
  • LAG(SAL, 2, 0) → 가져올 행이 없는 경우(NULL) 0으로 채워라.

5. 그룹 내 비율 함수

• RATIO_TO_REPORT 함수 (SQL Server 미지원)

  💡 파티션 내 전체 SUM(칼럼) 값에 대한 행별 백분율을 소수점으로 구할 수 있다. 결과 값은 > 0 & <= 1 의 범위이고, 개별 RATIO의 합은 1

  SELECT ...
  	RATIO_TO_REPORT(컬럼) OVER (PARTITION BY partition_expression) AS ALIAS
  FROM 테이블명;

• PERCENT_RANK 함수 (SQL Server 미지원)

  💡 행의 순서별 백분율을 구함. 가장 먼저 나온 행 = 0, 가장 나중 나온 행 = 1 / 동일 값은 작은 백분율을 중복 적용

• CUME_DIST 함수 (SQL Server 미지원)

  💡 현재 행에 대해, 현재 행보다 작거나 같은 건수에 대한 누적 백분율. 0 초과 1 이하의 값을 가짐 / 동일한 값은 큰 백분율 중복 적용

• NTILE 함수

  💡 파티션별 전체 건수를 N 등분한 결과

7) DCL

1. DCL 개요

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

2. 유저와 권한

• Oracle에서 제공하는 유저들

• 유저 생성과 시스템 권한 부여

  💡 데이터베이스에 접속했다고 해서 테이블, 뷰, 인덱스 등과 같은 오브젝트(OBJECT)를 생성할 수는 없다.

• OBJECT에 대한 권한 부여 (○ : Oracle / SQL Server 공통)

• ROLE을 이용한 권한 부여

  💡 많은 DBMS에서는 유저와 권한 사이에 중개역할을 하는 ROLE을 제공한다. 데이터베이스 관리자는 ROLE에 필요한 권한을 미리 부여해 놓고, 유저에게는 개별 권한이 아닌 ROLE을 부여하거나 회수함으로써 권한을 보다 쉽고 편리하게 관리할 수 있다.

  CREATE ROLE 롤명;
  GRANT [부여할 권한] TO 롤명;
  GRANT 롤명 TO 사용자;

  • ROLE의 개념 : ROLE에는 시스템 권한과 오브젝트 권한을 모두 부여할 수 있으며, ROLE은 유저에게 직접 부여될 수도 있고, 다른 ROLE에 포함하여 유저에게 부여될 수도 있다.

8) 절차형 SQL

1. 절차형 SQL 개요

2. PL/SQL 개요

• PL/SQL 특징
• PL/SQL 구조
• PL/SQL 기본 문법

3. T-SQL 개요

• T-SQL 특징
• T-SQL 구조
• T-SQL 기본 문법

4. Procedure의 생성과 활용

5. User Defined Function의 생성과 활용

6. Trigger의 생성과 활용

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