SQLD 2024

SQLD

1과목
1. 데이터 베이스 스키마 구조 3단계
1) 외부 스키마 – 여러 사용자 관점으로 구성
2) 개념 스키마 – 모든 사용자 관점을 통합
3) 내부 스키마 – 물리적인 단계.

2. ERD 작성 순서
   1) 엔터티를 그린다.
   2) 엔터티를 적절하게 배치한다.
   3) 엔터티간 관계를 설정한다.
   4) 관계명을 기술한다.
   5) 관계의 참여도를 기술.
   6) 관계의 필수여부 기술.

3. 엔터티의 특징
   1) 반드시 해당 업무에서 필요하고 관리하고자 하는 업무여야한다.
   2) 유일한 식별자에 의해 식별이 가능해야한다.
   3) 영속적으로 존재하는 인스턴스의 집합 이여야 한다. (한개가 아닌 두개이상)
   4) 엔터티는 업무 프로세스에 의해 이용되어야 한다.
   5) 엔터티는 반드시 속성이 있어야한다.
   6) 엔터티는 반드시 다른 엔터티와 최소 한 개 이상의 관계가 있어야 한다.

4. 발생 시점에 따른 엔터티 분류

• 기본, 키 엔터티
• 중심 엔터티
• 행위 엔터티

5. 속성

• 인스턴스에서 관리하고자 하는 의미상 더 이상 분리 되지 않는 최소의 데이터 단위

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

• 한 개의 엔터티는 두 개 이상의 인스턴스 집합이어야 한다.
• 한 개의 엔터티는 두 개 이상의 속성을 갖는다.
• 한 개의 속성은 한 개의 속성값을 갖는다
• 한 개의 인스턴스에서 각각의 속성은 한 개의 속성값을 가져야한다.

7. 속성의 특징에 대한 분류

• 기본속성 : 업무분석을 통해 바로 정의한 속성
• 설계속성 : 원래 업무상 존재하지는 않지만 설계를 하면서 도출해 내는 속성
• 파생속성 : 다른 속성으로부터 계산이나 변형이 되어 생성되는 속성

8. 도메인 : 각 송성이 가질 수 있는 값의 범위. 데이터 타입의 크기와 제약사항을 지정한다.

9. 속성의 명칭 부여

• 해당 업무에 사용하는 이름을 부여
• 서술식 속성명 사용 X
• 약어 사용 자제
• 전체 데이터 모델에서 유일성을 확보하는게 좋다.

10. 관계의 표기법

• 관계명 : 관계의 이름
• 관계차수 : 1:1, 1:M, M:N
• 관계선택사양 : 필수관계, 선택관계

11. 주 식별자의 특징

• 유일성 : 주식별자에 의해 엔터티 내에 모든 인스턴스들을 유일하게 구분함
• 최소성 : 주식별자를 구성하는 속성의 수는 유일성을 만족하는 최소의 수가 되어야 함
• 불변성 : 식별자가 한번 특정 엔터티에 지정되면 그 식별자의 값은 변하지 않아야 함
• 존재성 : 주식별자가 지정되면 반드시 데이터 값이 존재해야 함(NULL 허용 X)

12. 식별자에 대한 설명

• 주식별자 : 대표성을 가지며, 엔터티 내의 여러 인스턴스 중 하나를 유일하게 구분할 수 있는 식별자.
• 보조식별자 : 엔터티 내의 여러 인스턴스 중 하나를 유일하게 구분할 수 있으나. 대표성을 가지지 못하는 식별자
• 본질식별자 : 엔터티 내의 집합을 명확하게 설명할 수 있는 업무적으로 의미가 부여된 식별자
• 외부식별자 : 다른 엔터티로부터 상속되어 정의된 식별자

13. 정규형

• 1정규형 : 모든 속성은 반드시 하나의 값을 가져야하낟
• 2정규형 : 엔터티의 일반 속성은 주식별자 전체에 종속이어야 한다.
• 3정규형 : 엔터티의 일반속성 간에는 서로 종속적이지 않다.

2과목

SQL 명령어 정리

1. 기본 명령어

1) COLESCE(...)

• 선택한 컬럼의 가장 처음 값(NULL을 제외한)을 하나의 컬럼으로 만듬

2) NVL(인수1, 인수2)

• 두 인수가 NULL 이라면 결과는 NULL
• 인수1이 NULL일 경우 인수 2를 반환, NULL이 아닐 경우 인수 1을 반환

3) NULIF(인수1, 인수2)

• 인수 1,2 가 같으면 NULL 반환, 같지 않으면 인수 1 반환

4) SIGN()

• 해당 숫자가 양수면 1, 음수면 –1 반환

5) ESCAPE

• ‘’ 혹은 ‘%’가 포함된 문자를 검색할 때 ESCAPE를 지정해 쿼리 작성. ‘#’로 지정해 주면 ‘#%’ 혹은 ‘#’ 문자 그대로 인식 될 수 있다.

6) SUBSTR(COL, 수1, 수2)

• 수1의 인덱스부터 수2의 인덱스까지 출력
• EX) SUBSTR(“HELLO WORLD”, 2, 5)
• ELLO 출력

7) REPLACE(COL, ‘ ’, ‘’)

• COL에 어떤 수나 빈칸을 대체해줌

8) CONCAT(인수1, 인수2)

• 인수1과 인수2를 연결해줌.

9) 숫자 관련 SQL
// 음수라면 절대값 취한 후 다시 음수를 붙임

9-1) ROUND(수1, 수2)

• 지정된 자리수까지 반올림 해줌

9-2) FLOOR(수1)

• 소수점 이하의 수를 버림

9-3) CEIL(수1)

• 소수점 이하의 수를 올림

9-4) TRUNC(수1)

• 지정된 자리수까지 버림, 자리값 명시 X시 기본값

9-5) ABS(수1)

• 절댓값을 반환.

2. 윈도우 함수

1) 순위 함수
① RANK

• 순위를 매기며 같은 순위가 존재하면 존재하는 만큼 다음 순위를 건너뛴다
• EX) 1,1,3,4....

② DENSE_RANK

• 순위를 매기면서 같은 순위가 존재하더라도 다음 순서를 건너뛰지 않고 이어서 매긴다.
• EX) 1,1,2,3,4...

③ ROW_NUMBER

• 같은 값에 순위를 매기더라도 다른 순위를 부여
• 1,2,3,4,5

2) 집계 함수
① SUM

• 합계

② MAX

• 최댓값

③ MIN

• 최솟값

④ AVG

• 평균값

⑤ COUNT

• 데이터의 개수
• COUNT 후 상수가 들어가도 모든 수 집계하란 뜻임

3) 행 순서 함수
① FIRST_VALUE (MY SQL 지원 X)

• 파티션별 가장 선두에 위치한 데이터를 구하는 함수

② LAST_VALUE (MY SQL 지원 X)

• 파티션별 가장 끝에 위치한 데이터를 구하는 함수

③ LAG

• 파티션 별로 특정 수 만큼 앞선 데이터를 구하는 함수
• EX) (LAG(Score, 3)) => 위에서부터 세 번째 값을 출력

④ LEAD

• 파티션 별로 특정 수 만큼 뒤에있는 데이터를 구하는 함수
• EX) (LEAD(Score, 3)) => 밑에서 세 번째 수를 출력

4) 비율 함수

① RATIO_TO_REPORT

• 파티션별 합계에서 차지하는 비율을 구하는 함수
• EX) 전체에서 비율

② PERCENT_RANK

• 파티션별 맨 위행을 0, 맨 아래행을 1로 두고 백분위 순서값을 구하는 함수.
• EX) 0.11111, 0.22222 ...

③ CUME_DIST

• 해당 파티션의 누적 백분율을 구하는 함수
• 결과값은 0보다 크고 1보다 작거나 같은 값을 가짐
• EX) COUNT / TOTAL_COUNT

④ NITLE

• 주어진 수만큼 행들을 N등분 한 후 현재 행에 해당하는 등급을 매기는 함수

3. TOP – N 쿼리

1) ROWNUM

• 슈도컬럼 / 가짜값을 생성해 번호를 매긴다

2) 윈도우 함수의 순위함수

• ROW_NUMBER
• RANK
• DANSE_RANK

4. 계층쿼리

1) LEVEL : 현재의 깊이 반환, 루트 노드는 1이된다.

2) SYS_CONNECT_BY_PATH : 루트노드 – 현재노드 까지의 경로 출력

3) START_WITH : 경로 시작되는 루트노드 생성

4) CONNECT BY : 루트로부터 자식노드 생성, 만족하는 데이터가 없을때까지 노드를 생성

5) PRIOR : 바로 앞에 있는 부모 노드 값 변환

6) CONNECT_BY_ROOT 컬럼 : 루트노드에 주어진 컬럼 값 변환

7) CONNECT_BY_ISLEAF : 가장 하위는 1 반환 나머지는 0 반환

5. 관리구문

1) DML (M I D U)
① INSERT

• 테이블에 데이터를 입력

② UPDATE

• 데이터 수정

③ DELETE

• 데이터 삭제

④ MERGE

• 변경작업을 한번에 할 수 있더록 해주는 명령어

2) TCL

① 트랜잭션의 특징
(1) 원자성 : 모두성공 Or 실패해야한다.
(2) 일관성 : 데이터에 일관성이 있어야한다.
(3) 고립성 : 하나의 트랜잭션은 고립되어 수행되야한다.
(4) 지속성 : 영구적으로 저장되야한다,

② COMMIT

• 변경된 내용을 확정, 반영하는 명령어
  ③ ROLLBACK
• 변경된 내용을 취소하는 명령어

④ SAVEPOINT

• 일부만 되돌릴 수 있게하는 기능.

3) DDL (데이터 정의어)
① CREATE

• 테이블 생성 명령어

• 규칙 :
  (1) 테이블명은 고유해야함.
  (2) 한테이블 내에서 컬럼명은 고유해야함.
  (3) 컬럼명 뒤에서 데이터 유형과 데이터 크기가 명시 되어야한다,
  (4) 컬럼에 대한 정의는 괄호안에 기술
  (5) 각 컬럼들은 ,로 구분된다
  (6) 테이블명, 컬럼명은 숫자로 시작 하지 못한다.
  (7) 마지막은 ;으로 끝난다.

• 제약조건
  (1) PRIMARY KEY

• ROW에 대한 고유성 보장

(2) UNIQUE KEY

• ROW에 대해 NULL 허용.

(3) FOREIGN KEY

• 참조키

(4) CHECK

• 컬럼에 저장될 수 있는 값의 범위 제한

(5) NOT NULL

• 해당 컬럼에 NULL 값 허용 안함.

(6) CASCADE

• 부모값 삭제시 자식 값 같이 삭제

(7) SET DEFAULT

• 부모값 삭제시 해당컬럼 DEFALT 처리

8) RESTRICT

• 자식 테이블에 해당 데이터가 PK로 존재하지 않는 경우에만 부모값 삭제 및 수정 가능

(9) NO ACTION

• 참조 무결성 제약이 걸려있는 경우 삭제 및 수정 불가

② ALTER

(1) ADD COLUMN

• 새로운 컬럼 추가

(2) DROP COLUMN

• 컬럼 삭제

(3) MODIFY COLUMN

• 컬럼 변경

(4) RENAME COLUMN

• 이름 변경

(5) ADD CONSTRAINT

• 제약조건 추가

③ DROP

• 테이블을 삭제할 때 쓰는 명령어
  -> 다른 테이블이 존재하면 CASCADE 옵션 없으면 삭제 X

④ RENAME TABLE

• 테이블명 변경

⑤ TRUNCATE TABLE

• 테이블 데이터 제거

4) DCL (User을 생성, 권한 부여)
4-1) USER 관련 명령어
① CREATE USER

• 사용자 생성

② ALTER USER

• 사용자 변경
  ③ DROP USER
• 사용자 삭제

4-2) 권한 관련 명령어

① GRANT ON 권한 TO 사용자명 (그온투)

• 권한 부여

② REVOKE 권한 FROM 사용자명

• 권한 회수

6. 연산자

• <> : 같지않다.
• 연산자 우선순위
  산술(*,/,+,-) – 연결(||) – 비교( =, <>, >, < , >=, <= ) – IN, LIKE, BETWEEN, IS NULL – NOT – ALL - OR

7. JOIN

7-1) CROSS JOIN : M * N 건의 데이터 조합이 발생한다/

7-2) LEFT OUTER JOIN : 조인 수행시 먼저 표기된 좌측 테이블에 해당하는 데이터를 먼저 읽은 후, 나중 표기된 우측 테이블에서 JOIN 대상 데이터를 읽어온다. 즉, TABLE A와 B가 있을 때 (TABLE A가 기준이 됨) A, B를 비교해서 B의 JOIN 칼럼에서 같은 값이 있을 때 그 데이터를 가져오고, B의 JOIN 칼럼에서 같은 값이 없는 경우에는 B 테이블에서 가져오는 칼럼들은 NULL 처리가 된다.

7-3) FULL OUTER JOIN : 조인 수행시 좌측, 우측테이블의 모든 데이터를 읽어 JOIN 하여 결과를 생성. 즉, TABLE A와 B가 있을 때 (TABLE A, B 모두 기준이됨 ), RIGHT OUTER JOIN과 LEFT OUTER JOIN의 결과를 합집합으로 처리한 결과와 동일.

8. 그룹 함수

① ROLLUP

• 소그룹 간의 소계 및 총계를 계산하는 함수
  EX) ROLLUP(A,B,C) : A,B,C로 그루핑 / A,B로 그루핑 / A로 그루핑 / 총합계

② CUBE

• 소그룹 간의 소계 및 총계를 다차원적으로 계산할 수 있는 함수 / 조합할 수 있는 모든 그룹에 대한 소계를 집계
  EX) CUBE(A,B,C) : A,B,C / A, B / B,C / A / B / C 로 그루핑

③ GROUPING SETS

• 특정 항목에 대한 소계를 계산 / 인자값으로 ROLLUP이나 CUBE 사용 가능

④ GROUPING

• ROLLUP, CUBE, GROUPING SETS와 같이 쓰임 ROW를 구분할 수 있게 해준다.

문제 풀이

1 과목

1. 데이터 모델링의 특징으로 적절하지 않은 것

• 시스템 구현만을 위해 진행하는 사전 단계의 작업으로서 ~ => 단지 시스템 구현만을 위해 수행하는 테스크가 아니다.

2. 데이터 모델링에 대한 설명으로 적절하지 않은 것

• 데이터 베이스를 구축하는 목적으로 데이터 모델링을 수행하여 업무에 대한 설명은 별도의 표기법을 이용한다 X => 일정한 표기법으로 표현

3. 데이터 모델링을 할 때 유의해야 할 상황으로 적절하지 않은 것.

• 사용자가 처리하는 프로세스나 장표 등에 따라 매핑이 될 수 있도록 프로그램과 테이블 간의 연계성을 높인다. X => 데이터 모델링을 할 때 유의사항은 중복성, 비유연성, 비일관성이 있다.

4. 데이터 독립성 구성요소에 대한 설명으로 가장 적잘하지 않은 것.

• View 단계는 여러 사용자 관점으로 구성하는 개념스키마 X => 외부 스키마 (여러 사용자 관점)

5. 엔터티에 이름을 부여하는 방법으로 적절하지 않은 것

• 가능하면 약어를 사용해 엔터티의 이름을 간결하고 명확하게 한다. X => 현업에서 사용하는 이름 사용 + 약어 사용 X

6. 데이터를 조회할 때 빠른 성능을 낼 수 있도록 하기 위해 원래 속성값을 계산하여 저장할 수 있도록 만든 속성? => 파생속성

7. 데이터 모델링의 관계에 대한 설명을 적절하지 않은 것은?

• 연관관계는 오퍼레이션, 의존관계는 소스코드 X => 연관은 소스코드 / 의존은 오퍼레이션

8. 1:1, 1:M과 같이 두 엔터티 간의 관계에서 참여자의 수를 나타내는 것은?

• 관계차수

9. 두 엔터티 사이에서 관계를 도출할 때 확인해야 할 사항

• 두 개의 엔터티 사이에 관심 있는 연관규칙이 존재하는가?
• 두 개의 엔터티 사이에 정보의 조합이 발생되는가?
• 업무 기술서, 장표에 관계연결에 대한 규칙이 서술되어 있는가?
• 업무 기술서, 장표에 관계연결을 가능하게 하는 동사가 있는가? => 여기서 명사로 잘못된 지문이 나올수도 있음.

10. SQL 연산 순서
    FROM – WHERE – GROUP BY – HAVING – SELECT – ORDERBY
    프 웨 그 헤 셀 오

11. 식별자로 적절하지 않은 것?

• 명칭, 내역과 같은 이름으로 기술되는 것들은 주식별자로 지정하기 어렵다.

12. 데이터 모델링에서 비식별자 관계로 연결하는 경우로 가장 적절하지 않은 것?

• SQL 문의 조인 관계를 최소화 하는 경우 비식별자 관계로 연결해야한다. => 식별자 관계로 연결해야한다.

13. 정규화를 수행하면 조회성능이 저하된다. => 보장 OR 향상 X

14. 정규화는 논리 데이터 모델의 일관성을 확보하고 중복을 제거하여 ... => 개념 데이터 라고 오답조심

15. 관계와 조인에 대한 설명

• 부모의 식별자를 자신의 식별자에 상속하면 식별 관계 / 부모의 식별자를 자신의 일반속성으로 상속하면 비식별 관계이다.

16. NULL에 대한 설명

• 공백문자 혹은 숫자 0을 의미

17. 순차적으로 수행되는 작업 A와 B가 반드시 모두 수행되거나 모두 수행되지 않았다고 할 때, 이에 대한 설명으로 가장 적절한 것?

• A와 B를 수행한 후 각각 커밋을 해줘야한다,

18. NULL에 대한 설명

• NULL 값에 어떤 숫자를 더해도 결과는 항상 NULL 이다

2과목

1. WHERE 절에는 집계함수를 쓸 수 없다!

2. SUM 함수는 NULL 제외 후 계산한다.

3. NULL이 아닌 경우를 찾아내는 문장으로 가장 적절한 것은?

• IS NOT NULL 문장 쓰기

4. LTRIM은 첫 번째 인자값인 문자열의 첫 왼쪽문자부터 확인해서 두 번째 인자값인 지정문자가 나타나면 해당 문자를 제거한다. 다른 문자 사이 또는 오른쪽에 있는 지정문자는 제거되지 않는다.
   EX) LTRIM('xxYYZZxYZxx', 'x') : 'YYZZxYZxx’

5. 1/24/60 = 1분을 의미한다.

6. 내림차순 DESC : 큰 값이 위에 / 오름차순 ASC : 작은값이 맨 위에

7. 5개의 테이블로부터 필요한 칼럼을 조회하려 할 때, 최소 몇 개의 JOIN조건이 필요한가?

• M-1개 해서 총 4개가 필요하다.

8. 순수 관계 연산자 셀프조디

• SELECT 시그마 / PROJECT 파이 / JOIN 나비 / DIVIDE 나누기

9. 엔터티를 JOIN시 적절한 JOIN 조건이 없으면 카티시안 곱이 발생한다.

10. UNION 연산자를 사용한 SQL은 각각의 집합에 ORDER BY절을 사용 할 수 없다.

11. 오라클의 계층형 질의문에서 PRIOR 키워드는 CONNECT BY 절에만 사용할 수 있다. X => SELECT, WHERE 절에서도 사용 할 수 있다.

12. 서브쿼리에 대한 설명으로 가장 적절한 것?

• 다중 행 서브쿼리 비교 연산자는 단일 행 서브쿼리의 비교 연산자로도 사용할 수 있다.

13. 뷰는 복잡한 SQL 문장을 단순화 하는 장점이 있는 반면, 테이블 구조가 변경되면 응용프로그램을 변경 해줘야한다 X => 응용프로그램 변경은 안한다.

14. 외래키 값은 NULL을 가질 수 있다 => 가질 수 없다 오답으로 나옴

15. 관계형 데이터베이스에서 자식 테이블의 FK 데이터 생성시 부모 테이블에 PK가 없는 경우, 자식테이블 데이터 입력을 허용하지 않는 참조동작은? => DEPENDENT