[SQLD/IT자격증] SQLD 2과목 "SQL 활용" 이론 정리

SQL 2과목 SQL 활용 중 SQL 활용

SQLD(SQL개발자) 시험의 2과목인 SQL 활용 중 SQL 활용에서 요구하는 항목은 아래 사진과 같습니다.

아래 유튜브 채널 강의를 들으며 정리하였습니다.

https://www.youtube.com/watch?v=hLvv0GN0rT8

 

서브쿼리

• 하나의 SQL문 안에 포함되어 있는 또 다른 SQL문
• 반드시 괄호로 묶어 표현해야 한다.

서브쿼리를 사용할 수 있는 곳

• SELECT절
• FROM절
• WHERE절
• HAVING절
• ORDERY BY절
• 기타 DML절

GROUP BY절은 사용할 수 없다.

서브쿼리 주의사항

• 특별한 경우(TOP-N 분석 등)을 제외하고는 서브 쿼리절에 ORDERY BY절을 사용할 수 없다.

 

서브쿼리 종류

동작하는 방식에 따른 종류

• UN-CORRELATED(비연관) 서브쿼리
  • 서브쿼리가 메인쿼리 컬럼을 가지고 있지 않은 형태
  • 메인쿼리에 서브쿼리가 실행된 결과 값을 제공하기 위해 사용
• CORRELATED(연관) 서브쿼리
  • 서브쿼리가 메인쿼리 컬럼을 가지고 있는 형태
  • 메인쿼리가 먼저 수행된 후 서브쿼리 조건이 맞는지 확인하기 위해 사용

위치에 따른 종류

• 스칼라 서브쿼리
  • SELECT에 사용하는 서브쿼리
  • 서브쿼리 결과를 마치 하나의 컬럼처럼 사용하기 위해 사용
  • 조인의 대체 연산으로 사용할 수 있다.
  • 스칼라 서브쿼리를 사용한 조인 처리 시에는 OUTER JOIN이 기본값이다.

SELECT * | 컬럼명 | 표현식,
	(SELECT * | 컬럼명 | 표현식
    FROM 테이블명 | 뷰명
    WHERE 조건)
FROM 테이블명 | 뷰명;

• 인라인뷰
  • FROM절에 사용하는 서브쿼리
  • 서브쿼리 결과를 테이블처럼 사용하기 위해 주로 사용
  • 쿼리 안에 뷰의 형태로 테이블처럼 조회할 데이터를 정의하기 위해 사용
  • 테이블명이 존재하지 않기 때문에 테이블 별칭이 필요하다.
  • 서브쿼리 결과를 메인 쿼리의 어느 절에서나 사용할 수 있다.
  • 인라인뷰의 결과와 메인쿼리 테이블과 조인할 목적으로 주로 사용한다.
  • 모든 연산자를 사용할 수 있다.

SELECT * | 컬럼명 | 표현식,, 
FROM (SELECT * | 컬럼명 | 표현식
	FROM 테이블명 | 뷰명)
WHERE 조건;

#EMP테이블에서 부서별 최대 급여자를 최대 급여와 함께 출력하기
SELECT E.EMPNO, E.ENAME, E.SAL, I.MAX_SAL
FROM EMP E (SELECT DEPTNO, MAX(SAL) AS MAX_SAL
		FROM EMP
            GROUP BY DEPTNO) I
WHERE E.DEPTNO = I.DEPTNO AND E.SAL=I.MAX_SAL;

• WHERE절 서브쿼리
  • 가장 일반적인 서브쿼리
  • 비교 상수 자리에 값을 전달하기 위한 목적으로 주로 사용
  • 리턴 데이터의 형태에 따라 단일행, 다중행, 다중컬럼, 상호연관 서브쿼리로 구분 가능

SELECT * | 컬럼명 | 표현식
FROM 테이블명 | 뷰명
WHERE 조건연산자 (SELECT * | 컬럼명 | 표현식
		FROM 테이블명 | 뷰명
                WHERE 조건);

WHERE절 서브쿼리의 종류

• 단일행 서브쿼리
  • 서브쿼리 결과가 1개의 행이 리턴 되는 형태
  • 연산자 종류에는 =, <>, >, >=, <. <=이 있다.
• 다중행 서브쿼리
  • 서브쿼리 결과가 여러 행이 리턴 되는 형태
  • =, >, < 와 같은 비교 연산자 사용이 불가하다.
  • 서브쿼리 결과를 하나로 요약하거나 다중행 서브쿼리 연산자를 사용한다.

연산자	의미	예제
IN	같은 값을 찾음
> ANY	최솟값을 반환	> ANY(2000, 3000)은 3000보다 작은 행들 반환
< ANY	최댓값을 반환	< ANY(2000, 3000)은 3000보다 작은 행들을 반환
< ALL	최솟값을 반환	< ALL(2000,3000)은 2000보다 작은 행들을 반환
> ALL	최댓값을 반환	> ALL(2000,3000)은 3000보다 큰 행들을 반환

• 다중컬럼 서브쿼리
  • 서브쿼리 결과가 여러 컬럼이 리턴되는 형태
  • 메인쿼리와의 비교 컬럼이 2개 이상이다.
  • 대소비교 전달이 불가능하다.
• 상호연산 서브쿼리
  • 메인쿼리와 서브쿼리의 비교를 수행하는 형태
  • 비교할 집단이나 조건은 서브쿼리에 명시한다.
  • 상호연산 서브쿼리의 연산 순서
    1. 메인쿼리 테이블 READ
    2. 메인쿼리 WHWER절 확인
    3. 서브쿼리 테이블 READ
    4. 서브쿼리 WHERE절 확인
    5. 메인쿼리의 컬럼 값을 서브쿼리의 컬럼과 비교하여 WHERE절 완성
    6. WHERE절에 성립하는 행의 그룹연산 결과 확인
    7. 위 결과를 메인쿼리에 전달하여 해당 조건을 만족하는 행만 추출

 

집합 연산자

• SELECT문 결과를 하나의 집합으로 간주하여 그 집합에 대해 합집합, 교집합, 차집합을 연산한다.
• 두 집합의 컬럼이 동일하게(타입, 순서) 구성도ㅚ어야 한다.
• 전체 집합의 데이터 타입과 컬럼명은 첫번째 집합에 의해 결정된다.

집합연산자 사용 시 주의사항

1. 두 집합의 컬럼 수가 일치해야 한다.
2. 두 집합의 컬럼 순서가 일치해야 한다.
3. 두 집합의 각 컬럼의 데이터 타입이 일치해야 한다.
4. 각 컬럼의 사이즈는 달라도 된다.
5. 개별 쿼리에 ORDER BY를 전달할 수 없고 집합연산자 전체 결과에 ORDER BY를 전달할 수는 있다. (GROUP BY는 개별 쿼리에도 전달할 수 있다.)

합집합

• 두 집합의 총 합을 출력한다.

UNION

• 중복된 데이터를 한 번만 출력한다.
• 중복된 데이터를 제거하기 위해 내부적으로 정렬을 수행한다.
• 중복된 데이터가 없을 경우에는 UNION ALL을 사용하는 것이 불필요한 정렬을 줄일 수 있다.

UNION ALL

• 중복된 데이터도 모두 출력한다.

교집합

• 두 집합에서 공통으로 있는 행을 출력한다.

차집합

• 두 집합에서 한 쪽 집합에만 존재하는 행을 출력한다.
• A-B와 B-A는 다르기 때문에 집합의 순서가 중요하다.

 

그룹함수

• 숫자함수 중 여러값을 전달하여 하나의 요약값을 출력하는 다중행 함수이다.
• GROUP BY절에 의해서 그룹별 연산결과를 리턴한다.
• 반드시 하나의 컬럼만 전달해야 한다.
• NULL은 무시하고 연산한다.

COUNT

• 행의 수를 세는 함수
• 대상 컬럼은 * 또는 단 하나의 컬럼만 전달 가능하다. 
• 문자/숫자/날짜/컬럼 모두 전달이 가능하다.
• 행의 수를 세는 경우에는 NOT NULL 컬럼을 찾아 세는 것이 좋다.

SUM

• 총 합을 출력하는 함수
• 숫자 컬럼만 전달이 가능하다.

MIN/MAX

• 최대, 최소를 출력하는 함수
• 날짜/숫자/문자 모두 전달 가능하다. (오름차순 순서대로 최소와 최대를 출력한다.)

VARIANCE/STDDEV

• 분산과 표준편차를 출력하는 함수

 

GROUP BY FUNCTINON

• GROUP BY절에 사용하는 함수
• 여러 GROUP BY 결과를 동시에 출력(합집합)하는 기능이다.

GROUP SETS(A,B)

• A별, B별 그룹 연산 결과를 출력한다.
• 나열 순서는 중요하지 않다.
• 기본 출력에 전체 총계는 출력되지 않는다.
• NULL 혹은 ()을 사용하여 전체 총계를 출력할 수는 있다.

ROLL UP(A, B)

• A별, (A,B)별, 전체 그룹 연산 결과를 출력한다.
• 나열 대상의 순서가 중요하다.
• 기본적으로 전체 총계가 출력된다.
• UNION ALL로 대체할 수 있다.

CUBE(A,B)

• A별, B별, (A,B)별, 전체 그룹 연산 결과를 출력한다.
• 나열 대상의 순서가 중요하지 않다.
• 기본 출력에 전체 총계가 출력된다.
• GROUPING SETS로 대체할 수 있다.

 

윈도우 함수

• 서로 다른 행의 비교나 연산을 위한 함수
• GROUP BY를 쓰지 않고 그룹 연산을 할 수 있다.

SELECT 윈도우함수([대상]) OVER([PARTITION BY 컬럼]
		[ORDER BY 컬럼 ASC|DESC]
       		 [ROWS|RANGE BETWEEN A AND B]);

• PARTITION BY : 출력할 총 데이터 수 변화 없이 그룹연산을 수행할 GROUP BY 컬럼이다.
• ORDER BY : RANK의 경우 필수이며 누적값 출력 시에 사용한다.
• ROWS|RANGE BETWEEN A AND B : 연산 범위를 설정하며 ORDER BY절이 필수이다.
• ORDER BY절을 PARTITION BY 전에 사용할 수 없다.
• ROWS vs RANGE
  • ROWS : 값이 같더라도 각 행씩 연산한다.
  • RANGE : 같은 값의 경우 하나의 RANGE로 묶어 동시에 연산한다. (DEFALUT)
• BETWEEND A AND B
  • 시작점 정의(A)
    • CURRENT ROW : 현재 행부터
    • UNBOUNDED PRECEDING : 처음부터 (DEFAULT)
    • N PRECEDING :  N 이전부터
  • 마지막시점 정의(B)
    • CURRENT ROW : 현재 행까지 (DEFUALT)
    • UNBOUNDED FOLLOWING : 마지막까지
    • N FOLLOWING : N 이후까지

 

그룹 함수

• OVER절을 사용하여 윈도우 함수로 사용할 수 있다.
• 반드시 연산할 대상을 그룹함수의 입력값으로 전달해야 한다.

종류

• SUM OVER()
• AVG OVER()
• MIN/MAX OVER()

 

순위 관련 함수

RANK

RANK WITHIN GORUP

• 특정값에 대한 순위 확인
• 일반함수이다.

SELECT RANK(값) WITHIN GROUP(ORDER BY 컬럼);

RANK() OVER()

• 전체 혹은 특정 그룹 중 값의 순위를 확인한다.
• ORDER BY 절이 필수이다.
• 순위를 구할 대상을 ORDER BY절에 명시한다.
• 그룹 내에서 순위를 구할 때에는 PARTITION BY절을 사용한다.

SELECT RANK() OVER([PARTITION BY 컬럼] ORDER BY 컬럼 ASC|DESC);

DENSE_RANK

• 누적 순위를 말한다.
• 값이 같을 때 동일한 순위를 부여 후 다음 순위가 바로 이어지는 방식
  • 1등이 5명이면 그 다음은 2등

ROW_NUMBER

• 연속된 행 번호
• 동일한 순위를 인정하지 않고 단순히 순서대로 나열한대로의 순서 값을 리턴한다.

LAG/LEAD

• 행 순서대로 이전 값(LAG), 이후 값(LEAD) 가져오기
• ORDER BY절이 필수이다.

SEELCT LAG(컬럼,					#가져올 값을 가지는 컬럼
	[N])						#몇번째 값을 가져올기
    OVER ([PARTITION BY 컬럼]				#행의 이동 크룹
    ORDER BY 컬럼 [ASC|DESC]]);				#정렬 컬럼

FIRST_VALUE, LAST_VALUE

• 정렬 순서대로 정해진 범위에서 처음 값과 마지막 값을 출력하낟.
• 순서와 범위 정의에 따라서 최솟값과 최댓값을 리턴할 수 있다.
• PARTITION BY, ORDER BY절 생략이 가능하다.

NTILE

• 행을 특정 컬럼 순서에 따라 정해진 수의 그룹으로 나누기 위한 함수
• 그룹 번호가 리턴된다.
• ORDER BY가 필수이다.
• PARTITION BY를 사용하여 특정 그룹을 원하는 수만큼 분리할 수 있다.
• 총 행의 수가 명확히 나눠지지 않을 때 앞 그룹의 크기가 더 크게 분리된다.
  • 14명을 3개 그룹 → 5,5,4

SELECT NTILE(N) OVER([PARTITION BY 컬럼]
			ORDER BY 컬럼 ASC|DESC)

 

비율 관련 함수

RATIO_TO_REPORT

• 각 값의 비율을 리턴한다.
• ORDER BY절 사용이 불가능하다.

RATION_TO_REPORT(대상) OVER([PARTITION BY 컬럼])

CUME_DIST

• 각 행의 수에 대한 누적비율을 리턴한다.
• 특정 값이 전체 데이터 집합에서 차지하는 위치를 백분위수로 계산하여 출력한다.
• ORDER BY를 사용하여 누적비율을 구하는 순서를 정할 수 있다.
• ORDER BY가 필수이다.

CUME_DIST(대상) OVER([PARTITION BY 컬럼]
		ORDER BY 컬럼 ASC|DESC)

PERCENT_RANK

• 분위수를 출력한다.
• 전체 COUNT 중에서 상대적 위치를 출력한다. (0~1 범위 내에서)
• ORDER BY가 필수이다.

PERCENT_RANK(대상) OVER([PARTITION BY 컬럼]
		ORDER BY 컬럼 ASC|DESC)

 

TOP N QUERY

• 페이징 처리를 효과적으로 수행하기 위해 사용한다.
• 전체 결과에서 특정 N개를 추출한다.

ROWNUM

• 출력된 데이터를 기준으로 행 번호를 부여한다.
• 가상의 번호이기 때문에 특정 행을 지정하거나 연산할 수 없다.
• = 연산과 > 연산이 불가능하다.
• 상위 N명의 결과를 출력하고 싶다면 인라인뷰에서 각 행마다 순위를 직접 부여한 이후에 ROWNUM을 사용해야 한다.
• 혹은 윈도우 함수의 RANK를 사용할 수 있다.

FETCH

• 출력될 행의 수를 제한하는 절이다.
• ORABLE 12C부터 제공하며 SQL Server는 사용가능하다.
• ORDER BY절 뒤에서 사용된다.

SELECT
FROM
WHERE
GROUP BY
GABING
ORDER BY
OFFSET N {ROW|ROWS}
FETCH {FIRST | NEST} N {ROW|ROWS} ONLY

• OFFSET : 건너뛸 행의 수
• N : 출력할 행의 수 
• FETCH : 출력할 행의 수를 전달하는 구문
• FIRST : OFFSET을 사용하지 않았을 때 처음부터 N행 출력을 명령
• NEXT: OFFSET을 사용했을 때 제외한 행 다음부터 N행 출력을 명령
• ROW|ROWS : 행의 수에 따라 하나일 겨우 단수, 여러 값이면 복수형

TOP N

• SQL Server에서만 사용한다.
• 서브쿼리 사용없이 하나의 쿼리로 정렬된 순서대로 상위 N개 추출이 가능하다.
• WITH TIES를 사용하여 동순위까지 함께 출력할 수 있다.

SELECT TOP N 컬럼1, 컬럼2, ...
FROM 테이블명
ORDER BY 정렬컬럼명 [ASC|DESC] ...

 

계층형 질의

• 하나의 테이블 내 각 행끼리 관계를 가질 때 연결고리를 통해 행과 행 사이의 계층(depth)를 표현하는 기법
• PRIOR의 위치에 따라 연결하는 데이터가 다르다.

SELECT 컬럼명
FROM 테이블명
START WITH 시작조건
CONNECT BY [NOCYCLE] PRIOR 연결조건;

• START WITH : 데이터를 출력할 시작을 지정하는 조건
• CONNECT BY PRIOR : 행을 이어나갈 조건
• NOCYCLE : 순환 방지

계층형 질의 가상 컬럼

• LEVEL : 각 depth를 표현한다. (시작점부터 1)
• CONNECT_BY_ITLEAF : LEAF NODE 여부

계층형 질의 가상 함수

• CONNECT_BY_ROOT 컬럼명 : 루트노드에 해당하는 컬럼값
• SYS_CONNECT_BY_PATH (컬럼, 구분자) : 이어지는 경로 출력
• ORDER SIBILINGS BY 컬럼 : 같은 LEVEL일 경우 정렬 수행
• CONNECT_BY_ISCYCLE : 계층형 쿼리의 결과에서 순환이 발생했는지 여부

 

데이터 구조

LONG DATA

• 하나의 속성이 하나의 컬럼으로 정의되어 값들이 여러 행으로 쌓이는 구조
• RDBMS의 테이블 설계 방식
• 다른 테이블과의 조인 연산이 가능한 구조

WIDE DATA

• 행과 컬럼에 유의미한 정보 전달을 목적으로 작성하는 교차표
• 하나의 속성값이 여러 컬럼으로 분리되어 표현
• RDBMS에서는 값이 추가될 때마다 컬럼이 추가되어야 하기 때문에 비효율적이다.
• 다른 테이블과의 조인 연산이 불가능한 구조
• 주로 데이터 요약을 목적으로 사용

 

데이터 구조 변경

PIVOT

• LONG 에서 WIDE로
• 교차표를 만드는 기능이다.
• STACK, UNSTACK, VALUE 컬럼의 정의가 중요하다.
• FROM 절에서 STACK, UNSTACK, VALUE 컬럼명을 정의해야 한다.
• PIVOT 절에서 UNSTACK, VALUE 컬럼명을 정의한다.
• PIVOT 절의 IN 연산자에 UNSTACK 컬럼값을 정의한다.

SELECT *
FROM 테이블명 | 서브쿼리
	PIVOT (VLAUE컬럼명 FOR UNSTACK컬럼명 IN (값1, 값2, 값3));

UNPIVOT

• WIDE에서 LONG으로
• SATCK컬럼 : 이미 UNSTACK 되어 있는 컬럼을 하나의 컬럼으로 STACK 시 새로 만들어지는 컬럼명
• VALUE컬럼 : 교차표에서 VALUE값을 하나의 컬럼으로 표현하고자 할 때 새로 만들어지는 컬럼명
• 값1, 값2, ... : 실제 UNSTACK 되어 있는 컬럼명들

SELECT *
FROM 테이블명 | 서브쿼리
	UNPIVOT (VLAUE컬럼명 FOR STACK컬럼명 IN (값1, 값2, 값3));

 

정규 표현식

• 문자열의 공통된 규칙을 보다 일반화 하여 표현하는 방법

REGEXP_REPLACE

• 정규식 표현을 사용하여 문자열 치환이 가능하다.

REGEXP_REPLACE(대상, 찾을문자열, [바꿀문자열], [검색위치], [발견횟수], [옵션])

특징

• 바꿀 문자열 생략 시에 문자열을 삭제한다.
• 검색 위치 생략 시 DEFAULT 값은 1이다.
• 발견 횟수 생략 시에에는 0이다.

옵션

• c : 대소를 구분하여 검색한다.
• i : 대소를 구분하지 않고 검색한다.
• m : 패턴을 다중라인으로 선언할 수 있다.

REGEXP_SUBSTR

• 정규식 표현을 사용하여 문자열을 추출할 수 있다.

REGEXP_SUBSTR(대상, 패턴, [검색위치], [발견횟수], [옵션], [추출그룹])

특징

• 검색 위치 생략 시 DEFAULT 값은 1이다.
• 발견 횟수 생락 시에는 1이다.
• 추출그룹은 서브패턴 번호를 이야기 한다.

옵션

• c : 대소를 구분하여 검색한다.
• i : 대소를 구분하지 않고 검색한다.
• m : 패턴을 다중라인으로 선언할 수 있다.

REGEXP_INSTR

• 주어진 문자열에서 특정패턴의 시작위치를 반환한다.

REGEXP_INSTR(원본, 찾을문자열, [시작위치], [발견횟수], [옵션])

특징

• 시작위치 생략 시에는 처음부터 확인한다.
• 발견횟수 생락 시에는 처음 발견된 문자열의 위치를 반환한다.

옵션

• c : 대소를 구분하여 검색한다.
• i : 대소를 구분하지 않고 검색한다.
• m : 패턴을 다중라인으로 선언할 수 있다.

REGEXP_LIKE

• 주어진 문자열에서 특정패턴을 가지는 경우를 반환한다.

REGEXP_LIKE(원본, 찾을문자열, [옵션])

옵션

• c : 대소를 구분하여 검색한다.
• i : 대소를 구분하지 않고 검색한다.
• m : 패턴을 다중라인으로 선언할 수 있다.

REGEXP_COUNT

• 주어진 문자열에서 특정패턴의 횟수를 반환

REGEXP_COUNT(원본, 찾을문자열, [옵션])

옵션

• c : 대소를 구분하여 검색한다.
• i : 대소를 구분하지 않고 검색한다.
• m : 패턴을 다중라인으로 선언할 수 있다.