SQLD 35회 기출 문제 풀이

SQLD 35회 기출 문제 풀이.

 

# 반정규화의 대상

• 디스크 I/O량이 많아 성능저하가 일어난 경우
• 경로가 너무 멀어 조인으로 성능저하가 일어난 경우
• 컬럼을 계산하여 읽을 때 성능저하가 일어난 경우

 

# 분산데이터베이스 설계 장단점

  - 장점

• 지역 자치성, 점증적 시스템 용량 확장
• 신뢰성과 가용성 / 효용성과 융통성
• 빠른 응답속도와 통신비용 절감
• 데이터의 가용성과 신뢰성 증가
• 시스템 규모의 적절한 조절
• 각 지역 사용자의 요구 수용 증대

 

  - 단점

• 소프트웨어 개발 비용의 증대
• 오류의 잠재성 증대
• 처리비용의 증대
• 설계,관리의 복잡성과 비용
• 불규칙한 응답 소고, 통제의 어려움
• 데이터 무결성에 대한 위협( = 분산데이터베이스는 데이터 무결성을 보장하지 못한다.)

 

# 스키마 구조

• 외부스키마 : 사용자 관점
• 개념스키마 : 통합 관점 (=모든 사용자 관점을 통합한 조직 전체 관점)
• 내부스키마 : 물리적 관점

 

# 식별자 관계와 비식별자 관계 구분이 어려움

 

# 인덱스

• 테이브 설계시 주로 B-Tree 인덱스로 되어 있다.
• 외래키가 설게되어 있는데 인덱스가 없는 상태면 입력/삭제/수정의 부하가 덜 생긴다.
• 테이블에 만들 수 있는 인덱스 수는 제한 x 그러나 너무 많이 만들면 성능 부하가 발생한다.
• 조회는 인덱스가 있는게 유리하다.

# JOIN

  - NATURAL JOIN : EMP.DEPTNO처럼 OWNER 명을 사용하면 에러발생
  - CROSS JOIN : 두 테이블 모든 데이터에 대해서 조인을 수행하는 방식

 

# 트랜잭션의 특징 (영.고.원.일)

• 원자성
• 일관성
• 고립성
• 영고석

 

# 특수문자 검색

SELECT * FROM TABLE WHERE NAME LIKE '%@_%' ESCAPE '@';

SELECT * FROM TABLE WHERE NAME LIKE '%[ _ ]%' ;

  = EXCAPE 기호의 뒤에 오는 기호를 검색해라~

 

WHERE NAME LIKE 'A%';

  = LIKE 뒤에 검색할 땐 대소문자 구문함!

 

# RANGE BETWEEN start_point AND end_point 

  : start point는 end_point와 같거나 작은 값이 들어간다.

   - Default값은 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW가 된다.

   - UNBOUNDED PRECEDING : start_point만 들어갈 수 있으며 파티션의 first row가 된다.

   - UNBOUNDED FOLLOWING : end_point만 들어갈 수 있으며 파티션의 last_row가 된다.

   - CURRENT ROW : start_point, end_point 둘 다 가능하다. 윈도우는 CURRENT ROW에서 start하거나 end한다.

 

# 인덱스 (INDEX)

• 인덱스는 검색 조건을 만족하는 데이터를 쉽게 찾을 수 있도록 돕는다.
• 인덱스는 테이블을 기반으로 선택적으로 생성할 수 있는 구조이다.
• 인덱스의 기본적인 목적은 검색 성능의 최적화이다.
• DML(INSERT,SELECT,UPDATE,DELETE)작업은 테이블과 인덱스를 함께 변경하므로 느려진다.
• 인덱스 데이터는 인덱스를 구성하는 칼럼의 값으로 정렬한다.
• B-TREE 인덱스는 EQUAL조건 이외에 BETWEEN, >과 같은 연산자로 검색하는 범위 검색도 사용가능

 

# 서브쿼리

• SELECT(스칼라 서브쿼리) : 한 행, 한 컬럼만을 반환하는 서브쿼리
• FROM(인라인*뷰) : 서브쿼리를 FROM절에 사용하면 동적으로 생성된 테이블인 것처럼 사용 가능                               ( 인라인뷰 : SQL문이 실행될 때만 임시로 생성되는 동적 뷰이기 때문에 데이터베이스에 해당 정보가 저장되지 않음.)
• WHERE/HAVING(서브쿼리) : 그룹함수와 함께 사용될 때 그룹핑된 결과에 대해 부가적 조건을 줌

 

# 단일행/다중행 서브쿼리

  1) 단일행 서브쿼리

• 서브쿼리의 실행 결과가 항상 1건 이하인 서브쿼리
• 비교연산자와 반드시 함께 사용
• 비교연산자 뒤에는 단일행이 와야하는데 뒤에 GROUP BY DEPT는 다중행 함수로 멀티행이 반환되어 에러발생

2) 다중행 서브쿼리

• 서브쿼리의 실행 결과가 여러 건인 서브쿼리
• 메인 쿼리의 조건 절에 여러 칼럼을 동시 비교 가능
• 서브쿼리와 메인쿼리의 칼럼 수와 칼럼 순서가 동일

 

# 뷰(VIEW)

• 독립성 : 테이블 구조가 변경되어도 뷰를 사용하는 응용프로그램은 변경하지 않음
• 편리성 : 복잡한 질의를 뷰로 생성하여 관련 질읠르 단순하게 만들 수 있음
• 보안성 : 뷰를 생성할 때 숨기고 싶은 칼럼을 빼고 생성하여 정보를 숨길 수 있음

 

# PL/SQL

• 변수와 상수 등을 사용하여 일반 SQL문장을 실행할때 WHERE절의 조건 등으로 대입 가능
• PROCEDURE, USER DEFINED FUCTION, TRIGGER 객체 PL/SQL로 작성 가능
• PROCEDURE 내부에 작성된 절차적 코든느 PL/SQL엔진이 처리하고 일반적인 SQL문장은 SQL실행기가 처리
• PL/SQL에서 예외처리는 필수X(BEGIN~END는 필수 / DECLARE,EXCEPTION은 선택)

 

# 무결성

  : 데이터 임의 갱신으로부터 보호하는 것

• 제약조건
• TRIGGER
• 어플리케이션

** LOCK/UNLOCK = 병행성 제어(동시성) 기법. 무결성과 관련X

 

# INSERT WHEN~ ELSE

INSERT FIRST

    WHEN C1>=2 THEN INTO T1

    WHEN C1>=3 THEN INTO T2

    ELSE INTO T3

SELECT * FROM TABLE;

 

  :  WHEN은 CASE와 마찬가지로 한 번 걸리면 그 밑에부터의 WHEN은 수행하지 않는다.

COL1에 1,2,3이렇게 있는데 COL1이 1일 때 T3에 저장되고 2일 때 T1에 저장되고 3일 때 T1에 저장된다.

즉, COL1은 2,0,1D이다.

 

# SELECT DISTINCT

SELECT COUNT(COL1), COUNT(COL2)

    FROM (

               SELECT DISTINCT COL1, COL2

               FORM TABLE

              );

 

  : SELECT DISTINCT COL1,COL2가 나오면 따로 생각할 것이 아니라 (COL1,COL2)가 겹치지않고 생기는 쌍을 다 세어줘야한다. 즉 여기서는 (1,A),(1,B)이렇게 2쌍이 SELECT 되는 것! 이렇게 나온 애들을 COL1, COL2 갯수를 각각 세어줘야하니까 2개 2개가 된다!

 

# 순위함수

• RANK() : 중복등수O 중복등수가 이후 순위에 영향O (1등,1등,3등,4등)
• DENSE_RANK() :중복등수O 중복등수가 이후 순위에 영향O (1등,1등,2등,3등)
• ROW_NUMBER() : 중복등수X (1등,2등,3등,4등)

 

# HASH JOIN

• 조인 컬럼의 인덱스가 없어도 사용 가능
• 해시 함수를 이용하여 조인을 수행하므로 =로 수행하는 조인이고 동등조건에서만 사용가능
• 해시함수가 적용될 때 동일한 값을 항상 같은 값으로 해싱됨이 보장
• HASH JOIN 작업을 수행하기 위해 해시테이블을 메모리에 생성해야한다.
• 메모리에 적재할 수 있는 영역의 크기보다 커지면 임시영역(디스크)에 해시 테이블을 저장
• HASH JOIN을 할 때 결과 행의 수가 적은 테이블을 선행 테이블로 사용하는 것이 좋다.
• 선행 테이블을 BUILD INPUT 이라 하며 후행 테이블을 PROVE INPUT

 

# COLUMN HEADER

  : 컬럼헤더는 대문자

 

# CONNECT BY PRIOR ~

이런 테이블이 있고 SQL문이 아래와 같이 제공되면 결과를 찾아보자

 

SELECT COUNT(*)

FROM SQLD_50

WHERE COL1 <> 4

START WITH COL1 = 1

CONNECT BY PRIOR COL1 = COL2 ;

 

이럴 때 먼저 START가 COL1=1인 애부터 시작이니까 (1,NULL)을 먼저 찾음. 그 CONNECT BY PRIOR COL1=COL2니까 (1,NULL) 의 1값을 COL2의 값으로 가지는 (3,1)(4,1)이 찾아진다. 이제 COL2의 값이 3이거나 4인 애를 찾으면 (7,3),(8,4) 이 두 가지고 COL2가 7이나 8인 행은 없으므로 여기서 멈추게 된다. 이중에 WHERE CIL1<>4라는 조건이 있으니 찾은 애중 (4,1)을 제외한 (1,NULL),(3,1),(7,3),(8,4) 이렇게 4개가 셀렉트되고 답은 4가 된다.

SELECT *

FROM SQLD_50

START WITH COL1 = 1

CONNECT BY PRIOR COL1 = COL2 ;

 

번외로 이러한 경우에 출력 순서는 어떤지 궁금해서 위와 같은 코드를 넣어보았다. 순서는 제일 먼저 (1,NULL) 그다음 서치된 (3,1),(4,1)이 나란히 나오는 것이 아니라 (3,1)을 출력하고 (1,3)의 COL2=3을 가진 (7,3)이 그 다음 출력된다. 그리고 나서 (4,1)을 출력하고 (1,4)의 COL2=4를 가진 (8,4)가 그 다음 출력된다.