[정보처리기사 실기] 과목7 - SQL 응용, 오답노트

 

Chapter ① 데이터베이스 기본

 

• 트랜잭션의 특성

특성	설명	주요기법
원자성 (Atomicity)	- 트랜잭션을 구성하는 연산 전체가 모두 정상적으로 실행되거나 모두 취소되어야 하는 성질 - 트랜잭션의 연산 전체가 성공 또는 실패(All or Nothing)되어야 하는 성질	- Commit/Rollback - 회복성 보장
일관성 (Consistency)	- 시스템이 가지고 있는 고정요소는 트랜잭션 수행 전과 트랜잭션 수행 완료 후의 상태가 같아야 하는 성질	- 무결성 제약조건 - 동시성 제어
격리성=고립성 (Isolation)	- 동시에 실행되는 트랜잭션들이 서로 영향을 미치지 않아야 한다는 성질	- Read Uncommitted - Read Committed - Repeatable Read - Serializable
영속성 (Durability)	- 성공이 완료된 트랜잭션의 결과는 영속적으로 데이터 베이스에 저장되어야 하는 성질	- 회복기법

 

---

 

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

명령어	핵심	설명
커밋(COMMIT)	트랜잭션 확정	트랜잭션을 메모리에 영구적으로 저장하는 명령어
롤백(ROLLBACK)	트랜잭션 취소	트랜잭션 내역을 저장 무효화시키는 명령어
체크포인트(CHECKPOINT)	저장 시기 설정	ROLLBACK을 위한 시점을 지정하는 명령어

 

---

 

• 로킹(Locking)

• 같은 자원을 액세스하는 다중 트랜잭션 환경에서 DB의 일관성과 무결성을 유지하기 위해 트랜잭션의 순차적 진행을 보장하는 직렬화 기법
• 데이터베이스, 파일, 레코드 등은 로킹 단위가 될 수 있음
• 로킹 단위가 작아지면 데이터베이스 공유도가 증가
• 로킹 단위가 작아지면 로킹 오버헤드가 증가
• 한꺼번에 로킹할 수 있는 객체의 크기를 로킹 단위라고 함

 

---

 

• SELECT 명령어

• 단순 SELECT의 경우 전체 테이블의 튜플을 검색한다.
• SELECT 뒤에 명시하지 않은 경우 ALL로 인식

 

---

 

• 로그 기반 회복 기법

지연 갱신 회복 기법 (Deferred Update)	트랜잭션이 완료되기 전까지 데이터베이스에 기록하지 않는 기법
즉각 갱신 회복 기법 (Immediate Update)	트랜잭션 수행 중 갱신 결과를 바로 DB에 반영하는 기법

 

---

 

• CREATE INDEX 문법

        CREATE [UNIQUE] INDEX 인덱스명 ON 테이블명(컬럼명1, 컬럼명2, ...);

예) - CREATE INDEX 사번인덱스 ON 사원(사번);

        → 사원 테이블의 사번 컬럼에 대해 사번인덱스라는 인덱스 명으로 인덱스 생성

예) - CREATE INDEX IDX_NAME ON STUDENT(NAME);

        → STUDENT 테이블의 NAME 속성에 대해 IDX_NAME라는 인덱스 명으로 인덱스 생성

 

---

 

• 인덱스(Index)의 개념

• 인덱스는 검색 연산의 최적화를 위해 데이터베이스 내 값에 대한 주소 정보로 구성된 데이터 구조이다.
• 인덱스는 데이터를 빠르게 찾을 수 있는 수단으로서, 테이블에 대한 조회 속도를 높여 주는 자료 구조이다.
• 인덱스는 테이블의 특정 레코드 위치를 알려 주는 용도로 사용한다.
• 데이터베이스 파일 구조는 순차 방법, 인덱스 방법, 해싱 방법이 있다.

파일 구조	설명
순차 방법	- 레코드들의 물리적 순서가 레코드들의 논리적 순서와 같게 순차적으로 저장하는 방법
인덱스 방법	- 인덱스가 가리키는 주소를 따라 원하는 레코드에 접근할 수 있도록 하는 방법 - <키값, 주소>의 쌍으로 구성
해싱 방법	- 키값을 해시 함수(Hash Fuction)에 대입시켜 계산한 결과를 주소로 사용하여 레코드에 접근할 수 있게 하는 방법

 

---

 

• DDL 대상

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

 

---

 

• UPDATE 명령어

        UPDATE 테이블명 SET 속성명=데이터, ... WHERE 조건;

예) - UPDATE 회원 SET 전화번호 = '010-14' WHERE 회원번호 = 'N4';

        → 회원 테이블의 회원번호가 'N4'인 튜플의 전화번호 속성을 '010-14'로 갱신

 

---

 

• 내부 조인 문법

        SELECT A.컬럼1, A.컬럼2, ..., B.컬럼1, B.컬럼2, ... FROM 테이블1 A [INNER] JOIN 테이블2 B ON 조인조건 [WHERE 검색조건];

예) - SELECT 학생정보.학번, 학생정보.이름, 학과정보.학과, 학과정보.지도교수 FROM 학생정보 JOIN 학과정보 ON 학생정보.학과=학과정보.학과;

        → 학생정보 테이블과 학과정보 테이블의 학과가 같은 것끼리 내부 조인

 

---

 

• 데이터 제어어(DCL)의 유형

유형	동작	설명
GRANT	사용 권한 부여	관리자(DBA)가 사용자에게 데이터베이스에 대한 권한을 부여하는 명령어
REVOKE	사용 권한 취소	관리자(DBA)가 사용자에게 부여했던 권한을 회수하기 위한 명령어

 

---

 

• 트랜잭션(Transaction)의 개념

        인가받지 않은 사용자로부터 데이터를 보장하기 위해 DBMS가 가져야 하는 특성이자, 데이터베이스 시스템에서 하나의 논리적 기능을 정상적으로 수행하기 위한 작업의 기본 단위이다.

 

---

 

• CREATE TABLE 문법

        CREATE TABLE  테이블명

        (

        컬럼명 데이터타입 PRIMARY KEY, -- 기본 키 설정

        컬럼명 데이터타입 FOREIGN KEY REFERENCES 참조테이블(기본 키), -- 외래 키 설정

        컬럼명 데이터타입 UNIQUE,

        컬럼명 데이터타입 NOT NULL,

        컬럼명 데이터타입 CHECK(조건식) -- 제약 조건 설정

        컬럼명 데이터타입 DEFAULT 값

        );

예) - CREATE TABLE 사람 (이름 VARCHAR(10), 성별 CHAR(1) CHECK(성별 = 'M' OR 성별 = 'F');

        → 사람이라는 테이블의 이름, 성별 컬럼을 각각 VARCHAR(10)과 CHAR(1) 데이터 타입으로 선언하고 성별은 'M'과 'F' 값만 가질 수 있는 제약조건을 건 테이블 생성

 

---

 

• ALTER TABLE 문법

        ALTER TABLE 테이블명 ADD 컬럼명 데이터타입 [제약조건];

예) - ALTER TABLE 사원 ADD 전화번호 VARCHAR(11);

        → 사원 테이블에 VARCHAR(11) 타입으로 전화번호라는 컬럼을 추가

 

---

 

• CREATE VIEW 문법

        CREATE VIEW  뷰이름 AS 조회쿼리;

예) - CREATE VIEW 사원뷰 AS SELECT 사번, 이름 FROM 사원 WHERE 성별 = 'M';

        → 사원 테이블에 성별 값이 'M'을 가진 사번, 이름 컬럼으로 구성된 '사원뷰'라는 뷰를 생성하는 쿼리

 

---

 

• IS NOT NULL

        컬럼이 NULL이 아닌 데이터 조회할 경우 WHERE 절에서 IS NOT NULL 구문을 사용한다.

예) - SELECT 주소 FROM 학생 WHERE 주소 IS NOT NULL;

        → 학생 테이블에서 주소 컬럼이 NULL이 아닌 주솟값을 출력하는 쿼리

 

---

 

• 왼쪽 외부 조인 문법

        SELECT A.컬럼1, A.컬럼2, ..., B.컬럼1, B.컬럼2, ... FROM 테이블1 A LEFT [OUTER] JOIN 테이블2 B ON 조인조건 [WHERE 검색조건];

예) - SELECT A.책번호, A.책명, B.책번호, B.가격 FROM 도서 A LEFT JOIN 도서가격 B ON A.책번호 = B.책번호;

        → 도서 테이블과 도서가격 테이블의 책번호 컬럼을 기준으로 왼쪽 외부 조인

 

---

 

• INSERT 문법

        INSERT INTO 테이블명(속성명1, ...) VALUES (데이터1, ...);

예) - INSERT INTO EMPLOYEE(NAME, AGE, SALARY) VALUES('홍길동', 24, 300);

        → 직원 테이블에 사원명이 홍길동, 나이가 24, 급여가 300인 직원을 테이블에 삽입하는 쿼리

 

---

 

• DELETE 문법

        DELETE FROM 테이블명 WHERE  조건;

예) - DELETE FROM EMPLOYEE WHERE SALARY <= 300;

        → 직원 테이블에서 급여가 300이하인 직원들을 삭제하는 쿼리

 

---

 

• GRANT 문법

        GRANT 권한 ON 테이블 TO 사용자

예) - GRANT UPDATE ON 학생 TO 장길산;

        → 사용자 장길산에게 '학생' 테이블에 대해 UPDATE할 수 있는 권한을 부여하는 쿼리

 

 

Chapter ② 응용 SQL 작성하기

 

• GROUP BY, HAVING 구문

예) - SELECT 과목이름, MIN(점수) AS 최소점수, MAX(점수) AS 최대점수 FROM 성적 GROUP BY 과목이름 HAVING AVG(점수) >= 90;

        → 과목별 점수의 평균이 90 이상인 과목이름, 최소점수, 최대점수를 구하는 SQL문

        → 과목별 점수의 평균을 구하기 위해서는 GROUP BY 절과 HAVING 절을 이용

 

---

 

• GROUP BY 구문

예) - SELECT 부서, SUM(급여) AS 직책별 급여 FROM 급여 GROUP BY 부서, 직책;

        → 급여 테이블에서 부서와 직책별 급여의 합계를 구하는 쿼리

        → GROUP BY 절은 속성값을 그룹으로 분류하고자 할 때 사용

 

---

 

• GROUP BY, HAVING 구문

예) - SELECT 이름 FROM 학생 GROUP BY 이름 HAVING AVG(성적) > 4.0;

        → 학생 테이블에서 평균 성적이 4.0을 초과하는 학생의 이름을 출력하는 쿼리

        → 이름에 따라 평균 성적을 구하려는 쿼리를 구하기 위해서는 GROUP BY 절에 '이름' 컬럼을 추가

        → HAVING 절은 GROUP BY에 의해 분류한 후 그룹에 대한 조건을 지정할 때 사용

        → 평균 성적이 4.0을 초과하는지 확인하기 위해 HAVING 절에 평균 성적이 4.0보다 크다라는 조건의 쿼리를 작성

 

---

 

• ROLLUP 함수

예) - SELECT 학교명, 학년, SUM(인원) AS 인원 FROM 학교 GROUP BY ROLLUP(학교명, 학년)

        → ROLLUP에 의해 지정된 컬럼은 소계 등 중간 집계 값을 산출하기 위해 사용

        → 소계 집계 대상(학교명, 학년)이 되는 컬럼을 ROLLUP 뒤에 기재

 

---

 

• DENSE_RANK 함수

예) - SELECT 이름, 점수, DENSE_RANK() OVER(ORDER BY 점수 DESC) AS 등수 FROM 학생;

        → 동일 순위의 레코드 존재 시에도 후순위를 넘어가지 않는 경우 DENSE_RANK 함수를 사용

        → ORDER BY 뒤에는 SORT할 컬럼을 입력(어떤 열을 어떤 순서대로 순위를 매길지를 지정)

 

▼ 순위 함수

순위 함수	설명
RANK	- 특정 항목(컬럼)에 대한 순위를 구하는 함수 - 동일 순위의 레코드 존재 시 후순위는 넘어감(2가 3개인 레코드인 경우: 2위, 2위, 2위, 5위, 6위, ...)
DENSE_RANK	- 레코드의 순위를 계산 - 동일 순위의 레코드 존재 시에도 후순위를 넘어가지 않음(2위가 3개인 레코드인 경우: 2위, 2위, 2위, 3위, 4위, ...)
ROW_NUMBER	- 레코드의 순위를 계산 - 동일 순위의 값이 존재해도 이와 무관하게 연속 번호를 부여(2위가 3개인 레코드인 경우: 2위, 3위, 4위, 5위, 6위, ...)

 

▼ 순위 함수 문법

    SELECT 함수명(파라미터) OVER ([PARTITION BY 컬럼1, ...]) [ORDER BY 컬럼A, ...] FROM 테이블명;

 

 

Chapter ③ 절차형 SQL 활용하기

 

• 절차형 SQL 종류

종류	설명
프로시저 (Procedure)	일련의 쿼리들을 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합
사용자 정의 함수 (User-Defiend Function)	일련의 SQL 처리를 수행하고, 수행 결과를 단일 값으로 반환할 수 있는 절차형 SQL
트리거 (Trigger)	데이터베이스 시스템에서 삽입, 갱신, 삭제 등의 이벤트가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL

 

---

 

• 프로시저와 사용자 정의 함수에서 파라미터를 입력받는 모드

IN	운영체제에서 프로시저로 값을 전달하는 모드
OUT	프로시저에서 처리된 결과를 운영체제로 전달하는 모드
INOUT	IN과 OUT의 두 가지 기능을 동시에 수행하는 모드

 

 

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

 

• 옵티마이저(Optimizer)의 개념

• 옵티마이저는 SQL을 가장 빠르고 효율적으로 수행할 최적의 처리경로를 생성해주는 DBMS 내부의 핵심엔진이다.
• 옵티마이저가 생성한 SQL 처리경로를 실행계획(Execution Plan)이라고 부른다.

 

    ▶ 옵티마이저의 힌트 사용

• SQL 성능 개선의 핵심 부분으로 옵티마이저의 실행 계획을 원하는 대로 변경할 수 있게 한다.
• 옵티마이저가 항상 최선의 실행 계획을 수립할 수 없어 명시적인 힌트를 통해 실행계획을 변경한다.

 

 

Reference

• https://www.yes24.com/Product/Goods/105812550

2022 수제비 정보처리기사 실기 (1권+2권 합본세트) - YES24