SQL 개발자 (SQLD) 개념 정리

개요

이 단원은 업무에 필요한 데이터를 어떻게 정리해 그림(모델)으로 나타내는지를 배웁니다. 먼저 데이터 모델링의 단계와 3층 스키마를 익히고, 이어서 모델을 이루는 네 가지 요소인 엔터티·속성·관계·식별자를 차례로 공부합니다.

데이터 모델링은 개념적 모델링에서 시작해 논리적 모델링, 물리적 모델링 순서로 점점 구체적으로 내려갑니다. 개념적 모델링은 핵심 엔터티와 관계를 크게 그리는 단계이고, 논리적 모델링은 키와 속성을 정하고 정규화를 하는 단계이며, 물리적 모델링은 실제 DBMS 에 만들 테이블과 인덱스, 저장 구조를 정하는 단계입니다. 3층 스키마는 사용자 관점의 외부 스키마, 전체를 통합한 개념 스키마, 저장 구조인 내부 스키마로 나뉘어 데이터 독립성을 지켜 줍니다.

엔터티는 업무에서 관리해야 할 대상으로, 식별자가 있고 인스턴스가 두 개 이상이며 속성과 관계를 가져야 합니다. 속성은 엔터티가 가진 더 나눌 수 없는 정보 하나이며, 기본 속성·설계 속성·파생 속성으로 나뉩니다. 관계는 엔터티끼리의 연결로, 몇 개와 연결되는지(관계 차수)와 반드시 있어야 하는지(관계 선택성)를 함께 나타냅니다.

식별자는 인스턴스를 하나씩 구별하는 속성으로, 주식별자는 유일성·최소성·불변성·존재성을 갖춰야 합니다. 부모의 주식별자가 자식의 주식별자 일부가 되면 식별 관계, 자식의 일반 속성이 되면 비식별 관계라고 하며, IE 표기법에서는 각각 실선과 점선으로 그립니다.

개요

이 단원은 데이터 모델이 실제 SQL 과 어떻게 이어지는지를 배웁니다. 정규화와 반정규화로 테이블을 나누고 합치는 원리를 먼저 익히고, 관계가 조인으로 바뀌는 방식, 트랜잭션을 모델에 표현하는 방법, NULL 의 성질, 본질식별자와 인조식별자를 차례로 공부합니다.

정규화는 중복을 없애 입력·수정·삭제 이상을 줄이는 작업입니다. 제1정규형은 모든 칸에 값이 하나씩 들어간 상태, 제2정규형은 주식별자 일부에만 종속된 속성을 떼어 낸 상태, 제3정규형은 일반 속성끼리의 종속(이행 함수 종속)을 떼어 낸 상태, BCNF 는 모든 결정자가 후보키인 상태입니다. 반대로 반정규화는 조회 성능을 위해 일부러 중복을 허용하는 것으로, 무결성이 깨질 위험이 있어 다른 방법을 먼저 검토합니다.

모델의 관계는 테이블에서 부모의 기본키와 자식의 외래키로 만들어지며, 조인할 때 이 둘을 같게 비교합니다. 하나의 트랜잭션으로 함께 저장되어야 하는 엔터티는 서로 필수 관계로 표현합니다. NULL 은 '모르는 값'이라 산술 연산 결과는 NULL, 비교 결과는 '알 수 없음'이 되고, 집계 함수는 NULL 을 빼고 계산합니다.

식별자는 업무에서 자연스럽게 생기는 본질식별자와, 복잡한 식별자를 대신해 일련번호처럼 만든 인조식별자로 나뉩니다. 인조식별자는 키가 단순해지지만 같은 데이터가 중복 입력될 수 있고, 이를 막기 위해 인덱스가 더 필요할 수 있습니다.

개요

이 단원은 SQL 로 데이터를 꺼내 보는 기본기를 배웁니다. 관계형 데이터베이스의 구조를 먼저 익히고, SELECT 문의 각 절(FROM·WHERE·GROUP BY·HAVING·ORDER BY)과 여러 함수, 두 테이블을 잇는 조인을 차례로 공부합니다.

SELECT 문은 적힌 순서와 달리 FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY 순서로 처리됩니다. WHERE 는 묶기 전에 행을 거르므로 집계 함수를 쓸 수 없고, 묶은 결과에 조건을 걸 때는 HAVING 을 씁니다. GROUP BY 를 쓰면 SELECT 절에는 GROUP BY 에 적은 컬럼과 집계 함수만 올 수 있습니다.

NULL 은 = 로 비교할 수 없어 IS NULL 로 찾고, NOT IN 목록에 NULL 이 있으면 결과가 하나도 나오지 않습니다. COUNT(*) 는 모든 행을, COUNT(컬럼) 은 NULL 이 아닌 행만 셉니다. NVL·COALESCE·NULLIF·CASE·DECODE 같은 함수는 결과를 손으로 따라갈 수 있어야 하며, Oracle 은 빈 문자열을 NULL 로 다루고 정렬할 때 NULL 을 가장 큰 값으로 본다는 점도 자주 나옵니다.

조인은 두 테이블의 행을 짝지어 하나로 보여 줍니다. INNER JOIN 은 짝이 있는 행만, LEFT OUTER JOIN 은 왼쪽 테이블의 행을 모두 남기고, FULL OUTER JOIN 은 양쪽 행을 모두 남깁니다. CROSS JOIN 은 모든 조합을 만들어 행 수가 두 테이블 행 수의 곱이 되고, NATURAL JOIN 과 USING 은 이름이 같은 컬럼으로 조인합니다.

개요

이 단원은 SELECT 문을 한 단계 더 깊이 쓰는 방법을 배웁니다. 서브쿼리와 집합 연산자로 여러 결과를 엮는 법을 먼저 익히고, 소계를 만드는 그룹 함수, 행마다 순위·누적값을 붙이는 윈도우 함수, 상위 몇 건만 뽑는 Top N, 계층형 질의와 셀프 조인, PIVOT·UNPIVOT, 정규 표현식을 차례로 공부합니다.

서브쿼리는 위치에 따라 SELECT 절의 스칼라 서브쿼리, FROM 절의 인라인 뷰, WHERE 절의 중첩 서브쿼리로 나뉩니다. 결과가 여러 행이면 IN·ANY·ALL·EXISTS 를 쓰며, > ANY 는 가장 작은 값보다 크다는 뜻이고 > ALL 은 가장 큰 값보다 크다는 뜻입니다. 집합 연산자는 UNION(중복 제거), UNION ALL(중복 그대로), INTERSECT(교집합), MINUS(차집합)가 있고, 합치는 SELECT 문들은 컬럼 수가 같아야 합니다.

그룹 함수 ROLLUP(A, B) 는 (A, B)·(A)·전체 소계를, CUBE(A, B) 는 모든 조합의 소계를, GROUPING SETS 는 적은 기준의 소계만 만듭니다. 윈도우 함수는 행 수를 줄이지 않고 각 행에 결과를 붙이며, 같은 값이 있을 때 RANK 는 순위를 건너뛰고 DENSE_RANK 는 건너뛰지 않으며 ROW_NUMBER 는 언제나 다른 번호를 줍니다. ORDER BY 만 쓰면 기본 범위가 RANGE 라서 같은 값의 행이 함께 누적됩니다.

Oracle 의 ROWNUM 은 정렬보다 먼저 붙으므로 Top N 은 인라인 뷰에서 정렬한 뒤 바깥에서 ROWNUM 을 겁니다. 계층형 질의는 START WITH 로 시작 행을, CONNECT BY PRIOR 로 전개 방향을 정하고, LEVEL·CONNECT_BY_ISLEAF·SYS_CONNECT_BY_PATH 로 깊이와 경로를 봅니다. PIVOT 은 행을 열로, UNPIVOT 은 열을 행으로 바꾸며, REGEXP_LIKE·REGEXP_SUBSTR·REGEXP_REPLACE 로 정규 표현식을 씁니다.

개요

이 단원은 데이터와 테이블을 직접 바꾸고 관리하는 명령을 배웁니다. SQL 명령의 네 가지 종류를 먼저 구분하고, 데이터를 넣고 고치고 지우는 DML, 변경을 확정하거나 취소하는 TCL, 테이블 구조를 만드는 DDL 과 제약조건, 권한을 다루는 DCL 을 차례로 공부합니다.

DML 에는 INSERT·UPDATE·DELETE·MERGE 가 있습니다. INSERT 에서 컬럼을 빼면 기본값(DEFAULT)이, 기본값이 없으면 NULL 이 들어갑니다. MERGE 는 원본 데이터 가운데 대상에 있는 행은 고치고(WHEN MATCHED) 없는 행은 새로 넣습니다(WHEN NOT MATCHED).

트랜잭션은 원자성·일관성·고립성·지속성(ACID)을 가져야 합니다. COMMIT 은 변경을 확정하고, ROLLBACK 은 확정하지 않은 변경을 취소하며, ROLLBACK TO 저장점은 그 저장점 뒤의 변경만 취소합니다. Oracle 에서는 CREATE·ALTER·DROP·TRUNCATE 같은 DDL 을 실행하면 그 전까지의 변경이 자동으로 COMMIT 됩니다.

DDL 로 테이블을 만들 때 기본키·UNIQUE·NOT NULL·CHECK·외래키 같은 제약조건을 겁니다. 외래키에는 부모 행을 지울 때 자식 행도 지우는 ON DELETE CASCADE, 자식의 외래키를 NULL 로 바꾸는 ON DELETE SET NULL 옵션이 있습니다. DCL 의 GRANT 는 권한을 주고 REVOKE 는 빼앗으며, 여러 권한을 묶은 롤(ROLE)로 한꺼번에 관리할 수 있습니다.