함께 배우는 수업 · 컴퓨터/정보
데이터베이스와 SQL 핵심 쿼리
관계형 데이터베이스(RDBMS)의 키(PK/FK)와 무결성 제약조건, SQL의 논리적 처리 단계와 물리 실행 계획의 차이, JOIN과 인덱스(Index), 트랜잭션 ACID를 학습합니다.
이 수업에서 해 볼 일
- SQL의 행 필터와 그룹 조건을 구분해 질의를 읽는다
먼저 알면 좋은 것: 2차원 표(행 Row/튜플, 열 Column/속성) 구조와 집합 연산 · 이진 탐색 트리와 B+Tree 자료구조의 탐색 원리 · 동시성 제어와 Lock의 기본 개념
오늘의 목표
가입 없이 시작할 수 있어요. 설명을 읽고, 함께 연습한 뒤, 처음 보는 문제에서 직접 확인합니다.
1. 관계형 데이터베이스(RDBMS)의 키(Key)와 SQL 처리 단계
- 핵심 키(Key)와 무결성 제약조건: 1. 기본키 (Primary Key, PK): 테이블의 각 행(튜플)을 유일하게 식별하는 컬럼입니다. 반드시 고유성(Unique, 중복 불가)과 최소성을 만족해야 하며, 절대NULL 값을 가질 수 없습니다(개체 무결성, Entity Integrity).
2. 외래키 (Foreign Key, FK): 다른 테이블의 기본키(PK)를 참조하여 두 테이블 간의 관계를 연결하는 컬럼입니다. 참조하는 테이블에 없는 값은 넣을 수 없습니다(참조 무결성, Referential Integrity).
- SQL 작성 순서와 논리적 처리 단계:
- 문법 작성 순서: SELECT ➔ FROM ➔ WHERE ➔ GROUP BY ➔ HAVING ➔ ORDER BY ➔ LIMIT
- 결과의 의미를 설명하는 논리적 처리 모델은 다음과 같습니다:
1. FROM (+ JOIN): 대상 테이블들을 가져와 결합합니다.
2. WHERE: 그룹화하기 전 개별 행(Row) 단위로 조건에 맞지 않는 데이터를 먼저 필터링합니다 (집계함수 SUM, COUNT 사용 불가!).
3. GROUP BY: 지정한 컬럼 기준으로 행들을 그룹으로 묶습니다.
4. HAVING: 그룹화된 결과에 대해 집계 함수 조건(예: HAVING COUNT(*) >= 5)으로 그룹을 필터링합니다.
5. SELECT: 출력할 컬럼을 계산하고 별칭(AS)을 부여합니다.
6. ORDER BY ➔ LIMIT: 결과를 정렬(ASC/DESC)하고 반환할 개수를 제한합니다.
- 물리 실행 계획은 별도로 선택됩니다: 데이터베이스의 파서와 최적화기는 같은 결과를 내는 여러 계획을 비교해 실행 방법을 고릅니다. 예를 들어 순차 스캔과 인덱스 스캔, 조인 순서와 중첩 루프·해시·병합 조인을 선택할 수 있습니다. 따라서 위 논리 단계가 디스크 읽기나 CPU 연산이 실제로 일어나는 고정된 순서를 뜻하지는 않습니다. PostgreSQL에서는 EXPLAIN으로 선택된 계획을 확인할 수 있습니다. ([planner and optimizer](https://www.postgresql.org/docs/current/planner-optimizer.html); [table expressions](https://www.postgresql.org/docs/current/queries-table-expressions.html))논리적 처리 모델에서 WHERE는 그룹화 전 개별 행을, HAVING은 그룹화 후 집계된 그룹을 거릅니다. 최적화기는 같은 결과를 유지하는 물리 실행 계획을 따로 선택합니다.2. 테이블 결합(JOIN)과 트랜잭션의 ACID 4대 특성
- JOIN의 핵심 종류: -INNER JOIN (내부 조인): 양쪽 테이블 모두에 조인 조건(ON)이 일치하는 교집합 행만 반환합니다.
- LEFT OUTER JOIN (왼쪽 외부 조인): 왼쪽 기준 테이블의 모든 행은 무조건 다 살리고, 오른쪽 테이블에 매칭되는 값이 없으면 해당 컬럼을 NULL로 채워서 반환합니다 (예: 주문 내역이 0건인 회원까지 모두 조회할 때 필수!).
- 트랜잭션 (Transaction)과 ACID 4대 특성:
- 데이터베이스의 상태를 변화시키는 쪼갤 수 없는 하나의 논리적 작업 단위(예: 계좌 이체 = A 계좌 인출 + B 계좌 입금)입니다.
1. 원자성 (Atomicity): All or Nothing! 트랜잭션 내의 모든 연산이 100% 성공적으로 반영(COMMIT)되거나, 하나라도 실패하면 실행 전 상태로 완전히 취소(ROLLBACK)되어야 합니다 (Undo 로그 활용).
2. 일관성 (Consistency): 트랜잭션 실행 전후로 DB의 모든 무결성 제약조건(잔액 , PK/FK 규칙 등)이 일관되게 유지되어야 합니다.
3. 고립성 / 독립성 (Isolation): 동시에 실행 중인 여러 트랜잭션들이 서로의 중간 연산 결과에 끼어들어 간섭하지 못하도록 격리되어야 합니다.
4. 영속성 / 지속성 (Durability): 일단 성공적으로 COMMIT된 트랜잭션 결과는 직후에 정전이나 서버 다운이 발생하더라도 Redo 로그를 통해 디스크에 영구적으로 보존되어야 합니다.예제로 살펴보기
직원 테이블Emp(id, name, dept_id, salary)에서 급여(salary)가 3000 이상인 직원들을 대상으로 부서(dept_id)별 평균 급여를 구하되, 부서 평균 급여가 5000 이상인 부서만 평균 급여 내림차순으로 조회하는 SQL 쿼리를 작성하시오.- 1) 개별 직원의 급여 조건(
salary >= 3000)은 그룹화 전에 걸러야 하므로WHERE절에 작성합니다. 2) 부서별로 묶기 위해GROUP BY dept_id를 사용합니다. 3) 그룹별 집계 조건인 평균 급여(AVG(salary) >= 5000)는 반드시HAVING절에 작성합니다. 4) 내림차순 정렬을 위해ORDER BY avg_sal DESC를 붙입니다.SELECT dept_id, AVG(salary) AS avg_sal FROM Emp WHERE salary >= 3000 GROUP BY dept_id HAVING AVG(salary) >= 5000 ORDER BY avg_sal DESC;
3. 데이터베이스 인덱스(B+Tree) 최적화와 정규화(Normalization)
- 인덱스 (Index)의 원리와 트레이드오프: - 책 맨 뒤의 색인(찾아보기)처럼 특정 컬럼 값을 B+Tree 구조로 정렬해 두면 인덱스에서 후보 위치를 찾는 탐색 비용을 줄일 수 있습니다. 다만 반환할 행이 많거나 조건 선택도가 낮으면 전체 테이블 순차 스캔이 더 저렴할 수 있으므로, 물리 실행 계획은 최적화기가 비용을 비교해 선택합니다. - 주의점: 인덱스를 걸면SELECT 조회 속도를 높일 수 있지만, 데이터를 추가·수정·삭제(INSERT, UPDATE, DELETE)할 때마다 인덱스도 갱신해야 하므로 쓰기 비용과 디스크 공간이 듭니다. 인덱스의 효과는 컬럼의 고유값 수뿐 아니라 조건의 선택도, 데이터 분포, 조회할 열에 따라 달라집니다.
- 정규화 (Normalization: 1NF ➔ 2NF ➔ 3NF ➔ BCNF):
- 하나의 거대한 테이블에 중복된 데이터가 쌓이면 삽입·삭제·수정 시 데이터가 불일치하는 이상 현상(Anomaly)이 발생합니다. 이를 막기 위해 함수적 종속성을 분석하여 테이블을 역할별로 잘게 쪼개어 중복을 제거하는 설계 과정이 정규화입니다.실무 쿼리 튜닝 팁: 인덱스 컬럼을 WHERE 절에서 가공(WHERE YEAR(created_at) = 2026 또는 WHERE price * 2 > 10000)하거나 앞에 와일드카드를 붙인 LIKE '%검색어'를 쓰면 B+Tree 정렬 순서를 활용할 수 없어 인덱스가 무력화(Full Table Scan 발생)됩니다!참고 근거와 확인 범위
- PostgreSQL · Querying a Table ↗
SELECT·WHERE·ORDER BY를 이용한 데이터 조회
설명·예제·문제 구성 및 오류 수정 책임: 프리런. 위 자료는 표시한 주제를 확인하거나 더 읽기 위한 근거이며, 외부 기관의 공식 수업이나 감수본을 뜻하지 않습니다.
참고 자료 연결 점검: 2026-11-02. 제작 방식·검수 범위와 수정 기록에서 확인 범위를 볼 수 있습니다. 오류는 상단의 의견 보내기 또는 contact@freelearn.io로 알려 주세요.