2020 정보처리기사 - SQL응용

정보처리기사가 이번년도에 NCS 기반으로 전면 개편되었다길래 어렵게 나올 줄 알았는데 ... 2020 정보처리기사 실기 1회 기출문제를 보아하니 대부분의 문제가 정의 위주로 쉽게 나왔다. 

그래서 정의 정리나 할 겸 대충 써본다. 

 

1) 절차형 SQL

 

 

ㆍ프로시저

절차형 SQL을 활용하여 특정기능을 수행할 수 있는 트랜잭션 언어.

 

ㆍ프로시저 구성 : D B CON S E T

DECRARE [선언부] - BEGIN/END [시작/종료부]  - CONTROL [제어부] - SQL - EXCEPTION [예외부] - TRANSACTION [실행부]

 

ㆍ프로시저 선언부 [MODE]

IN : 운영체제에서 프로시저로 값 전달

OUT : 프로시저에서 처리된 결과를 운영체제로 전달.

INOUT : IN 과 OUT 기능 동시에 수행.

 

 

ㆍ프로시저 SQL

SELECT [데이터조회], ALTER [데이터생성], UPDATE [데이터변경], DELETE [데이터삭제]

 

ㆍ프로시저 예외부

EXCEPTION

  WHEN 조건 THEN

      SET 명령어

 

ㆍ프로시저 실행부

COMMIT : 저장, ROLLBACK : 되돌리기

 

ㆍ프로시저 호출문

EXCUTE

---

ㆍ사용자 정의함수

절차형 SQL을 활용하여 일련의 SQL 처리를 수행하고, 수행결과를 단일 값으로 반환.

 

ㆍ사용자 정의함수 구성 : D B CON S E R

DECRARE [선언부] - BEGIN/END [시작/종료부]  - CONTROL [제어부] - SQL - EXCEPTION [예외부] -RETURN [반환부]

 

 

 

---

 

ㆍ트리거 

특정 테이블에 데이터 변경 이벤트가 발생하면 DBMS에서 자동적으로 실행되도록 구현된 프로그램.

 

ㆍ종류

행 트리거 : 데이터 변화가 생길 때마다 실행  

문장 트리거 : 트리거에 의해 단 한 번 실행

 

ㆍ트리거 구성 : D E B CON S E

DECRARE [선언부] - EVENT [이벤트부] - BEGIN/END [시작/종료부]  - CONTROL [제어부] - SQL - EXCEPTION [예외부]

 

ㆍ트리거 선언부 : OR REPLACE

기존 트리거 존재 시에 현재 컴파일 하는 내용으로 덮어 씀.

 

ㆍ이벤트 순서

BEFORE, AFTER : DML과 트리거가 수행되는 순서의 전/후 관계 결정.

 

ㆍ이벤트 유형

INSERT [행 삽입], UPDATE [행 변경] , DELETE [행 삭제]

 

---

2) 응용 SQL

 

 

ㆍ데이터 분석 함수의 종류

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

그룹 함수 : 소그룹 간의 소계, 중계 등의 중간 합계 분석 데이터를 산출하는 함수 (* 소계 : 소그룹의 합계 )

윈도 함수 (OLAP 함수) : DB를 이용한 온라인 분석 처리 용도로 사용하기 위해 표준 SQL에 추가된 기능. 

 

ㆍ집계 함수 종류

COUNT [수],

SUM [합계],

AVG [평균],

MAX [최곳값],

MIN [최솟값],

STDDEV [컬럼 간의 표준 편차],

VARIAN [컬럼 간의 분산]

 

ㆍ그룹 함수 유형

ROLLUP : 소계 등 중간 집계값을 산출하기 위한 그룹 함수

CUBE : 결합 가능한 모든 값에 대해 다차원 집계를 생성하는 그룹 함수.

GROUPING SETS : 집계 대상 컬럼들에 대한 개별 집계를 구할 수 있는 그룹 함수.

 

ㆍ윈도 함수의 분류

1. 순위 함수 : 레코드의 순위를 계산하는 함수.

RANK : 동일 순위의 레코드 존재 시 후순위는 넘어간다 (ex. 2위가 3개인 레코드인 경우 - 1, 2, 2, 2, 5위)

DENSE_RANK : 동일 순위 레코드 존재 시에도 후순위 넘어가지 않음. (ex. 1, 2, 2, 2, 3위)

ROW_NUMBER : 동일 순위의 값이 존재해도 무관하게 연속 번호 부여 (ex. 1, 2, 3, 4, 5위)

 

2. 행순서 함수 : 레코드에서 가장 먼저 나오거나 뒤에 나오는 값과 이전/이후에 나오는 값들을 출력하는 함수.

FIRST_VALUE : 파티션별 윈도에서 가장 먼저 나오는 값을 찾음. (집계 함수의 MIN과 동일한 결과 출력)

LAST_VALUE : 파티션별 윈도에서 가장 늦게 나오는 값을 찾음. (집계 함수의 MAX와 동일)

LAG : 파티션별 윈도에서 이전 로우의 값 반환. 

LEAD : 파티션별 윈도에서 이후 로우의 값 반환.

 

3. 그룹 내 비율 함수 : 백분율을 보여주거나 행의 비율과 관련된 통계를 보여주는 함수.

RATIO_TO_REPORT : 그룹에 대해 합을 기준으로 각 로우의 상대적 비율을 반환하는 함수. (0~1)

PERCENT_RANK : 그룹에 대해 제일 먼저 나오는 것을 0, 제일 늦게 나오는 것을 1로 하여, 값이 아닌 행의 순서별 백분율을 구하는 함수.

---

ㆍ트랜잭션

인가받지 않은 사용자로부터 데이터를 보장하기 위해 DBMS가 가져야 하는 특성. DBMS에서 논리적 기능을 수행하기 위한 작업 기본단위.

 

ㆍ트랜잭션 특성 (ACID)

Atomicity (원자성) : 작업의 최소단위. 연산이 하나라도 실패할 경우 전체 취소.

Consistency (일관성) : 트랜잭션 성공 후 일관된 db상태를 보존해야함.

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

Durability (영속성) : 완료된 트랜잭션의 결과는 영속적으로 db에 저장.

 

ㆍJDBC (Java DataBase Connectivity 자바 데이터베이스 연결)

자바언어를 사용하여 개발하며, SQL을 사용하여  DBMS에 질의하고 데이터를 조작하는 API를 제공.

 

 

ㆍMyBatis

SQL MAPPING 기반 오픈 소스 Access framework로 DBMS에 질의하기 위한 SQL 쿼리를 별도의 XML파일로 분리하고, Mapping 을 통해서 SQL을 실행한다.

 

ㆍMyBatis 장점

JDBC코드를 단순화,

SQL을 거의 그대로 사용,

SPRING기반 프레임워크와 통합 기능 제공,

우수한 성능.

 

 

ㆍ 데이터 제어어 (DCL : Data Control Language)

DBA가 사용하는 제어용 언어로, 데이터보안, 무결성유지, 병행제어 , 회복을 위해 사용.

 

ㆍDCL의 유형

GRANT (사용 권한 부여) : DBA가 사용자에게 DB에 대한 권한을 부여하는 명령어.

REVOKE (사용 권한 취소) : DBA가 사용자에게 권한을 회수하기 위한 명령어.