[과목 II] 제 2장 SQL 활용

집합 연산자

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

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

일반 집한 연산자

UNION

여러 개의 쿼리 결과를 합치는 연산자로 중복된 영역은 제외하여 합친다.(합집합, 정렬 O)

UNION ALL

여러 쿼리 결과를 합치는 연산자로 중복된 영역 모두 포함하여 합친다.(합집합, 정렬 X)

INTERSECT

여러 개의 SELECT 결과에서 공통된 부분만 결과로 추출한다.(교집합)

MINUS

선행 SELECT 결과에서 다음 SELECT 결과와 겹치는 부분을 제외한 나머지 부분을 추출한다.(차집합)

CROSS JOIN

카테시안 곱(Cartesian Product)라고도 하며, 조인되는 테이블의 각 행들이 모두 매핑된 데이터가 검색되는 조인 방법이다.

검색되는 데이터 수는 '행의 칼럼 수 * 또 다른 행의 칼럼 수'로 나온다. (곱집합)

ALIAS는 처음 테이블, 정렬은 마지막 테이블을 기준으로 한다.

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

1. SELECT → WHERE 절로 구현한다.

2. PROJECT → SELECT 절로 구현한다.

3. NATURAL JOIN → 다양한 JOIN으로 구현한다.

4. DIVIDE → 사용 안 함.

FROM 절 JOIN 형태

1. INNER JOIN

2. NATURAL JOIN

3. USING 조건절

4. ON 조건절

5. CROSS JOIN

6. OUTER JOIN

INNER JOIN 

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

NATURAL JOIN

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

NATURAL JOIN이 명시되면, 추가로 USING, ON, WHERE 절에서 JOIN 조건을 정의할 수 없다.

SQL Server는 지원하지 않는다.

USING 조건절

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

JOIN 칼럼에 대해서 ALIAS나 테이블 이름과 같은 접두사를 붙일 수 없다.

SQL Server는 지원하지 않는다.

ON 조건절

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

ALIAS나 테이블 명을 반드시 사용해야 한다.

CROSS JOIN = 카테시안 곱(Cartesian Product)

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

OUTER JOIN(LEFT, RIGHT, FULL)

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

USING이나 ON 조건절을 반드시 사용해야 한다.

SQL 식에선 (+) 안 붙은 쪽으로 JOIN 한다.

LEFT OUTER JOIN

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

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

RIGHT OUTER JOIN

LEFT OUTER JOIN의 반대이다.

FULL OUTER JOIN

좌측과 우측 테이블의 모든 데이터를 읽어 JOIN 하여 결과를 생성한다.

단, 중복 데이터는 삭제한다.

---

계층형 질의(HIERARCHICAL QUERY)

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

계층형 데이터란 동일한 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 의미한다. (EX. 상위 사원(관리자)과 하위 사원)

 

START WITH

계층 구조 전개의 시작 위치를 지정한다. 즉 루트 데이터를 지정한다.(액세스)

CONNECT BY

다음에 전개될 자식 데이터를 지정한다.

PRIOR

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

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

반대는 역방향 전개이다.

NOCYCLE

동일한 데이터가 전개되지 않는다.

ORDER SIBLINGS BY

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

WHERE

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

 

LEVEL

루트 데이터이면 1, 그 하위 데이터이면 2, 리프 데이터까지 1씩 증가한다.

CONNECT_BY_ISLEAF

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

SYS_CONNECT_BY_PATH

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

CONNECT_BY_ROOT

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

셀프 조인

한 테이블 내 두 칼럼이 연관 관계가 있을 때 동일 테이블 사이의 조인이다.

FROM 절에 동일한 테이블이 2번 이상 나타난다.

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

서브 쿼리

하나의 SQL문 안에 포함되어 있는 또 다른 SQL문으로 알려지지 않은 기준을 이용한 검색에 사용된다.

• 서브 쿼리는 괄호로 감싸서 사용한다.
• 서브 쿼리는 단일 행 또는 복수 행 비교 연산자와 함께 사용 가능하다.
• 단일 행 비교 연산자는 서브 쿼리의 결과가 반드시 1건 이하여야 하고 복수 행 비교 연산자는 결과 건수에 상관없다.
• 서브 쿼리에선 ORDER BY를 사용하지 못한다.
• SELECT, FROM, WHERE, HAVING, ORDER BY, INSERT-VALUES, UPDATE-SET 절에 사용 가능하다.

단일행 비교 연산자

=, <, >, <> 등

다중행 비교 연산자

IN, ALL, ANY, SOME 등

스칼라 서브 쿼리

한 행, 한 칼럼만을 반환하는 서브 쿼리이다.

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

비연관 서브 쿼리

서브 쿼리가 메인 쿼리 칼럼을 갖지 않고, 메인 쿼리 값을 제공하는 것이 목적이다.

연관 서브 쿼리

서브 쿼리가 메인 쿼리 칼럼을 갖는다.

반환 데이터에 따른 서브 쿼리 종류

단일행 서브 쿼리: 실행 결과 1건 이하

다중행 서브 쿼리: 실행 결과 여러 건

다중 칼럼 서브 쿼리: 실행 결과 칼럼 여러 개

---

뷰(VIEW)

SELECT 쿼리의 실행 결과를 화면에 저장한 논리적 가상 테이블이다.

실제 테이블과 다르게 실질적 데이터를 저장하고 있진 않지만 사용자는 테이블을 사용하는 것과 동일하게 사용 가능하다.

뷰 사용 장점

독립성

테이블 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 된다.

편리성

복잡한 질의를 뷰로 생성함으로써 관련 질의를 단순하게 작성할 수 있다. 또한 해당 형태의 SQL문을 자주 사용할 때 뷰를 이용하면 편하게 사용할 수 있다.

보안성

직원의 급여 정보와 같이 숨기고 싶은 정보가 존재한다면, 뷰를 생성할 때 해당 칼럼을 빼고 생성함으로써 사용자에게 정보를 감출 수 있다.

인라인 뷰(INLINE VIEW)

FROM 절에서 사용되는 서브 쿼리를 인라인 뷰 라고 한다.

SQL문이 실행될 때만 임시적으로 생성되는 동적인 뷰이기 때문에 데이터베이스에 해당하는 정보가 저장되지 않는다.

---

ROLLUP

인자로 전달받은 그룹 중 가장 먼저 지정한 그룹별로 추가적 집계 결과를 반환한다.

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

인수 순서에 주의해야 한다.

CUBE

인자로 지정된 그룹들로 가능한 모든 조합 별로 집계한 결과를 반환한다.

결과에 대한 정렬이 필요한 경우는 ORDER BY 절에 명시적으로 정렬 칼럼이 표시가 되어야 한다.

ROLLUP에 비해 시스템 부하가 심하다는 단점이 있다.

2^N

CUBE(A, B) = GROUPING SETS(A, B, (A, B), ())

GROUPING SETS(집합 연산자 사용과 동일)

다양한 소계 집합을 만들 수 있는데, GROUPING SETS에 표시된 인수들에 대한 개별 집계를 구할 수 있으며, 이때 표시된 인수들 간의 계층 구조인 ROLLUP과는 달리 평등한 관계이므로 인수의 순서가 바뀌어도 결과는 같다.

결과에 대한 정렬이 필요한 경우는 ORDER BY 절에 명시적으로 정렬 칼럼이 표시되어야 한다.

---

윈도 함수

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

윈도우 함수는 결과에 대한 함수 처리이기 때문에 결과 건수는 줄어들지 않는다.

순위 관련 함수

RANK

ORDER BY를 포함한 QUERY 문에서 특정 항목(칼럼)에 대한 순위를 구하는 함수이며, 동일한 값에 대해서는 동일한 순위를 부여하게 된다.

같은 등수가 있을 때는 다음 등수 값을 건너뛴다.(1, 2, 2, 4)

DENSE_RANK

RANK 함수와 흡사하나, 동일한 순위를 하나의 등수로 간주한다.

같은 순위일 때도 순위 값을 건너뛰지 않는다.(1, 2, 2, 3)

ROW_NUMBER

RANK나 DENSE_RANK함수가 동일 값에 대해서 동일한 순위를 부여하는데 반해, 동일한 값이라도 고유한 순위를 부여한다.

즉 ORDER BY 절에 의해 정렬된 데이터에 동일 값이 존재하더라도 유일한 순위를 부여하는 함수로서 데이터 그룹 내에 유일한 순위를 추출할 때 사용하는 함수이다.

ROWNUM과는 관계가 없다.

집계 관련 함수(그룹 함수)

SUM

해당 칼럼 값들의 총합을 반환한다.

파티션 별 윈도의 합을 구할 수 있다.

EX) 같은 팀장을 두고 있는 직원들의 월급 합

AVG

해당 칼럼 값들의 평균을 반환한다.

원하는 조건에 맞는 데이터에 대한 통계 값이다.

EX) 같은 팀장 내에서 앞의 사번과 뒤의 사번의 월급 평균

MAX/MIN

그룹의 최댓값과 최솟값을 반환한다.

파티션 별 윈도의 최대, 최소 값을 구할 수 있다.

COUNT

테이블 조건을 만족하는 행의 개수를 반환한다.

조건에 맞는 데이터에 대한 통계 값이다.

행 순서 관련 함수

FIRST_VALUE: 파티션 별 윈도우의 처음 값을 가져오는 함수이다.

LAST_VALUE: 파티션 별 윈도우의 마지막 값을 가져오는 함수이다.

LAG: 파티션 별 윈도에서 이전 몇 번째 행의 값을 가져오는 함수이다.

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

LAG와 LEAD는 SQL Server에선 지원하지 않는 함수이다.

비율 관련 함수

RATIO_TO REPORT: 파티션 내 전체 SUM에 대한 행 별 칼럼 값의 백분율을 소수점으로 구할 수 있는 함수이다. >0, <=1

PERCENT_RANK: 파티션 별 윈도우에서 처음 값을 0, 마지막 값을 1로 하여 행의 순서별 백분율을 구한다. 0>=, <=1

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

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

---

DCL

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

ORACLE과 SQL Server의 사용자 아키텍처 차이

ORACLE

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

SQL Server

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

시스템 권한

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

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

 

GRANT: 시스템 권한 부여

REVOKE: 시스템 권한 회수

CASCADE: 객체 권한 회수

CASCADE CONSTRAINTS: 객체 권한에서 사용된 참조 무결성 제한을 같이 삭제

ROLE

DBMS 관리자가 사용자 별로 권한을 관리해야 하는 부담과 복잡함을 줄이기 위하여 다양한 권한을 그룹으로 묶어 관리할 수 있도록 사용자와 권한 사이에서 중개 역할을 수행하는 것을 의미한다.

---

절차형 SQL

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

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

저장 모듈

PL/SQL 문장을 DB 서버에 저장하여 사용자와 애플리케이션 사이에서 공유할 수 있도록 만든 일종의 SQL 컴포넌트 프로그램이며, 독립적으로 실행되거나 다른 프로그램으로부터 실행될 수 있는 완전한 실행 프로그램이다.

PL/SQL 특징

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

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

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

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

T-SQL

근본적으로 SQL Server를 제어하는 언어이다. 

CREATE Procedure shema_Name.Procedure_name

TRIGGER

특정한 테이블에 INSERT, UPDATE, DELETE와 같은 DML문이 수행되었을 때, 데이터베이스에서 자동으로 동작하도록 작성된 저장 프로그램이다.

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

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