[SQL] SQLD 시험 준비!

전주은·2023년 3월 17일

문제유형

SQL 기본 문법 및 함수: SELECT, FROM, WHERE, GROUP BY, ORDER BY 등의 SQL 기본 문법과 COUNT, SUM, AVG, MAX, MIN 등의 집계 함수, 그리고 JOIN, 서브쿼리, 뷰 등의 개념과 사용법
데이터 모델링: 개념적, 논리적, 물리적 모델링에 대한 이해와 테이블 설계, 관계 표현
정규화: 정규화 개념과 정규화 수준을 판단
트랜잭션 관리: 트랜잭션 개념, COMMIT, ROLLBACK, SAVEPOINT
인덱스 및 성능 최적화: 인덱스 개념과 사용법, 성능 최적화를 위한 쿼리 튜닝
데이터베이스 보안: 권한 관리, 접근 제어, 암호화 등의 개념과 보안 정책
기타: 뷰, 저장 프로시저, 트리거 등의 개념과 사용법, SQL과 NoSQL 데이터베이스 비교

유형1.SQL 기본 문법 및 함수

SELECT 문법을 이용한 데이터 조회
문제: employees 테이블에서 모든 사원의 이름과 연봉(salary)을 조회하려고 합니다. 이를 위한 SQL 문을 작성해보세요.
```
SELECT emp_name, salary
FROM employees;
```
WHERE 절을 이용한 조건 필터링
문제: employees 테이블에서 연봉이 50000 이상인 사원의 이름, 직책, 연봉을 조회하려고 합니다. 이를 위한 SQL 문을 작성해보세요.
```
SELECT emp_name, job_title, salary
FROM employees
WHERE salary >= 50000;
```
GROUP BY 절을 이용한 그룹화 및 집계
문제: sales 테이블에서 각 제품(product)별로 판매량(quantity)과 판매금액(amount)의 합을 조회하려고 합니다. 이를 위한 SQL 문을 작성해보세요.
```
SELECT product, SUM(quantity) as total_quantity, SUM(amount) as total_amount
FROM sales
GROUP BY product;
```
ORDER BY 절을 이용한 정렬
문제: employees 테이블에서 직책(job_title)이 'Manager'인 사원의 이름(emp_name)과 연봉(salary)을 연봉이 높은 순으로 정렬하여 조회하려고 합니다. 이를 위한 SQL 문을 작성해보세요.
```
SELECT emp_name, salary
FROM employees
WHERE job_title = 'Manager'
ORDER BY salary DESC;
```
JOIN을 이용한 데이터 결합
문제: employees 테이블과 departments 테이블을 조인하여, 각 부서의 이름과 부서에 소속된 사원의 수를 조회하려고 합니다. 이를 위한 SQL 문을 작성해보세요.
```
SELECT d.dept_name, COUNT(e.emp_id) as num_employees
FROM departments d
JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_name;
```
서브쿼리를 이용한 데이터 조회
문제: employees 테이블에서 연봉이 80000 이상인 사원 중에서 가장 연봉이 높은 사원의 이름과 연봉을 조회하려고 합니다. 이를 위한 SQL 문을 작성해보세요.
```
SELECT emp_name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees WHERE salary >= 80000);
```
뷰 생성 및 조회
문제: employees 테이블에서 연봉이 50000 이상인 사원만을 대상으로 한 뷰(view)를 생성하고, 이를 이용하여 뷰를 조회하는 SQL 문을 작성해보세요.

CREATE VIEW high_salary_employees AS
SELECT *
FROM employees
WHERE salary >= 50000;

SELECT emp_name, job_title, salary
FROM high_salary_employees;

유형2. 데이터 모델링

데이터 모델링은 데이터베이스 설계의 핵심 과정 중 하나로, 데이터 요구 사항을 이해하고 구조화하여 데이터베이스를 설계하는 과정이다. 데이터 모델링은 주로 개념적 모델링, 논리적 모델링 및 물리적 모델링의 세 단계로 나뉜다.

구분	하는 일	-
개념적 모델링	데이터 요구 사항을 수집하고 개념적 모델을 만든다.	업무 프로세스, 엔티티, 관계, 속성 등
논리적 모델링	개념적 모델을 상세화하여 논리적 모델을 만든다.	테이블, 관계, 속성, 제약 조건 등
물리적 모델링	논리적 모델을 구현 가능한 물리적 데이터베이스 모델로 변환한다.	테이블, 인덱스, 제약 조건, 데이터 유형, 크기 등

개념적 모델링 문제

다음 요구사항을 반영하는 개념적 데이터 모델을 작성하십시오.

- 은행에서는 계좌와 고객 정보를 관리합니다.
- 고객은 여러 계좌를 가질 수 있으며, 계좌는 하나의 고객에 속합니다.
- 계좌는 계좌 번호, 계좌 종류, 잔액 및 개설일을 포함합니다.
- 고객은 고객 번호, 이름, 생년월일, 전화번호 및 주소를 포함합니다.

정답:

업무 프로세스: 은행에서는 계좌와 고객 정보를 관리한다.
엔티티: 고객, 계좌
관계: 고객은 여러 개의 계좌를 가질 수 있으며, 계좌는 하나의 고객에 속한다.
속성:
✔ 고객 엔티티

속성	데이터 타입
고객 번호	INT
이름	VARCHAR
생년월일	DATE
전화번호	VARCHAR
주소	VARCHAR

✔ 계좌 엔티티

속성	데이터 타입
계좌 번호	INT
고객 번호	INT
개설일	DATE
계좌 종류	VARCHAR
잔액	INT

논리적 모델링 문제

다음 요구사항을 반영하는 논리적 데이터 모델을 작성하십시오.

- 은행에서는 계좌와 고객 정보를 관리합니다.
- 고객은 여러 계좌를 가질 수 있으며, 계좌는 하나의 고객에 속합니다.
- 계좌는 계좌 번호, 계좌 종류, 잔액 및 개설일을 포함합니다.
- 고객은 고객 번호, 이름, 생년월일, 전화번호 및 주소를 포함합니다.
- 고객은 계좌를 개설하거나 종료할 수 있습니다.
- 은행은 모든 계좌에 대한 입금, 출금 및 이체를 기록합니다.

해답:

엔티티: 고객, 계좌, 거래
관계: 고객은 여러 개의 계좌를 가질 수 있으며, 계좌는 하나의 고객에 속한다. 거래는 계좌에 대한 입금, 출금 및 이체를 포함한다. 고객은 계좌를 개설하거나 종료할 수 있다.
속성
✔ 고객 테이블

속성	데이터 타입
고객 번호	INT
이름	VARCHAR
생년월일	DATE
전화번호	VARCHAR
주소	VARCHAR

✔ 계좌 테이블

속성	데이터 타입
계좌 번호	INT
고객 번호	INT
개설일	DATE
종료일	DATE
계좌 종류	VARCHAR
잔액	INT

✔ 거래 테이블

속성	데이터 타입
거래 ID	INT
계좌 번호	INT
거래 유형	VARCHAR
거래 일자	DATE
거래 금액	INT

물리적 모델링 문제

다음 요구사항을 반영하는 물리적 데이터 모델을 작성하십시오.

- 은행에서는 계좌와 고객 정보를 관리합니다.
- 고객은 여러 계좌를 가질 수 있으며, 계좌는 하나의 고객에 속합니다.
- 계좌는 계좌 번호, 계좌 종류, 잔액 및 개설일을 포함합니다.
- 고객은 고객 번호, 이름, 생년월일, 전화번호 및 주소를 포함합니다.
- 고객은 계좌를 개설하거나 종료할 수 있습니다.
- 은행은 모든 계좌에 대한 입금, 출금 및 이체를 기록합니다.

해답:

엔티티: 고객, 계좌, 거래
관계: 고객은 여러 개의 계좌를 가질 수 있으며, 계좌는 하나의 고객에 속한다.
속성:
✔ 고객 테이블

속성	데이터 타입
고객 ID	INT(PK)
이름	VARCHAR(50)
생년월일	DATE
전화번호	VARCHAR(50)
주소	VARCHAR(100)

✔ 계좌 테이블

속성	데이터 타입
계좌 ID	INT(PK)
고객 ID	INT(FK)
개설일	DATE
종료일	DATE
계좌 종류	VARCHAR(50)
계좌 번호	VARCHAR(20)
잔액	INT

✔ 거래 테이블

속성	데이터 타입
거래 ID	INT(PK)
계좌 ID	INT(FK)
거래 유형	VARCHAR(50)
거래 일자	DATE
거래 금액	INT

👀 엔티티 vs 테이블
엔티티: 업무상 의미가 있는 개념 또는 정보의 단위를 의미한다. 엔티티는 특정 업무 영역에서 중요한 개념이며, 이를 데이터베이스에 표현하기 위해 테이블을 생성한다.
테이블: 데이터베이스에서 데이터를 저장하기 위한 구조이며, 행(row)과 열(column)의 형태로 데이터를 저장한다. 테이블은 여러 개의 엔티티를 담을 수 있으며, 하나의 엔티티를 담을 수도 있다.

유형3. 정규화 개념과 정규화 수준을 판단

정규화 개념 이해
문제: 정규화란 무엇인가요?

답변: 정규화는 데이터베이스 설계 시 중복을 최소화하고 데이터 무결성을 보장하기 위한 프로세스로, 테이블을 작은 단위로 분해하여 중복을 최소화하고 각각의 테이블이 의도한 대로 데이터를 저장할 수 있도록 설계하는 것을 말한다.

정규화 수준 판단
문제: 아래의 테이블을 정규화하면 몇 차 정규화인가요?

order_id	customer_id	customer_name	customer_address	product_id	product_name	quantity
1	101	John Smith	123 Main St.	1001	iPhone	2
1	101	John Smith	123 Main St.	1002	iPad	1
2	102	Jane Doe	456 Oak St.	1003	MacBook	3

답변: 위의 테이블은 1차 정규화를 만족하지 않습니다. 중복된 데이터가 많고, 각각의 속성이 원자적이지 않다. 이를 1차 정규화로 분해하면 다음과 같은 결과가 나온다.

order_id	customer_id	customer_name	customer_address
1	101	John Smith	123 Main St.
2	102	Jane Doe	456 Oak St.

order_id	product_id	product_name	quantity
1	1001	iPhone	2
1	1002	iPad	1
2	1003	MacBook	3

이렇게 분해된 테이블은 1차 정규화를 만족하며, 각각의 테이블은 원자적인 속성을 갖는다. 따라서, 위의 테이블은 1차 정규화를 만족한다.

전주은

이전 포스트

[Java] 상속

다음 포스트