250x250

Notice

Recent Posts

Recent Comments

Link

« 2024/11 »
일	월	화	수	목	금	토
					1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28	29	30

Tags more

Archives

Today

Total

관리 메뉴

개발일지

2 SQL 기본 및 활용 - 3장 SQL 활용SQL 활용 JOIN, 계층형 질의, 셀프 조인, 서브쿼리, 그룹함수, 윈도우 함수, DCL, 절차형 SQL 본문

Database/SQLD 자격증

2 SQL 기본 및 활용 - 3장 SQL 활용SQL 활용 JOIN, 계층형 질의, 셀프 조인, 서브쿼리, 그룹함수, 윈도우 함수, DCL, 절차형 SQL

wa_n 2023. 7. 13. 12:35

728x90

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');

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;

뷰(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 VIEW
`DROP 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;

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는 현재 행의 앞뒤 건수를 말하는 것임)

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

[데이터 전문가 포럼] SQL 개발자 스터디 교재

https://yurimac.tistory.com/40

https://gent.tistory.com/289