[SQLD] 시험 essential 개념 및 기출 마지막 정리

 

SQLD 시험은 총 3과목으로 데이터모델링의 이해(10문제), SQL(3~40문제), 주관식(8~10문제)로 이루어진다. 

 

---

 

데이터 모델링의 이해

 

01 엔터티 문제

 

엔터티

엔터티는 사람, 장소, 물건, 사건, 개념 등의 '명사'에 해당한다. 엔터티는 업무상 관리가 필요한 관심사로 저장이 되기 위한 어떤 것(Thing)이다.

 

엔터티의 특징

반드시 해당 업무에 필요하고 관리하고자 하는 정보여야 한다. 유일한 식별자에 의해 식별이 가능해야 한다. 

영속적으로 존재하는 두 개 이상의 인스턴스의 집합이어야 한다.

엔터티는 업무 프로세스에 의해 이용되어야 한다. 엔터티는 반드시 속성이 있어야 한다.

엔터티는 다른 엔터티와 최소 한 개 이상의 관계가 있어야 한다.

 

Q. 다음 중 엔터티의 특징이 아닌 것은?
1. 반드시 해당 업무에서 필요하고 관리하고자 하는 정보여야 한다.
2. 영속적으로 존재하는 인스턴스의 집합이다.
3. 유일한 식별자에 의해 식별이 가능해야 한다.
4. 속성이 없는 엔터티도 존재 할 수 있다.

 

 

02 주식별자

 

식별자

식별자는 엔터티를 구분짓는 논리적인 이름/엔터티의 대표 속성/반드시 하나의 유일한 식별자가 존재

 

식별자의 특징

1) 유일성: 모든 인스턴스들은 유일하게 구분되어야 함

2) 최소성: 유일성을 만족하는 최소의 수가 되어야 함

3) 불변성: 식별자가 한 번 특정 엔터티에 지정되면 값은 변하지 않아야 함

4) 존재성: 식별자는 반드시 데이터값이 존재해야함 (NULL 불가)

 

Q. 식별자의 특징 중 주식별자에 대한 특징이 아닌것은?
1. 주식별자에 의해 엔터티내의 모든 인스턴스들이 유일하게 구분되어야 한다.
2. 주식별자를 구성하는 속성의 수는 유일성을 만족하는 최소의 수가 되어야 한다.
3. 지정된 주식별자의 값은 자주 변하지 않는 것이어야 한다.
4. 주식별자가 지정이 되면 반드시 값이 들어오지 않아도 된다.

 

 

03 속성

 

1) 기본속성: 일반적으로 많은 속성 ex) 제품이름, 제조년월

2) 설계속성: 업무상 필요한 데이터의 속성을 새로 만들거나 변형하여 정의하는 속성

3) 파생속성: 보통 다른 속성에서 계산된 값들이 이에 해당됨

 

 

04 관계

 

관계 표기법

1) 관계명 : 관계이름

2) 관계차수 <CARDINALITY> 1 : 1, 1 : M, M : N

3) 관계선택사양(OPTIONALITY) 필수관계, 선택관계

 

 

05 정규화

 

정규화: 프로젝트 수행에서 정규화는 필수 사항/한 데이터블의 용량이 최소화 되는 효과

 

1) 1차 정규형: 모든 속성이 원자값으로 구성

2) 2차 정규형: 부분적 함수 종속 / 식별자가 아닌 모든 속성들은 식별자 전체 속성에 완전 종속되어야 한다.

3) 3차 정규형: 이행적 함수 종속제거 / 2차 정규형을 만족하고 식별자를 제외한 나머지 속성들 간의 종속이 존재하면 안된다.

 

반정규화: 정규화된 시스템의 성능향상, 개발, 운영의 단순화를 위해 중복, 통합, 분리 등을 수행

 

사용이유

1) 데이터의 I/O량이 많아서 성능이 저하된 경우

2) 경로가 너무 멀어 조인으로 성능저하가 예상되는 경우

3) 칼럼을 계산하여 읽을 때 성능저하가 예상되는 경우

 

Q. 아래의 설명 중 반정규화 대상이 아닌 것은?
1) 자주 사용되는 테이블에 접근하는 프로세스의 수가 많고 항상 일정한 범위만을 조회하는 경우
2) 테이블의 대량의 데이터가 있고 대량의 데이터 범위를 자주 처리하는 경우 처리범위를 일정하게 줄이지 않으면 성능을 보장할 수 없는 경우
3) 통계성 프로세스에 의해 통계 정보를 필요로 할 때 별도의 통계 테이블을 생성해야 하는 경우
4) 테이블에 지나치게 많은 조인과 Sorting, Order by 프로세스가 많은 경우

 

 

06 데이터 모델링의 단계

 

1) 개념적 데이터 모델링: 추상화 / 전사적(EA) 모델링 사용

2) 논리적 데이터 모델링: KEY, 속성, 관계 등을 정확히 표현

3) 물리적 데이터 모델링: 실제로 데이터베이스에 이식할 수 있도록 성능, 저장 등 물리적인 성격을 고려하고 설계

 

Q. 데이터 모델링이 최종적으로 완료된 상태라고 정의할 수 있는, 즉, 물리적인 스키마 설계를 하기 전 단계를 가리키는 말은 무엇인가?
1) 개괄적 모델링
2) 개념적 모델링
3) 논리적 모델링
4) 물리적 모델링

 

 

07 파티셔닝

 

테이블이 많은 양의 데이터가 예상 될 경우 파티셔닝을 적용하거나 PK에 의해 테이블을 분할하는 방법을 적용할 수 있다.

 

ORACLE의 경우

1) Range Partition: 가장 많이 사용(보편적, 일반적) / 대상 테이블이 날짜, 숫자 값으로 분리가능 / 데이터 보관 주기에 따라 데이터를 쉽게 지우는 것이 가능

2) List Partition: 대용량 데이터를 특정 값에 따라 분리, 저장 가능 / 쉽게 삭제되지 않음

3) Hash Partition: 저장된 해시조건의 알고리즘이 적용되어 테이블 분리 / 데이터의 확인이 어려움 / 쉽게 삭제 되지 않음

4) Composite Partition: 범위와 해시가 복잡

 

Q. 아래에서 설명하고 있는 파티셔닝은 어느 것인가?
대상 테이블이 날짜 또는 숫자 값으로 분리가 가능하고 각 영역별로 트랜잭션이 분리될 때 적용한다. 데이터 보관 주기에 따라 데이터를 쉽게 지우는 것이 가능하므로 데이터 보관 주기 별 테이블 관리가 용이하다. 가장 많이 사용한다.
1) LIST PARTITION
2) HASH PARTITION
3) RANGE PARTITION
4) ORIGINAL PARTITION

 

 

08 슈퍼/서브 타입의 모델링 변환 기술

 

1) 1 : 1 - ONE TO ONE TYPE : 개별 테이블로 접근이 많은 경우 선택

2) 슈퍼 + 서브타입 테이블 - PLUS TYPE : 전체를 일괄적으로 처리하는 경우

3) 하나의 테이블 - SINGLE TYPE : 전체를 일괄적으로 처리하는 경우

 

 

09 분산 테이블의 투명성

 

1) 분할투명성

2) 위치투명성

3) 지역사상투명성

4) 중복 투명성

5) 장애 투명성

6) 병행 투명성

 

 

10 도메인: 각 속성이 가질 수 있는 값의 범위

 

 

11 ERD 작업 순서

 

[도→배→설→명→참→필]

엔터티 도출 → 엔터티 배치 → 엔터티 간 관계 설정 → 관계명 기술 → 관계의 참여도 기술 → 관계의 필수여부 기술

 

Q. ERD 작업 순서로 올바르게 나열된 것은?
ㄱ. 엔터티를 적절하게 배치한다.
ㄴ. 관계의 필수여부를 기술한다. 
ㄷ. 엔터티를 그린다.
ㄹ. 관계명을 기술한다.
ㅁ. 엔터티간 관계를 설정한다.
ㅂ. 관계의 참여도를 기술한다.

1. ㄱ-ㄴ-ㄷ-ㄹ-ㅁ-ㅂ
2. ㄷ-ㄱ-ㅁ-ㄹ-ㅂ-ㄴ
3. ㄷ-ㄱ-ㄹ-ㅂ-ㄴ-ㅁ
4. ㄷ-ㄱ-ㅁ-ㄹ-ㄴ-ㅂ
5. ㄷ-ㄴ-ㅁ-ㄹ-ㄴ-ㅂ

 

데이터 모델의 성능

 

* 데이터 모델링을 할 때 정규화를 정확하게 수행

* 데이터베이스 용량산정을 수행

* 데이터베이스에 발생되는 트랜잭션의 유형을 파악

* 용량과 트랜잭션의 유형에 따라 반정규화를 수행한다.

* 이력모델의 조정, PK/FK 조정, 슈퍼타입/서브타입 조정 등을 수행한다.

* 성능관점에서 데이터 모델을 검증한다.

 

 

12 대량 데이터에 따른 성능

 

로우 마이그레이션(ROW MIGRATION): 수정된 데이터를 해당 데이터에 저장하지 못하고 다른 블록의 빈 공간을 찾아 저장하는 방식

로우 체이닝(ROW CHAINING): 로우 길이가 너무 길어서 데이터 블록 하나에 모두 저장이 안되서 두 개 이상의 블록에 걸쳐 하나의 로우가 저장되어 있는 형태

 

Q. Row Migration과 Row Chaining에 대한 아래의 설명 중 바른 것은?
1) Row Chaining과 Row Migration이 많아지게 되더라도 성능 저하는 일어나지 않는다.
2) 로우 길이가 너무 길어서 데이터 블록 하나에 데이터가 모두 저장되지 않고 두 개 이상의 블록에 걸쳐 하나의 로우가 저장되는 현상을 Row Chaining 이라고 한다.
3) Row Migration은 신규 데이터의 입력이 발생할 때 발생되는 현상이다.
4) Row Chaining과 Row Migration이 발생되면 디스크 I/O가 발생될 때 I/O가 많아지게 되어 성능이 향상될 수 있다.

 

 

---

 

 

SQL

 

01 트랜잭션의 특징/특성

 

원자성 - All or Nothing

고립성 - 트랜잭션 실행도중 다른 트랜잭션의 영향을 받아서는 안된다.

일관성 - 트랜잭션이 실행되기 전의 데이터베이스 내용이 잘못되어있지 않다면 실행 이후도 같아야 한다.

지속성 - 한번 바뀌면 영구적으로 지속되어야 한다.

 

Q. 트랜잭션의 특징 중 옳지 않은 것은?
1) 일관성(Consistency)
2) 원자성(Atomicity)
3) 지속성(Durability)
4) 중복성(Duplication)
5) 고립성(Isolation)

 

오라클은 DML 사용 이후 COMMIT, MS-SQL은 AUTO COMMIT

 

 

02 SQL 조작어

 

DDL(Data Definition Language) - CREATE, ALTER, DROP

DML(Data Manipulation Language) - SELECT, UPDATE, DELETE, INSERT

DCL(Data Control Langauge) - GRANT, REVOKE

TCL(Transaction Control Language) - COMMIT, ROLLBACK

 

Q. DML에 대한 설명으로 옳은 것은?
1) DELETE 사용 시 FROM 문구는 생략이 불가능하다.
2) 원하는 데이터 DELETE시 WHERE 절은 반드시 붙이지 않아도 된다.
3) DML 문장은 실시간으로 테이블에 영향을 미친다.
4) DML은 반드시 COMMIT을 입력하여 TRANSACTION을 종료해야 한다.

Q. 아래의 DML, DCL, DDL이 잘못 짝지워지는 것은?
1) DDL: CREATE
2) DML: UPDATE
3) DCL: ROLLBACK
4) DCL: SELECT

Q. SQL 명령어 중 TCL 인 것은 어떤 것인가?
1) UPDATE
2) GRANT
3) SELECT
4) COMMIT

 

 

03 SQL 실행순서

 

FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY

 

Q. 다음 중 옳지 않은 것은?
1) 기본적인 정렬 순서는 오름차순(ASC)이다.
2) ORDER BY 절에는 SELECT 목록에 나타나지 않은 문자형 항목이 포함될 수 있다. 
3) 4개의 테이블이 있다면 JOIN 조건은 적어도 3개 이상이 필요하다.
4) SQL 문장의 파싱 순서는 From - Where - Order By - Select 순이다.
5) Non Equi Join은 두 테이블 간 컬럼 값이 서로 정확하게 일치하지 않는 경우 사용한다.

Q. 다음 설명 중 틀린 하나는?
1) ORDER BY 절에서 쿼리 내 SELECT 절에 정의되지 않은 컬럼도 사용할 수 있다.
2) 권장하지 않지만 복수의 테이블을 JOIN하여 WHERE 절을 작성할 시 문장 내 유일하게 사용하는 컬럼이라면 ALIAS를 붙이지 않아도 된다.
3) "=" 연산자가 아닌 다른 연산자를 사용하는 JOIN은 모두 NON EQUI JOIN이다.
4) SELECT 문은 SELECT 절이 가장 먼저 실행된다.
5) GROUP BY 절에서는 SELECT 절과는 달리 ALIAS 명을 사용할 수 없다.

 

 

04 NULL

 

Q. 다음 중 NULL의 설명으로 가장 부적절한 것은?
1) 모르는 값을 의미한다.
2) 값의 부재를 의미한다.
3) 공백문자(Empty String) 혹은 숫자 0을 의미한다.
4) NULL과의 모든 비교는 알 수 없음(Unknown)을 반환한다.

 

NULL 함수

 

- NVL(컬럼,0) : 컬럼이 NULL이면 0으로 치환한다.

- ISNULL : (컬럼, '대체값') : 컬럼이 NULL이면 '대체값'으로 치환한다.

- NULLIF(컬럼A, 컬럼B) : 컬럼A가 NULL이면 컬럼B로 치환한다.

- COALESCE(컬럼A, 컬럼B, 컬럼C ..)

 

 

05 연산자 우선순위

 

괄호 → NOT 연산자 → 비교 연산자 → AND → OR

 

 

06 숫자형 함수

 

ABS(숫자) : 절댓값을 산출

SIGN(숫자)

MOD(숫자1, 숫자2) : % 나머지

CEIL/CEILING(숫자) ↔️ FLOOR(숫자)

ROUND(숫자, m) : 소수점 m자리 반올림 ↔️ TRUNC(숫자, m) : 소수점 m자리 절삭

 

Q. 다음 숫자형 함수 사용시 결과값이 올바르지 않은 것은?
1) CEIL(-38.123) : -38
2) FLOOR(-38.123) : -38
3) MOD(8,3) : 2
4) SIGN(0) : 0
5) ROUND(38.5235) : 소수점 M을 지정하지 않으면 첫째자리 반올림

Q. 숫자형 함수 적용과 그 결과값이 아닌 것은? 
1) ABS(-20) = 20
2) SIGN(-50) = -1
3) MOD(7,3) = 2 
4) CEIL(38.123) = 39
5) FLOOR(38.123) = 38

 

 

07 그룹함수 (GROUP BY에 이어서 쓴다)

 

ROLLUP() / CUBE() / GROUPING SETS()

 

 

08 집계함수

 

COUNT() : 숫자세기 / SUM() : 합계산출 / AVG() : 평균 산출

* NULL 값이 포함되어 있는 한 컬럼의 SUM()의 경우 NULL 값을 0으로 치환 후 계산

 

- COUNT(*) : NULL 포함한 행의 수

- COUNT(컬럼) : NULL 값을 제외한 행의 수

조건절에 해당하는 데이터가 없을때 COUNT(*)의 결과 값은 0

 

 

09 문자형 함수

 

- LOWER() : () 안의 모든 문자를 소문자로 치환

- UPPER() : () 망의 모든 문자를 대문자로 치환

- SUBSTR() / SUBSTRING

- LENGTH / LEN : * 차이점 오라클에서는 LENGTH()가 CHAR_LENGTH()의 역할 SQL에서는 LEN()가 CHAR_LENGTH()의 역할

- RTRIM / LTRIM / TRIM : 오른쪽 공백 / 왼쪽 공백 / 양쪽 공백

- CONCAT() : 문자열 + 문자열 합침

 

 

10 CHAR VS. VARCHAR

 

CHAR | 비교 시 공백을 채워서 비교

VARCHAR | 공백은 비교안함. 공백을 하나의 문자로 취급

 

 

11 조인 함수

 

- INNER JOIN : 기본형 / 일반적으로 JOIN은 INNER JOIN이다. 

- LEFT OUTER JOIN : 왼쪽 기준 동그라미 전체

- RIGHT OUTER JOIN : 오른쪽 기준 동그라미 전체

- FULL OUTER JOIN : 전체

- NATURAL JOIN : 자동으로 알아서 조인하는 테이블의 같은 이름을 가진 컬럼을 찾아서 조인한다.

알아서 컬럼 조인하기 때문에 ON, USING, WHERE절에서 같은 컬럼 정의하지 못함

ALIAS나 테이블명과 같은 접두사 사용 불가

조인되는 컬럼들은 같은 데이터 유형이어야 함

 

- ON 절 : WHERE 절의 = 와 같다. 같은 컬럼 조인시 사용

- USING 절 : ON 이나 = 와 같은데, 같은 이름의 컬럼이면 USING 으로 간단하게 사용

 

 

12 컴퓨터가 처리하는 조인 방식 - SQL SET OPERATION

 

1) NL 조인

2) SORT MERGET JOIN : 조인하기전 정렬 / 넓은 데이터 처리에 유용 / 데이터가 많을 시 디스크를 사용하기 때문에 성능이 떨어질 수 있음 / 인덱스 유무에 영향을 받지 않음 / 정렬 (SORT) 필요

3) HASH 조인 : 동등조인(=)에서 사용 / 대용량 테이블 조인작업에 유리 / 인덱스를 사용하지 않음 / HASH 사용 / 한쪽 테이블의 크기가 작을수록 유리 / NL 조인의 랜덤

4) NEST LOOP JOIN : 프로그래밍에서 사용되는 중첩된 반복문과 유사한 방식으로 조인을 수행하는 방식

 

Q. SQL Set Operation에서 중복 제거를 위해 정렬 작업을 하지 않는 집합 연산자는?
1) UNION
2) UNION ALL
3) INTERSECT
4) MINUS

Q. SQL Set Operation에서 중복 제거를 위해 정렬 작업을 하지 않는 집합 연산자는?
1) Sort Merge Join
2) Hash Join
3) Nested Loop Join
4) Cartesian Join

 

 

13 서브쿼리

 

SELECT 절에서 사용 : SCARLAR SUBQUERY (스칼라 서브쿼리) = NESTED SUBQUERY와 같은 개념

* SELECT 절에 나오는 서브쿼리는 단일행 이어야 한다.

 

FROM 절에서 사용 : INLINE VIEW

* 단일 행으로 사용해야 할 때 : 비교연산자 =, <, <=, >=, <>와 함께 사용 시

* 다중 행으로 사용해야 할 때 : 다중행 비교연산자 IN, ALL, ANY, SOME과 함께 사용 시

 

WHERE 절에서 사용 : 올바른 사용처, 서브쿼리는 레벨과 상관없이 항상 메인 쿼리 레벨로 결과 집합생성

 

Q. 다음 중 SELECT절에 사용하는 서브쿼리인 스칼라 서브쿼리에 대한 설명으로 가장 적절한 것을 2개 고르시오.
1) 내부적으로 UNIQUE 작업을 실행한다.
2) 하나의 로우에 해당하는 결과 건수는 1건 이하여야 한다.
3) MIN 또는 MAX 함수를 사용해야 한다.
4) 결과 컬럼의 개수는 1개 이어야 한다.

 

 

14 계층형 쿼리 문제

 

계층형 쿼리에서 최대 계층의 수를 구할 때는> MAX(LEVEL)

 

SELECT EML.직원, EMP.직급
FROM EMP
START WITH EMP.직원 IS NULL
CONNECT BY PRIOR EMP.직원 = EMP.직속상사

* PRIOR 옆에는 항상 자식이 온다. 

 

Q. 계층형 질의 구문에 대한 설명이다. 빈칸 안에 알맞은 개념을 작성하시오.
(  )은 계층 구조 전개의 시작 위치에 지정하는 구문이다. 즉, 루트 데이터를 저장한다.
(  )은 다음에 전개될 자식 데이터를 지정하는 구문이다.
(  ) 데이터를 전개하면서 이미 나타났던 동일한 데이터가 전개 중에 다시 나타남을 막기 위해 이 옵션을 추가한다.

START WITH
CONNECT BY
NO_CYCLE

 * 루트 데이터는 LEVEL1이다. 

 

- LEVEL

- CONNECT_BY_ISLEAF : 해당 데이터가 리프면 1, 아니면 0

- CONNECT_BY_ISCYCLE : 해당 데이터가 조상으로 존재하면 1, 그렇지 않으면 0

- SYS_CONNECT_BY_PATH(컬럼,분리자) : 루트-현재 전개할 데이터까지 경로 표시

- CONNECT_BY_ROOT(컬럼) : 현재 데이터의 루트 데이터 표시

 

 

15 WINDOW 함수

 

- RANK() OVER : 동일 동수를 허용

- DENSE_RANK() OVER : 동일 동수를 비허용

- ROW_NUMBER() OVER : 동일한 순위 없음

* 오라클의 ROWNUM은 WHERE절에서 행의 개수를 제한하는 목적으로 사용

- PERCENT_RANK() OVER : 파티션 별 윈도우에서 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 하여 값이 아닌 행의 순서별 백분율을 구하는 Window Function

 

Q. 특정 그룹에서 특정 컬럼으로 정렬된 결과에서 첫번째 값을 구하는 Window Function을 고르시오.
1) LAST_VALUE() OVER
2) FIRST_VALUE() OVER
3) MAX_VALUE() OVER
4) 없다.

 

 

16 파티션

 

- FIRST_VALUE(컬럼) OVER (PARTITION.. : 파티션의 가장 첫번째 값 가지고 오기

- LAST_VALUE(컬럼) OVER (PARTITION.. : 파티션의 가장 마지막 값 가지고 오기

- LAG(인자 1,2,3) OVER (ORDER BY.. :

- LEAD(인자 1,2,3) OVER (ORDER BY.. :

 

반복 누적 문제 시:

SUM(매출액) OVER (PARTITION BY 매출월 ORDER BY 매출월

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

 

범위 지정 시:

ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

 

Q. 5번의 테이블 구조에서 아래와 같은 누적 SUM을 구하기 위한 SQL을 완성하시오. (COL3 기준으로 정렬된 누적 평균)

SELECT COL1, COL2,
ROUND ( AVG (COL3) OVER (   ), 0) AS 누적평균
FROM TAB1
ORDER BY COL1, COL3;

SUM(매출액) OVER (PARTITION BY 매출월 ORDER BY 매출월 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

 

 

17 ROLE - 유저를 생성하고 권한을 제어하는 일을 대신해주는 중간 역할

 

 

18 옵티마이저

 

- 성능기반 옵티마이저

- 비용기반 옵티마이저

 

 

19 INDEX

 

ORACLE에서 사용하는 INDEX 기법의 종류

- INDEX UNIQUE SCAN

- INDEX RANGE SCAN 

- INDEX RANGE SCAN DESCENDING

- INDEX FULL SCAN

- FAST FULL INDEX SCAN

- INDEX SKIP SCAN

 

 

---

 

 

주관식

 

 

Q. 데이터 입력 시 컬럼의 값이 지정되지 않았을 때 자동 입력될 기본값은? DEFAULT

 

Q. 데이터의 무결성을 유지하기 위한 데이터베이스의 보편적인 방법으로 종류로는 PK, FK, UNIQUE KEY등이 있다. 제약조건

 

Q. CTAS 기법으로 생성한 테이블은 기존 테이블의 제약조건 중에 **만 새로운 복제 테이블에 적용된다. NOT NULL

 

Q. DROP 명령어로 테이블의 구조 삭제시 ** 옵션은 해당 테이블과 관계가 있었던 참조되는 제약 조건에 대해서도 삭제된다는 것을 의미한다. CASCADE CONSTRAINT

 

Q. SQL 문장에서 집계함수의 조회조건을 제한하기 위해 사용되는 절은? HAVING 절