[정보처리기사 실기] 07. SQL 응용 (빈칸버전) ★

이 게시글은 정보처리기사 실기 준비를 위해 수제비책과 ToDev님 블로그를 참조했으며

N번째 시험을 겪으며 중요하다고 느껴지는 개념을 위주로 담았고

이번 시험이 마지막이길 기원하며 정리한 글입니다.

개념의 설명을 보고 어떤 개념인지 유추하고 맞춰보며 학습하도록 빈칸을 쳐 놓았습니다.

드래그를 통해 답을 맞춰보실 수 있습니다.

---

01 데이터베이스 기본

▶ 트랜잭션

하나의 논리적 기능을 정상적으로 수행하기 위한 작업의 기본 단위

 

▶ 트랜잭션의 특성

⦁  원자성(Atomicity) : 분해가 불가능한 작업의 최소단위, 연산 전체가 성공 또는 실패

⦁  일관성(Consistency) : 트랜잭션이 실행 성공 후 항상 일관된 데이터베이스 상태를 보존

⦁  격리성(Isolation) : 트랜잭션 실행 중 연산의 중간 결과를 다른 트랜잭션이 접근 불가

⦁  영속성(Durability) : 성공 완료된 트랜잭션의 결과는 영속적으로 데이터베이스에 저장

 

▶ 트랜잭션 제어언어 TCL

⦁  커밋(COMMIT) : 트랜잭션을 메모리에 영구적으로 저장

⦁  롤백(ROLLBACK) : 오류가 발생했을 때, 오류 이전의 특정 시점 상태로 되돌려주는 제어어

⦁  체크포인트(CHECKPOINT) : 롤백을 위한 시점을 지정

 

▶ 병행제어(Concurrency Control) - 일관성 주요 기법

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

 

▶ 병행제어 기법 종류

⦁  로킹(Locking) : 일관성과 무결성을 유지하기 위한 트랜잭션의 순차적 진행을 보장하는 직렬화 기법

⦁  낙관적 검증 : 일단 트랜잭션을 수행하고, 트랜잭션 종료 시 검증을 수행

⦁  타임 스탬프 순서 : 타임 스탬프를 부여해 부여된 시간에 따라 트랜잭션 수행

⦁  다중버전 동시성 제어(MVCC) : 타임스탬프를 비교해 직렬가능성이 보장되는 적절한 버전을 선택해 접근하도록 함

 

▶ 고립화 수준(Isolation Level) – 격리성 주요 기법

다른 트랜잭션이 현재의 데이터에 대한 무결성을 해치치 않기 위해 잠금을 설정하는 정도

 

▶ 고립화 수준 종류

⦁  Read Uncommitted : 한 트랜잭션에서 연산 중인 데이터를 다른 트랜잭션이 읽는 것 허용

⦁  Read Committed : 한 트랜잭션의 연산이 완료된 후 읽기 가능

⦁  Repeatable Read : 트랜잭션 종료 시까지 해당 데이터에 대한 갱신, 삭제 제한

⦁  Serializable Read : 특정 데이터 영역을 순차적으로 읽을 때, 해당 데이터 영역 전체에 대한 접근 제한

 

▶ 회복(Recovery) 기법 – 영속성 주요 기법

트랜잭션 수행 도중 장애로 인해 손상된 데이터베이스를 손상 이전의 정상적인 상태로 복구시키는 작업

 

▶ 데이터 회복(Data Recovery)

데이터 베이스 운영 중 예기치 못한 장애가 발생한 경우, 데이터베이스를 장애 발생 이전의 일관된 상태로 복원시키는 기법

 

▶ 회복 기법의 종류

⦁  로그 기반 회복 기법 

- 지연 갱신 회복 기법(Deferred) : 트랜잭션이 완료된 후에 데이터베이스에 기록

- 즉각 갱신 회복 기법(Immediate) : 트랜잭션 수행 중 갱신 결과를 바로 DB에 반영

⦁  체크 포인트 회복 기법 : 장애 발생 이전의 상태로 복원

⦁  그림자 페이징 회복 기법 : DB 트랜잭션 수행 시 복제본을 생성하여 데이터베이스 장애   시 이를 이용해 복구하는 기법

데이터베이스 회복 기법의 구성도

▶ REDO/UNDO

REDO  : 장애 발생 시, 진행된 트랜잭션에 대해 로그 기록을 이용하여 재실행하여 복원하는 기법

UNDO :  장애 발생 시, 모든 변경 사항, Commit안된 트랜잭션 작업을 취소하여 일관성 보장 기법

 

▶ 데이터 정의어(DDL)

데이터과 같은 데이터 구조를 정의하는데 사용되는 명령어

 

▶ DDL의 대상

⦁  도메인(Domain) : 하나의 속성이 가질 수 있는 원자 값들의 집합

⦁  스키마(Schema) : 데이터베이스의 구조, 제약조건 등의 정보를 담고 있는 기본적인 구조

        - 외부, 개념, 내부 스키마

⦁  테이블 : 데이터 저장 공간

⦁  뷰 : 가상의 테이블

⦁  인덱스 : 검색을 빠르게 하기 위한 데이터 구조

 

▶ 테이블

⦁  테이블 관련 용어 : 

- 튜플(행), 애트리뷰트(열), 식별자,

- 카디널리티 : 튜플의 개수

- 차수 : 애트리뷰트의 개수

- 도메인 : 하나의 애트리뷰트가 취할 수 있는 같은 타입의 원자값 들의 집합

⦁  테이블 관련 DDL

- CREATE TABLE : 테이블을 생성하는 명령어

- ALTER TABLE : 테이블을 수정하는 명령어

- DROP TABLE : 테이블을 삭제하는 명령어

- TRUNCATE TABLE : 테이블 내의 데이터들을 삭제하는 명령어

 

▶ 데이터조작어(DML)

데이터베이스에 저장된 자료들을 입력, 수정, 삭제, 조회하는 언어

⦁ SELECT 명령어

SELECT [ALL | DISTINCT] 속성명1, 속성명2,....

FROM 테이블명1,...

[WHERE 조건]

[GROUP BY 속성명1, ...]

[HAVING 그룹조건]

[ORDER BY 속성 [ASC | DESC]];

⦁ INSERT 명령어

INSERT INTO 테이블명(속성명1,...)

VALUES (데이터1,...);

⦁ UPDATE 명령어

UPDATE 테이블명

SET 속성명 = 데이터,...

WHERE 조건;

⦁ DELETE 명령어

DELETE FROM 테이블명

WHERE 조건;

▶ 데이터제어어(DCL)

데이터베이스 관리자가 데이터 보안, 무결성 유지, 병행 제어, 회복을 위해 관리자(DBA)가 사용하는 제어용 언어

⦁ GRANT : 관리자가 사용자에게 데이터베이스에 대한 권한을 부여하는 명령어

GRANT 권한 ON 테이블 TO 사용자;

⦁ REVOKE : 관리자가 사용자에게 부여했던 권한을 회수하기 위한 명령어

REVOKE 권한 ON 테이블 FROM 사용자;

 

02 응용 SQL 작성하기

▶ 데이터 분석 함수의 종류

⦁  집계 함수 : 여러 행 또는 테이블 전체 행으로부터 하나의 결과 값을 반환하는 함수

⦁  그룹 함수 : 소그룹 간의 소계 및 중계 등의 중간 합계 분석 데이터를 산출하는 함수, 컬럼 값에 따라 그룹화해 그룹별로 결과를 출력하는 함수

- ROLLUP, CUBE, GROUPING SETS

⦁  윈도 함수 : DB를 사용한 온라인 분석 처리 용도로 사용하기 위해서 표준 SQL에 추가된 기능, OLAP(온라인 분석 처리, 의사결정 지원 시스템) 함수라고도 함

- 순위함수, 행 순서 함수, 그룹 내 비율 함수

 

 

03 절차형 SQL 활용하기

▶ 절차형 SQL의 종류 : 프로시저, 사용자 정의함수, 트리거

 

▶ 프로시저(Procedure)

일련의 쿼리들을 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합

⦁  프로시저 구성(디비컨SET) :
DECLARE(선언부)
BEGIN/END
CONTROL(제어부)
SQL
EXCEPTION(예외부)
TRANSACTION(실행부)

 

▶ 사용자 정의 함수

일련의 SQL처리를 수행하고, 수행 결과를 단일 값으로 반환할 수 있는 절차형 SQL

⦁  사용자 정의 함수 구성(디비컨 SER) : RETURN(반환부)가 추가됨

 

▶트리거(Trigger)

데이터베이스 시스템에서 삽입, 삭제, 갱신 등의 이벤트가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL

⦁  트리거의 구성(디이비컨 SE) : EVENT(이벤트부)가 추가됨

 

 

04 데이터 조작 프로시저 최적화

▶ 옵티마이저(Optimizer)

SQL을 가장 빠르고 효율적으로 수행할 최적의 처리경로를 생성해주는 DBMS 핵심엔진

 

▶ 옵티마이저 유형

⦁  규칙기반 옵티마이저(RBO) : 통계 정보가 없는 상태에서 사전 등록된 규칙에 따라 질의 실행계획을 선택

⦁  비용기반 옵티마이저(CBO) : 통계 정보로부터 모든 접근 경로를 고려한 질의 실행계획을 선택

 

▶ (SQL)힌트

실행하려는 SQL문에 사전에 정보를 주어 SQL문 실행에 빠른 결과를 가져오는 효과를 만드는 기법

⦁  옵티마이저의 실행 계획을 원하는 대로 변경할 수 있게 함

⦁  옵티마이저가 항상 최선의 실행 계획을 수립할 수 없어 힌트를 사용