[SQL] SQLD 모의고사 빵가루

고3 때 사회탐구 공부하던 (호랑이 담배피던..) 시절 모든 모의고사의 선지를 하나하나 뜯어서 봤던 게 사탐 1등급의 비결이었다!
그래서 SQLD 실전 모의고사의 선지로 나온 문장들을 뜯어 먹어보려 한다.
그 전에 시험 직전에 볼 간단 개념 정리부터 레쓰고

1과목 | 데이터 모델링의 이해

SQL 종류

• DDL (Data Definition Language)
  • CREATE, ALTER, DROP, RENAME, TRUNCATE
• DML (Data Manipulation Language)
  • INSERT, DELETE, UPDATE, SELECT
• DCL (Data Control Language)
  • GRANT, REVOKE
• TCL (Transaction Control Language)
  • COMMIT, ROLLBACK, SAVEPOINT

식별자

• 최소성, 대표성, 유일성, 불변성
• 대표성 여부에 따라 주식별자(유일, 최소, 대표) / 보조식별자(유일, 최소)
• 생성 여부에 따라 내부식별자(스스로 생성) / 외부식별자(타 엔터티와 관계)
• 속성 여부에 따라 단일식별자 / 복합식별자(두 개 이상 속성)
• 대체 여부에 따라 본질식별자 / 인조식별자(인위적으로 생성)

엔터티

• 유형, 무형에 따라 유형, 개념, 사건
• 발생 시점에 따라 기본(키), 중심, 행위

속성

• 특성에 따라 기본(본래 속성), 설계(데이터 모델링 과정, 유일값 부여), 파생(다른 속성에 의해)

정규화

• 1: 속성의 원자성 확보, PK 설정
• 2: 기본키 두 개 이상의 속성이면, 부분함수종속성 제거
• 3: 컬럼 간 종속성 (이행함수종속성) 제거
• BCNF: 후보키가 기본키 종속시키면 분해
• 4: 여러 칼럼이 한 칼럼 종속시킬 때, 다중값 종속성 (다치함수종속성) 제거
• 5: 조인에 의해 종속성 발생되면 분해

2과목 | SQL 기본 및 활용

NULL 함수

• NVL(컬럼, 0): 컬럼 NULL이면 0으로
• NVL2(컬럼, 1, 0): 컬럼 NULL이 아니면 1, NULL이면 0
• NULLIF(컬럼1, 컬럼2): 컬럼1 = 컬럼2면 NULL, 컬럼1 != 컬럼2면 컬럼1
• COALESCE(컬럼1, 컬럼2, 컬럼3...): NULL이 아닌 첫 번째 컬럼 반환

숫자형 함수

• SIGN(n): n이 양수인지(1), 음수인지(-1), 0인지 구별
• MOD(n, m): n을 m으로 나눈 나머지
• TRUNC(n, m): 소수점 m에서 절삭

DECODE

• IF문 처럼 사용
• DECODE(empno, 1000, a, b): empno = 1000이면 a, 아니면 b

Subquery

• FROM구: Inline View
• SELECT문: Scala Subquery (반드시 한 행에 한 칼럼 반환)
• WHERE구: Subquery
• 단일행 조회: 비교연산자(=, >, <...)
• 다중행 조회: IN, ALL, ANY, EXISTS
• Correlated Subquery: Main쿼리 데이터 받아서 실행

그룹 함수

• ROLLUP
  • GROUP BY 칼럼의 subtotal값
  • GROUP BY 구에 칼럼이 2개 이상이면, 순서에 따라 결과가 달라질 수도
• GROUPING SETS
  • GROUP BY의 칼럼 순서에 관계없이 다양한 소계
  • 칼럼 각각 개별적으로 합계 반환
• CUBE
  • 합산 가능한 모든 집계 계산 (모든 경우의 수)
• GROUPING
  • ROLLUP, CUBE, GROUPING SETS에서 생성된 합계 구분
  • 계산된 합계면 1, 아니면 0

순위 함수

• RANK
  • 동일 순위는 동일 값
  • ex) 1등, 2등(2명), 4등...
• DENSE_RANK
  • 동일 순위는 하나의 건수
  • ex) 1등, 2등(2명), 3등...
• ROW_NUMBER
  • 동일 순위여도 고유 순위 부여
  • ex) 1등, 2등, 3등, 4등...

행 순서 함수

• LAG(컬럼, n)
  • 컬럼의 n번째 이전의 행
• LEAD(컬럼, n, 디폴트)
  • 컬럼의 n번째 뒤의 행 값 반환, 없으면 디폴트값
  • 디폴트값 지정 안 할 시 기본값은 1

비율 관련 함수

• CUME_DIST
  • 현재 행보다 작거나 같은 건수의 누적 백분율
• PERCENT_RANK
  • 파티션 제일 먼저 나온 걸 0으로, 제일 늦게 나온 걸 1로 두고 행의 순서별 백분율
• NTILE
  • 전체 건수를 인자 값으로 등분한 결과
  • 나머지가 있을 경우 위부터 차례로 배분
• RATIO_TO_REPORT
  • SUM()에 대한 행 별 칼럼값의 백분율 (소수점까지)

트랜잭션

• 원자성 : 연산을 전부 실행하거나 안하거나. 일부 실행은 트랜잭션의 기능 X
• 일관성 : 트랜잭션 실행 후에도 일관성 유지
• 고립성 : 실행 중 부분 연산 결과를 다른 트랜잭션이 접근 X
• 지속성 : 실행 성공 후 그 결과의 영구적 보장

---

실전 모의고사 4개 선지 뜯어먹기😋

속성

• 엔터티를 설명하고 인스턴스의 구성요소가 된다.
• 주소는 복합 속성이다.

엔터티

• 기본 엔터티는 키 엔터티라고도 하며 발생 시점에 따라 엔터티를 분류할 때 독립적으로 생성되는 엔터티이다.
• 한 개의 엔터티에는 두 개 이상의 속성과 두 개 이상의 인스턴스가 있다.

도메인

• 도메인별로 데이터 타입과 길이를 지정한다.
• 각 엔터티 속성에 도메인을 할당한다.
• 공통으로 발생하는 명사는 한 개의 도메인으로 생성한다.
• 속성을 명사로 분리한다.
• 속성명과 도메인명이 반드시 동일할 필요는 없다.
• 릴레이션에서 모든 속성의 도메인은 원자적이어야 한다.

스키마

• 외부 스키마는 사용자 관점 또는 사용자 뷰를 표현하며, 업무상 관련이 있는 데이터만 접근한다.
• 개념 스키마는 사용자 전체 집단의 데이터베이스 구조를 표현하며, 전체 데이터베이스 내 모든 데이터에 관한 규칙과 의미를 묘사한다.
• 내부 스키마는 데이터베이스의 물리적 저장구조로, 운영체제와 하드웨어에 종속적이다.
• 내부 스키마는 데이터 저장 구조, 레코드의 구조, 필드의 정의, 색인과 해시를 생성한다.

식별자

• 어떤 업무에서 자주 이용되는 것을 주식별자로 한다.
• 주식별자를 구성하는 속성의 수는 유일성을 만족하는 최소의 수가 되어야 한다.
• 주식별자의 속성 수는 최소여야 한다.(최소성 만족)
• 기본키는 후보키 중 엔터티를 대표하는 키이다.
• 후보키는 최소성과 유일성을 만족한다.
• 슈퍼키는 유일성은 만족하지만 최소성(Not null)은 만족하지 못한다.
• 내부 식별자(ex.부서코드, 주문번호, 종목코드..)는 엔터티 스스로 생성되는 식별자이고, 외부 식별자(ex. 계좌의 회원ID)는 다른 엔터티 간의 관계에 의해 만들어진다.
• 대체 여부에 따라, 업무에 의해 만들어지는 식별자는 본질 식별자이고 인위적으로 만들어진 식별자는 인조 식별자이다.

모델링

• 개념적 모델링은 추상화 수준이 높고 업무에 대한 엔터티를 도출하고 관계를 정의하는 단계이다.
• 성능, 데이터 저장 방법 등을 고려하는 모델링은 물리적 모델링이다.
• 데이터베이스 모델링 단계는 개념적, 논리적, 물리적 단계로 수행하고 상세화된다.
• 개념적 모델링 이후에 구체적인 업무 중심의 모델링과 정규화를 수행하는 단계가 논리적 모델링 단계이다.

정규화/반정규화

• 반정규화 수행 시 뷰 혹은 클러스터링 기법을 적용해야 하는 단계는 '다른 방법 결정'이다.
• 업무 혹은 물리적 서버가 다른 경우에 동일한 테이블 구조를 중복하여 원격 조인을 제거하는 반정규화 기법은 '중복 테이블 추가'이다.
• '중복 테이블 추가' 반정규화 기법은 데이터 무결성 및 보안 관리의 문제점을 유발한다.
• 데이터를 조회할 때 디스크 입출력량이 많아 성능이 저하되는 경우, 반정규화를 실행한다.
• 여러 개의 테이블 조인으로 인해 성능 저하가 예상되는 경우, 반정규화를 실행한다.
• 칼럼의 합계 및 평균 등을 계산하여 읽을 때 성능이 저하될 것이 예상되는 경우, 반정규화를 실행한다.
• 기본키를 제외하고 칼럼 간에 종속성이 발생하면 테이블을 분할하는 것은 제 3정규화다.
• 정규화를 수행하지 않고 엔터티에 데이터를 입력할 때 불필요한 데이터를 같이 입력하거나 삭제하면 다른 데이터까지 같이 삭제되는 문제를 '이상현상'이라고 한다. (삽입 이상, 갱신 이상, 삭제 이상)

카디널리티

• 카디널리티는 두 엔터티에서 관계에 참여하는 수이다. (1:1, 1:N, M:N)
• 카디널리티의 계산식은 선택도 * 전체 레코드 수이다.

분산 데이터베이스

• 지역 데이터베이스에서 사용자 Query를 실행하고 빠르게 응답할 수 있다.
• 여러 개의 데이터베이스가 존재하므로 관리하기 어렵다.
• 각 데이터에 대한 무결성 관리가 어렵다.
• 데이터베이스 무결성을 확보하기 위한 방법으로는 제약조건, 트리거, 애플리케이션이 있다.
• 중앙집중적인 보안 관리가 어렵고 처리 비용이 증가한다.
• 장애 시에 다른 데이터베이스가 서비스하게 하여 가용성이 좋아진다.
• 분산 데이터베이스의 투명성에는 분할 투명성, 위치 투명성, 지역사상 투명성, 중복 투명성이 있다.
• 분할 투명성은 하나의 엔터티가 여러 개의 분산 데이터베이스에 저장되어 있지만 사용자는 그 내용을 알 필요가 없다.
• 여러 데이터베이스에 중복되어서 데이터가 저장되는 것이 중복 투명성이다.
  여러 개의분산

파티션 기법

• List Partition은 파티셔닝할 항목을 관리자가 직접 지정하는 방법으로 제품에 대해 파티션을 생성한다.
• List Partition은 데이터 보관 주기에 따라 쉽게 데이터를 삭제할 수 없다.
• Range Partition은 데이터 값의 범위를 기준으로 파티션을 수행한다.
• Range Partition은 날짜 및 숫자처럼 연속된 값을 기준으로 만드는 기법이다.
• Hash Partition은 해시 함수를 적용하여 파티션을 수행한다.
• Composite Partition은 범위와 해시를 복합적으로 사용해서 파티션을 수행한다.

DDL

• 테이블에 존재하는 칼럼의 데이터 유형, 디폴트값, NOT NULL 제약조건을 변경하는 명령어는 ALTER문의 modify이다.
• DROP TABLE 테이블명 CASCADE CONSTRAINT 옵션은 해당 테이블과 관계가 있었던 참조 제약 조건에 대해서도 삭제를 수행한다.
• TRUNCATE TABLE로 특정 행을 삭제할 수 없다.(DELETE FROM으로 가능)
• TRUNCATE구는 테이블 용량이 줄어들고, 인덱스 등도 모두 삭제된다.
• TRUNCATE구로 삭제한 데이터는 다시 되돌릴 수 없다.
• DROP은 테이블의 구조를 포함한 테이블 자체를 삭제한다.
• DELETE구 실행 시 데이터는 삭제되어도, 테이블 용량은 줄어들지 않는다.

DCL

• WITH GRANT OPTION으로 사용자가 연쇄적으로 권한을 부여할 경우, 먼저 권한을 할당 받은 사용자의 권한이 취소되면 연쇄 취소된다.
  • WITH ADMIN OPTION의 경우 해당 사용자의 권한만 취소됨
• DDL, DCL, TCL은 비절차형 언어이고, DML은 절차형 언어이다.

프로시저, 트리거

• 프로시저는 COMMIT, ROLLBACK이 가능하지만, 트리거는 불가능하다.
• 프로시저와 트리거 모두 생성하면 소스코드와 실행코드가 생성되고, 소스코드는 데이터베이스 내에 저장되어 있다.
• 프로시저와 트리거는 모두 CREATE구로 생성한다.
• 프로시저는 execute 명령어로 실행하지만 트리거는 생성 후 자동으로 실행된다.

인덱스

• Clustered Index를 생성하면 물리적으로 정렬되기 때문에 빠르게 연속적인 데이터 블록을 검색할 수 있다.
• B-Tree Index는 분기를 수행하는 브랜치 블록, 인덱스를 구성하는 칼럼값을 가지는 리프 블록으로 구성된다.
• B-Tree Index는 Equal 조건뿐만 아니라 BETWEEN, > 과 같은 연산자로 검색하는 범위 검색에도 사용할 수 있다.
• Bitmap Index는 내부 구조에 해당 데이터가 있으면 1, 없으면 0으로 저장한다.
• Bitmap Index는 정보계와 같은 Data warehouse에서 사용하고, 많은 양의 데이터를 검색할 때 사용한다.
• 테이블에 대해 입력, 수정, 삭제 작업을 할 때엔 데이터와 함께 인덱스도 같이 변경되므로 속도가 느려진다.

테이블 제약조건

• UNIQUE : 칼럼에 중복된 값을 허용하진 않지만, NULL 값은 포함한다.
• PRIMARY KEY : 기본키로 테이블당 한 개만 생성 가능하고 자동으로 인덱스가 생성된다.

그룹 함수

• CUBE는 결합 가능한 모든값에 대해 다차원 집계를 생성한다.
• CUBE(DEPTNO, JOB)과 동일한 것은 GROUPING SETS(DEPTNO, JOB, (DEPTNO, JOB), ())이다.
• CUBE는 ORDER BY를 사용해 정렬할 수 있다.
• CUBE는 ROLLUP에 비해 시스템 연산을 많이 발생시킨다.

문자열

• 문자열 내부에 이 있는 것을 검색할 때, `LIKE %@% ESCAPE @`
  • _ 는 단일 문자 와일드카드이므로, 특수문자로 인식되지 않게 하기 위해 ESCAPE 사용
• SYSDATE는 Date 타입으로, TO_DATE로 형 변환시 동일 타입을 변환하기 때문에 오류가 발생한다. 따라서 TO_CHAR를 사용해서 문자형으로 변환해야 함.

트랜잭션

• 트랜잭션이 가지는 특징에는 원자성, 일관성, 고립성, 지속성이 있다.

JOIN

• FULL OUTER JOIN의 결과 값은 LEFT OUTER JOIN과 RIGHT OUTER JOIN 결과를 UNION 연산 하는 결과와 동일하다.
• NATURAL JOIN이 사용된 열은 식별자를 가질 수 없다. EMP.DEPTNO처럼 OWNER 명을 사용하면 에러가 발생한다.

옵티마이저 조인

• Hash Join은 해시 함수를 사용해서 주소를 계산하기 때문에 CPU를 많이 사용한다.
• Hash Join은 선행 테이블로 작은 테이블이 선정되어야 한다.
• Hash Join은 정렬 작업이 없어 정렬이 부담되는 대량 배치작업에 유리하다.
• Hash Join의 순서
  1. 선행 테이블에서 조건에 만족하는 데이터에 대해 필터링을 수행
  2. 선행 테이블의 조인 키를 해시 함수의 입력으로 넣고 해시 테이블 생성
  3. 1, 2 작업을 선행 테이블에서 조건을 만족하는 모든 행에 대해 실행
  4. 후행 테이블에 조건이 있으면 데이터에 대해 필터링
  5. 후행 테이블의 조인 키를 해시 함수의 입력에 넣어서 해시값을 생성하고 선행 테이블의 해시 값과 비교 검색
• Hash Join은 해시 함수를 사용하므로 Equal Join에만 가능하다.
• Hash Join은 조인 칼럼에 인덱스가 없어도 사용이 가능하다.
• Sort Merge Join은 Equal Join과 Non EqualJoin에서 모두 사용 가능하다.
• Sort Merge Join은 정렬된 결과들을 통해 조인 작업이 수행되며, 조인에 성공하면 추출 버퍼에 넣는 작업을 수행한다.
• Nested Loop Join은 조인 칼럼의 인덱스가 존재해야 한다.
• Nested Loop Join은 Random Access를 유발한다.

Window Function

• sum, min, max와 같은 집계 Window Function을 사용할 때 window 절과 함께 사용하면 집계의 대상이 되는 레코드 범위를 지정할 수 있다.
• Window Function 처리로 결과 건수가 줄어들지는 않는다.
• GROUP BY 구문과 Windows Function은 병행하여 사용할 수 없다.
• UNBOUNDED PRECEDING은 end point에 사용될 수 없다. 마찬가지로, UNBOUNDED FOLLOWING은 start point에 사용될 수 없다.
• RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW가 default 값이다.
• NTILE(i) : 속성 값 n개를 균일하게 i 등분하고, 나머지는 앞에서부터 순차적으로 할당한다.

실행계획

• SQL 문장에서 실행계획은 우선 안에서 밖으로 읽으면서, 같은 레벨에서는 위에서 아래로 읽는다.

서브쿼리

• 서브쿼리에는 Order by구를 사용할 수 없다.
• 서브쿼리에서 여러 개의 행이 반환되면 IN, ANY, ALL 같은 다중행 서브쿼리 함수를 사용해야 한다.
• FROM구에 사용되면 인라인 뷰, WHERE구에 사용되면 서브쿼리, SELECT구에 사용되면 Scala Subquery이다.
• 서브쿼리는 메인쿼리의 칼럼을 모두 사용할 수 있지만, 메인쿼리는 서브쿼리의 칼럼을 사용할 수 없다.
  • 단, 서브쿼리 중에서 INLINE VIEW의 칼럼은 메인쿼리에서도 사용 가능하다.

계층형 쿼리

• PRIOR 자식 = 부모 형태는 계층 구조에서 순방향 전개를 수행한다.
• CONNECT_BY_ISLEAF는 해당 데이터가 리프 데이터면 1, 아니면 0을 반환한다.
• CONNECT BY는 부모 계층형 쿼리에서 부모와 자식 노드의 관계를 나타내는 데 사용된다.
• 계층형 쿼리문의 내장함수에는 LEVEL, SYSCONNECT_BY_PATH, NOCYCLE, CONNECT_BY(ROOT/ISLEAF/ISCYCLE)이 있다.
• ORDER SIBLINGS BY구는 형제 노드 사이에 정렬을 수행한다
• ROOT NODE의 LEVEL값은 1이다.
• LPAD(대상 문자, 지정 길이, 앞에 채울 문자)

ORACLE vs SQL SERVER

• ORACLE에서 기본적으로 AUTO COMMIT은 실행되지 않는다. SQL SERVER는 기본 설정이 AUTO COMMIT이다.
• SQL SERVER와 ORACLE에서 ROLLBACK을 하는 경우 UPDATE구는 취소된다.
• ORACLE에서 CREATE TABLE 같은 DDL구는 ROLLBACK으로 취소되지 않는다.
  • ORACLE의 경우 기본값이 auto commit off로 설정된 상태로 DDL이 수행되면 묵시적으로 commit이 수행된다.
  • SQL SERVER는 묵시적으로 commit이 수행되지 않음 (SET IMPLICIT_TRANSACTIONS ON 설정 후 수행하면 CREATE도 취소됨)
• ORACLE은 NULL을 가장 큰 값으로 취급하고, SQL SERVER는 가장 작은 값으로 취급한다.
• CASCADE는 ORACLE에서 옵션으로 존재하고, SQL SERVER에는 존재하지 않는다.