[DB] SQLD 자격증 요약 내용 필기 정리

데이터 모델 특징  = 추상화, 단순화, 정확화

 

데이터 모델링 유의점 = 중복, 비유연성, 비일관성

 

데이터 독립성 구성요소

개념 스키마 - 모든 사용자 관점

외부 스키마 - 여러 사용자 관점

내부 스키마 - 물리적인 저장구조 표현

 

엔터티 특징

반드시 해당업무에서 필요하고 관리하고자 하는 정보

유일한 식별자에 의해 식별가능

영속성으로 존재하는 인스턴스의 집합 (두개이상)

업무프로세스에 의해 이용

반드시 속성 존재

다른엔터티와 최소 한개 이상 관계

 

발생시점에 따른 엔터티 구분  - 기본엔터티, 중심엔터티, 행위엔터티

 

엔터티, 인스턴스,속성, 속성값 관계

한개의 엔터티는 두개이상의 인스턴스 집합

한개의 엔터티는 두개이상의 속성을 가짐

한개의 속성은 한개의 속성값을 가짐

 

데이터 모델링의 관계

연관관계

  - 항상 이용하는 관계로 존재적 관계에 해당

  - 소스코드에서 멤버변수로 선언하여 사용

의존관계

  - 상대방 클래스의 행위에 의해 관계가 형성되는 행위적 관계 해당

  - 오퍼레이션에서 파라미터 등으로 이용

관계의 표기법 - 관계명, 관계차수, 선택성(선택사양) [필수관계,선택관계]

 

주식별자 특징 : 유일성, 최소성, 불변성, 존재성

 

속성의 특성에 따른 분류

  - 기본속성 : 업무분석을 통해 정의

  - 설계속성 : 업무규칙화를 위해 설계하면서 도출

  - 파생속성 : 다른 속성의 영향을 받아 도출

 

정규화

논리데이터 모델 상세화 과정의 대표환경

논리데이터 모델의 일광성을 확보하고 중복을 제거하여 속성들이 가장 적절한 엔터티에 배치 되도록 함으로써

보다 더 신뢰성 있는 데이터 구조를 얻는데 목적

 

관계

부모의 식별자를 자식의 식별자에 포함하면 식별관계, 부모의 식별자를 자식의 일반속성으로 상속하면 비식별 관계

 

정규형

제1 정규형 : 모든 속성은 반드시 하나의 값을 가짐

제2 정규형 : 엔터티의 일반속성은 주식별자 전체에 종속

제3 정규형 : 엔터티의 일반속성 간에는 서로 종속적이지 않음

 

SQL 문장 종류

데이터 조작어 (DML) : SELECT , INSERT, UPDATE, DELETE

데이터 정의어 (DDL) : CREATE, ALTER, DROP, RENAME

데이터 제어어 (DCL) : GRANT, REVOKE

트랙젠션 제어어 (TCL) : COMMIT, ROLLBACK

 

WHERE 절에는 집계 함수 사용불가 (집계함수 : COUNT, AVG, SUM, MAX, MIN)

 

SQL 연산자 IN(list) : 리스트에 있는 값중에 어느 하나라도 일치하면 됨

 

해당연월 추출 = TO_CHAR(SVC_END_DATE, 'YYYYMM')

TO_DATE('202405024','YYYYMM') = SVC_END_DATE - >문자를 날짤 변환해서 비교 

 

GROUP BY 절과 HAVING 절의 특성

집계함수의 통계정보는 NULL값을 가진 행을 제외하고 수행

GROUP BY 절에서는 ALIAS명 사용 불가

집계함수는 WHERE 절 사용불가

GROUP BY 절에 의해 소그룹 별로 만들어진 집계 데이터 중 HAVING절에서 제한조건을 두어 조건을

만족하는 내용만 출력

 

CASE WHEN LOC = 'NEW YORK' THEN 'EAST' ELSE 'ETC' END

  == CASE LOC WHEN 'NEW YORK' THEN 'EAST' ELSE 'ETC' END

  == DECODE (LOC,'NEW YORK', 'EAST', 'ETC')

 

NVL (표현식1, 표현식2) = IS NULL (표현식1, 표현식2 )

  - > 표현식1의 값이 NULL이면 표현식2의 값 출력

 

NULLIF (표현식1, 표현식2 )

  - > 표현식1이 표현식2와 같으면 NULL을 , 다르다면 표현식 1 리턴

 

COALESCE (표현식1, 표현식2 ... )

  - > 임의의 개수 표현식에서 NULL이 아닌 최초의 표현식 리턴 , 모든 표현식이 NULL 이면 NULL을 반환

 

단일행 문자형 함수

  - > CONCAT (문자열1, 문자열2 ) : 문자열 1과 문자열 2 연결

         - > 합성 연산자 '||' [ORACLE] 아니면 '+'  [SQL SERVER]와 동일

 

FROM 절 서브쿼리 에서는 상관관계 서브쿼리를 사용하면 안됨. - > 메인항목 ID 등

 

ORDER BY 절 특징

  - 기본적인 정렬순서는 오름차순(ASC)으로 정렬

  - 오라클에서 NULL값은 가장 큰 값으로 간주하여 오름차순(ASC) 정렬 시  가장 마지막,

                                                                                 내림차순(DESC) 정렬 했을 시 가장 먼저 출력 (SQL SERVER는 반대 )

 

순수관계 연산자 : SELECT, PROJECT, JOIN DIVIDE

 

USING 조건을 이용한 EQUI JOIN에서도 NATURAL JOIN과 마찬가지로 JOIN 컬럼에 대해 ALIAS 나 테이블 이름과 같은

접두사를 볼수 없음.

 

테이블 간 JOIN 조건이 없는 경우, CROSS JOIN 사용  : 생길 수 있는 모든 데이터 조합 발생 , 결과는 M x N 데이터 집합

 

집합 연산자를 사용한 SQL의 ORDER BY 절은 최종결과를 정렬하며, 가장 마지막 줄에 한번만 사용

 

GROUPING SETS ( grade, (job, grade)) 

   - grade , ROLLUP(job)

   - grade, CUBE(job)

 

INTERSECT = NOT EXIST 구문 + MINUS

 

RATIO_TO_REPORT : 전체 합계 대비 비율 또는 백분율 구하기 위해 사용

 

1:1 보장된 두집합의 관계 (동일한 집합)

교집합 INTERSECT , 합집합 UNION => 전체 건수와 동일

합집합 UNION ALL => 전체건수의 2배

차집합 EXCEPT => 공집합

 

ORDER SIBLINGS BY : 같은 부모를 가진 형제들의 정렬 순서를 바꿔줌

 

PK 항목 ( 유니크, NOT NULL)

부모 항목 ( NULL, 중복가능)

PRIOR 가 PK에 붙으면 부모 -> 자식 (순방향)

PRIOR 가 부모에 붙으면 자식 -> 부모 (역방향)

 

오라클 계층형 질의에서 루트노트 LEVEL 값은 1

CONNECT BY : 계층 전개 조건

START WITH : 계층 시작 조건

오라클 계층형 질의문에서 PRIOR 키워드는 CONNECT 절 뿐만 아니라 SELECT 절, WHERE 절에서도 사용 가능.

 

PERCENT-RANK : 파티션별 윈도우에서 제일 먼저 나오는 것을 0 으로 제일 늦게 나오는 것을 1 로 하여

값이 아닌 행의 순서별 백분율을 구함

 

DENSE - RANK : 동일 값에 대해 동일 순위 부여 ( 순위 건너 뛰지 않음 ! )

CUME -DIST : 현재 행보다 작거나 같은 같은 건수에 대한 누적 백분율

 

RANK : 동일 값에 대해 동일 순위 부여 ( 그다음 순위 건너뜀)

NTILE : 파티션 별 전체 건수를 인수값으로 N등분한 결과를 구함. => ~를 기준으로 N개 그룹 분류

 

뷰 (VIEW) 사용의 장점

   - 독립성 : 테이블 구조가 변경 되어도 뷰를 사용하는 프로그램은 변경하지 않아도 됨

   - 편리성 : 복잡한 SQL 문장을 단순화

   - 보안성 : 숨기고 싶은 정보를 감추고 생성 가능

 

그룹 함수 ( GROUP BY + )

  - ROLL UP : 소그룹 간 소계를 계산 ( 계층구조 집계 출력)

  - CUBE : 결합 가능한 모든 값에 대해 다차원 집계

  - GROUPING SETS : 계층 구조 없이 지역에 대한 합계와 월별 합계 각 각 생성 (특정 항목에 대한 소계 계산)

 

ROW_NUMBER : 동일한 값이라도 고유한 순위부여

 

LAG : 파티션 별 윈도우에서 이전 몇번째 행의 값을가져옴

LEAD : 파티션 별 윈도우에서 이후 몇번째 행의 값을 가져옴

 

CUBE(X.key b , Y.col 1 )

   = GROUPING SETS ((X.key b, Y.col 1) , (X.key b) , (Y.col 1), () ) 과 동일한 결과

 

PK 제약 조건 생성

   CREATE TABLE 테이블 명 ~ ,

      1. CONSTRAINT 테이블명_PK PRIMARY KEY( 컬럼명) 

      2. ALTER TABLE 테이블명 , ADD CONSTRAINT 테이블명_PK PRIMARY KEY (컬럼명)

 

데이터 컬럼에 대한 정의 변경

   - ALTER TABLE 테이블명 ALTER COLUNM 컬럼명  데이터 유형 [NOT NULL]

   -COLUNM 동시 수정불가, 괄호 사용 X

 

 

트랜젝션 특성

  -  원자성 : 트랙젝션에 정의된 연산들은 모두 성공적으로 실행 되든지 아니면 전혀 실행되지 않은 상태로 남아 있어야 함.

  -  일관성 : 트랙젠션이 실행 되기 전 데이터베이스 내용이 잘못 되어 있지 않으면 트랙젝션이 실행된 이후에도 데이터베                       이스의 내용이 잘못이 있으면 안됨.

 

 - 고립성 : 트랙젝션이 실행되는 도중에 다른 트랙젝션의 영향을 받아 잘못된 결과를 만들어서는 안됨.

 - 지속성 : 트랙젝션이 성공적으로 수행되면 그 트랜젝션이 갱신한 데이터베이스의 내용은 영구적으로 저장

 

테이블의 불필요한 컬럼 삭제

  - ALTER TABLE 테이블명 DROP COLUNM 삭제할 컬럼명 

 

DELETE ACTION

  - CASCADE : MASTER와 삭제 시 CHILD 같이 삭제

  - RESTRICT : CHILD 테이블에 PK값이 없는 경우에만 MASTER 삭제 허용

 

INSERT ACTION 

  - AUTOMATIC : MASTER 테이블에 PK가 없는 경우 MASTER PK 생성 수 CHILD 입력

  - DEPENDENT : MATER 테이블에 PK가 존재 할때만 CHILD 입력 허용