[SQL Developer] 제31회 SQL 개발자 자격검정시험 후기 및 출제 문제 정리 (2)

[SQL Developer] 제31회 SQLD 시험 후기

 - 일시: 2018-12-01 (토요일) 13:00 ~ 14:30

 - 장소: 건국대학교 공학관

 > 이전 글 (1/2) [SQL Developer] 제31회 SQL 개발자 자격검정시험 후기 및 출제 문제 정리 (1)

 - 추가

 - 데이터베이스(DB) 전문가 포럼에 많은 분들께서 정리한 내용

31회차 1과목.txt

31회차 2과목.txt

31회차 단답 8문제.txt

문제 그대로를 복구하기에는 한계가 있어 기출 문제의 개념사항만을 작성했습니다.

빨간 글씨의 개념을 이해해야 풀 수 있었습니다.

많은 고민을 줬던 문제

SELECT

    '''A'''''

FROM dual; 의 결과값은?

과목2 SQL 기본 및 활용

제 1장 SQL 기본

제1절 관계형 데이터베이스 개요

제2절 DDL

1. 데이터 유형

1) CHAR 비교

- CHAR에서는 문자열을 비교할 때 공백(BLANK)을 채워서 비교하는 방법을 사용

- 공백 채우기 비교에서는 우선 짧은 쪽의 공백을 추가하여 2개의 데이터가 같은 길이가 되도록 한다.

- 앞에서부터 한 문자씩 비교, 그렇기 때문에 끝의 공백만 다른 문자열은 같다고 판단

2) VARCHAR 비교

- 맨 처음부터 한 문자씩 비교, 공백도 하나의 문자로 취급하므로 끝의 공백이 다르면 다른 문자로 판단

2. CREATE TABLE

1) CTAS(Create Table ~ As Select ~)

- 기존 테이블의 조건 중에 NOT NULL만 새로운 복제 테이블에 적용

- 기본키, 고유키, 외래키, Check등의 다른 제약 조건은 없어짐

- 제약 조건 추가 시 ALTER TABLE 기능 사용

3.ALTER TABLE

1) 컬럼 변경 시 몇 가지 고려사항

① 해당 컬럼의 크기를 늘릴 수는 있지만 줄이지 못함 (기존 데이터의 훼손)

② 해당 컬럼이 NULL 값만 가지고 있거나 테이블에 아무 행도 없으면 컬럼의 폭을 줄일 수 있다.

③ 해당 컬럼이 NULL 갑만을 가지고 있으면 데이터 유형을 변경할 수 있다.

④ 해당 컬럼의 Default 값을 바꾸면 변경 작업 이후 발생하는 행 삽입에만 영향을 미치게 된다.

⑤ 해당 컬럼의 NULL 값이 없을 경우에만 NOT NULL 제약조건을 추가할 수 있다.

제3절 DML

1. SELECT

SELECT [ALL/DISTINCT] 컬럼명 ...

FROM 해당 컬럼들이 있는 테이블명;

1) DISTINCT 옵션

제4절 TCL

1. 트랜잭션의 내용

1) 원자성: 트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되던지 아니면 전혀 실행되지않은 상태로 남아 있어야 한다.(All or Nothing)

2) 일관성: 트랜잭션이 실행되기 전의 데이터베이스 내용이 잘못되어 있지 않다면 트랜잭션이 실행된 이후에도 데이터베이스의 내용에 잘못이 있으면 안된다.

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

4) 지속성: 트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 데이터베이스의 내용은 영구적으로 저장된다.

제5절 WHERE 절

1. 비교연산자

1) 문자 유형 비교

① CHAR 유형타입

- 길이가 서로 다른 CHAR형 타입이면 작은쪽에 SPACE를 추가하여 길이를 같게 한 후에 비교

- 서로 다른 문자가 나올 때까지 비교

- 달라진 첫 번째 문자의 값에 따라 크기를 결정

- BLANK의 수만 다르다면 서로 같은 값으로 결정

② VARCHAR 

- 서로 다른 문자가 나올 때까지 비교

- 길이가 다르다면 짧은 것이 끝날 때까지만 비교 후에 길이가 긴 것이 크다고 판단

- 길이가 같고 다른 것이 없다면 같다고 판단

- VARCHAR는 NOT NULL까지 길이를 말한다.

③ 상수값

- 상수 쪽을 변수 타입과 동일하게 바꾸고 비교

- 변수 쪽이 CHAR 유형 타입이면 위의 CHAR 유형 타입의 경우를 적용

- 변수 쪽이 VACHAR 유형 타입이면 위의 VARCHAR 유형 타입의 경우를 적용

2) NULL

- NULL 값의 비교는 비교 연산자인 =, >, >=, <, <= 를 통해서 비교할 수 없다.

- 만일 비교 연산을 하게 되면 결과는 거짓(FALSE)을 리턴

- 수치 연산자(+, -, *, /)등을 통해서 NULL 값과 연산을 할 때 NULL 값을 리턴

- NULL 값의 비교 연산은 IS NULL, IS NOT NULL이라는 정해진 문구를 사용

2. ROWNUM

 - Oracle의 ROWNM은 컬럼과 비슷한 성경의 Pseudo Column으로 SQL 처리 결과 집합의 각 행에 대해 임시로 부여되는 일련번호

 - 테이블이나 집합에서 원하는 만큼의 행만 가져오고 싶을 때 WHERE 절에서 행의 개수를 제한하는 목적으로 사용

제6절 함수

1. 문자형 함수

문자형 함수	함수 설명
LOWER(문자열)	문자열의 알파벳 문자를 소문자로 바꾸어 준다.
UPPER(문자열)	문자열의 알파벳 문자를 대문자로 바꾸어 준다.
ASCII(문자)	문자나 숫자를 ASCII 코드 번호로 바꾸어 준다.
CHR/CHAR(ASCII번호)	ASCII 코드 번호를 문자나 숫자로 바꾸어 준다.
CONCAT(문자열1, 문자열2)	Oracle, MySQL에서 유효한 함수이며 문자열 1과 문자열 2를 연결한다. 합성연산자 '||'(Oracle)나 '+'(SQL Server)와 동일
SUBSTR/SUBSTRING (문자열, m[, n])	문자열 중 m위치에서 n개의 문자 길이에 해당하는 문자를 돌려준다. n이 생략되면 마지막 문자까지
LENGTH/LEN(문자열)	문자열의 개수를 숫자값으로 돌려준다.
LTRIM (문자열 [, 지정문자])	문자열의 첫 문자부터 확인해서 지정 문자가 나타나면 해당 문자를 제거한다 (지정 문자가 생략되면 공백 값이 디폴트) SQL Server 에서는 LTRIM 함수에 지정문자를 사용할 수 없다 즉, 공백만 제거할수 있다.
RTRIM (문자열 [, 지정문자])	문자열의 마지막 문자부터 확인해서 지정 문자가 나타나는 동안 해당 문자를 제거한다. (지정 문자가 생략되면 공백 값이 디폴트) SQL Server에서는 LTRIM 함수에 지정문자를 사용할 수 없다. 즉, 공백만 제거할 수 있다.
TRIM ([leading | trailing | both]  지정문자 FROM 문자열)	문자열에서 머리말, 꼬리말, 또는 양쪽에 있는 지정 문자를 제거한다. (leading | trailing | both가 생략되면 both가 디폴트) SQL Server에서는 TRIM 함수에 지정문자를 사용할 수 없다. 즉, 공백만 제거할 수 있다.

[제 31 회 SQLD 시험 문제 유형]

SELECT (   ) FROM dual;

결과 값 

"자격"

SUBSTR("SQLD자격증명", 5, 2)  숫자만 바꾸어서 객관식 문제

2. NULL관련 함수

1) NULL 포함 연산의 결과

연산	연산의 결과
NULL + 2	NULL
NULL - 2	NULL
NULL * 2	NULL
NULL / 2	NULL

2) 단일행 NULL 관련 함수의 종류

일반형 함수	함수 설명
NVL(표현식1, 표현식2) / ISNULL(표현식1, 표현식2)	표현식 1의 결과값이 NULL이면 표현식 2의 값을 출력한다. 단, 표현식1과 표현식2 의 결과 데이터 타입이 같아야 한다. NULL 관련 가장 많이 사용되는 함수이므로 상당히 중요하다.
NULLIF(표현식1, 표현식 2)	표현식1이 표현식 2와 같으연 NULL을 같지 않으면 표현식1을 리턴한다.
COALESCE(표현식1, 표현식 2 ...)	임의의 개수 표현식에서 NULL 이 아닌 최초의 표현식을 나타낸다. 모든 표현식이 NULL이라면 NULL을 리턴한다.

[제 31 회 SQLD 시험 문제 유형]

NVL2(expr, expr1, expr2) 함수 알아야 맞출 수 있는 문제

 - expr의 값이 NULL이 아닐 경우에는 expr1의 값을 반환, NULL일경우 expr2의 값을 반환

 - if (expr == null ) {

return expr1;

   } else {

return expr2;

   } 

제7절 Group By, Having 절

1. 집계함수

집계 함수	사용 목적
COUNT(*)	NULL값을 포함한 행의 수를 출력
COUNT(표현식)	표현식의 값이 NULL값인 것을 제외한 행의 수를 출력
SUM([DlSTINCT I ALL] 표현식)	표현식의 NULL 값을 제외한 합계를 출력
AVG([DlSTINCT I ALL] 표현식)	표현식의 NULL 값을 제외한 평균을 출력
MAX([DlSTINCT I ALL]표현식)	표현식의 최대값을 출력 (문자, 날짜 데이터 타입도 사용가능)
MIN([DlSTINCT I ALL]표현식)	표현식의 최소값을 출력 (문자, 날짜 데이터 타입도 사용가능)
STDDEV([DlSTINCT I ALL] 표현식)	표현식의 표준 편차를 출력
V ARIAN([DlSTINCT I ALL] 표현식)	표현식의 분산을 출력
기타 통계 함수	벤더별로 다양한 통계식을 제공

2. Group by 절과 HAVING 절의 특성

- Group by 절을 통해 소그룹별 기준을 정한 후, SELECT 절에 집계 함수를 사용

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

- GROUP BY 절에서는 SELECT 절과는 달리 ALIAS 명을 사용할 수 없다.

- 집계 함수는 WHERE 절에는 올 수 없다.

 (집계 함수를 사용할 수 있는 GROUP BY 절보다 WHERE 절이 먼저 수행된다.)

- WHERE 절은 전체 데이터를 GROUP으로 나누기 전에 행들을 미리 제거시킨다.

- HAVING 절은 GROUP BY 절의 기준 항목이나 소그룹의 집계 함수를 이용한 조건을 표시할 수 있다.

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

 만족하는 내용만 출력

- HAVING 절은 일반적으로 GROUP BY 절 뒤에 위치

제8절 Order By 절

1. ORDER BY 정렬 방식

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

- 숫자형 데이터 타입은 오름차순으로 정렬했을 경우에 가장 작은 값부터 출력

- 날짜형 데이터 타입은 오름차순으로 정렬했을 경우 날짜 값이 가장 빠른 값이 먼저 출력

- Oracle에서는 NULL값을 가장 큰 값으로 간주하여 오름차순으로 정렬했을 경우에는 가장 마지막에, 내림차순으로 정렬했을 경우에는 가장 먼저 위치한다.

- 반면, SQL Server에서는 NULL 값을 가장 작은 값으로 간주하여 오름차순으로 정렬했을 경우에는 가장먼저, 내림차순으로 정렬했을 경우에는 가장 마지막에 위치한다.

[제 31 회 SQLD 시험 문제 유형]

문자형, 숫자형, 날짜형 컬럼을 갖고 있는 테이블이 주어지고 결과 값을 출력

쿼리문에 ORDER BY 문자형 DESC, 숫자형 ASC, 날짜형 DESC 이었을 때의 결과를 선택하는 문제

2. ORDER BY  {column} DESC nulls last

- 처음보는 ORDER BY 옵션

- 반환되는 결과 값 중 NULL 값은 나중에 출력하게끔 하는 옵션

[제 31 회 SQLD 시험 문제 유형]

SELECT C1, C2 FROM t1 ORDER BY 1 DESC (     ); 

괄호 안에 들어갈 쿼리를 작성하시오.

결과 값

c1	c2
1
2	2
2	1
2
3	3
3	2
3

제9절 조인(Join)

1. EQUI JOIN

EQUI JOIN 형태	ANSI/ISO SQL
SELECT 테이블1.칼럼명, 테이블2.칼럼명 FROM 테이블1, 테이블2 WHERE 테이블1.컬럼명 1 = 테이블2.칼렴명2;	SELECT 테이블1.컬럼명, 테이블2.컬럼명 FROM 테이블1 INNER JOIN 테이블2 ON 테이블1.컬럼명1 = 테이블2.컬럼명2;

[제 31 회 SQLD 시험 문제 유형]

해당 쿼리를 ANSI/ISO 조인 쿼리로 바꾸시오

제 2장 SQL 활용

제1절 표준 조인

1. 일반 집합 연산자

연산	기능	내용
UNION	UNION	합집합 (중복 O)
	UNION ALL	합집합 (중복 X)
INTERSECTION	INTERSECT	교집합
DIFFERENCE	EXCEPT(Oracle MINUS)	차집합
PRODUCT	CROSS JOIN	곱집합

2. 순수 관계 연산자

연산	기능	내용
SELECT	WHERE	조건절 기능
PROJECT	SELECT	컬럼 선택 기능
(NATURAL) JOIN	JOIN(다양)	NATURAL JOIN, INNER JOIN OUTER JOIN,  USING 조건절, ON 조건절
DIVIDE	X

2. ANSI/ISO SQL에서 표시하는 FROM 절의 JOIN 형태

1) INNER JOIN

- 내부 JOIN

- 동일한 값이 있는 행만 반환

- USING 조건절, ON 조건절을 필수적으로 사용

- JOIN에 사용된 같은 이름의 컬럼이라도 별개의 컬럼으로 표시

2) NATURAL JOIN

- 두 테이블 간의 동일한 이름을 갖는 모든 컬럼들에 대해 EQUI(=) JOIN을 수행

- USING, ON의 조건을 정의할 수 없음

- SELECT에 컬럼 순서를 지정하지 않으면 NATURAL JOIN 기준이 되는 컬럼들이 다른 컬럼보다 먼저 출력

- JOIN에 사용된 같은 이름의 컬럼을 하나로 처리

3) USING 조건절

4) ON 조건절

5) CROSS JOIN (PRODUCT)

6) OUTER JOIN

① LEFT OUTER JOIN

- 좌측 테이블에 해당하는 데이터를 먼저 읽은 후, 나중에 표기된 우측 테이블에서 JOIN 대상 데이터를 읽어온다.

- TABLE A와 B가 있을때 A와 B를 비교해서 B의 JOIN 컬럼에서 같은 값이 있을 때 그 해당 데이터를 가져오고,
 B의 JOIN 컬럼에서 같은 값이 없는 경우에는 B 테이블에서 가져오는 컬럼들은 NULL 값으로 채운다.

② RIGHT OUTER JOIN

- 조인 수행 시 LEFT JOIN과 반대로 우측 테이블이 기준이 되어 결과를 생성

- 즉, TABLE A와 B가 있을 때(TABLE 'B'가 기준)

- A와 B를 비교해서 A의 JOIN 컬럼에서 같은 값이 있을 때 그 해당 데이터를 가져오고, A의 JOIN 컬럼에서 같은 값이 없는 경우에는 A 테이블에서 가져오는 컬럼들은 NULL 값으로 채운다.

- 그리고 OUTER 키워드를 생략해서 RIGHT JOIN으로 사용할 수 있다.

③ FULL OUTER JOIN

- RIGHT OUTER JOIN과 LEFT OUTER JOIN결과를 합집합으로 처리한 결과와 동일

- UNION 기능과 같고 UNION ALL과는 다르다.

- FULL JOIN으로 OUTER 키워드를 생략이 가능

[제 31 회 SQLD 시험 문제 유형]

하나의 두 개의 테이블이 주어지고

RIGHT JOIN, LEFT JOIN 을 수행했을 경우

COUNT(*) 값이 몇개나 나오는 지 문제

제2절 집합 연산자

1. 집합 연산자의 종류

집합 연산자	연산자의 의미
UNION	여러 개의 SQL문의 결과에 대한 합집합으로 결과에서 모든 중복 된 행은 하나의 행으로 만든다.
UNION ALL	여러 개의 SQL문의 결과에 대한 합집합으로, 중복된 행도 그대로 결과로 표시 즉, 단순히 결과만 합쳐놓은 것이다.  일반적으로 여러 질의 결과가 상호 배타적인(Exclusive)일 때 많이 사용한다. 개별 SQL문의 결과가 서로 중복되지 않는 경우, UNION과 결과가 동일  (결과의 정렬 순서에는 차이가 있을 수 있음)
INTERSECT	여러 개의 SQL문의 결과에 대한 교집합. 중복된 행은 하나의 행으로 만듦
EXCEPT	앞의 SQL문의 결과에서 뒤의 SQL문의 결과에 대한 차집합 중복된 행은 하나의 행으로 만듦 (일부 데이터베이스는 MINUS)

제3절 계층형질의와 셀프 조인

1. 계층형 질의 ** (제 31회 SQLD 3문제 정도 나옴)

1) Oracle 계층형 질의

SELECT ...

FROM 테이블

WHERE condition AND condition ...

START WITH condition

CONNECT BY [NOCYCLE] condition AND condition ...

[ORDER SIBLINGS BY column, column, ...]

키워드	내용
START WITH절	계층 구조 전개의 시작 위치를 지정하는 구문. 즉, 루트 데이터를 지정
CONNECT BY절	전개될 자식 데이터를 지정하는 구문. 자식 데이터는 CONNECT BY절에 주어진 조건을 만족해야 함(조인)
PRIOR	CONNECT BY 절에 사용되며, 현재 읽은 컬럼을 지정. PRIOR 자식 = 부모 형태 사용 시 계층구조에서 부모 데이터에서 자식 데이터(부모 → 자식) 방향으로 전개 PRIOR 부모 = 자식 형태 사용 시 반대로 자식 데이터에서 부모 데이터 (자식 → 부모) 방향으로 전개
NOCYCLE	데이터를 전개하면서 이미 나타났던 동일한 데이터가 전개 중에 다시 나타난다면 이것을 가리켜 사이클(Cylcle)이 형성되었다라고 말한다. 사이클이 발생한 데이터는 런타임 오류가 발생한다. 그렇지만 NOCYCLE를 추가하면 사이클이 발생한 이후의 데이터는 전개하지 않는다.
ORDER SIBLINGS BY	형제 노드(동일 LEVEL) 사이에서 정렬을 수행
WHERE	모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출

제4절 서브쿼리

1. 서브쿼리 종류

1) 동작하는 방식에 따른 서브쿼리 분류

서브쿼리 종류	설명
Un-Correlested(비연관) 서브쿼리	서브쿼리가 메인쿼리 컬럼을 가지고 있지 않는 형태의 서브쿼리 메인쿼리에 값(서브쿼리가 실행된 결과)을 제공하기 위한 목적으로 주로 사용
Correlated(연관) 서브쿼리	서브쿼리가 메인쿼리 컬럼을 가지고 있는 형태의 서브쿼리  일반적으로 메인쿼리가 먼저 수행되어 읽혀진 데이터를 서브쿼리에서 조건이 맞는지  확인하고자 할때 주로 사용

2) 반환되는 데이터의 형태에 따른 서브쿼리 분류

서브쿼리 종류	설명
Single Row 서브쿼리 (단일 행 서브쿼리)	서브쿼리의 실행 결과가 항상 1건 이하인 서브쿼리를 의미한다. 단일 행 서브쿼리는 단일 행 비교 연산자와 함께 사용 단일 행 비교 연산자에는 =, <, <=, >, >=, <>이 있다.
Multi Row 서브쿼리 (다중 행 서브쿼리)	서브쿼리의 실행 결과가 여러 건인 서브쿼리를 의미 다중 행 서브쿼리는 다중 행 비교 연산자와 함께 사용 다중 행 비교 연산자에는 IN, ALL, ANY, SOME, EXISTS가 있다.
Multi Column 서브쿼리 (다중 컬럼 서브쿼리)	서브쿼리의 실행 결과로 여러 컬럼을 반환 메인쿼리의 조건절에 여러 컬럼을 동시에 비교 서브쿼리와 메인쿼리에서 비교하고자 하는 컬럼 개수와 컬럼의 위치가 동일해야 한다.

[제 31 회 SQLD 시험 문제 유형]

Subquery의 특징으로 부적절한 것

보기) 

 IN은 단일 행 서브쿼리이다 (X) 

제5절 그룹 함수 *** (문제가 많이 나옴)

1. 집계함수

1) Aggregate function

- Group Aggregate Function이라고 부르며 Group Function의 한 부분으로 분류할 수 있다.

- Count, Sum, Avg, Max, Min외 각종 집계 함수

2) Group Function *** (문제가 많이 나옴)

- Rollup: Group by의 확장된 형태로 사용하기가 쉬우며 병렬로 수행이 가능하기 때문에 매우 효과적일 뿐 아니라 시간 및 지역처럼 계층적 분류를 포함하고 있는 데이터의 집계에 적합하도록 되어 있다.

- Cube: 결합 가능한 모든 값에 대하여 다차원적인 집계를 생성하게 되므로 Rollup에 비해 다양한 데이터를 얻는 장점이 있는 반면에, 시스템에 부하를 많이 주는 단점이 있다.

- Grouping Sets: 원하는 부분의 소계만 손쉽게 추출할 수 있는 장점이 있다.

[제 31 회 SQLD 시험 문제 유형]

1. ROLLUP, CUBE 비교 문제

2. CUBE함수 사용의 결과 값을 보기로 주고 어떤 그룹 함수를 사용했는지

C1, C2 각각 소계가 존재

C1, C2 값O Null

C1, C2 Null 값O

C1, C2 전체 집계 O

3) Window Function

- 행과 행간의 관계를 쉽게 정의하기 위해 만든 함수

- 분석 함수(Analytic Function), 순위 함수(Rank Function)

① 그룹 내 순위(RANK) 관련 함수: RANK, DENSE_RANK, ROW_NUMBER함수

② 그룹 내 집계(AGGREGATE) 관련 함수: SUM, MAX, MIN, AVG, COUNT 함수

③ 그룹 내 행 순서 관련 함수: FIRST_VALUE, LAST_VALUE, LAG, LEAD 함수

④ 그룹 내 비율 관련 함수: CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT 함수

⑤ 선형 분석을 포함한 통계 분석 관련 함수

제6절 윈도우 함수

SELECT WINDOW_FUNCTION (ARGUMENTS) OVER ( [PARTITION BY 컬럼] [ORDER BY 절] [WINDOWING 절] )

FROM  테이블 명;

 - Partition By 절

  : 전체 집합을 기준에 의해 소그룹으로 나눌 수 있다.

 - WINDOWING 절

  : WINDOWING 절은 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다.

   ROWS는 물리적인 결과 행의 수를, RANGE는 논리적인 값에 의한 범위를 나타내는데, 둘 중의 하나를 선택해서 사용

BETWEEN 사용 타입

ROWS | RANGE BETWEEN

 UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING / FOLLOWING

AND

 UNBOUNDED FOLLOWING | CURRENT ROW | VALUE_EXPR PRECEDING / FOLLOWING

BETWEEN 미사용 타입

ROWS | RANGE

 UNBOUNCED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING

1. 그룹 내 순위 함수

1) RANK

- RANK 함수는 ORDER BY를 포함한 QUERY문에서 특정 항목(컬럼)에 대한 순위를 구하는 함수

- 이때 특정 범위(PARTITION) 내에서 순위를 구할 수도 있고 전체 데이터에 대한 순위를 구할 수도 있다.

- 또한 동일한 값에 대해서는 동일한 순위를 부여하게 된다.

2) DENSE_RANK

- 동일한 순위를 하나의 건수로 취급

3) ROW_NUMBER 함수

- RANK, DENSE_RANK 함수가 동일한 값에 대해서는 동일한 순위를 부여

- 동일한 값이라도 고유한 순위를 부여

2. 그룹 내 행 순서 함수

1) FIRST_VALUE

- 파티션별 윈도우에서 가장 먼저 나온 값을 구한다.

- MIN 함수 활용하여 같은 결과를 얻을 수도 있다.

- RANGE UNBOUNDED PRECEDING: 현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위를 지정

SELECT DEPTNO, ENAME, SAL,

FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO 

ORDER BY SAL DESC 

ROWS UNBOUNDED PRECEDING) AS DEPT_RICH

FROM EMP;

2) LAST_VALUE

- 파티션별 윈도우에서 가장 나중에 나온 값을 구한다.

- MAX 함수를 활용하여 같은 결과를 얻을 수도 있다.

- ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING: 현재 행을 포함하여 파티션 내의 마지막 행까지의 범위를 지정

SELECT DEPTNO, ENAME, SAL,

LAST_VALUE(ENAME) OVER(PARTITION BY DEPTNO 

ORDER BY SAL DESC 

ROWS BETWEEN CURRENT ROW 

AND UNBOUNDED FOLLOWING) AS DEPT_POOR

FROM EMP;

3) LAG 함수

- 파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있다.

SELECT ENAME, HIREDATE, SAL,

LAG(SAL) OVER(ORDER BY HIREDATE) AS PREV_SAL

FROM  EMP

WHERE JOB = 'SALESMAN';

- LAG 함수는 3개의 Arguments까지 사용할 수 있다.

- 두 번째 인자는 몇 번째 앞의 행을 가져올지 결정 (default, 1)

- 세 번째 인자는 첫 번째 행의 경우 가져올 데이터가 없어 NULL 값이 들어올 경우 다른 값으로 바꾼다.

SELECT ENAME, HIREDATE, SAL,

LAG(SAL, 2, 0) OVER(ORDER BY HIREDATE) AS PREV_SAL

FROM EMP

WHERE JOB = 'SALESMAN';

4) LEAD 함수

- 파티션별 윈도우에서 이후 몇 번째 행의 값을 가져올 수 있다.

- LEAD 함수는 3개의 Arguments 까지 사용할 수 있다.

- 두 번째 인자는 몇 번째 후의 행을 가져올 지 결정 (Default, 1)

- 세 번째 인자는 파티션의 마지막 행의 경우 가져올 데이터가 없어 NULL 값이 들어오는 경우 다른 값으로 바꾸어줌

SELECT ENAME, HIREDATE,

LEAD(HIREDATE, 1) OVER(ORDER BY HIREDATE) AS 'NEXTHIRED'

FROM EMP;

[제 31 회 SQLD 시험 문제 유형]

1. 

ROWS UNBOUNDED PRECEDING

RANGE UNBOUNDED PRECEDING

ROWS BETWEEN UNBOUNDED PRECEDING AND PRECEDING

RANGE BETWEEN UNBOUNDED PRECEDING AND PRECEDING

차이 구분 문제

2. 

쿼리 결과값 보기가 주어진 뒤에 LAG 함수를 맞추는 문제

제7절 DCL

제8절 절차형 SQL

1. PL(Procedural Language)/SQL

[제 31 회 SQLD 시험 문제 유형]

  [PL/SQL]

    <LABEL 1>

    DECLARE 

        v_lv = 1

     ....

          < LABEL 2 >

               DECLARE

                   v_lv = 2 

               DBMS_OUTPUT .... L1.v_lv ... L2.v_lv.... v_lv

결과값: 1, 2, 2

-> 레이블 지정 시, 다른 레이블의 변수 값을 출력하기 위해서는 레이블을 붙여줘야 함 그게 아니면 내부에 선언된 값이 출력

제 3장 SQL 최적화 기본 원리

제1절 옵티마이저와 실행계획

1. 규칙기반 옵티마이저

2. 비용기반 옵티마이저

제2절 인덱스 기본

1. 인덱스 종류

(B-트리, 클러스터형/IOT)

2. 전체 테이블 스캔과 인덱스 스캔

1) 전체 테이블 스캔

① SQL문에 조건이 존재하지 않는 경우

② SQL문의 주어진 조건에 사용 가능한 인덱스가 존재하지 않는 경우

③ 옵티마이저의 취사 선택

④ 그 밖의 경우(병렬처리 방식으로 처리, 전체 테이블 스캔 방식의 힌트 사용)

2) 인덱스 스캔

① 인덱스 유일 스캔(Index Unique Scan): 

- 유일 인덱스(Unique Index)를 사용하여 단 하나의 데이터를 추출하는 방식

- Unique 인덱스는 중복값이 없으므로 수직적 탐색만을 수행

② 인덱스 범위 스캔(Index Range Scan): 

- 루트 블록에서 리프 블록까지 수직적으로 탐색 후 리프 블록을 범위 스캔

③ 인덱스 역순 범위 스캔: 

- Index Range Scan과 동일하지만 뒤에서 앞으로 스캔하므로 내림차순으로 정렬

④ 인덱스 전체 스캔(Index Full Scan): 

- 수직 탐색 없이 리프 블록을 처음부터 끝까지 탐색

⑤ 인덱스 고속 전체 스캔(Fast Full Index Scan): 

- 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock Read 방식으로 스캔(결과 정렬 보장X)

⑥ 인덱스 스킵 스캔(Index Skip Scan):

- 조건절에 인덱스 절이 없어도 레코드를 포함할 "가능성이 있는" 하위 블록만 골라서 스캔

제3절 조인 수행 원리