2024 #정보처리기사 필기요약 #3-1. SQL 응용, #3-2. SQL 활용

728x90

참고1. 노랑마킹은 시험에 나온 중요한 부분입니다.

참고2. 주황강조는 약어 혹은 중요한 내용입니다.

참고3. 회색마킹은 예시입니다.

제 3과목 데이터베이스 구축 > SQL 응용

절차형 SQL 작성

(1) 트리거(Trigger)

트리거 구성 (다이비컨 SE)

구성요소		설명
선언부	DECLARE	트리거 명칭 정의
이벤트부	EVENT	트리거 실행 타이밍, 이벤트 명시
시작/종료부	BEGIN/END	트리거 시작과 종료 표현, 블록으로 구성
제어부	CONTROL	순차처리, 비교 조건에 따라 블록 또는 문장 실행, 조건에 따라 반복 실행
SQL	SQL	DML을 주로 사용, 자주 사용되지 않지만 DDL(TRUNCATE 등) 사용
예외부	EXCEPTION	BEGIN~END 절의 SQL문 예외 발생 시 예외 처리 방법을 정의하는 처리부

트리거 작성 예시

CREATE TRIGGER T_STUDENT
BEFORE INSERT ON STUDENT
BEGIN
	IF (AGE < 19) THEN
    	RAISE_APPLICATION_ERROR(-20502, '미성년자 추가 불가');
    END IF;
END;

트리거 작성 시 주의사항

트리거 내에는 COMMIT, ROLLBACK 등의 트랜잭션 제어어(TCL) 사용 시 컴파일 에러 발생
트리거 실행 중 오류가 발생하게 되면 트리거 실행의 원인을 제공한 데이터 작업에도 영향을 준다.

(2) 이벤트(Event)

이벤트는 특정 시간에 특정한 쿼리, 프로시저, 함수 등을 실행시키는 기능이다.

이벤트 등록 구문

CREATE EVENT [IF NOT EXISTS] /* 해당 이벤트를 호출할 때 사용할 이름을 지정 */
ON SCHEDULE /* 이벤트의 실행시간과 간격을 지정 */
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT '주석']
DO
[BEGIN]
	[실행할 SQL문;]
[END]

이벤트 등록 구문 예시

CREATE EVENT T_TEST
	ON SCHEDULE
    	EVERY 50 SECOND
    DO INSERT INTO TEST
    	  SET REGDATE=NOW();

(3) 사용자 정의함수(User-Defined Function)

사용자 정의함수는 절차형 SQL을 활용하여 일련의 연산 처리 결과를 단일 값으로 반환할 수 있는 함수이다.
SQL 작성 시 데이터를 조작하는 INSERT, DELETE, UPDATE는 사용할 수 없다.
정보 은닉을 통해 캡슐화를 제공하는 데에도 많이 사용한다.
데이터 조작어(DML) 문장을 활용하여 사용자 정의함수를 호출한다.

사용자 정의함수 구성 (디비컨 SER)

DECLARE / BEGIN/END / CONTROL / SQL / EXCEPTION / RETURN

사용자 정의함수 예시

CREATE FUNCTION GET_AGE
	(V_BIRTH_YEAR IN CHAR(4))
IS
	V_THIS_YEAR CHAR(4);
BEGIN
	SELECT TO_CHAR(SYSDATE, 'YYYY')
      INTO V_THIS_YEAR
      FROM DUAL;
      
    RETURN V_THIS_YEAR
           - V_BIRTH_YEAR + 1;
END;

(4) SQL(Structured Query Language) 문법

SQL 문법은 데이터베이스를 접근하고 조작하는 데 필요한 표준 언어를 활용할 수 있게 해주는 규칙이다.

SQL 문법의 분류 (정조제) [2020년 3회]

분류	설명
데이터 정의어(DDL)	CREATE, ALTER, DROP, TRUNCATE 다양한 응용 프로그램과 데이터베이스가 서로 인터페이스를 할 수 있는 방법을 제공하는 기능
데이터 조작어(DML)	SELECT, INSERT, UPDATE, DELETE 사용자와 데이터베이스 사이의 인터페이스를 위한 수단을 제공하는 기능
데이터 제어어(DCL)	GRANT, REVOKE DBA가 사용하는 제어용 언어 작성예시 GRANT UPDATE ON STUDENT TO SIMUING; REVOKE UPDATE FROM STUDENT;

데이터 정의어(DDL) 대상 (도스테뷰인)

DDL 대상	설명
도메인 (Domain)	하나의 속성이 가질 수 있는 원자값들의 집합
스키마 (Schema)	데이터베이스의 구조, 제약조건 등의 정보를 담고 있는 기본적인 구조
테이블 (Table)	데이터 저장 공간
뷰 (View)	하나 이상의 물리 테이블에서 유도되는 가상의 테이블
인덱스(Index)	검색을 빠르게 하기 위한 데이터 구조

데이터 제어어(DCL) 기능 (보무병회)

데이터 보안 / 무결성 유지 / 병행수행 제어 / 회복

WHERE 조건 연산자 종류

구분	연산자
비교	=,<>,<,<=,>,>=
범위	[A] BETWEEN [B] AND [C]: A는 B보다 크거나 같고 C보다 작거나 같다
집합	IN, NOT IN
패턴	LIKE
NULL	IS NULL, IS NOT NULL
복합조건	AND, OR, NOT

LIKE와 같이 사용하는 와일드 문자

와일드 문자	설명	예시	예시결과
+	문자열 연결	'연결된' + ' ' + '문자열'	'연결된 문자열' 문자열 검색
%	0개 이상 문자열 일치	LIKE '노란색%'	'노란색'으로 시작하는 문자열 검색
		LIKE '%노란색'	'노란색'으로 끝나는 문자열 검색
		LIKE '%노란색%'	'노란색'이 포함된 문자열 검색
[ ]	1개 문자 일치	'[0-8]%'	0-8 사이 숫자로 시작하는 문자열 검색
[ ^ ]	1개 문자 불일치	'[^0-8]%'	0-8 사이 숫자로 시작하지 않는 문자열 검색
_	특정 위치의 1개 문자 일치	'_동%'	두 번째 위치에 '동'이 들어가는 문자열 검색

응용 SQL 작성

집계 함수 [2020년 3회]

집계 함수	설명
COUNT	줄 수를 구하는 함수
SUM	합계를 구하는 함수
AVG	평균을 구하는 함수
MAX	최댓값을 구하는 함수
MIN	최솟값을 구하는 함수
STDDEV	표준편차를 구하는 함수
VARIAN	분산을 구하는 함수

순위 함수

순위 함수	설명
RANK	순위를 구하는 함수 (2위,2위,2위,5위,6위...)
DENSE_RANK	레코드의 순위를 계산하는 함수, 동일 순위는 후순위로 넘어감 (1위,2위,2위,3위,4위...)
ROW_NUMBER	레코드의 순위를 계산하는 함수, 동일 순위는 동일 순위로 부여 (1위,2위,3위,4위,5위,...)

OLAP(On-Line Analytical Processing)

OLAP은 사용자가 동일한 데이터를 여러 기준을 이용하는 다양한 방식으로 바라보면서 다차원 데이터 분석을 할 수 있도록 도와주는 의사결정 지원 시스템이다.
OLAP의 연산은 Roll-Up, Drill-Down, Drill-Up, Slicing, Dicing, Pivoting 이 있다. [2020년 3회]
윈도우 함수를 OLAP함수라고도 한다.

윈도우 함수의 분류 (집순행비)

집계 함수
순위 함수
행순서 함수
비율 함수

그룹 함수(Group Function)

그룹 함수는 테이블의 전체 행을 하나 이상의 컬럼을 기준으로 컬럼 값에 따라 그룹하하여 그룹별로 결과를 출력하는 함수이다.
그룹 함수의 유형에는 ROLLUP, CUBE, GROUPING SETS 이 있다.

오류 처리(Error Handling)

오류 처리는 프로그램 코드상의 오류나 프로시저 실행 시 예외나 에러가 발생했을 때, 문제를 해결하고 의미 있는 에러 메시지를 부여하는 과정이다.

핸들러 선언 구문

핸들러를 선언하기 위해서는 DECLARE HANDLER 문을 사용한다.

제 2과목 소프트웨어 개발 > SQL 활용

관계형 데이터 모델(Relation Data Model)

데이터 간의 관계를 기본 키와 이를 참고하는 외래 키로 표현한다.
테이블 간 관계를 1:1, 1:N, M:N 관계로 목적에 맞게 표현한다.

테이블 관련 용어

용어	설명
튜플(Tuple) / 행(Row)	테이블 내의 행, 레코드(Record)
애트리뷰트(Attribute) / 열(Column)	테이블 내의 열
식별자(Identifier)	구분할 수 있는 논리적 개념
카디널리티(Cardinality)	튜플(Tuple)의 수
차수(Degree)	애트리뷰트(Attribute)의 수
도메인(Domain)	하나의 속성이 가질 수 있는 원자값들의 집합

CASECADE

참조하는 테이블까지 연쇄적으로 제거하는 옵션

RESTRICT

참조 중이면 제거하지 않는 옵션

트랜잭션(Transaction)

일련의 연산 집합으로 데이터베이스의 상태를 변환시키기 위하여 논리적 기능을 수행하는 하나의 작업 단위

트랜잭션의 특성 (ACID) (중요!!)

특성	설명
원자성(Atomicity)	트랜잭션 연산은 데이터베이스에 모두 반영되든지 아니면 전혀 반영되지 않아야 함
일관성(Consistency)	트랜잭션이 그 실행을 성공적으로 완료하면 언제나 일관성 있는 데이터베이스 상태로 변환
격리성(Isolation)	트랜잭션 실행 중 실행하는 연산의 중간 결과를 다른 트랜잭션이 접근 불가
영속성(Durability)	성공적으로 완료된 트랜잭션의 결과는 시스템이 고장 나더라도 영구적으로 반영되어야 함

트랜잭션 연산

커밋(Commit) / 롤백(Rollback)

트랜잭션의 상태 (활부완실철)

활동 / 부분완료 / 완료 / 실패 / 철회 상태

트랜잭션 제어(TCL; Transaction Control Language)

트랜잭션 제어 언어는 트랜잭션의 결과를 허용하거나 취소하는 목적으로 사용되는 언어이다.

트랜잭션 제어(TCL) 명령어

COMMIT / ROLLBACK / CHECKPOINT

병행 제어(일관성 주요 기법)

병행 제어는 다수 사용자 환경에서 여러 트랜잭션을 수행할 때, 데이터베이스 일관성 유지를 위해 상호작용을 제어하는 기법이다.

병행 제어의 목적

데이터베이스의 공유를 최대화한다.
시스템의 활용도를 최대화한다.
데이터베이스의 일관성을 유지한다.
사용자에 대한 응답시간을 최소화한다.

병행 제어 미보장 시 문제점 (갱현모연)

갱신 손실(Lost Update)
현황 파악 오류(Dirty Read)
모순성(Inconsistency)
연쇄복귀(Cascading Rollback)

병행 제어 기법의 종류 (로 낙타다)

로킹(Locking)
낙관적 검증
타임 스탬프 순서(Time Stamp Ordering)
다중버전 동시성 제어(MVCC; Multi Version Concurrency Control)

로킹(Locking)의 특징 (중요!! 설명 외우기)

데이터베이스, 파일, 레코드 등은 로킹 단위가 될 수 있다.
로킹 단위가 작아지면 데이터베이스 공유도, 로킹 오버헤드가 증가하고, 병행성 수준이 낮아진다. [2020년 3회]
한꺼번에 로킹할 수 있는 객체의 크기를 로킹 단위라고 한다.

회복 기법(영속성 주요 기법)

회복(Recovery) 기법은 트랜잭션을 수행하는 도중 장애로 인해 손상된 데이터베이스를 손상되기 이전의 정상적인 상태로 복구시키는 작업이다.

회복 기법 종류 (회로체크)

로그 기반 회복 기법 [2020년 3회]
- 지연 갱신 회복 기법
- 즉각 갱신 회복 기법
체크 포인트 회복 기법
그림자 페이징 회복 기법

제 2과목 소프트웨어 개발 > SQL 활용 > 고급 SQL 작성

(1) 뷰(View)

뷰는 논리 테이블이다.
뷰는 저장장치 내에 물리적으로 존재하지 않는다.
뷰 테이블은 ALTER문으로 변경이 불가능하다. 변경이 필요할 경우 DROP하고 CREATE한다.
뷰가 정의된 기본 테이블이 제거되면 뷰도 자동적으로 제거된다.

뷰의 특징

논리적 데이터 독립성 제공 / 데이터 조작 연산 간소화 / 보안 기능(접근제어) 제공 / 뷰 변경 불가

뷰의 장점과 단점 [2020년 3회]

장점

논리적 독립성 제공
사용자 데이터 관리 용이
데이터 보안의 용이

단점

뷰 자체 인덱스 불가
뷰 정의 변경 불가
데이터 변경 제약 존재

뷰 생성 명령어

CREATE VIEW 뷰 이름 컬럼 목록 AS 데이터 조회 쿼리;

(2)인덱스(Index)

인덱스는 데이터를 빠르게 찾을 수 있는 수단이다.
인덱스는 자동으로 생성되지 않는다.
기본키(PK; Primary Key) 컬럼은 자동으로 생성된다.

인덱스 종류

유형	설명
순서 인덱스 (Ordered Index)	데이터가 정렬된 순서로 생성되는 인댁스, B-Tree 알고리즘 활용(오름차순/내림차순 지정가능)
해시 인덱스 (Hash Index)	해시 함수에 의해 직접 데이터에 키 값으로 접근하는 인덱스
비트맵 인덱스 (Bitmap Index)	각 컬럼에 적은 개수 값이 저장된 경우 선택하는 인덱스
함수기반 인덱스 (Functional Index)	수식이나 함수를 적용하여 만든 인덱스
단일 인덱스 (Singled Index)	하나의 컬럼으로만 구성한 인덱스, 주 사용 컬럼이 하나일 경우 사용
결합 인덱스 (Concatenated Index)	두 개 이상의 컬럼으로 구성한 인덱스, WHERE 조건으로 사용하는 빈도가 높은 경우 사용
클러스터드 인덱스 (Clustered Index)	기본 키(PK) 기준으로 레코드를 묶어서 저장하는 인덱스

인엑스 스캔 방식 (범전단생)

범위 스캔 / 전체 스캔 / 단일 스캔 / 생략 스캔

(3) 집합 연산자(Set Operator)

집합 연산자는 두 개 이상의 질의 결과를 하나의 결과로 결합하는 연산자이다.

집합 연산자 유형 (유유인마)

UNION / UNION ALL / INTERSECT / MINUS

(4) 조인(Join)

조인은 두 개 이상의 테이블을 연결하여 데이터를 검색하는 방법이다.

조인 유형

조인	유형	설명
논리적 조인 (내외교셀)	내부 조인 (Inner Join)	공통 존재 컬럼의 값이 같은 경우를 추출
	외부 조인 (Outer Join)	Left Outer Join: 왼쪽 테이블의 모든 데이터와 오른쪽 테이블의 동일 데이터를 추출
		Right Outer Join: 오른쪽 테이블의 모든 데이터와 왼쪽 테이블의 동일 데이터를 추출
		Full Outer Join: 양쪽의 모든 데이터를 추출
	교차 조인 (Cross Join)	조인 조건이 없는 모든 데이터 조합을 추출
	셀프 조인 (Self Join)	자기 자신에게 별칭을 지정한 후 다시 조인
물리적 조인 (네소해)	중첩 반복 조인 (Nested-Loop Join)	2개 이상의 테이블에서 하나의 집합을 기준으로 순차적으로 상대방 Row를 결합하여 원하는 결과를 조합하는 조인 방식
	정렬 합병 조인 (Sort-Merge Join)	조인의 대상 범위가 넓을 경우 발생하는 임의 접근(Random Access)을 줄이기 위한 경우나 연결고리에 마땅한 인덱스가 존재하지 않을 경우 해결하기 위한 조인 방식
	해시 조인 (Hash Join)	해싱 함수를 활용하여 테이블 간 조인을 수행하는 방식

스키마(Schema)의 정의

데이터베이스의 구조와 제약 조건에 관한 전반적인 명세(Specification)를 기술(Description)한 메타데이터(Meta-Data)의 집합이다.

외부 스키마	사용자나 응용 프로그래머가 각 개인의 입장에서 필요로 하는 데이터베이스의 논리적 구조를 정의한 것
개념 스키마	데이터베이스의 전체적인 논리적 구조로서, 모든 응용 프로그램이나 사용자들이 필요로 하는 데이터를 종합한 조직 전체의 데이터베이스로, 하나만 존재
개념 스키마	개체 간의 관계와 제약 조건을 나타내고, 데이터베이스의 접근 권한, 보안 및 무결성 규칙에 관한 명세를 정의
내부 스키마	물리적 저장장치의 입장에서 본 데이터베이스 구조로서, 실제로 데이터베이스에 저장될 레코드의 형식을 정의하고 저장 데이터 항목의 표현 방법, 내부 레코드의 물리적 순서 등을 나타냄 [2020년 3회]

스키마의 정의 참고

2018/01/03 - [IT License] - 스키마의 정의 (Schema Definition)

스키마의 정의 (Schema Definition)

스키마의 정의 (Schema Definition) 디비(DB database)용어 중 가장 기본개념인 스키마... 이해하기쉽게 정리된 도식화가 없는 듯하여... 만들어버렸습니다 ^^ ■ 스키마의 3계층 외부 스키마(External Schema)

simuing.tistory.com