[SQLD] 2과목_2장. SQL 활용

제 2절. 집합 연산자

1) 일반 집합 연산자

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

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

- INTERSECT : 교집합(INTERSECTION)

- EXCEPT,MINUS : 차집합(DIFFERENCE)

- CROSS JOIN : 곱집합(PRODUCT)

 

2) 순수 관계 연산자

: 관계형 DB를 새롭게 구현

 

- SELECT -> WHERE

- PROJECT -> SELECT

- NATRUAL JOIN -> 다양한 JOIN

- DIVIDE -> 사용x

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

 

3) FROM 절 JOIN 형태

- INNER JOIN

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

 

- NATURAL JOIN

: 두 테이블 간의 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI JOIN 수행, NATURAL JOIN이 명시되면 추가로 USING, ON, WHERE 절에 서 JOIN 조건을 정의할 수 없다, SQL Sever는 지원x

 

- USING 조건절

: 같은 이름을 가진 칼럼들 중에서 원하는 칼럼에 대해 서만 선택적으로 EQUI JOIN을 할 수 있다, JOIN 칼럼에 대해서 ALIAS나 테이블 이름과 같은 접두사를 붙일 수 없다, SQL Server 지원x

 

- ON 조건절

: ON 조건절과 WHERE 조건절을 분리하여 이해가 쉬우며, 칼럼명이 다르더라도 JOIN 조건을 사용할 수 있는 장점이 있다, ALIAS나 테이블명 반드시 사용

 

- CROSS JOIN

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

 

- OUTER JOIN JOIN

: 조건에서 동일한 값이 없는 행도 반환 가능하다, USING이나 ON 조건절 반드시 사용해야 함

 

- LEFT OUTER JOIN

: 조인 수행시 먼저 표기된 좌측 테이블에 해당하는 데 이터를 읽은 후, 나중 표기된 우측 테이블에서 JOIN 대상 데이터를 읽어 온다. 우측 값에서 같은 값이 없 는 경우 NULL 값으로 채운다.

 

- RIGHT OUTER JOIN

: LEFT OUTER JOIN의 반대 FULL OUTER JOIN 조인 수행시 좌측, 우측 테이블의 모든 데이터를 읽어 JOIN하여 결과를 생성한다. 중복 데이터는 삭제한다.

 

- FULL OUTER GOIN

: 조인 수행시 좌측, 우측 테이블의 모든 데이터를 읽어 JOIN 하여 결과를 생성한다. (UNION 기능과 유사)

 

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

1) 계층형 질의

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

 

- START 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 : 현재 전개할 데이터의 루트 데이터를 표시한다. 단항 연산자이다.

 

2) 셀프 조인

: 동일 테이블 사이의 조인, FROM 절에 동일 테이블이 2번 이상 나타난다. 반드시 테이블 별칭을 사용해야 함

 

[예제]

SELECT E1.사원, E1.관리자, E2.관리자 차상위_관리자 
FROM 사원 E1, 사원 E2 
WHERE E1.관리자 = E2.사원 
ORDER BY E1.사원;

# 결과
# B A null
# C A null
# D C A
# E C A

 

제 4절. 서브쿼리

1) 서브쿼리 사용시 주의 사항

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

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

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

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

 

2) 서브쿼리 분류

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

• 비연관 서브쿼리: 서브쿼리가 메인쿼리 컬럼을 가지고 있지 않는 형태. 메인쿼리에 값을 제공하기 위한 목적
• 연관 서브쿼리: 서브쿼리가 메인쿼리 컬럼을 가지고 있는 형태. 일반적으로 메인쿼리가 먼저 수행되어 읽혀진 데이터를 서브쿼리에서 조건이 맞는지 확인하고자 할때 사용.

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

• 단일 행 서브쿼리: 실행 결과가 항상 1건 이하인 서브쿼리. 단일행 비교 연산자와 함께 쓰임(=, <, >, <=, <>)
• 다중 행 서브쿼리: 실행결과가 여러 건인 서브쿼리. 다중행 비교 연산자와 함께 쓰임(IN, ALL, ANY, SOME, EXISTS)
• 다중 컬럼 서브쿼리: 실행 결과로 여러 컬럼을 반환. 메인쿼리의 조건절에 여러 컬럼을 동시에 비교 가능

3) 뷰(View)

: 테이블은 실제로 데이터를 가지고 있는 반면, 뷰 는 실제 데이터를 가지고 있지 않다. 가상 테이블이라고도 함

 

- 뷰 사용 장점

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

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

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

CREATE VIEW V_PLAYER_TEAM AS SELECT P.PLAYER_NAME, P.POSITION, P.BACK_NO, P.TEAM_ID, T.TEAM_NAME 
FROM PLAYER P, TEAM T 
WHERE P.TEAM_ID = T.TEAM_ID;

 

제 5절. 그룹 함수

 

원본데이터 출처: https://androphil.tistory.com/166

1) ROLLUP 함수

: ROLLUP 에 지정된 Grouping Columns 의 List 는 Subtotal 을 생성하기 위해 사용되어지며, Grouping Columns 의 수를 N 이라고 했을 때 N+1 Level 의 Subtotal 이 생성된다. (그룹들의 합과 전체 총합)

ROLLUP 결과 출처:https://androphil.tistory.com/166

2) CUBE 함수

: ROLLUP 에서는 단지 가능한 Subtotal 만을 생성하였지만, CUBE 는 결합 가능한 모든 값에 대하여 다차원 집계를 생성한다. 

CUBE 결과 출처: https://androphil.tistory.com/166

3) GROUPING SETS 함수

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

GROUPING SETS 결과

제 6절. 윈도우 함수

1) 그룹 내 순위 함수

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

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

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

 

2) 일반 집계 함수

- SUM : 파티션별 윈도우의 합 구할 수 있다. ex)같은 매니저를 두고 있는 사원들의 월급 합

- MAX,MIN : 파티션별 윈도우의 최대,최소 값을 구할 수 있다. ex)같은 매니저를 두고 있는 사원들 중 최대 값

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

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

    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

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

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

 

3) 그룹 내 행 순서 함수

- FIRST_VALUE : 파티션별 윈도우에서 가장 먼저 나 온 값을 구한다.(SQL Server는 지원x)

- LAST_VALUE : 파티션별 윈도우에서 가장 나중에 나온 값을 구한다.(SQL Server 지원x)

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

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

 

4) 그룹 내 비율 함수

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

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

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

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

 

제 8절. 절차형 SQL

1) 절차형 SQL 개요

: SQL문의 연속적인 실행이나 조건에 따 른 분기처리를 이용하여 특정 기능을 수행하는 저장 모듈을 생성할 수 있다, Procedure, User Defined Function, Trigger 등이 있음

 

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

 

2) PL/SQL 개요

- PL/SQL 특징

• Block 구조로 되어있어 각 기능별로 모듈화 가능
• 변수, 상수 등을 선언하여 SQL 문장 간 값을 교환
• IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 한다.
• DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있다.
• PL/SQL은 Oracle에 내장되어 있으므로 호환성이 좋음
• 응용 프로그램의 성능을 향상시킨다.
• Block 단위로 처리 -> 통신량을 줄일 수 있다.

- PL/SQL 구조

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

CREATE Procedure Procedure_name;
REPLACE Procedure Procedure_name;
DROP Procedure Procedure_name;

 

3) T-SQL 개요

: 근본적으로 SQL Server 를 제어하기 위한 언어

CREATE Procedure schema_NAME.Procedure_name;

 

4) Trigger

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

CREATE Trigger Trigger_name;

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

: 프로시저는 BEGIN~END 절 내에 COMMIT, ROLLBACK과 같은 트랜잭션 종료 명령어 사용가능, DB 트리거는 BEGIN~END 절 내에 사용 불가