SQLD를 준비하며 공부했던 내용으로, 2020 이기적 SQL 개발자(Developer) 이론서 + 기출문제를 참고했다.
-
조인(Join)
(1) EQUI(등가)조인(교집합)-
조인은 여러 개의 릴레이션을 사용해 새로운 릴레이션을 만드는 과정
-
조인의 가장 기본은 교집합을 만드는 것
-
두 개의 테이블 간 일치하는 것을 조인
-
EQUI 조인은 EMP 테이블과 DEPT 테이블에서 DEPTNO 칼럼을 사용하여 같은 것을 조인함
-
조인문에 추가 조건 및 정렬을 할 수 있음
SELECT * FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO AND EMP.ENAME LIKE '임%' ORDER BY ENAME; -
EQUI 조인을 한 후에 실행 계획을 확인해서 내부적으로 두 개의 테이블을 어떻게 연결했는지 확인할 수 있음
-
위의 예를 보면 DEPT 테이블과 EMP 테이블 전체를 읽은 다음에 (TABLE ACCESS FULL)해시 함수를 사용해서 두 개의 테이블을 연결한 것
-
EQUI 조인을 한 후에 실행 계획을 확인해서 내부적으로 두 개의 테이블을 어떻게 연결했는지 확인할 수 있음
-
위의 예를 보면 DEPT 테이블과 EMP 테이블 전체를 읽은 다음(TABLE ACCESS FULL)해시 함수를 사용해서 두 개의 테이블을 연결한 것, 해시 함수는 테이블을 해시 메모리에 적재한 후 해시 함수로써 연결하는 방법, 해시 조인은 EQUI 조인만 사용 가능한 방법
-
해시 조인: 해시 조인은 먼저 선행 테이블을 결정하고 선행 테이블에서 주어진 조건(WHERE구)에 해당하는 행을 선택함, 해당 행이 선택되면 조인 키(Join Key)를 기준으로 해시 함수를 사용해서 해시 테이블을 메인 메모리에 생성하고 후행 테이블에서 주어진 조건에 만족하는 행을 찾음, 후행 테이블의 조인 키를 사용해서 해시 함수를 적용하여 해당 버킷을 검색함
(2) INNER JOIN
-
ON문을 사용해서 테이블을 연결
-
INNER JOIN구에 두 개의 테이블명을 서술하고 ON구조 조인 조건을 서술함
-
조인문에 추가 조건 및 정렬을 할 수 있음
SELECT * FROM EMP INNER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO AND EMP.ENAME LIKE '임%' ORDER BY ENAME;
(3) INTERSECT 연산
-
두 개의 테이블에서 교집합(공통된 값)을 조회
SELECT DEPTNO FROM EMP INTERSECT SELECT DEPTNO FROM DEPT;
-
-
Non-EQUI(비등가) 조인
- 두 개의 테이블 간 조인하는 경우 =을 사용하지 않고 >, <, >=, <= 등을 사용
- 정확하게 일치하지 않는 것을 조인하는 것
-
OUTER JOIN
-
두 개의 테이블 간에 교집합(EQUI JOIN을 조회하고 한쪽 테이블에만 있는 데이터도 포함시켜서 조회
-
ex) DEPT 테이블, EMP 테이블 OUTER JOIN하면 DEPTNO가 같은 것을 조회하고 DEPT 테이블에만 있는 DEPTNO도 포함시킴
-
이 때 왼쪽 테이블의 행만 포함하면 LEFT OUTER JOIN, 오른쪽 테이블의 행만 포함하면 RIGHT OUTER JOIN
-
FULL OUTER JOIN은 LEFT OUTER JOIN과 RIGHT OUTER JOIN 모두를 하는 것
-
(+) 기호 사용
SELECT * FROM DEPT, EMP WHERE EMP.DEPTNO (+) DEPT.DEPTNO; -
LEFT OUTER JOIN과 RIGHT OUTER JOIN
-
LEFT OUTER JOIN: 두 개의 테이블에서 같은 것을 조회하고 왼쪽 테이블에만 있는 것 포함해서 조회
SELECT * FROM DEPT LEFT OUTER JOIN EMP ON EMP.DEPTNO = DEPT.DEPTNO; -
RIGHT OUTER JOIN: 두 개의 테이블에서 같은 것을 조회하고 오른쪽 테이블에만 있는 것 포함해서 조회
SELECT * FROM DEPT RIGHT OUTER JOIN EMP ON EMP.DEPTNO = DEPT.DEPTNO;
-
-
-
CROSS JOIN
-
조인 조건구 없이 2개의 테이블을 하나로 조인함
-
조인구가 없기 때문에 카테시안 곱이 발생
-
행이 14개인 테이블과 행이 4개인 테이블을 조인하면 56개의 행 조회
-
CROSS JOIN은 FROM절에 'CROSS JOIN'구 사용
SELCT * FROM EMP CROSS JOIN DEPT;
-
-
UNION: 두 개의 테이블을 하나로 만드는 연산
-
두 개의 테이블의 칼럼 수, 칼럼의 데이터 형식 모두가 일치해야 함, 두 개의 테이블을 하나로 합치면서 중복된 데이터를 제거함, 정렬(SORT)과정 발생시킴
SELECT DEPTNO FROM EMP UNION SELECT DEPTNO FROM DEPT;- UNION은 중복된 데이터를 제거하면서 테이블을 합침
-
-
UNION ALL: 두 개의 테이블을 하나로 합치는 것, UNION처럼 중복을 제거하거나 정렬을 유발하지 않음
SELECT DEPTNO FROM EMP UNION ALL SELECT DEPTNO FROM DEPT;- UNION ALL은 단순하게 테이블을 합침, 중복을 제거하지않고 테이블을 합침
-
차집합을 만드는 MINUS
-
MINUS 연산은 두 개의 테이블에서 차집합을 조회함, 먼저 쓴 SELECT문에는 있고 뒤에 쓰는 SELECT문에는 없는 집합을 조회함
-
DEPT와 EMP를 MINUS 연산하면 DEPT에만 있는 행을 조회함
-
MINUS와 동일한 연산은 EXCEPT (MS-SQL에서)
SELECT * FROM DEPT MINUS SELECT * FROM EMP;
-
-
계층형 조회(Connect by)
-
계층형으로 데이터를 조회할 수 있음
-
부장에서 차장, 차장에서 과장, 과장에서 대리 순으로 트리 형태의 구조를 위에서 아래로 탐색하면서 조회하는 것, 역방향 조회도 가능
-
트리 형태의 구조로 지릐를 수행하는 것으로 START WITH구는 시작조건을 의미하고 CONNECT BY PRIOR는 조인 조건임, Root 노드로부터 하위노드의 질의를 실행함
-
계층형 조회에서 MAX(LEVEL)을 사용하여 최대 계층 수를 구할 수 있음, 계층형 구조에서 마지막 Leaf Node의 계층값을 구함
-
MAX(LEVEL)이 4이면 트리의 최대 깊이는 4
SELECT MAX(LEVEL) FROM Limbest.EMP START WITH MGR IS NULL CONNECT BY PRIOR EMPNO = MGR;SELECT LEVEL, EMPNO, MGR, ENAME FROM Limbest.EMP START WITH MGR IS NULL CONNECT BY PRIOR EMPNO = MGR;
-
-
계층형 조회 결과를 명확히 보기 위해 LPAD 함수 사용
SELECT LEVEL, LPAD(' ', 4 * (LEVEL -1) ) ||EMPNO, MGR, CONNECT_BY_ISLEAF FROM EMP START WITH MGR IS NULL CONNECT BY PRIOR EMPNO = MGR;-
4LEVEL-1에서, ROOT이면 LEVEL값이 1이 되어 4(1-1) = 0 이라 LPAD(' ', 0)이므로 아무 의미 없음
-
LEVEL값이 2이면 4*(2-1)=4로, 왼쪽 공백 4칸을 화면에 찍음
-
LPAD는 트리 형태로 보기 위해서 사용한 것, LEVEL 값은 Root에 1을 되돌리고 그다음 자식은 2, 그다음 자식은 3이 나오기 때문
-
CONNECT BY 키워드
1) LEVEL: 검색 항목의 깊이를 의미함, 계층구조에서 가장 상위 레벨이 1이 됨
2) CONNECT_BY_ROOT: 계층구조에서 가장 최상위 값을 표시함
3) CONNECT_BY_ISLEAF: 계층구조에서 가장 최하위를 표시함
4) SYS_CONNECT_BY_PATH: 계층구조의 전체 전개 경로를 표시함
5) NOCYCLE: 순환구조가 발생지점까지만 전개됨
6) CONNECT_BY_ISCYCLE: 순환구조 발생 지점을 표시함
-
-
서브쿼리(Subquery)
-
Subquery는 SELECT문 내에 다시 SELECT문을 사용하는 SQL문
-
Subquery의 형태는 FROM구에 SELECT문을 사용하는 인라인 뷰(view)와 SELECT문에 Subquery를 사용하는 스칼라 서브쿼리(Scala Suqbquery) 등이 있음
-
WHERE구에 SELECT문을 사용하면 서브쿼리(Subquery), 서브쿼리 밖에 있는 SELECT문은 메인쿼리(Main Query)
SELECT * FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DEPTNO = 10); -
FROM구에 SELECT문을 사용하여 가상의 테이블을 만드는 효과를 얻을 수 있음
SELECT * FROM (SELECT ROWNUM NUM, ENAME FROM EMP) a WHERE NUM < 5;
-
-
단일 행 서브쿼리와 다중 행 서브쿼리
- 서브쿼리(Subquery)는 반환하는 행 수가 한개인 것과 여러 개인 것에 따라서 단일 행 서브쿼리와 멀티 행 서브쿼리로 분류됨
- 단일 행 서브쿼리는 단 하나의 행만 반환하는 서브쿼리로 비교 연산자 (=, <, <=, >=, <>)를 사용함
- 다중 행 서브쿼리는 여러 개의 행을 반환하는 것으로 다중 행 비교 연산자인 IN, ANY, ALL, EXISTS를 사용해야 함
- 다중 행 비교연산자
- IN(Subquery): Main query의 비교조건이 Subquery의 결과 중 하나만 동일하면 참이 됨(OR조건)
SELECT ENAME, DNAME, SAL FROM EMP, DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND EMP.EMPNO IN (SELECT EMPNO FROM EMP WHERE SAL > 2000); - ALL(Subquery): Main query와 Subquery의 결과가 모두 동일하면 참이 됨, <ALL(최솟값 반환), >ALL(최댓값반환)
SELECT * FROM EMP WHERE DEPTNO <= ALL(20, 30); - ANY(Sunquery): Main query의 비교조건이 Subquery의 결과 중 하나 이상 동일하면 참이 됨, <ANY: 하나라도 크게 되면 참이 됨, >ANY: 하나라도 작게 되면 참이 됨
- EXISTS(Subquery): Main query와 Subquery의 결과가 하나라도 존재하면 참이 됨, Subquery로 어떤 데이터 존재 여부를 확인하는 것, EXITS의 결과는 참과 거짓 반환
SELECT ENAME, DNAME, SAL FROM EMP, DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND EXISTS (SELECT 1 FROM EMP WHERE SAL >2000);
- IN(Subquery): Main query의 비교조건이 Subquery의 결과 중 하나만 동일하면 참이 됨(OR조건)
- 스칼라 Subquery
-
스칼라 subquery는 반드시 한 행과 한 칼럼만 반환하는 서브쿼리
-
만약 여러 행이 반환되면 오류 발생
SELECT ENAME AS "이름" SAL AS "급여", (SELECT AVG(SAL) FROM EMP ) AS "평균급여" FROM EMP WHERE EMPNO=1000;
-
- 연관(Correlated) Subquery
-
연관 Subquery는 Subquery 내에서 Main Query 내의 칼럼을 사용하는 것을 의미함
FROM EMP a WHERE a.DEPTNO = (SELECT DEPTNO FROM DEPT b WHERE b.DEPTNO=a.DEPTNO);
-
-
그룹함수(GROUP FUNCTION)
- ROLLUP
-
GROUP BY의 칼럼에 대해서 Subtotal을 만들어 줌
-
ROLLUP을 할 때 GROUP BY구에 칼럼이 두 개 이상 오면 순서에 따라서 결과가 달라짐
SELECT DECODE(DEPTNO, NULL,'전체합계',DEPTNO), SUM (SAL) FROM EMP GROUP BY ROLLUP(DEPTNO);- DEPTNO가 NULL이면 '전체합계' 문자를 출력함 / ROLLUP을 사용하면 부서별 합계 및 전체합계가 계산됨
-
부서별, 직업별 ROLLUP을 실행하면 부서별 합계, 직업별 합계, 전체합계가 모두 조회됨
-
ROLLUP으로 실행되는 칼럼별로 Subtotal을 만들어 줌
SELECT DEPTNO, JOB, SUM(SAL) FROM EMP GROUP BY ROLLUP(DEPTNO, JOB);
-
- GROUPING함수
-
ROLLUP, CUBE, GROUPING SETS에서 생성되는 합계값을 구분하기 위해서 만들어진 함수
SELECT DETPTNO, GROUPING(DEPTNO), JOB, GROUPING(JOB), SUM(SAL) FROM EMP GROUP BY ROLLUP(DEPTNO, JOB) -
GROUPING 함수의 기능을 사용하면 사용자가 필요로 하는 데이터를 SELECT문으로 작성하여 제공할 수 있음
-
- GROUPING SETS 함수
-
GROUP BY에 나오는 칼럼의 순서와 관계없이 다양한 소계를 만들 수 있음
-
GROUP BY에 나오는 칼럼의 순서와 관계없이 개별적으로 모두 처리함
SELECT DEPTNO, JOB, SUM(SAL) FROM EMP GROUP BY GROUPING SETS(DEPTNO, JOB);- DEPTNO가 NULL이면 '전체합계' 문자를 출력함 / ROLLUP을 사용하면 부서별 합계 및 전체합계가 계산됨
-
- CUBE 함수
-
CUBE 함수에 제시한 컬럼에 대해서 결합 가능한 모든 집계를 계산함
-
다차원 집계를 제공하여 다양하게 데이터를 분석할 수 있게 함
-
조합할 수 있는 모든 경우의 수가 모두 조합됨
SELECT DETPNO, JOB, SUM(SAL) FROM EMP GROUP BY CUBE(DEPTNO, JOB);
-
- ROLLUP
-
윈도우 함수(Window Function)
- 행과 행 간의 관계를 정의하기 위해서 제공되는 함수
- 순위, 합계, 평균, 행 위치 등을 조작할 수 있음
- 윈도우 함수 구조
SELECT WINDOW_FUNCTION(ARGUMENTS) OVER (PARTITION BY 칼럼 ORDER BY WINDOWING절) FROM 테이블명;- ARGUMENTS(인수): 윈도우 함수에 따라서 0~N개의 인수를 설정함
- PARTITION BY: 전체 집합을 기준에 의해 소그룹으로 나눔
- ORDER BY: 어떤 항목에 대해서 정렬함
- WINDOWING: 행 기준의 범위를 정함, ROWS는 물리적 결과의 행 수이고 RANGE는 논리적인 값에 의한 범위
- ROWS: 부분집합인 윈도우 크기를 물리적 단위로 해으이 집합을 지정함
- RANGE: 논리적인 주소에 의해 행 집합을 지정함
- BETWEEN~AND: 윈도우의 시작과 끝의 위치를 지정함
- UNBOUNDED PRECEDING: 윈도우의 시작 위치가 첫 번째 행임을 의미함
- UNBOUNDED FOLLOWING: 윈도우 마지막 위치가 마지막 행임을 의미함
- CURRENT ROW: 윈도우 시작 위치가 현재 행임을 의미함
- 순위 함수(RANK function)
-
윈도우 함수는 특정 항목과 파티션에 대해서 순위를 계산할 수 있는 함수를 제공함
1) RANK: 특정항목 및 파티션에 대해 순위를 계산함, 동일한 순위는 동일한 값이 부여됨
2) DENSE_RANK: 동일한 순위를 하나의 건수로 계산함, 2등이 2명이어도 3등이 있음
3) ROW_NUMBER: 동일한 순위에 대해서 고유의 순위를 부여함
SELECT ENAME, SAL, RANK() OVER (ORDER BY SAL DESC) ALL RANK, RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK FROM EMP; -
RANK함수는 순위를 계산하며 동일한 순위에는 같은 순위가 부여됨
-
- 집계함수(RANK function)
-
윈도우 함수를 제공함
1) SUM: 파티션 별로 합계를 계산함
2) AVG: 파티션 별로 평균을 계산함
3) COUNT: 파티션 별로 행 수를 계산함
4) MAX와 MIN: 파티션 별로 최댓값과 최솟값을 계산함
SELECT ENAME, SAL, SUM(SAL) OVER (PARTITION BY MGR) SUM MGR FROM EMP;- 같은 관리자(MGR)에 파티션을 만들고 합계(SUM)를 계산함 / 같은 관리자의 급여합계를 보여줌
-
- 행 순서 관련 함수
-
행 순서 관련 함수는 상위 행의 값을 하위에 출력하거나 하위 행의 값을 상위 행에 출력할 수 있음
-
특정 위치의 행을 출력할 수 있음
1) FIRST_VALUE: 파티션에서 가장 처음에 나오는 값을 구함, MIN 함수를 사용해서 같은 결과를 구할 수 있음
SELECT DEPTNO, ENAME, SAL, FIRST VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS UNBOUNDED PRECEDING) AS DEPT_A FROM EMP;2) LAST_VALUE: 파티션에서 가장 나중에 나오는 값을 구함, MAX 함수를 사용해서 같은 결과를 구할 수 있음
SELECT DEPTNO, ENAME, SAL LAST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS DEPT A FROM EMP;3) LAG: 이전 행을 가지고 옴
SELECT DEPTNO, ENAME, SAL, LAG(SAL) OVER(ORDER BY SAL DESC) AS PRE_SAL FROM EMP;4) LEAD: 윈도우에서 특정 위치의 행을 가지고 옴, 기본값은 1
SELECT DEPTNO, ENAME, SAL, LEAD(SAL,2) OVER(ORDER BY SAL DESC) AS PRE_SAL FROM EMP; -
비율 관련 함수: 누적 백분율, 순서별 백분율, 파티션을 N분으로 분할한 결과 등 조회 가능
1) CUME_DIST: 파티션 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율을 조회, 누적 분포상에 위치를 0~1사이의 값을 가짐
2) PERCENT_RANK: 파티션에서 제일 먼저 나온 것을 0으로 제일 늦게 나온 것을 1로 하여 값이 아닌 행의 순서별 백분율을 조회
SELECT DEPTNO, ENAME, SAL, PERCENT_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) AS PERCENT SAL3) NTILE: 파티션별로 전체 건수를 ARGUMENT 값으로 N등분한 결과를 조회
SELECT DEPTNO, ENAME, SAL, NTILE(4) OVER(ORDER BY SAL DESC) AS N_TILE FROM EMP;4) RATIO_TO_REPORT: 파티션 내에 전체 SUM(칼럼)에 대한 행 별 칼럼값의 백분율을 소수점까지 조회
-
-
테이블 파티션
-
Partition 기능
- 파티션은 대용량의 테이블을 여러 개의 데이터 파일에 분리해서 저장함
- 테이블의 데이터가 물리적으로 분리된 데이터 파일에 저장되면 입력, 수정, 삭제, 조회 성능이 향상됨
- 파티션은 각각의 파티션 별로 독립적으로 관리될 수 있음, 파티션 별로 백업하고 복구가 가능하면 파티션 전용 인덱스 생성도 가능함
- 파티션은 Oracle DB의 논리적 관리 단위인 테이블 스페이스 간 이동이 가능함
- 데이터를 조회할 때 데이터의 범위를 줄여서 성능을 향상시킴
-
Range Partition
- Range Partition은 테이블의 칼럼 중에서 값의 범위를 기준으로 여러 개의 파티션으로 데이터를 나누어 저장하는 것
- ex) SAL 값 2000~4000은 File1, 5000~7000은 File2에 저장
-
List Partition
- List Partiotion은 특정 값을 기준으로 분할하는 방법
- ex) DEPTNO가 10인 것은 File1, 20인것은 File2에 저장
-
Hash Partition
- Hash Partition은 데이터베이스 관리 시스템이 내부적으로 해시 함수를 사용해서 데이터를 분할함
- DBMS가 알아서 분할하고 관리하는 것
- Composite partition: 여러 개의 파티션 기법을 조합해서 사용하는 것
-
파티션 인덱스
1) Global Index: 여러 개의 파티션에서 하나의 인덱스를 사용함
2) Local Index: 해당 파티션 별로 각자의 인덱스를 사용함
3) Prefixed Index: 파티션 키와 인덱스 키가 동일함
4) Non Prefixed Index: 파티션 키와 인덱스 키가 다름
-
