SQLD 자격증 SQL 기본 및 활용 기출문제 / SQL시험 31회 시행

# SQLD 두 번째 : 자주 틀리는 기출문제 개념 정리

#2018년 12월 1일 시행 시험 오답 개념 정리 - SQL 기본 및 활용

1. SQL  실행 순서

parsing(구문분석) -> Execution(실행) -> Fetch(인출)

 

 

2. 그룹함수( Group Function)

2-1 ROLLUP(컬럼1, 컬럼2);

 

GROUP BY ROLLUP (부서별, 직업별) ;

 -> 그룹바이와 같이 사용하면 부서별 서브토탈(합계), 부서와 직업별 서브토탈(합계), 전체 합계가 모두 조회된다.

즉, ROLLUP으로 실행되는 칼럼 별로 Subtotal을 만들어준다.

 

컬럼의 순서가 바뀌면 결과 값도 바뀐다.

 

2-2 CUBE(컬럼1, 컬럼2);

 

 GROUP BY CUBE(부서별, 직업별) ;

-> 그룹바이와 같이 사용하면, CUBE 함수에 제시한 칼럼에 대해서 결합 가능한 모든 집계를 계산한다.

위와 같이 실행하면 부서별 합계, 직업별 합계, 부서별 직업별 합계, 전체 합계가 조회된다.

조합할 수 있는 경우의 수가 모두 조합된다.

 

2-3  GROUPING SET

 

GROUPING SETS (부서별, 직업별);

-> 부서별 합계와 직업별 합계가 개별적으로 조회된다.

즉, 서로 관계가 없이 결과값이 도출된다.

 

 

3. RANK Function in window function

RANK :  동일한 순위는 동일한 값 부여

DENSE_RANK : 동일한 순위는 하나의 건수로 계산

ROW_NUMBER : 동일한 순위에 대해서 고유의 순위를 부여한다.

 

사용: RANK() OVER (PARTITION BY 컬럼 ORDER BY 컬럼 DESC) AS RANK

값	RANK 함수	DENSE_RANK 함수	ROW_NUMBER
5000	1	1	1
3000	2	2	2
3000	2	2	3
2975	4	3	4
2850	5	4	5

 

 

4. NULL 관련 함수

4-1 NVL 함수

     - NULL이면 다른 값으로 바꾸는 함수이다.

     - NVL(TOTAL, 0)'은 TOTAL 칼럼이 NULL이면 0으로 바꾼다.

 

4-2 NVL2 함수

     - NVL 함수와 DECODE 함수를 하나로 만든 것이다.

     - NVL2(TOTAL, 1, 0) TOTAL칼럼이 NULL이 아니면 1을, NULL이면 0을 반환한다.

 

4-3 NULLIF 함수

     - 두 개의 값이 같으면 NULL을, 같지 않으면 첫 번째 값을 반환한다.

     - NULLIF(TOATAL1, TOTAL2) 은 두 값이 같으면 NULL을 같지 않으면 TOTAL1을 반환한다.

 

4-4 COALESCE 함수

     - NULL이 아닌 최초의 인자 값을 반환한다.

     - COALESCE(컬럼1, 컬럼2, 컬럼3, ......) 컬럼1이 NULL이 아니면 컬럼1의 값을 반환

        NULL 이면 그 뒤의 값의 NULL 여부를 판단하여 값을 반환한다.

 

5. 조인(JOIN)

5-1 EQUI 조인(등가 조인)

SELECT *

FROM 테이블1, 테이블2

WHERE 테이블1.ID = 테이블2.ID

 

해시 조인은 EQUI  조인만 사용 가능한 방법!

 

5-2 Non - EQUI(비등가) 조인

>, <, >=,<= 을 사용해서, 정확하게 일치하지 않는 것을 조인하는 것이다.

 

6. 인덱스 유일 스캔(index Unique SCAN)

인덱스의 키 값이 중복되지 않는 경우, 해당 인덱스를 사용할 때 발생된다.

= 을 사용해서 유일한 값을 찾는다.

 

7. 옵티마이저 조인(Optimizer Join)

7-1 Nested Loop 조인

하나의 테이블에서 데이터를 먼저 찾고 그다음 테이블을 조인하는 방식으로 실행된다.

먼저 조회되는 테이블을 외부 테이블이라 하고, 그다음 조회되는 테이블을 내부 테이블이라고 한다.

외부 테이블(선행 테이블)의 크기가 작은 것을 먼저 찾는 것이 중요하다.

그래야 데이터가 스캔되는 범위를 줄일 수 있기 때문이다.

네스티드 루프 조인은  RANDOM ACCESS가 발생한다. 이를 줄여야 성능이 향상된다.

 

7-2 Sort Merge 조인

두 개의 테이블을 SORT_AREA라는 메모리 공간에 모두 로딩(Loading)하고 정렬을 수행한다.

두 개의 테이블에 대해서 정렬이 완료되면 두 개의 테이블을 병합(Merge)한다.

Sort Merge 조인은 정렬(SORT)이 발생하기 때문에

데이터양이 많아지면 성능이 떨어지게 된다.

정렬 데이터양이 너무 많으면 정렬은 임시 영역에서 수행된다.

임시 영역은 디스크에 있기 때문에 성능이 급격히 떨어진다.

 

7-3 Hash 조인

해시 조인은 두 개의 테이블 중에서 작은 테이블을  HASH 메모리에 로딩하고

두 개의 테이블의 조인 키를 사용해서 해시 테이블을 생성한다.

해시 조인은 해시 함수를 사용해서 주소를 계산하고 해당 주소를 사용해서

테이블을 조인하기 때문에 CPU 연산을 많이 한다.

특히 해시 조인 시에는 선행 테이블이 충분한 메모리에 로딩되는 크기여야 한다.

 

8. 윈도우 함수에서 파티션별 가장 먼저 나온 값을 구하는 함수는?

FIRST_VAUE : 파티션에서 가장 처음에 나오는 값을 구한다.

                       MIN 함수를 사용해서 같은 결과를 구할 수 있다.

 

LAST_VALUE : 파티션에서 가장 나중에 나오는 값을 구한다.

                       MAX 함수를 사용해서 같은 결과를 구할 수 있다.

 

LAG 이전 행을 가지고 온다

 

LEAD 윈도우에서 특정 위치의 행을 가지고 온다. 기본값은 1이다.

 

 

9. 올림, 내림, 반올림

ABS 절대 값을 계산하는 함수

CEIL 올림

FLOOR 버림

ROUND 반올림

TRUNC 주어진 값보다 작거나 같은 최대 정수값을 구하는 함수

 

10. 테이블을 변경하는 명령어

테이블 속성의 구조를 변경하는 SQL문

SQL SEVER : ALTER TABEL, ALTER COLUMN

ORACLE : MODIFY

 

 

11. 인덱스

인덱스

-원하는 데이터를 빠르게 찾기 위해 탐색키를 갖는 테이블을 의미한다.

-인덱스는 원하는 데이터를 쉽게 찾을 수 있도록 돕는 책의 목차 기능과 유사한 개념이다.

 

12. 인덱스의 특징

-테이블 기반으로 선택적으로 생성할 수 있는 구조이다.

-테이블에 인덱스를 생성하지 않아도 되고 여러 개를 생성해도 된다.

-Insert, Update, Delete 등과 같은 DML 작업은 테이블과 인덱스를

  함께 변경해야 해서 오히려 느려질 수 있다.

-유일 인덱스 스캔은 기본키 또는 유니크 인덱스로 엑세스해야 한다.