대부분의 독자가 알고 있드시 2024년 SQLD가 개정되면서 출제범위가 변경되었다. 기존에는 표준조인이 SQL 활용에 들어갔으나 새로운 시험에서는 SQL 기본으로 영역이 바뀌었다. 따라서 본 글은 어쩔 수 없이 두 장에 걸친 내용을 담게 되었다.
2과목 1장 SQL 기본에 관한 요약을 보고 싶다면 이전글1에 있는 순서대로 공부한다면 도움이 될 것이다. 한편, 2과목 2장에서도 과거의 목차에서 달라진 점이 있기에 이 부분을 명확히 표기하고자 한다. (2024.03.03 작성)
※필독! SQL 활용 순서: 서브쿼리 > 집합연산자 > 그룹 함수~윈도우 함수 (이전글2) > TOP-N > 계층형 질의와 셀프 조인 > PIVOT > 정규표현식 순.
2과목, SQL 기본 및 활용의 새로운 장으로 넘어왔다. 제2장, SQL 활용에서는 보다 실전적이고 어려운 내용을 다룬다. 이번에도 Data On Air를 참고하였음을 밝힌다. 확실히 이전에는 이론 설명이 많았는데 이번부터는 문제 풀이에 대한 내용이 많아졌다.
Index
1절 표준 조인
SQL 프로그램 개발사마다 조인 방법이 달라 이에 따른 불편함을 해소하고자 통일된 조인 방법을 고안. (ANSI 조인, ANSI: 미국국립표준협회)
ORACLE 조인과 표준조인의 차이를 알고 변환할 줄 알아야 함.
1. STANDARD SQL 개요
- E.F.Codd 박사의 논문 ☞ 8가지 관계형 대수는 각각 4개의 일반 집합 연산자와 순수 관계 연산자로 구분.
- 일반 집합 연산자
- 1) UNION 연산은 UNION 기능으로 (합집합)
- 2) INTERSECTION 연산은 INTERSECT 기능으로 (교집합)
- 3) DIFFERENCE 연산은 EXCEPT(Oracle은 MINUS) 기능으로 (차집합)
- 4) PRODUCT 연산은 CROSS JOIN 기능으로 구현 (곱집합-생길 수 있는 모든 데이터의 조합)
- 순수 관계 연산자
- 관계형 데이터베이스를 구현하기 위해 새롭게 만들어진 연산자
- 5) SELECT 연산은 WHERE 절로 구현
- 6) PROJECT 연산은 SELECT 절로 구현
- 7) (NATURAL) JOIN 연산은 다양한 JOIN 기능으로 구현
- 8) DIVIDE 연산은 현재 사용되지 않음.
2. FROM 절 JOIN
- 1) INNER JOIN
- JOIN의 DEFAULT 옵션. '내부 JOIN'이라고도 함.
- JOIN 조건에서 동일한 값이 있는 행만 반환.
- USING 조건절 or ON 조건절 필수.
- * ORACLE: WHERE절에 조인조건 명시
- 2) NATURAL JOIN
- INNER JOIN의 하위 개념
- 두 테이블 간의 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI JOIN을 수행.
- NATURAL JOIN이 명시되면, USING 조건절, ON 조건절, WHERE 절에서 JOIN 조건을 정의할 수 없음.
- SQL Server에서는 지원 X.
- 3) USING 조건절
- 같은 이름을 가진 칼럼들 중 원하는 칼럼에 대해서만 선택적 EQUI JOIN 가능
- SQL Server에서는 지원 X.
- 4) ON 조건절
- WHERE 절의 JOIN 조건을 FROM 절의 ON 조건절로 분리하여 표시 ☞ 이해 용이.
- 칼럼명이 다르더라도 JOIN 조건을 사용 가능
- ON 조건절과 WHERE 검색 조건의 혼용 가능
- 5) CROSS JOIN
- 양쪽 집합의 M*N 건의 데이터 조합 발생.
- 튜닝이나 리포트를 작성하기 위해 고의적으로 사용.
- 모든 DIMENSION의 CROSS PRODUCT를 먼저 구할 때 유용.
- 카티션 조인과 동일.
- 표현방법: ORACLE- FROM에 콤마로 테이블명 표기 ↔ ANSI- FROM 테이블명 CROSS JOIN 테이블명.
- ON 사용 불가.
- 6) OUTER JOIN
- JOIN 조건에서 동일한 값이 없는 행도 반환. (조건에 부합하지 않는 대상도 출력)
- USING 조건절 or ON 조건절 필수.
- * ORACLE: 컬럼 우측에 (+)를 넣어 OUTER JOIN 표현.
- (1) LEFT OUTER JOIN
- 표기된 좌측 테이블에 해당하는 데이터를 먼저 읽고, 나중 표기된 우측 테이블에서 대상 데이터를 읽음.
- 같은 값이 없으면 Null.
- *ORACLE: (+)가 있는 테이블 반대편을 기준으로 함. (예. A.사원ID = B.사원ID (+) | A.사원ID를 기준으로 LEFT OUTER JOIN)
- (2) RIGHT OUTER JOIN
- 우측 테이블이 기준이 되어 결과를 생성.
- (3) FULL OUTER JOIN
- 좌측, 우측 테이블의 모든 데이터를 읽어 JOIN하여 결과를 생성.
- 중복 데이터 삭제 (= Union)
- FULL OUTER JOIN과 CROSS JOIN으로 출력되는 행(튜플) 수는 다름.
* 조인조건은 병합하고자 하는 테이블의 수보다 1개 적은 수의 조건이 필요함.
이후 동등, 비동등 JOIN으로!
[SQLD] 2과목 1장 SQL 기본 요약 - 함수, GROUP BY, JOIN (구 함수~JOIN + SQL 노랭이 오답 / 2024 개정사항 반
※ 이전글1: 2023.03.01 - [Cerificate/SQLD] - [SQLD] 2과목 1장 SQL 기본 요약 - WHERE (구 DML ~ WHERE절 / 2024 개정사항 반영) ※ 이전글2: 2023.02.27 - [Cerificate/SQLD] - [SQLD] 2과목 1장 SQL 기본 요약 - 관계형 데이터베
fromitot.tistory.com
2절 집합 연산자
서브쿼리 후, 집합 연산자 학습 개시.
- 집합 연산자 (Set Operator): 여러 질의(SQL)의 결과를 연결하여 하나로 결합하는 방식. ▷ 2개 이상의 질의 결과를 하나의 결과로 만듦.
- 주의사항
- 집합연산자와 ORDER BY를 같이 쓰는 경우, ORDER BY는 마지막에 나와야 함.
- 비교하는 집합의 컬럼명이 다를 때, 위에 있는 컬럼명을 우선.
- SELECT 절의 칼럼 수가 동일 & SELECT 절의 동일 위치에 있는 칼럼의 데이터 타입이 상호 호환 가능.
- 집합연산자가 2개 이상이라면, 위에서 아래로 연산하되 중복 제거 여부에 주의.
- 집합 연산자의 종류
- UNION과 UNION ALL의 차이: 후자는 중복을 포함하고, 정렬도 하지 않음. (UNION ALL만 중복 유지.)
- MINUS: 차집합 표현. (SQL SERVER에서는 EXCEPT 사용.)
이전글2의 그룹함수로!
※ TOP-N
윈도우 함수에 이어 새로운 챕터 돌입. 기존에는 WHERE절의 하위 범주 (2과목 1장)로 분류됨.
- 상하위 N개를 인라인뷰와 ROWNUM을 사용해 추리는 기능.
- ROWNUM: 테이블의 행 (튜플)에 부여되는 임시의 일련번호.
- WHERE에 ROWNUM을 사용해 조건을 부여하여 반환되는 튜플 개수 제한. (LIMIT과 유사.)
- 1을 먼저 사용하고 순차적으로 큰 수 사용 가능.
- 'ROWNUM = 2', 이와 같은 조건을 적용하기 위해 인라인뷰 적용.
- ORDER BY보다 WHERE가 먼저 실행되므로 정렬처리가 기완료된 테이블 (인라인뷰)이 필요.
TOP절 (Data On Air - 2과목 1장 WHERE에서 발췌)
- 결과 집합으로 출력되는 행의 수를 제한 (SQL Server)
- 형식 : TOP (Expression) [PERCENT] [WITH TIES]
- - Expression : 반환할 행의 수
- - PERCENT : 쿼리 결과 집합에서 처음 Expression%의 행만 반환.
- - WITH TIES : ORDER BY 절이 지정된 경우에만 사용 가능. TOP N(PERCENT)의 마지막 행과 같은 값이 있다면, 추가 행이 출력됨.
3절 계층형 질의와 셀프 조인
1. 계층형 질의
- 계층형 데이터: 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터. (Data On Air)
- 계층형 질의 (이기적)
- 동일 테이블 안에 계층 관계가 있는 컬럼이 있고, 이를 활용해 계층 관계를 표현하는 질의 (SQL). (이기적)
- 계층 관계를 지니는 데이터를 간단하게 뽑아오고자 만든 쿼리.
- 사이트의 메뉴 화면 연상.
- 엔터티를 순환관계 데이터 모델로 설계 ▷ 계층형 데이터 발생
- 테이블에 계층형 데이터가 존재하면, 데이터 조회를 위해서 계층형 질의(Hierarchical Query) 사용.
1) Oracle 계층형 질의
SELECT ~
,
, LEVEL
, ~
FROM ~
WHERE ~
START WITH 상위컬럼명 IS NULL
CONNECT BY NOCYCLE 상위 컬럼명 = PRIOR 컬럼명
ORDER SIBILING BY 컬럼명;- 전개 순서: FROM > START WITH ~ > WHERE 순. (전개를 먼저 하고 필터링)
- START WITH: 계층 전개 시작위치 조건 작성 (루트 지정 | 상위컬럼명이 NULL이면 시작.)
계층 구조 전개의 시작 위치 지정. (루트 데이터 지정) - CONNECT BY: 이후 전개될 자식 데이터 조건 지정.
자식 데이터는 CONNECT BY절의 조건을 만족해야 함.(조인) - NOCYCLE: 사이클이 발생한 이후 데이터 전개 멈춤. (같은 데이터가 전개 중에 반복되면 이를 Cycle이라 함 ▷ 오류.)
- PRIOR: 기준 데이터 표시.
CONNECT BY절에 사용하며 현재 읽은 칼럼 지정.의 순방향 전개.) - ORDER SIBLINGS BY : 형제 노드(동일 LEVEL) 간 정렬 수행.
- LEVEL: 현재 행의 계층 레벨 출력. (가상컬럼)
- WHERE : 모든 전개를 수행한 후, 조건에 맞는 데이터만 추출.
- 가상 칼럼(Pseudo Column) 제공
- CONNECT_BY_ISLEAF: 단말 여부 확인 (단말이면 1, 아니면 0)
- CONNECT_BY_ISCYCLE: 사이클 여부 확인 (있으면 1, 아니면 0)
* 순방향과 역방향 (빈출!)
- 부모 = PRIOR 자식 ▷순방향
- PRIOR 자식 = 부모 ▷순방향
- 자식 = PRIOR 부모 ▷역방향
- PRIOR 부모 = 자식 ▷역방향
하위 개념으로 내려가면 순방향. 상위 개념으로 올라오면 역방향. (현재 자식을 기준으로 부모와 비교하며 내려감 - 순방향)
- 계층형 질의 관련 함수
- SYS_CONNECT_BY_PATH: root data부터 현재 데이터까지의 경로 표시 | SYS_CONNECT_BY_PATH(칼럼, 경로 분리 문자)
- CONNECT_BY_ROOT: 현재 전개 데이터의 root data 표시. (최상위메뉴) | CONNECT_BY_ROOT 칼럼
2) SQL Server 계층형 질의
- 생략
2. 셀프 조인 (Self Join)
SELECT ALIAS명1.칼럼명, ALIAS명2.칼럼명, ...
FROM 테이블1 ALIAS명1, 테이블2 ALIAS명2
WHERE ALIAS명1.칼럼명2 (상위메뉴) = ALIAS명2.칼럼명1; (메뉴)- 셀프 조인: 동일 테이블 사이의 조인. 위의 계층 질의가 없었을 때, 셀프 조인 사용함.
- 식별을 위해 반드시 테이블 별칭(Alias)을 사용.
- 바로 상위의 정보만 알 수 있고, 상위의 상위 정보 등을 보려면 셀프조인을 더 했어야 함.
TOP-N, 계층형 질의, 셀프 조인 끝. 아래 4절 서브쿼리 이후, PIVOT 등 신규 범위 추가.
4절 서브쿼리
이전글1에서 JOIN을 끝내고 새로운 주제인 서브쿼리 시작. 서브쿼리는 난이도가 높은 편.
- 서브쿼리 (Subquery)
- 하나의 SQL문안에 포함되어 있는 또 다른 SQL문. (Data On Air)
- 작성된 쿼리 내부에 다른 쿼리 삽입. (이기적)
- 알려지지 않은 기준을 이용한 검색을 위해 사용.
- 주의사항
- ① 서브쿼리를 괄호로 감싸서 사용
- ② 서브쿼리는 단일 행(Single Row) or 복수 행(Multiple Row) 비교 연산자와 함께 사용 가능
- ③ 서브쿼리에서는 ORDER BY 사용X (ORDER BY절은 SELECT절에서 오직 한 개만 올 수 있기 때문)
- 사용 위치 : SELECT 절, FROM 절, WHERE 절, HAVING 절, ORDER BY 절, INSERT문의 VALUES 절, UPDATE문의 SET 절
- SELECT에 쓰이면 스칼라 서브쿼리
- FROM에 쓰이면 인라인뷰
- WHERE에 쓰이면 중첩 서브쿼리
- 스칼라 서브쿼리
- 하나의 컬럼에 대해 하나의 행만 반환 (값이 없으면 NULL 출력)
- 반환되는 컬럼은 1개만 가능.
- 아우터 조인으로 변경 가능.
- 인라인뷰
- 가상의 테이블을 동적으로 생성.
- 인라인뷰만 따로 실행 가능한 독립적 쿼리.
- 동작방식별 분류
- 비상관(비연관) 서브쿼리: 서브쿼리가 메인쿼리의 컬럼 안 가짐 (메인쿼리와 무관) / 메인쿼리에 결과값 제공 목적. / 서브쿼리 → 메인 쿼리 순으로 실행.
- 상관(연관) 서브쿼리 : 메인쿼리 컬럼 보유 / 주로 조건 확인 차 사용. / 실행순서: 메인쿼리에서 조건에 맞는 튜플 개수만큼 쿼리 실행 → 서브쿼리 안에 있는 메인쿼리 컬럼에 값 입력. → 이 과정을 메인쿼리의 출력 행까지 반복.
- 반환 데이터에 따른 분류
1. 단일 행 서브 쿼리
SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버
FROM PLAYER
WHERE TEAM_ID = (SELECT TEAM_ID FROM PLAYER WHERE PLAYER_NAME = '정남일')
ORDER BY PLAYER_NAME;- 단일 행 비교 연산자(=, <, <=, >, >=, <>)와 함께 사용할 때는 서브쿼리의 결과 건수가 반드시 1건 이하여야 함.
2. 다중 행 서브쿼리
- 결과가 2건 이상 반환될 수 있다면 반드시 다중 행 비교 연산자(IN, ALL, ANY, SOME)와 함께 사용.
- 다중 행 비교 연산자
* EXISTS 문법 (NOT EXISTS)
- WHERE 뒤에 컬럼명 없이 EXISTS 입력.
- 서브쿼리 안 SELECT절에는 아무 컬럼명이나 입력.
- 상관 서브쿼리처럼 메인쿼리의 컬럼 참조.
3. 다중 칼럼 서브쿼리
- 개념: 서브쿼리의 결과로 여러 개의 컬럼이 반환되어 메인쿼리의 조건과 동시에 비교되는 것.
- 괄호 안은 AND로, 괄호 간은 OR로 묶임.
- ORACLE에서만 지원.
4. 연관 서브쿼리
- 연관 서브쿼리 (Correlated Subquery) : 서브쿼리 내에 메인쿼리 칼럼이 사용된 서브쿼리.
이것으로 서브쿼리 종료. 다음 주제는 위에 있는 집합 연산자.
5. 뷰
- 테이블은 실제로 데이터 보유 ↔ 뷰(View)는 실제 데이터 X
- 뷰 정의(View Definition)만을 가짐.
- 뷰 사용 시, 뷰 정의를 참조해서 DBMS 내부적으로 질의를 재작성(Rewrite)하여 질의를 수행.
- 뷰의 장점
- Create문으로 생성. ex) CREATE VIEW V_PLAYER_TEAM AS SELECT P.PLAYER_NAME, P.POSITION, P.BACK_NO, P.TEAM_ID, T.TEAM_NAME FROM PLAYER P, TEAM T WHERE P.TEAM_ID = T.TEAM_ID;
- 제거 시, DROP VIEW 사용.
※ PIVOT
- PIVOT: 행을 열로 바꾸는 것
- UNPIVOT: 열을 행으로 바꾸는 것
SELECT *
FROM (
SELECT 연도, 국가, 수출액
FROM 수출현황
) -- 인라인뷰로 가상테이블 생성
PIVOT (
SUM(수출액) FOR 연도 IN ('2022' AS "22", '2023' AS "23")
)
ORDER BY 국가;* PIVOT 해석
- FROM절에서 인라인뷰로 사용하려는 컬럼 표기.
- FOR에서 열로 쓰고자 하는 컬럼과 값 (별칭)을 표현.
- FOR 이전의 집계처리된 컬럼은 테이블의 값으로 쓰임.
- PIVOT 괄호 안에 들어가지 않은 컬럼은 행으로 사용됨. (국가별로 연도에 따른 수출액 추이 파악.)
UNPIVOT도 위와 유사한 코드 구성을 지님.
SELECT *
FROM (
SELECT *
FROM 피벗테이블
)
UNPIVOT(
수출액 FOR 국가 IN ("USA" AS '미국', "JPN" AS '일본')
);피벗 테이블 (2023년도 국가별 수출액 피벗 테이블로 가정)을 불러오고 UNPIVOT으로 피벗 테이블을 변환하여 해당 테이블의 값 (수출액)을 연도, 국가와 함께 표현한다. UNPIVOT에서는 집계함수가 쓰이지 않았음에 유의.
* SUBSTR(단어, 1, 2): 어떤 단어의 첫번째 문자로부터 2개 문자 추출.
※ 정규표현식
- Regular Expression, 데이터에서 특정 규칙을 지닌 문자열을 찾거나 처리하는데 유용한 고급 쿼리 스킬.
- REGEXP_LIKE: 문자열이 특정 패턴을 가졌는가?
- REGEXP_REPLACE: 특정 문자열 변환.
- REGEXP_SUBSTR: 특정 문자열 추출.
- REGEXP_COUNT: 특정 문자열 사용횟수 확인.
- REGEXP_INSTR: 특정 문자열 위치 파악. (일종의 인덱싱)
REGEXP_SUBSTR
. : 모든 문자와 일치. (문자 1개와 동일한지?)
| : 대체 문자 구분. (연결연산자 | OR과 같음.)
\ : 특수문자 이스케이프 처리. (일반문자처럼 취급)
\d : 숫자 [0-9] 1개
^ : 캐럿. 문자열의 시작.
$ : 달러. 문자열의 끝.
-- 수량사 (일치횟수 확인) (탐욕적: 최대한 많이 취하고자 함)
? : 0~1회 일치
* : 0~무제한
+ : 1~무제한
{n} : n회 일치
{n,} : 최소 n회 일치
{n, p} : 최소 n회, 최대 p회 일치
---
()안에 문자열을 넣어 여러 문자에 대해서도 표현 가능첫번째 인수와 제시된 패턴이 일치하는 문자열을 출력. (일치 시 문자열 그대로, 불일치 시 NULL 반환.)
-- 1)
SELECT REGEXP_SUBSTR('apple', 'a...e');
-- 2)
SELECT REGEXP_SUBSTR('apple', 'ap');
-- 3)
SELECT REGEXP_SUBSTR('apple', 'alp');
-- 4)
SELECT 주민번호, REGEXP_SUBSTR(주민번호, '\d{6}', 1, 1);
-- 5)
SELECT 주민번호, REGEXP_SUBSTR(주민번호, '\d{1}', 7, 1);
-- 6)
SELECT REGEXP_SUBSTR('hi0303', '^\d', 1, 1);
-- 7)
SELECT REGEXP_SUBSTR('hi0303@gmail.com', '\.com$', 1, 1);
-- 8)
SELECT REGEXP_SUBSTR('ttest', 'tt?');
-- 9)
SELECT REGEXP_SUBSTR('ttest', 't?es');
-- 10)
SELECT REGEXP_SUBSTR('ttest', 'tt?st');1)~3) 각각 apple, ap, (null) 출력함.
* 작은 따옴표로 묶인 단어는 대소문자를 구분하므로 주의!
4)~5)는 숫자와 관련된 정규표현식이다. 4)는 010101, 5)는 3을 출력할 수 있다. (데이터에 따라 NULL도 가능.)
6)은 (null)을 반환함. 문자열의 첫 문자가 숫자가 아니기 때문.
7)은 .com으로 끝나는지 묻는 것이다. 이 경우, .com을 출력한다.
8)~10)은 tt, tes, (null)을 출력함.10)은 ttst 혹은 tst라는 문자열이 없기에 null이 나왔다.
-- 문자리스트 활용 표현식
PERL 문자 클래스: [0-9] [a-z] [A-Z] [a-zA-Z]
POSIX 문자 클래스: [:digit:], [:lower:], [:alpha:]
\w : 숫자+영문자
-- PERL 정규표현식 연산자 (비탐욕적)
?? : 0~1회 일치
*? : 0~무제한 일치
+? : 1~무제한 일치
{n}? : n회 일치
{n, p}? : 최소 n회, 최대 p회 일치-- 11)
SELECT REGEXP_SUBSTR('SQLD0309', '[a-z]', 3, 2);
-- 12)
SELECT REGEXP_SUBSTR('SQLD0309', '[:alpha:]', 3, 2);11), 12)는 동일하게 D를 출력한다. 문자 부분 중 3번째인 L에서 시작해 2번째에 있는 D를 반환하는 것이다.
* 기호 끝에 ?가 한번 더 들어가 PERL 연산자는 위에 언급한 수량와 달리 비탐욕적, 즉 최소한으로 출력하는 것에 주의.
REGEXP_LIKE
LIKE 매칭처럼 정규표현식이 조건에 부합하면 행을 반환한다. WHERE 절에서 사용됨. 아래 코드는 -3이 있는 주민번호를 가진 행을 출력하는 것이다.
SELECT *
FROM ~~
WHERE REGEXP_LIKE (주민번호, '-3');
REGEXP_REPLACE
특정 문자열을 변경하는 기능을 가진다.
SELECT REGEXP_REPLACE('hi0303@naver.com', 'naver', 'gmail') from dual;
REGEXP_INSTR
특정 문자의 위치를 출력한다. 파이썬은 0부터 카운트하나 SQL은 1부터 시작이다.
REGEXP_COUNT
특정 패턴이 몇 번 나오는지 파악한다. 아래의 경우, 2를 출력한다.
SELECT REGEXP_COUNT('abcabc', 'ab');
이것으로 2과목 2장 SQL 활용 끝.
참고
- Data On Air
- 23.03.03 이론 작성 시작 & 종료.
- 24.03.03 2024 이기적 SQLD 강의 반영.
