SQL 자격 검정 실전 문제 풀이 1 (Feat. 주황이)

11월 18일에 보게 될 SQLD 자격증 시험을 위해 공부하게 되었습니다.

 

SQLD는 같은 경우 비전공자와 전공자와 차이가 있겠지만, 비교적 짧은 시간에 모두가 취득할 수 있는 시험이라고 생각합니다.

그만큼 공부 자료가 넘쳐나고 시험 스케줄 또한 많이 정리되어 있습니다. 자신에게 맞는 공부법을 찾아 공부한다면 누구나 취득할 수 있을 것 같습니다. 🎯

 

1주차와 같은 경우 시중에 있는 공부자료를 스윽 읽어보기만 했습니다. 자료들은 아무래도 제가 작성한 것이 아니라 공유하지는 못할 것 같습니다... 대신 주황이를 풀면서 알게된 점들을 글로 남겨보겠습니다.🧑‍🏫

현재 진행중인 2주차에는 주황이 문제집을 1회차 풀어보는 것을 목표로 하겠습니다.

3주차의 경우 주황이 문제집 2회차 풀이, 기출 문제 풀이한 뒤 시험을 보러 가보겠습니다.

 

SQLD와 관련된 주황이 문제는 대략 200문제로 주중 5일 동안 하루에 40문제씩 풀도록 계획했고, 각 챕터를 하루에 모두 푸는 것이 아닌 챕터 별로 10문제씩 끊어서 풀이해 주었습니다.

 

 

---

1일차

데이터 모델링 (1~10)

데이터 모델링의 특징

• 현실 세계를 일정한 형식에 맞추어 표현하는 추상화의 의미를 가질 수 있다.
• 복잡한 현실을 제한된 언어나 표기법을 통해 이해하기 쉽게 하는 단순화의 의미를 가지고 있다
• 애매모호함을 배제하고 누구나 이해가 가능하도록 정확하게 현상을 기술하는 정확화의 의미를 가짐
• 시스템 구현을 포함한 업무분석 및 업무 형상화를 하는 목적도 있다.

 

데이터 모델리의 목적

• 업무 정보를 구성하는 기초가 되는 정보들에 대해 일정한 표기법에 의해 표현함으로써 정보 시스템 구축의 대상이 되는 업무 내용을 정확하게 분석
• 분석된 모델을 가지고 실제 데이터베이스를 생성하여 개발 및 데이터 관리에 사용하기 위함

→ 단지 데이터베이스만을 구축하기 위한 용도가 아니라 업무를 설명하고 분석하는 부분에서도 매우 중요한 의미를 가지고 있다

 

데이터 모델링 유의 사항

중복성, 비유연성, 비일관성을 유의해야 한다

• 중복 - 데이터 모델은 같은 데이터를 사용하는 사람, 시간, 장소를 파악하는데 도움을 줌으로써 데이터베이스가 여러 장소에 같은 정보를 저장하는 잘못을 하지 않도록 한다.
• 비유연성 - 데이터의 정의를 데이터의 사용 프로세스와 분리함으로써 데이터 모델링은 데이터 혹은 프로세스의 작은 변화가 어플리케이션과 데이터베이스에 중대한 변화를 일으킬 수 있는 가능성을 줄인다
  (데이터 베이스 모델의 설계에 따라 사소한 업무 변화에도 데이터 모델이 수시로 변경될 수 있어 조심해야 한다)
• 비일관성 - 데이터 모델링 시 데이터와 데이터 간의 상호 연관 관계에 대해 명확하게 정의해야 한다.
  (데이터의 중복이 없더라도 비일관성은 발생할 수 있다. 서로 연관된 다른 데이터와의 모순이 발생할 수 있다는 것을 모르고 일련의 데이터를 수정할 수 있기 때문)

 

데이터 모델링 종류

• 개념적 모델링 - 추상화 수준이 높고 업무 중심적이고 포괄적인 수준의 모델링 진행. 전사적 데이터 모델링. EA(Enterprise Architecture) 수립시 많이 이용됨
• 논리적 모델링 - 시스템으로 구축하고자하는 업무에 대해 key, 속성, 관계 등을 정확하게 표현, 재사용성이 높다.
• 물리적 모델링 - 실제로 데이터베이스에 이식할 수 있도록 성능, 저장 등의 물리적인 성격을 고려한 데이터 모델링

 

3단계 구조

데이터가 어떻게 저장되고 유지되는 가에 대한 상세한 사항들을 숨기고 사용자에게 데이터에 대한 추상적인 뷰를 제공하는 것

 

1. 외부 스키마

데이터베이스를 개별 사용자 관점에서 이해하고 표현

각 사용자가 생각하는 데이터베이스의 모습을 표현한 논리적인 구조, 사용자마다 다르다

 

2. 개념 스키마

모든 사용자 관점을 통합한 조직 전체 관점의 통합적 표현

모든 응용시스템들이나 사용자들이 필요로 하는 데이터를 통합한 조직 전체의 DB를 기술한 것으로 DB에 저장되는 데이터와 그들간의 관계를 표현하는 스키마입니다.

 

3. 내부 스키마

저장 장치의 관점에서 이해하고 표현

전체 데이터베이스가 디스크와 테으프 같은 저장 장치에 실제로 저장되는 방법을 정의

 

데이터 독립성

• 논리적 독립성 : 개념 스키마가 변경되어도 외부 스키마에는 영향을 주지 않습니다.
• 물리적 독립성 : 내부 스키마가 변경되어도 개념 스키마에 영향을 주지 않습니다.

 

Mapping : 상호 독립적인 개념을 연결시켜 주는 것

• 논리적 사상 : 외부 스키마 ↔ 개념 스키마
• 물리적 사상 : 개념 스키마 ↔ 내부 스키마

 

ERD

• 1976년 피터첸에 의해 Entity-Relationship Model(E-R Model)이라는 표기법이 만들어졌습니다.
• 일반적으로 ERD를 작성하는 방법은 [ 엔티티 도출 → 엔티티 배치 → 관계 설정 → 관계명 기술 → 관계의 참여도 기술→관계의 필수 여부 기술 ]로 이루어집니다.
• 관계의 명칭은 관계 표현에 있어서 매우 중요한 부분에 해당합니다.

 

Entity의 특징

• 반드시 해당 업무에서 필요하고 관리하고자 하는 정보여야 합니다.
• 유일한 식별자에 의해 식별이 가능해야 합니다.
• 영속적으로 존재하는 2개 이상의 인스턴스의 집합이어야 합니다.
• 엔티티는 업무 프로세스에 의해 이용되어야 합니다.
• 엔티티는 반드시 속성을 가지고 있어야 합니다.
• 엔티티는 다른 엔티티와 최소 1개의 관계를 가지고 있어야 합니다. 하지만, 통계성 엔티티나 코드성 엔티티의 경우 생략할 수 있습니다.

 

 

---

성능 데이터 모델링(31~40)

성능 데이터 모델링이란

데이터베이스 성능 향상을 목적으로 설계 단계의 데이터 모델링 때부터 성능과 관련된 사항(정규화, 반정규화, 테이블 통합, 테이블 분할, 조인구조, PK, FK 등)이 데이터 모델링에 반영될 수 있도록 하는 것

 

성능을 고려한 데이터 모델링의 순서

1. 데이터 모델링을 할 때 정규화를 정확하게 수행합니다.
2. 데이터베이스 용량산정을 수행합니다.
3. 데이터베이스에 발생되는 트랜잭션의 유형을 파악합니다.
4. 용량과 트랜잭션의 유형에 따라 반정규화를 수행합니다.
5. 이력 모델의 조정, PK/FK 조정, 슈퍼타입/서브타입 조정 등을 수행합니다.
6. 성능 관점에서 데이터 모델을 검증합니다.

 

성능 데이터 모델링 시 고려사항

• 정규화와 같은 경우 기본적으로 중복된 데이터를 제거함으로써 조회 성능을 향상시킬 수 있습니다. 하지만, 상황에 따라 정규화된 엔티티에 대해 시스템의 성능 향상과 개발 운영의 단순화를 위해 중복, 통합 분리 등을 수행해 성능 저하를 막을 수 있습니다.
• 용량산정은 전체적인 데이터베이스에 발생되는 트랜잭션의 유형과 양을 분석하는 자료가 되므로 성능 데이터 모델링을 할 때 중요한 작업이 될 수 있다
• 물리적인 데이터 모델링을 할 때 PK/FK의 칼럽의 순서 조정, FK인덱스 생성 등은 성능 향상을 위한 데이터 모델링 작업에 중요한 요소가 됩니다.
• 이력 데이터는 시간에 따라 반복적으로 발생이 되기 때문에 대량 데이터일 가능성이 높아 특별히 성능을 구려하여 칼럼 등을 추가하도록 설계해야 합니다.

 

정규화

반복적인 데이터를 분리하고 각 데이터가 종속된 테이블에 적절하게 배치되도록 하는 것

 

1차 정규화

같은 성격, 내용 컬럼이 연속될 때 테이블로 분리 (컬컴이 원자값을 갖도록)

 

2차 정규화

1차 정규형 테이블의 PK가 복합키 구성일 때, 부분적 함수 종속 관계를 가진 컬럼들을 테이블로 분리 (모든 컬럼이 부분적 종속이 없도록, 완전 함수 종속 만족)

함수 종속 관계 : 데이터들이 어떤 기준 값에 의해 종속되는 현상

부분적 종속 : 기본키의 부분집합으로 결정되는 컬럼이 있는 경우

 

예시)

수강 테이블의 속성으로 학번, 과목, 교수, 성적이 있을 경우

(학번, 과목) → 성적

(과목) → 지도 교수

기본키인 학번과 과목을 알면 성적과 지도 교수를 알 수 있습니다. 하지만, 과목만 알고 있어도 지도 교수를 알 수 있기 때문에 부분적 종속된 컬럼이 존재합니다. 그러므로 Score(학번, 과목, 성적), Subject(과목, 지도 교수)으로 테이블을 나눌 수 있습니다.

 

3차 정규화

2차 정규형 테이블의 PK가 아닌 일반 컬럼에 의존하는 컬럼 분리 (기본키를 제외한 속성들 간의 이행 종속성이 없도록)

이행 종속성 : A → B , B → C 일 때 A → C 인 경우

이름, 도로명 주소, 우편 번호가 있을 경우

(이름 → 도로명 주소, 도로명 주소 → 우편 번호) 이고 이름 → 우편 번호 인 관계가 생성되므로 이를 분리해줄 수 있습니다.

 

BCNF (Boyce-codd Normal Form)

3차 정규화에서 해결할 수 없는 이상 현상 해결 가능. 3차 정규화를 만족하면서 모든 결정자가 후보키 집합에 속한 정규형 (모든 결정자가 후보키 집합에 속하도록)

 

반정규화

정규화된 엔티티, 속성, 관계에 대해 시스템의 성능 향상, 개발 운영의 단순화를 위해 중복, 통합, 분리 등을 수행하는 데이터 모델링 기법을 의미합니다.

반정규화는 데이터를 중복하여 성능을 향상시키기 위한 기법이라고 정의할 수 있고 좀 더 넓은 의미의 반정규화는 성능을 항상시키기 위해 정규화된 데이터 모델에서 중복, 통합, 분리 등을 수행하는 모든 과정을 의미합니다.

데이터 무결성이 깨질 수 있는 위험을 무릅쓰고 데이터를 중복하여 반정규화를 적용하는 이유는 데이터를 조회할 때 디스크 I/O량이 많아서 성능이 저하되거나 경로가 너무 멀어 조인으로 인한 성능저하가 예상되거나 칼럼을 계산하여 읽을 때 성능이 저하될 것이 예상되는 경우 반정규화를 수행하게 됩니다.

 

반정규화 시 고려사항

• 다량 데이터 탐색의 경우 인덱스가 아닌 파티션 및 데이터 클러스터링 등의 다양한 물리 저장 기법을 활용하여 성능 개선을 유도할 수 있습니다. 다만, 하나의 결과셋을 추출하기 위해 다량의 데이터를 탐색하는 처리가 반복적으로 빈번하게 발생한다면 반정규화를 하는 것이 좋습니다.
• 집계 테이블 이외에도 다양한 유형(다수 테이블의 키 연결 테이블 등)에 대하여 반정규화 테이블 적용이 필요할 수 있습니다.

 

기타

• 칼럼에 의한 반복적인 속성값(분류코드1, 분류코드2, 분류코드3 …)을 갖는 형태는 속성의 원자성을 위배한 제 1차 정규화의 대상이 됩니다.
• 반복적인 속성 나열 형태(속성에 분류코드1, 분류코드2, 분류코드3 …)에서는 각 속성에 대해 ‘or’ 연산자로 연결된 조건들이 사용되는데, 이 때 어느 한 속성이라도 인덱스가 정의되어 있지 않으면 ‘or’로 연결된 모든 조건절들이 인덱스를 사용하지 않고 한 번의 전체 데이터 스캔으로 처리되게 되어 성능 저하가 일어날 수 있다
• 모든 반복 속성에 대해 인덱스를 생성할 경우 검색 속도는 좋아지겠지만, 반대급부적으로 너무 많은 인덱스로 인해 입력, 수정, 삭제의 성능이 저하되므로, 1차 정규화를 통해서 자연스럽게 문제가 해결될 수 있도록 해야합니다.
• 이전 또는 이후 위치의 레코드에 대한 탐색은 윈도우 함수(LAG, LEAD)로 접근 가능합니다.

 

 

---

SQL 기본(1~10)

SQL 명령어 종류

명령어 종류	명령어	설명
데이터 조작어 DML (Data Manipulation Language)	SELECT	데이터 베이스에 들어 있는 데이터를 조회하거나 검색하기 위한 명령어를 말하는 것으로 RETRIEVE라고도 합니다.
	INSERT UPDATE DELETE	데이터베이스의 테이블에 들어 있는 데이터에 변형(삽입, 수정, 삭제)을 가하는 종류의 명령어들을 말합니다.
데이터 정의어 DDL (Data Definition Language)	CREATE ALTER DROP RENAME TRUNCATE	테이블과 같은 데이터 구조를 정의하는데 사용되는 명령어들로 그러한 구조를 생성, 변경, 삭제, 수정하여 데이터 구조와 관련된 명령어
데이터 제어어 DCL (Data Control Language)	GRANT REVOKE	데이터베이스에 접근하고 객체들을 사용하도록 권한을 주고 회수하는 명령어
트랜잭션 제어어 TCL (Transaction Control Language)	COMMIT ROLLBACK	논리적인 작업의 단위를 묶어서 DML에 의해 조작된 결과를 작업단위(Transaction) 별로 제어하는 명령어

 

Null의 의미

• 데이터 값이 존재하지 않는다는 것을 지시하는데 사용되는 표시어
• 조건에 맞는 데이터가 없을 때의 공집합과 다른 말이다
• 공백이나 숫자 0과는 다른 의미이다
• NULL과 모든 비교(IS NULL 제외)는 알 수 없음(Unknown)을 반환합니다.

 

UNIQUE 키워드

• 제약 조건으로 설정할 경우, 해당 필드는 유일성을 가짐 (중복된 값을 저장할 수 없다)
• 테이블 내에서 중복되는 값은 없지만 NULL 입력이 가능합니다.

 

 

 

---

SQL 활용(65~74)

순수 관계 연산자

새로운 관계형 데이터베이스를 만들 수 있는 연산자

• SELECT : Where절로 구현 (조건에 맞는 튜플만 선택)
• PROJECT : SELECT 절로 구현 (선택한 속성들로 결과 릴레이션을 생성)
• (NATURAL) JOIN : 다양한 JOIN 으로 구현 (공통 속성을 중심으로 2개의 릴레이션을 하나로 합침)
• DIVIDE : 현재 사용되지 않음

 

JOIN

공통 속성을 중심으로 2개의 릴레이션을 하나로 합쳐 새로운 릴레이션을 만드는 것

• INNER JOIN : 두 테이블에 JOIN 조건에서 동일한 값이 있는 행만 반환해줍니다.
• OUTER JOIN : 두 테이블에 JOIN 조건에서 동일한 값이 없는 행도 반환해줍니다.
• CROSS JOIN : 곱집합(M*N)으로, 두 테이블의 모든 행을 조인한 결과가 나옵니다.
• SELF JOIN : 자기 자신과 조인한 테이블을 의미합니다.

 

GROUP BY, Having

• GROUP BY 절을 통해 소그룹별 기준을 정한 후, SELECT에서 집계 함수를 사용할 수 있습니다.
• 집계 함수의 통계 정보는 NULL 값을 가진 행을 제외하고 수행됩니다.
• GROUP BY 절에서는 ALIAS 사용이 불가합니다.
• 집계 함수에서는 WHERE 절이 올 수 없습니다.
• HAVING 절에는 집계함수를 이용하여 조건 표시할 수 있습니다.
• HAVING 절은 일반적으로 GROUP BY 뒤에 위치합니다.