[SQLD (2-2)] SQL 활용 JOIN, 계층형 질의, 셀프 조인, 서브쿼리, 그룹함수, 윈도우 함수, DCL, 절차형 SQL
업데이트:
개요
이번 포스팅은 데이터 자격 검정인 SQL 개발자(SQLD) 시험을 기반으로 공부했던 자료이다.
시험을 위한 공부이긴 했지만, 정리했던 자료가 향후에 도움이 많이 될 것 같다.
1. 표준 조인(STANDARD JOIN)
일반 집합 연산자
- UNION : 합집합(중복 행을 1개로), 정렬O
- 무조건 중복 제거 후 unique한 행만 출력함
- UNION ALL : 합집합(중복 행도 표시), 정렬X
- INTERSECTION : 교집합(중복 행을 1개로)
- MINUS(difference) : 차집합(중복 행을 1개로)
- EXCEPT (SQL server)
- CROSS JOIN(product) : 곱집합
순수 관계 연산자
- SELECT → WHERE절로 구현
- PROJECT → SELECT절로 구현
- NATURAL JOIN → 여러가지 JOIN으로 구현
- DVIDE → 사용안함
INNER JOIN
- JOIN 조건에서 동일한 값이 있는 행만 반환
-
INNER JOIN 표시는 그 동안 WHERE 절에서 사용하던 JOIN 조건을 FROM 절에서 정의하겠다는 표시이므로, USING 조건절이나 ON 조건절을 필수적으로 사용해야함
#WHERE 절 JOIN 조건 SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO; #위 SQL과 아래 SQL은 같은 결과를 얻을 수 있다. #FROM 절 JOIN 조건 SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME FROM EMP INNER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO; #INNER는 JOIN의 디폴트 옵션으로 아래 SQL문과 같이 생략 가능하다. SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME FROM EMP JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;
NATURAL JOIN
- INNER JOIN의 하위 개념으로 NATURAL JOIN은 두 테이블 간의 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI(=) JOIN을 수행
- NATURAL JOIN이 명시되면, 추가로 USING 조건절, ON 조건절, WHERE 절에서 JOIN 조건을 정의 불가
- SQL Server에서는 지원 X
- INNER JOIN 과의 차이
- INNER JOIN은 테이블 순으로 컬럼 출력 ↔ NATURAL JOIN은 기준 컬럼 먼저 출력
- INNER JOIN은 기준 컬럼을 별개로 중복 출력 ↔ NATURAL JOIN은 기준 컬럼 1개만 출력
# 알아서 공통 컬럼인 DEPTNO를 인식하여 JOIN SELECT DEPTNO, EMPNO, ENAME, DNAME FROM EMP NATURAL JOIN DEPT;
USING 조건절
- 같은 이름을 가진 칼럼들 중에서 원하는 칼럼에 대해서만 선택적으로 EQUI JOIN을 할 수 있음
-
SQL Server에서는 지원 X
# DEPTNO컬럼을 이용한 JOIN SELECT * FROM DEPT JOIN DEPT_TEMP USING (DEPTNO); -
JOIN 컬럼에 대해서는 ALIAS나 테이블 이름과 같은 접두사 붙일 수 없음(DEPT.DEPTNO → DEPTNO)
#잘못된 사례: SELECT **DEPT.DEPTNO**, DEPT.DNAME, DEPT.LOC, DEPT_TEMP.DNAME, DEPT_TEMP.LOC FROM DEPT JOIN DEPT_TEMP USING (DEPTNO); #ERROR: #[USING 절의 열 부분은 식별자를 가질 수 없음] #바른 사례: SELECT **DEPTNO**, DEPT.DNAME, DEPT.LOC, DEPT_TEMP.DNAME,DEPT_TEMP.LOC FROM DEPT JOIN DEPT_TEMP USING (DEPTNO);
ON 조건절
- JOIN 서술부(ON 조건절)와 비 JOIN 서술부(WHERE 조건절)를 분리하여 이해가 쉬우며, 칼럼명이 다르더라도 JOIN 조건을 사용할 수 있는 장점
-
ALIAS나 테이블명 반드시 사용
SELECT E.EMPNO, E.ENAME, E.DEPTNO, D.DNAME FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO);
CROSS JOIN
- 두 개의 테이블에 대한 CARTESIAN PRODUCT 또는 CROSS PRODUCT와 같은 표현으로, 결과는 양쪽 집합의 M*N 건의 데이터 조합이 발생
OUTER JOIN
- 특징
- JOIN 조건에서 동일한 값이 없는 행도 반환할 때 사용
- JOIN 조건을 FROM 절에서 정의하겠다는 표시이므로 USING 조건절이나 ON 조건절을 필수적으로 사용해야 함
- 기준이 되는 테이블이 조인 수행시 무조건 드라이빙 테이블이 됨
LEFT (OUTER) JOIN- 좌측 테이블이 기준
- 좌측 테이블을 먼저 읽고, 우측 테이블에서 JOIN 대상 데이터를 읽어옴
-
같은 값이 없는 경우 NULL 로 채움
SELECT STADIUM_NAME, STADIUM.STADIUM_ID, SEAT_COUNT, HOMETEAM_ID, TEAM_NAME FROM STADIUM LEFT JOIN TEAM ON STADIUM.HOMETEAM_ID = TEAM.TEAM_ID ORDER BY HOMETEAM_ID;
RIGHT (OUTER) JOIN- LEFT와 반대
FULL (OUTER) JOIN- 좌측, 우측 테이블의 모든 데이터를 읽어 JOIN하여 결과를 생성
-
중복 데이터는 삭제됨
SELECT * FROM DEPT FULL JOIN DEPT_TEMP ON DEPT.DEPTNO = DEPT_TEMP.DEPTNO; #위 SQL과 아래 SQL은 같은 결과를 얻을 수 있다. # UNION을 이용한 방법 SELECT L.DEPTNO, L.DNAME, L.LOC, R.DEPTNO, R.DNAME, R.LOC FROM DEPT L LEFT OUTER JOIN DEPT_TEMP R ON L.DEPTNO = R.DEPTNO UNION SELECT L.DEPTNO, L.DNAME, L.LOC, R.DEPTNO, R.DNAME, R.LOC FROM DEPT L RIGHT OUTER JOIN DEPT_TEMP R ON L.DEPTNO = R.DEPTNO;
- Oracle
(+)를 이용한 OUTER JOIN-
(+)기호의 위치의 반대쪽 테이블이 OUTER JOIN의 기준이 되는 테이블# EMP를 기준으로 LEFT OUTER JOIN SELECT A.EMPNO, A.ENAME, A.JOB, B.DNAME FROM EMP A, DEPT B WHERE A.DEPTNO = B.DEPTNO(+) # 두개 이상의 컬럼을 사용하는 예제 (대상 테이블 컬럼에 모두 +를 붙여아함) SELECT A.EMPNO, A.ENAME, A.JOB, B.DNAME FROM EMP A, DEPT B WHERE A.DEPTNO = B.DEPTNO(+) AND A.LOCNO = B.LOCNO(+) AND B.USEYN(+) = 'Y'
-
2. 집합 연산자(SET OPERATOR)
집합 연산자
- 두 개 이상의 테이블에서 조인을 사용하지 않고 연관된 데이터를 조회하는 또 다른 방법
- 2개 이상의 질의 결과를 하나의 결과로 만들어 줌
- 다음의 제약조건을 만족해야함
- SELECT 절의 칼럼 수가 동일해야함
- SELECT 절의 동일 위치에 존재하는 칼럼의 데이터 타입이 상호 호환 가능(반드시 동일한 데이터 타입일 필요는 없음)해야함
SELECT PLAYER_NAME 선수명, BACK_NO 백넘버
FROM PLAYER
WHERE TEAM_ID = 'K02'
UNION
SELECT PLAYER_NAME 선수명, BACK_NO 백넘버
FROM PLAYER
WHERE TEAM_ID = 'K07' ORDER BY 1;
-
K-리그 소속 선수 중 소속이 삼성블루윙즈팀인 선수들의 집합과 K-리그 소속 선수 중 소속이 전남드레곤즈팀인 선수들의 집합의 합집합
SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 FROM PLAYER WHERE TEAM_ID = 'K02' UNION SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 FROM PLAYER WHERE TEAM_ID = 'K07' - K-리그 소속 선수 중 포지션별 평균키에 대한 집합과 K-리그 소속 선수 중 팀별 평균 키에 대한 집합의 합집합
- SELECT 문에 문자열로 구분코드를 넣을 수 있음
- 그룹함수도 집합 연산자에서 사용이 가능함 (단순히 합치면 되기 때문에)
- 다만 Header (컬럼명)는 앞의 기준을 따라감
SELECT 'P' 구분코드, POSITION 포지션, AVG(HEIGHT) 평균키 FROM PLAYER GROUP BY POSITION UNION SELECT 'T' 구분코드, TEAM_ID 팀명, AVG(HEIGHT) 평균키 FROM PLAYER GROUP BY TEAM_ID ORDER BY 1;
3. 계층형 질의와 셀프 조인
계층형 질의(Hierarchical Query)
- 테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해서 사용
- SQL server에서의 계층형 질의문
- CTE(Common Table Expression)를 재귀호출 함으로써 계층 구조를 전개
- 앵커 멤버를 실행하여 기본 결과 집합을 만들고 이후 재귀 멤버를 지속적으로 실행
START WITH: 계층 구조 전개의 시작 위치를 지정하는 구문CONNECT BY: 다음에 전개될 자식 데이터를 지정하는 구문PRIOR- CONNECT BY절에 사용되며, 현재 읽은 칼럼을 지정
- SELECT, WHERE 절에도 상ㅇ할 수 있음
PRIOR 자식 = 부모형태를 사용하면 계층구조에서 자식 데이터에서 부모 데이터(자식 → 부모) 방향으로 전개하는 순방향 전개 (반대는 역방향 전개)
NOCYCLE: 사이클(동일한 데이터가 나타나는 것)이 발생한 이후의 데이터는 전개하지 않음ORDER SIBLINGS BY: 형제 노드(동일 LEVEL) 사이에서 정렬을 수행WHERE: 모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출(필터링)
-
예시
SELECT LEVEL, LPAD(' ', 4 * (LEVEL-1)) || 사원 사원, 관리자, CONNECT_BY_ISLEAF ISLEAF FROM 사원 START WITH 관리자 IS NULL CONNECT BY PRIOR 사원 = 관리자; [실행 결과] LEVEL 사원 관리자 ISLEAF ----- -------- ----- ------ 1 A 0 2 B A 1 2 C A 0 3 D C 1 3 E C 1
SELECT CONNECT_BY_ROOT 사원 루트사원, SYS_CONNECT_BY_PATH(사원, '/') 경로, 사원, 관리자
FROM 사원
START WITH 관리자 IS NULL
CONNECT BY PRIOR 사원 = 관리자
[실행 결과]
루트사원 경로 사원 관리자
------- ------- ---- -----
A /A A
A /A/B B A
A /A/C C A
A /A/C/D D C
A /A/C/E E C
- SQL server는 다른 질의를 써야함 (책내용 참고)
SQLD 자격검정 실전문제 87번
- TAB1 테이블
SELECT *
FROM TAB1
START WITH C2 IS NULL
CONNECT BY PRIOR C1=C2
ORDER SIBLINGS BY C3 DESC;
START WITH C2 IS NULL: C2가 NULL인 ROW부터 시작하겠다.CONNECT BY PRIOR C1=C2: 현재 값(C1)의 하위 계층(C2)을 쭉 다찾고, 다시 상위로 올라가서 하위 계층을 쭉 찾고 반복, 연결된 애들 다 가져온다는 얘기ORDER SIBLINGS BY C3 DESC;: 같은 레벨의 원소끼리만 정렬하겠다는 의미- 여기서는 아래와 같은 트리구조 이므로, 2와 3간의 정렬만 일어남
- 1→2→4
- 1→3
- 따라서 실행 결과는
셀프 조인
- 개요
- 한 테이블 내 두 칼럼이 연관관계가 있을 때 동일 테이블 사이의 조인
- FROM 절에 동일 테이블이 2번 이상 나타남
- 반드시 테이블 별칭(ALIAS)을 사용해야함
- 예시 : 자신과 상위, 차상위 관리자를 같은 줄에 표시하라 같은 경우임
SELECT E1.사원, E1.관리자, E2.관리자 차상위_관리자
FROM 사원 E1 LEFT OUTER JOIN 사원 E2
ON (E1.관리자 = E2.사원)
ORDER BY E1.사원;
[실행 결과]
사원 관리자 차상위_관리자
---- ----- ----------
A
B A
C A
D C A
E C A
4. 서브쿼리
서브쿼리
- 개요
- 하나의 SQL문안에 포함되어 있는 또 다른 SQL문
- 알려지지 않은 기준을 이용한 검색을 위해 사용
- 서브쿼리는 서브쿼리 레벨과는 상관없이 항상 메인쿼리 레벨로 결과 집합이 생성 - 사용법
- 서브쿼리를 괄호로 감싸서 사용
- 서브쿼리는 단일 행(Single Row) 또는 복수 행(Multiple Row) 비교 연산자와 함께 사용 가능
- 단일 행 비교 연산자는 서브쿼리의 결과가 반드시 1건 이하
- 복수행 비교 연산자는 서브쿼리의 결과 건수와 상관 없음
- 서브쿼리에서는 ORDER BY를 사용하지 못함 (메인쿼리의 마지막 문장에 하나만 쓸 수 있음)
서브쿼리 분류
- 단일행 서브 쿼리 : 서브쿼리 결과가 항상 1건이하 이어야 함
-
다중행 서브쿼리 : 서브쿼리의 결과가 2건 이상 반환될 수 있다면 반드시 다중 행 비교 연산자(
IN,ALL,ANY,SOME)와 함께 사용SELECT REGION_NAME 연고지명, TEAM_NAME 팀명, E_TEAM_NAME 영문팀명 FROM TEAM WHERE TEAM_ID IN (SELECT TEAM_ID FROM PLAYER WHERE PLAYER_NAME = '정현수') ORDER BY TEAM_NAME; - 다중컬럼 서브쿼리 : 서브쿼리의 결과로 여러 개의 칼럼이 반환되어 메인쿼리의 조건과 동시에 비교되는 것
- 소속팀별 키가 가장 작은 사람들의 정보를 출력하는 문제
- 팀별로 MIN값에 해당하는 사람이 여러명 출력될 수 있음
- SQL Server 에서는 지원하지 않음
SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 FROM PLAYER WHERE (TEAM_ID, HEIGHT) IN (SELECT TEAM_ID, MIN(HEIGHT) FROM PLAYER GROUP BY TEAM_ID) ORDER BY TEAM_ID, PLAYER_NAME; [실행 결과] 팀코드 선수명 포지션 백넘버 키 ----- -------- ------ ---- --- K01 마르코스 FW 44 170 K01 박정수 MF 8 170 K02 고창현 MF 8 170 K02 정준 MF 44 170 K03 김중규 MF 42 170 19개의 행이 선택되었다. - 연관 서브쿼리 : 서브쿼리 내에 메인쿼리 칼럼이 사용된 서브쿼리
-
선수 자신이 속한 팀의 평균 키보다 작은 선수들의 정보를 출력
SELECT T.TEAM_NAME 팀명, M.PLAYER_NAME 선수명, M.POSITION 포지션, M.BACK_NO 백넘버, M.HEIGHT 키 FROM PLAYER M, TEAM T WHERE M.TEAM_ID = T.TEAM_ID AND M.HEIGHT < (SELECT AVG(S.HEIGHT) FROM PLAYER S WHERE S.TEAM_ID = M.TEAM_ID AND S.HEIGHT IS NOT NULL GROUP BY S.TEAM_ID ) ORDER BY 선수명; -
EXISTS: 아무리 조건을 만족하는 건이 여러개라고 해도 1건만 찾으면 추가적인 검색을 하지 않음- 예를들면 서브쿼리에서 SCHE_DATE를 만족하는 건수가 여러개라도 한건만 반환함
- https://www.stechstar.com/user/zbxe/study_SQL/73612
SELECT STADIUM_ID ID, STADIUM_NAME 경기장명 FROM STADIUM A WHERE EXISTS (SELECT 1 FROM SCHEDULE X WHERE X.STADIUM_ID = A.STADIUM_ID AND X.SCHE_DATE BETWEEN '20120501' AND '20120502')
-
- 스칼라 서브쿼리 : SELECT 절에 서브쿼리 사용
-
컬럼 하나에 팀 평균키를 출력해줌 (단일행 서브쿼리 이어야함)
SELECT PLAYER_NAME 선수명, HEIGHT 키, (SELECT AVG(HEIGHT) FROM PLAYER X WHERE X.TEAM_ID = P.TEAM_ID) 팀평균키 FROM PLAYER P
-
-
인라인 뷰 : FROM절에서 사용되는 서브쿼리 (여기서는 ORDERBY 사용할 수 있음)
SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 FROM (SELECT PLAYER_NAME, POSITION, BACK_NO, HEIGHT FROM PLAYER WHERE HEIGHT IS NOT NULL ORDER BY HEIGHT DESC) WHERE ROWNUM <= 5; - HAVING 절에서 서브쿼리 사용하기
-
평균키가 삼성 블루윙즈팀의 평균키보다 작은 팀의 이름과 해당 팀의 평균키를 구하기
SELECT P.TEAM_ID 팀코드, T.TEAM_NAME 팀명, AVG(P.HEIGHT) 평균키 FROM PLAYER P, TEAM T WHERE P.TEAM_ID = T.TEAM_ID GROUP BY P.TEAM_ID, T.TEAM_NAME HAVING AVG(P.HEIGHT) <(SELECT AVG(HEIGHT) FROM PLAYER WHERE TEAM_ID ='K02')
-
-
UPDATE문의 SET 절에서 서브쿼리 사용하기
UPDATE TEAM A SET A.STADIUM_NAME = (SELECT X.STADIUM_NAME FROM STADIUM X WHERE X.STADIUM_ID = A.STADIUM_ID); -
INSERT문의 VALUES 절에서 서브쿼리 사용하기
INSERT INTO PLAYER(PLAYER_ID, PLAYER_NAME, TEAM_ID) VALUES((SELECT TO_CHAR(MAX(TO_NUMBER(PLAYER_ID))+1) FROM PLAYER), '홍길동', 'K06');
뷰(View)
- 테이블은 실제로 데이터를 가지고 있는 반면, 뷰(View)는 실제 데이터를 가지고 있지 않음
- 실제 데이터를 저장하고 있는 뷰를 생성하는 기능을 지원하는 DBMS도 있음
- 가상 테이블(Virtual Table)이라고도 함
-
뷰 생성 :
CREATE VIEW ~ AS ~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; -
이미 존재하는 뷰를 참조해서 생성 가능
CREATE VIEW V_PLAYER_TEAM_FILTER AS SELECT PLAYER_NAME, POSITION, BACK_NO, TEAM_NAME FROM V_PLAYER_TEAM WHERE POSITION IN ('GK', 'MF'); -
뷰 제거 :
DROP VIEWDROP VIEW V_PLAYER_TEAM; DROP VIEW V_PLAYER_TEAM_FILTER;
5. 그룹 함수(Group 함수)
ROLLUP(그룹핑 컬럼 리스트)
- 개요
- 지정된 Grouping Columns의 List는 Subtotal을 생성하기 위해 사용
- Grouping Columns의 수를 N이라고 했을 때 N+1 Level의 Subtotal이 생성
- 인수는 계층 구조이므로 인수 순서가 바뀌면 수행 결과도 바뀌게 되므로 인수의 순서에도 주의
SELECT DNAME, JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal" FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY ROLLUP (DNAME, JOB) ORDER BY DNAME, JOB ;
- 결과
- L1 - GROUP BY 수행시 생성되는 표준 집계 (9건)
- L2 - DNAME 별 모든 JOB의 SUBTOTAL (3건)
- L3 - GRAND TOTAL (마지막 행, 1건)
GROUPING : Subtotal과 GRand total의 행이 어디인지 0,1값으로 알려줌
- ROLLUP이나 CUBE에 의한 소계가 계산된 결과에는 GROUPING(EXPR) = 1 이 표시되고,
- 그 외의 결과에는 GROUPING(EXPR) = 0 이 표시
SELECT DNAME, GROUPING(DNAME), JOB, GROUPING(JOB), COUNT(*) "Total Empl", SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP (DNAME, JOB);
CUBE(그룹핑 컬럼 리스트)
- 결합 가능한 모든 값에 대하여 다차원 집계를 생성
- Grouping Columns의 순서를 바꾸어서 또 한 번의 Query를 추가 수행하므로 ROLLUP보다 연산량 많음
- 인수의 순서가 바뀌어도 결과값들은 동일함
- 2^N Level의 Subtotal을 생성 (GROUPING COLUMNS이 가질 수 있는 모든 경우의 수에 대하여 Subtotal)
SELECT
CASE GROUPING(DNAME) WHEN 1 THEN 'All Departments' ELSE DNAME END AS DNAME,
CASE GROUPING(JOB) WHEN 1 THEN 'All Jobs' ELSE JOB END AS JOB,
COUNT(*) "Total Empl",
SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY CUBE (DNAME, JOB) ;
GROUPING SETS(그룹핑 컬럼 리스트)
- 인수들에 대한 개별 집계를 구할 수 있으며, 다양한 소계 집합을 얻을 수 있음
- 부서별, JOB별 인원수와 급여합
- 각각 GROUPBY(DNAME, JOB 따로)하고 UNION ALL 한 결과와 동일함
- 결국 SETS에 포함된 인수별로 각각 groupby를 해서 합쳐준다는 것임
- 순서바뀌어도 출력 결과는 동일
SELECT DECODE(GROUPING(DNAME), 1, 'All Departments', DNAME) AS DNAME, DECODE(GROUPING(JOB), 1, 'All Jobs', JOB) AS JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal" FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY GROUPING SETS (DNAME, JOB);
**GROUPING SETS((DNAME), (JOB),(DNAME, JOB), ())의 결과는CUBE(DNAME,JOB)과 동일함**GROUPING SETS(())이거는 전체 TOTAL 집계가 나옴
6. 윈도우 함수(WINDOW 함수)
윈도우 함수
- 행과 행간의 관계를 정의하거나, 행간을 비교, 연산하는 함수
- GROUPBY의 값을 다시 컬럼 LEVEL로 붙여주는 느낌??
그룹 내 순위 함수
RANK() OVER
- 특정 항목(칼럼)에 대한 순위를 구하는 함수
- 동일한 값에 대해서는 동일한 순위를 부여
- 예제 : 사원 데이터에서 급여가 높은 순서와 JOB 별로 급여가 높은 순서를 같이 출력
PARTITION BY의 JOB은 같은 JOB 범위 내에서만 순위를 매김
SELECT JOB, ENAME, SAL, RANK() OVER (ORDER BY SAL DESC) ALL_RANK, RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK FROM EMP;
- 예제 : 사원 데이터에서 급여가 높은 순서와 JOB 별로 급여가 높은 순서를 같이 출력
DENSE_RANK
-
RANK와 함수와 흡사하나 동일한 순위를 하나의 건수로 취급함
SELECT JOB, ENAME, SAL, RANK() OVER (ORDER BY SAL DESC) RANK, DENSE_RANK() OVER (ORDER BY SAL DESC) DENSE_RANK FROM EMP;
RANK는 2등이 2명이여서 다음으로 4등을 출력DENSE_RANK는 2등이 2명이지만 1명으로 간주하고, 다음으로 3등을 출력
ROW_NUMBER
- 동일한 순위도 고유값으로 인정(2등 2명이지만 한명은 2등 한명은 3등, 랜덤)
일반 집계 함수
SUM : 파티션별 윈도우의 합
-
예제 : 사원들의 급여와 같은 매니저를 두고 있는 사원들의 SALARY 합
SELECT MGR, ENAME, SAL, SUM(SAL) OVER (PARTITION BY MGR) MGR_SUM FROM EMP;
MAX , MIN : 파티션별 최대, 최소값
AVG
- AVG 함수와 파티션별 ROWS 윈도우를 이용해 원하는 조건에 맞는 데이터의 평균값
-
예제 : EMP 테이블에서 같은 매니저를 두고 있는 사원들의 평균 SALARY를 구하는데, 조건은 같은 매니저 내에서 자기 바로 앞의 사번과 바로 뒤의 사번인 직원만을 대상으로 한다.
SELECT MGR, ENAME, HIREDATE, SAL, ROUND (AVG(SAL) OVER (PARTITION BY MGR ORDER BY HIREDATE ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)) as MGR_AVG FROM EMP; #ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING : #현재 행을 기준으로 파티션 내에서 앞의 한 건, 현재 행, 뒤의 한 건을 범위로 지정한다. #(ROWS는 현재 행의 앞뒤 건수를 말하는 것임)
- ALLEN은 파티션 내의 첫번째 데이터라서 앞의 1건이 없음 : (1600+1250)/2=1425
COUNT
- 파티션별 조건에 맞는 값 카운트
-
예제 : 사원들을 급여 기준으로 정렬하고, 본인의 급여보다 50 이하가 적거나 150 이하로 많은 급여를 받는 인원수를 출력
SELECT ENAME, SAL, COUNT(*) OVER (ORDER BY SAL RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) as SIM_CNT FROM EMP; #RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING : #현재 행의 급여값을 기준으로 급여가 -50에서 +150의 범위 내에 포함된 모든 행이 대상이 된다. #(RANGE는 현재 행의 데이터 값을 기준으로 앞뒤 데이터 값의 범위를 표시하는 것임)
그룹 내 행 순서 함수
FIRST_VALUE (MIN 으로도 가능)
- 파티션별 윈도우에서 가장 먼저 나온 값을 출력
-
예제 : 부서별 직원들을 연봉이 높은 순서부터 정렬하고, 파티션 내에서 가장 먼저 나온 값 출력
SELECT DEPTNO, ENAME, SAL, FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS UNBOUNDED PRECEDING) as DEPT_RICH FROM EMP; #RANGE UNBOUNDED PRECEDING : #현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위를 지정한다.
LAST_VALUE (MAX 로도 가능)
- 파티션별 윈도우에서 가장 나중에 나온 값 출력
LAG(가져올 컬럼, 몇번째 앞의 행을 가져올지, 데이터가 없어 NULL대신 입력할 값)
- 파티션별 윈도우에서 이전 몇 번째 행의 값을 출력
-
몇번째 앞의 행을 가져올지 : 명시 안되어 있으면 DEFAULT는 1
SELECT ENAME, HIREDATE, SAL, LAG(SAL, 2, 0) OVER (ORDER BY HIREDATE) as PREV_SAL FROM EMP WHERE JOB = 'SALESMAN' #LAG(SAL, 2, 0)의 기능은 두 행 앞의 SALARY를 가져오고, 가져올 값이 없는 경우는 0으로 처리한다
LEAD(가져올 컬럼, 몇번째 뒤의 행을 가져올지, 데이터가 없어 NULL대신 입력할 값)
- 파티션별 윈도우에서 이후 몇 번째 행의 값을 출력
그룹 내 비율 함수
RATIO_TO_REPORT
- 파티션 내 전체 SUM(칼럼)값에 대한 행별 칼럼 값의 백분율을 소수점으로 구할 수 있음
- 결과 값은 >0 &<= 1 의 범위
PERCENT_RANK
- 파티션별 윈도우에서 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 하여, 값이 아닌 행의 순서별 백분율을 구함
- 결과 값은 >0 &<= 1 의 범위
CUME_DIST
- 파티션별 윈도우의 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율을 구함
- 결과 값은 >0 &<= 1 의 범위
- 동일값으면 뒤의 결과를 따름 : 0.2, 0.4, 0.6, 0.8, 1 일때, 앞의 두개가 값이 같으면 둘다 0.4임
NTILE
- 파티션별 전체 건수를 ARGUMENT 값으로 N 등분한 결과를 구함
NTILE(4): 14명의 팀원을 4개 조로 나눈다는 의미- 전체 14명을 4개의 집합으로 나누면 몫이 3명, 나머지가 2명
- 나머지 두 명은 앞의 조부터 할당
- 즉, 4명 + 4명 + 3명 + 3명으로 조를 나눔
7. DCL (Data Control Language)
DCL : 유저를 생성하고 권한을 제어할 수 있는 명령어
Oracle과 SQL Server의 사용자 아키텍처 차이
- Oracle
- 유저를 통해 데이터베이스에 접속을 하는 형태
- 아이디와 비밀번호 방식으로 인스턴스에 접속하고 그에 해당하는 스키마에 오브젝트 생성 등의 권한을 부여받음
- SQL Server
- 인스턴스에 접속하기 위해 로그인이라는 것을 생성
- 인스턴스 내에 존재하는 다수의 데이터베이스에 연결하여 작업하기 위해 유저를 생성한 후 로그인과 유저를 매핑해 주어야 함
- 로그인 방식 2가지 : Windows 인증 방식, 혼합 모드(Windows 인증 또는 SQL 인증) 방식
시스템 권한
GRANT 명령어 [ON object] TO user [WITH GRANT OPTION]: 권한 부여- ON object를 통해 다른 유저의 오브젝트에 접근하도록 함
- ex)
GRANT SELECT, UPDATE ON A_User.TB_A TO B_User WITH GRANT OPTION: 이 옵션을 사용하면 TO 절의 대상도 자신이 받은 권한을 다른 유저에게 부여할수 있음 (ROLE에 권한을 부여할때는 사용못함)
-
REVOKE 명령어 [ON object] FROM user: 권한 취소GRANT CREATE USER TO SCOTT; CONN SCOTT/TIGER # (아이디/패스워드) CREATE USER PJS IDENTIFEIED BY KOREA7; GRANT CREATE SESSION TO PJS; # 로그인하려면 또 권한 줘야함 GRANT CREATE TABLE TO PJS; REVOKE CREATE TABLE FROM PJS;
오브젝트 권한
- 모든 유저는 각각 자신이 생성한 테이블 외에 다른 유저의 테이블에 접근하려면 해당 테이블에 대한 오브젝트 권한을 소유자로부터 부여받아야 함
Role을 이용한 권한 부여
-
ROLE에 포함되어 있는 권한들이 필요한 유저에게는 해당 ROLE만을 부여함으로써 빠르고 정확하게 필요한 권한을 부여할 수 있음CONN SYSTEM/MANAGER #연결되었다. CREATE ROLE LOGIN_TABLE; #롤 생성 GRANT CREATE SESSION, CREATE TABLE TO LOGIN_TABLE; #롤에 권한을 부여 GRANT LOGIN_TABLE TO JISUNG; #롤을 유저에게 부여 -
오라클의 기본 내장 Role
-
유저 삭제 (
DROP USER,CASCADE)DROP USER JISUNG CASCADE; #사용자가 삭제되었다. #☞ JISUNG 유저가 만든 MENU 테이블도 같이 삭제되었다.
8. 절차형 SQL
절차형 SQL
- SQL문의 연속적인 실행이나 조건에 따른 분기처리를 이용하여 특정 기능을 수행하는 저장 모듈을 생성할 수 있음
- Procedure, User Defined Function,Trigger 등이 있음
저장 모듈
- PL(Procedural Language)/SQL(Oracle) 문장을 데이터베이스 서버에 저장하여 사용자와 애플리케이션 사이에서 공유할 수 있도록 만든 일종의 SQL 컴포넌트 프로그램
- 독립적으로 실행되거나 다른 프로그램으로부터 실행될 수 있는 완전한 실행 프로그램
PL/SQL 특징
- PL/SQL은 Block 구조로 되어있어 각 기능별로 모듈화가 가능하며, 트랜잭션을 분할할 수 있음
- 변수, 상수 등을 선언하여 SQL 문장 간 값을 교환하며, WHERE 절의 조건 등으로 대입할 수 있음
- Procedure 내부에 작성된 절차적 코드는 PL/SQL 엔진이 처리하고 일반적인 SQL 문장은 SQL 실행기가 처리함
- IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 함
- DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있음
- Oracle에 내장되어 있으므로 호환성이 좋음
- 응용 프로그램의 성능을 향상시킴
- 여러 SQL 문장을 Block으로 묶고 한 번에 Block 전부를 서버로 보내기 때문에 통신량을 줄일 수 있음
PL/SQL 구조
DECLARE: BEGIN ~ END 절에서 사용될 변수와 인수에 대한 정의 및 데이터 타입을 선언하는 선언부BEGIN ~ END: 개발자가 처리하고자 하는 SQL문과 여러 가지 비교문, 제어문을 이용하여 필요한 로직을 처리하는 실행부EXCEPTION: BEGIN ~ END 절에서 실행되는 SQL문이 실행될 때 에러가 발생하면 그 에러를 어떻게 처리할지 정의하는 예외 처리부
PL/SQL 기본 문법
CREATE [OR REPLACE] Procedure [Procedure_name]
( argument1 [mode] data_type1,
argument2 [mode] date_type2,
... ... )
IS [AS]
... ...
BEGIN
... ...
EXCEPTION
... ...
END;
/
- 간단히만 이해하자
T-SQL : 근본적으로 SQL Server를 제어하기 위한 언어
CREATE Procedure [schema_name.]Procedure_name
...
Procedure 생성 예제
-
SCOTT 유저가 소유하고 있는 DEPT 테이블에 새로운 부서를 등록하는 Procedure를 작성
CREATE OR REPLACE Procedure p_DEPT_insert -------------(1) ( v_DEPTNO in number, v_dname in varchar2, v_loc in varchar2, v_result out varchar2) IS cnt number := 0; BEGIN SELECT COUNT(*) INTO CNT -------------(2) FROM DEPT WHERE DEPTNO = v_DEPTNO AND ROWNUM = 1; if cnt >0 then -------------(3) v_result := '이미 등록된 부서번호이다'; else INSERT INTO DEPT (DEPTNO, DNAME, LOC) -------------(4) VALUES (v_DEPTNO, v_dname, v_loc); COMMIT; -------------(5) v_result := '입력 완료!!'; end if; EXCEPTION -------------(6) WHEN OTHERS THEN ROLLBACK; v_result := 'ERROR 발생'; END; / -
코드 설명
- ① DEPT 테이블에 들어갈 칼럼 값(부서코드, 부서명, 위치)을 입력 받는다.
- ② 입력 받은 부서코드가 존재하는지 확인한다.
- ③ 부서코드가 존재하면 ‘이미 등록된 부서번호입니다’라는 메시지를 출력 값에 넣는다.
- ④ 부서코드가 존재하지 않으면 입력받은 필드 값으로 새로운 부서 레코드를 입력한다.
- ⑤ 새로운 부서가 정상적으로 입력됐을 경우에는 COMMIT 명령어를 통해서 트랜잭션을 종료한다.
- ⑥ 에러가 발생하면 모든 트랜잭션을 취소하고 ‘ERROR 발생’라는 메시지를 출력값에 넣는다.
User Defined Function의 생성과 활용
- User Defined Function : Procedure처럼 절차형 SQL을 로직과 함께 데이터베이스 내에 저장해 놓은 명령문의 집합
Trigger의 생성과 활용
- 특정한 테이블에 INSERT, UPDATE, DELETE와 같은 DML문이 수행되었을 때, 데이터베이스에서 자동으로 동작하도록 작성된 프로그램
- 사용자가 직접 호출이 아닌 데이터베이스에서 자동적으로 수행
- 데이터베이스 보안의 적용(데이터 무결성, 일관성), 유효하지 않은 트랜잭션의 예방, 업무 규칙 자동 적용 제공 등에 사용
- Procedure와 달리 Commit 및 Rollback 과 같은 TCL을 사용할 수 없음
- 데이터 베이스에 로그인 하는 작업에도 정의할 수 있음
- 예시 : 트리거(Trigger)를 사용하여 주문한 건이 입력될 때마다, 일자별 상품별로 판매수량 과 판매금액을 집계하여 집계자료를 보관
프로시저와 트리거의 차이점
- 프로시저 : BEGIN ~ END 절 내에 COMMIT, ROLLBACK과 같은 트랜잭션 종료 명령어 사용 가능
-
트리거 : BEGIN ~ END 절 내에 사용 불가
Reference
https://yurimac.tistory.com/40
댓글남기기