책) SQLD

키워드 정리

1 데이터 모델링

---

1) 모델링 (Modeling)

현실세계를 추상화 표현

• 특징: 추상화, 단순화, 명확화
• 관점: 데이터, 프로세스, 데이터와 프로세스 상관
• 유의사항: 중복, 비유연성, 비일관성
• 모델링 3단계
  • 1 개념적: 전사적 추상화 모델링
  • 2 논리적: key, 속성, 관계 지정
  • 3 물리적: 실제 DB
• 데이터 독립성 - ANSI-SPARK 아키텍쳐
  • 1 외부 스키마: 각 사용자가 보는 DB View 정의
  • | ㅡㅡ 논리적 독립성 ㅡㅡ |
  • 2 개념 스키마: 모든 사용자가 보는 전체 DB 표현
  • | ㅡㅡ 물리적 독립성 ㅡㅡ |
  • 3 내부 스키마: 물리적인 저장 구조
• ERD
  • 표기방식: IE/Crow's Foot
  • 작성순서: 엔티티 배치 - 관계 설정 - 관계명 - 참여도 - 필수여부

2) 엔티티 (Entity)

식별 가능한 사물 객체

• 유형/무형에 따른 분류
  • 유형 엔티티: 물리적 형태 존재 ex) 학생
  • 개념 엔티티: 개념적 형태 존재 ex) 학과
  • 사건 엔티티: 행위로 인해 발생 ex) 주문
• 발생시점에 따른 분류
  • 기본 엔티티: ex) 상품
  • 중심 엔티티: ex) 주문
  • 행위 엔티티: ex) 주문 내역

3) 속성 (Attribute)

사물의 특징을 설명하는 항목

• 속성에 따른 분류
  • 기본속성: ex) 학생이름
  • 설계속성: ex) 학번 (학생이름 + 학년 + 학과) -> UNIQUE
  • 파생속성: ex) 재고 개수 (다른 속성의 속성값 계산)
• 구성방식에 따른 분류
  • PK 속성: ex) 회원번호
  • FK 속성: ex) 회원등급코드
  • 일반속성: ex) 회원명
• 도메인
  • 속성이 가질 수 있는 값의 범위

4) 관계 (Relationship)

엔티티와 엔티티와의 관계

• 분류
  • 존재 관계
  • 행위 관계
• 표기법
  • 관계명
  • 관계차수
  • 관계선택사양

5) 식별자 (Identifiers)

엔티티 내의 각각의 인스턴스를 구분 가능하게 해주는 속성

• 분류
  • 대표성 여부: 주식별자 | 보조식별자
  • 스스로 생성 여부: 내부식별자 | 외부식별자
  • 단일 속성 여부: 단일식별자 | 복합식별자 ex) 회원번호 | 주문일자, 순번
  • 대체 여부: 본질식별자 | 인조식별자 ex) 주문일자, 순번 | 주문번호
• 식별자 vs 비식별자
  • 식별자: 부모엔티티 식별자가 자식엔티티의 주식별자 - 부모엔티티 필수
  • 비식별자: 부모엔티티 식별자가 자식엔티티의 일반속성 - 부모엔티티 필수X

6) 정규화 (Normalization)

데이터 정합성을 위해 엔티티를 작은 단위로 분리

• 성능 향상: 조회(상황에 따라), 입력, 수정, 삭제
• 1차 정규화: 속성값은 하나의 값만 가짐, 유사한 속성 반복 X
• 2차 정규화: 일반속성은 주식별자에 부분 종속 X
• 3차 정규화: 일반속성끼리 종속 X

7) 반정규화 (De-normalization)

데이터 조회 성능 향상시키기 위해 데이터 중복 허용

• 성능 하락: 입력, 수정, 삭제
• 테이블 반정규화
  • 테이블 병합: JOIN이 많은 경우 통합 (1:1, 1:N)
  • 테이블 분할: 수직 분할 (1:1만 가능) / 수평 분할 (파티셔닝)
  • 테이블 추가: 통계, 이력, 부분 테이블 추가
• 칼럼 반정규화
  • 중복 칼럼 추가
  • 파생 칼럼 추가: 속성 계산값 미리 추가
  • 이력 테이블 칼럼 추가: 최신 데이터 여부 등
• 관계 반정규화
  • 중복 관계 추가

2 SQL 기본

---

1) SELECT

• 함수
  • 문자 함수
    • CHR, LOWER,UPPER,LTRIM,RTRIM,TRIM,SUBTSR, LENGTH, REPLACE, LPAD
  • 숫자 함수
    • ABS,SIGN,ROUND,TRUNC,CEIL, FLOOR, MOD
  • 날짜 함수
    • SYSDATE,EXTRACT,ADD_MONTH
  • 변환 함수
    • TO_NUMBER, TO_CHAR, TO_DATE
  • NULL 관련 함수
    • NVL, NULLIF, COALESCE, NVL2
  • CASE
    • CASE-WHEN-THEN-ELSE-END

2) WHERE

• 연산자
  • 비교 연산자
    • =, !=, not col = 1, ...
  • SQL 연산자
    • BETWEEN A AND B, LIKE '%', IN (LIST), IS NULL
  • 논리 연산자
    • () -> NOT -> AND -> OR

3) GROUP BY, HAVING

• 함수
  • COUNT(DISTINCT COL), SUM(COL), ...
• 실행 순서
  • FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY
• 주의점
  • HAVING에서 SELECT alias 못씀
  • HAVING 말고 WHERE 에서 필터해야 GROUP BY 할때 데이터 줄어듬

4) ORDER BY

• N/A

5) JOIN

• 종류
  • INNER JOIN
  • OUTER JOIN
  • NATURAL JOIN: 두 테이블 칼럼, 데이터 같은거
  • CROSS JOIN: 두 테이블 모든 경우의 수 조합
• 유의점
  • 그냥 JOIN = NATURAL JOIN
  • 그냥 FROM TABLE1, TABLE2 = CROSS JOIN

3 SQL 활용

---

1) 서브쿼리 (Subquery)

쿼리 안의 쿼리

• 위치에 따른 구분
  • 스칼라 서브쿼리: SELECT, 대부분 가능 -> 하나의 값 반환
  • 인라인 뷰: FROM
  • 중첩 서브쿼리: WHERE, HAVING -> 연관, 비연관

2) 뷰 (View)

재사용 가능한 오브젝트 가상 테이블

• 특징: 보안성, 독립성, 편리성

3) 집합 연산자

• UNION ALL: 중복 O
• UNION: 중복 X
• INTERSECT
• MINUS, EXCEPT

4) 그룹 함수

• ROLL UP(A, B): A,B / A / 총합계
• CUBE(A, B): A,B / A / B / 총합계
• GROUPING SET(A, B): A, B

5) 윈도우 함수

OVER 키워드와 함께 사용됨

• 순위 함수
  • RANK, DENSE_RANK, ROW_NUMBER
• 집계 함수
  • SUM, MAX, MIN, AVG, COUNT
• 행 순서 함수
  • FIRST_VALUE, LAST_VALUE, LAG, LEAD
• 비율 함수
  • CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT

6) Top-N 쿼리

• ROWNUM
• ROW_NUMBER()
• RANK()

7) 셀프 조인 (Self Join)

동일 테이블끼리의 조인

• 대-중-소 구조 가지고 있는 테이블의 경우 사용

8) 계층 쿼리

계층 구조를 이루고 있는 컬럼이 존재하는 경우 계층 데이터 출력

• LEVEL, SYS_CONNECT_BY_PATH, START WITH, CONNECT_ BY, PRIOR

9) PIVOT, UNPIVOT

• PIVOT: 행 -> 열
  • 집계 함수, FOR, IN
• UNPIVOT: 열 -> 행
  • 칼럼, FOR, IN

10) SQL 관리구문

• DML: Data Manipulate Language
  • INSERT, UPDATE, DELETE, MERGE
• TCL: Transaction Control Language
  • COMMIT, ROLLBACK, SAVEPOINT
• DDL: Data Definition Language
  • CREATE, ALTER, DROP, RENAME, TRUNCATE
• DCL: Data Control Language
  • USER 관련: CREATE, ALTER, DROP
  • 권한 관련: GRANT, REVOKE

4 기출 오답

---

1) 기출 1회

• 대체 여부에 따른 식별자
  • 본질식별자: 업무에 의해 만들어짐 ex) 주문일자
  • 인조식별자: 원조 식별자가 복잡해서 인위적 ex) 주문번호 + 순번
• CUBE(A,B)
  • A,B / A / B / 총합계
• MINUS
  • 중복 있으면 제거됨
• DDL
  • DDL 후 자동커밋 되서 ROLLBACK 불가
• COUNT
  • COUNT(숫자), COUNT(*)은 모든 행의 수, 아니면 NULL 제외한 수

2) 기출 2회

• 계층 쿼리
  • CONNECT BY COL1 = PRIOR COL2: start COL2와 다음 COL1 같은거
• DCL GRANT
  • GRANT 권한 ON TABLE명 TO 유저명
• Oracle DMBS OUTER JOIN
  • (+) 가 없는쪽이 기준

3) 기출 3회

• 행위엔티티
  • 두 개 이상의 부모 엔티티로부터 발생
• NULL 관련 함수
  • NVL(A, B): A가 NULL 이면 B, 아니면 A
  • NULLIF(A, B): A == B 이면 NULL 아니면 A
  • COALESCE(A, B, C, ...): NULL이 아닌 최초 인수
  • NVL2(A, B, C): A가 NULL이면 C 아니면 B
• 연산자 우선순위
  • 산술 > 연결(||) > 비교 > SQL(IN) > () > NOT > AND > OR
• DDL TRUNCATE
  • ROLLBACK 불가
• ROUND(7.45, 1)
  • 두번째 인자값만큼 소수 표시 (2번째 에서 반올림)

4) 기출 4회

• WHERE (COL1, COL2) IN ((20, 10),(0, 10))
  • = WHERE (COL1 = 20 AND COL2 = 10) OR (COL1 = 0 AND COL2 = 10)
• LAG, LEAD
  • LAG: 현재 행 위의 값, LEAD: 현재 행 아래 값
• NULL과의 연산은 항상 NULL
• LTRIM('SQLDEVELOPER', 'LQS')
  • 왼쪽 포함된거 지우다가 없으면 멈춤

5) 기출 5회

• 관계 엔티티
  • 예외적으로 주식별자만 가지고 있어도 됨
• 교차 엔티티
  • MxN 관계 해소를 위한 인위적 엔티티
• 집계 함수
  • ROLL UP(A, B): A,B / A / 총합계 -> A,B 순서 영향있음
  • CUBE(A, B): A,B / A / B / 총합계
  • GROUPING SET(A, B): A / B
• WHERE (COL1, COL2) IN ((1000, 2000))
  • COL1 = 1000 AND COL2 = 2000
• WHERE ANY <
  • 값들 중 최솟값보다 크면 TRUE
• PARTITION 백분율
  • PERCENT_RANK: 순서별 백분율
  • CUMB_TIST: 누적 백분율