✨ 분석함수 종류를 살펴보고 활용하자.
✨ join 또는 subquery를 살펴보고 구현해보자.
분석함수
분석함수를 사용하는 이유
: RDB상에서 컬럼과 컬럼연산, 비교, 연결은 쉬운반면 행과 행간의 관계를 정의하거나, 비교, 연산하는 것을 하나의 SQL로 처리하기 힘든점
프로그램 작성, 인라인 뷰를 이용해서 복잡한 SQL문 작성
분석함수를 도입해서 행간의 연산을 원활하게 연동한다. 단 중첩해서 사용하지 못하지만 서브 쿼리에서 사용할 수 있다.
[형식]
SELECT Analytic_Function(arguments) OVER
([PARTITION BY 절] [ORDER BY 절] [Windowing 절] 중 택1)
FROM 테이블명;
✔ Analytic_Fungtion( )
: avg, count, lag, lead, max, min, rank, ratio_to_report, row_number, sum 등
arguments 0~3 개 까지만 올 수 있다.
count(*)만 허용 *, DISTINCT 는 해당 집계 함수가 허용할 때만 지원해준다.
✔ PARTITION BY
: 쿼리 결과를 <expr_list> 별로 그룹핑한다.
생략시에는 하나의 그룹으로 리턴.
✔ ORDER BY
: ORDER BY <expr_list> [ ASC | DESC | NULLS FIRST | NULLS LAST ]
표현식에서는 별칭이나 숫자를 사용할 수 없다.
✔ Windowing
: ROWS는 물리적인 단위 (ROW위치) 고, RANGE 는 논리적인단위 (ROW의 값) 이다.
ROWS [RANGE] BETWEEN start_point AND end_point
→ start_point : 그룹별 시작점을 의미
UNBOUNDED PRECEDING, CURRENT ROW
value_expr PRECEDING or value_expr FOLLOWING 이 올 수 있다.
→ end_point : 그룹별 시작점을 의미
UNBOUNDED FOLLOWING, CURRENT ROW
value_expr PRECEDING or value_expr FOLLOWING 이 올 수 있다.
✔ OVER
: 해당 함수가 쿼리 결과 집합에 따라서 적용되는 지시어 이다.
FROM, WHERE, GROUP BY, HAVING 이후에 계산된다.
SELECT, ORDER BY 구문 뒤에 사용할 수 있다.
분석 함수 장점
✔ JOIN 이나 프로그램의 OVER HEAD를 줄임
: QUERY SPEED의 향상된 SELF-JOIN, 절차적 로직으로 표현하는 것을
NATIVE SQL 에서 바로 적용 할 수 있도록 JOIN 이나 프로그램의 OVER HEAD를 줄임
✔ 간결한 SQL로 복잡한 분석 작업을 수행이 가능
: 유지보수가 간편하고 생산성 향상
✔ 이해 및 활용이 용이
: 기존 SQL - syntax를 그래도 쓰기 때문에 ANSI SQL로 채택된다.
Ranking Family 특징
대상 집합에 대해서 특정 칼럼들을 기준으로 순위나 등급을 부여한다.
오름차순, 내림차순 가능
NULL은 순위의 가장 처음 또는 마지막으로 강제 처리된다.
RANK function 은 각 PARTITION 마다 초기화가 된다.
순위 또는 등급은 GROUP BY, CUBE, ROLLUP 절 마다 초기화가 된다.
Q1)
□ RANK ( ) : 각 ROW 마다 순위를 매긴다.
PARTITION → ORDER BY → 1부터 시작하여 동일한 값은 동일한 순위를 가지고,
동일한 순위의 수만큼 다음 순위는 건너 뛴다.
# 사원테이블에서 사원의 이름, 부서번호, 급여, 급여가 많은 사원으로 부터 순위를 조회하자.
SELECT ENAME, DEPTNO, SAL,
RANK() OVER(ORDER BY SAL DESC) "RANK"
FROM EMP;
SQL> SELECT ENAME, DEPTNO, SAL,
2 RANK() OVER(ORDER BY SAL DESC) "RANK"
3 FROM EMP;
ENAME DEPTNO SAL RANK
-------------------- ------ ----- -----
KING 10 5000 1
FORD 20 3000 2
JONES 20 2975 3
BLAKE 30 2850 4
CLARK 10 2450 5
ALLEN 30 1600 6
TURNER 30 1500 7
MILLER 10 1300 8
MARTIN 30 1250 9
WARD 30 1250 9
JAMES 30 950 11
ENAME DEPTNO SAL RANK
-------------------- ------ ----- -----
SMITH 20 800 12
12 rows selected.
SELECT ENAME, DEPTNO, SAL,
RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) "RANK"
FROM EMP;
SQL> SELECT ENAME, DEPTNO, SAL,
2 RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) "RANK"
3 FROM EMP;
ENAME DEPTNO SAL RANK
-------------------- ------ ----- -----
KING 10 5000 1
CLARK 10 2450 2
MILLER 10 1300 3
FORD 20 3000 1 # ← 쿼리결과를 DEPTNO 별로 그룹핑
JONES 20 2975 2
SMITH 20 800 3
BLAKE 30 2850 1
ALLEN 30 1600 2
TURNER 30 1500 3
WARD 30 1250 4
MARTIN 30 1250 4
ENAME DEPTNO SAL RANK
-------------------- ------ ----- -----
JAMES 30 950 6
12 rows selected.
SQL>
Q2)
□ 샘플 테이블 생성
: 칼럼명을 명시하면 1:1로 VALUES 값을 줘야한다.
CREATE TABLE TEST_EMP
AS
SELECT * FROM EMP;
INSERT INTO TEST_EMP (EMPNO, ENAME, SAL, DEPTNO) VALUES(111, 111, 3000, 30);
INSERT INTO TEST_EMP (EMPNO, ENAME, SAL, DEPTNO) VALUES(222, 222, 3000, 30);
INSERT INTO TEST_EMP (EMPNO, ENAME, SAL, DEPTNO) VALUES(333, 333, 3000, 20);
INSERT INTO TEST_EMP (EMPNO, ENAME, SAL, DEPTNO) VALUES(444, 444, 3000, 20);
SQL> SELECT *
2 FROM TEST_EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- -------------------- ------------------ ----- -------- ----- ----- ------
7369 SMITH CLERK 7902 80/12/17 800 20
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7566 JONES MANAGER 7839 81/04/02 2975 20
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7839 KING PRESIDENT 81/11/17 5000 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7900 JAMES CLERK 7698 81/12/03 950 30
7902 FORD ANALYST 7566 81/12/03 3000 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- -------------------- ------------------ ----- -------- ----- ----- ------
7934 MILLER CLERK 7782 82/01/23 1300 10
111 111 3000 30 #★
222 222 3000 30 #★
333 333 3000 20 #★
444 444 3000 20 #★
16 rows selected.
😎 알고가기
COMMIT; → INSERT, DELETE, UPDATE 명령을 수행 후 저장
Q3)
□ 샘플 테이블 생성 후 생성 테이블 RANK () 조회
SELECT ENAME, DEPTNO, SAL,
RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) "RANK"
FROM TEST_EMP;
SQL> SELECT ENAME, DEPTNO, SAL,
2 RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) "RANK"
3 FROM TEST_EMP;
ENAME DEPTNO SAL RANK
-------------------- ------ ----- -----
KING 10 5000 1
CLARK 10 2450 2
MILLER 10 1300 3
444 20 3000 1 # ===========
333 20 3000 1
FORD 20 3000 1
JONES 20 2975 4
SMITH 20 800 5
111 30 3000 1 # ===========
222 30 3000 1
BLAKE 30 2850 3
ENAME DEPTNO SAL RANK
-------------------- ------ ----- -----
ALLEN 30 1600 4
TURNER 30 1500 5
MARTIN 30 1250 6
WARD 30 1250 6
JAMES 30 950 8
777 1
17 rows selected.
Q4)
□ DENSE_RANK( ) : 동률순위
SELECT ENAME, DEPTNO, SAL,
DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) "RANK"
FROM TEST_EMP;
SQL> SELECT ENAME, DEPTNO, SAL,
2 DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) "RANK"
3 FROM TEST_EMP;
ENAME DEPTNO SAL RANK
-------------------- ------ ----- -----
KING 10 5000 1
CLARK 10 2450 2
MILLER 10 1300 3
444 20 3000 1 # ==== 동률순위=======
333 20 3000 1 # ==== 동률순위=======
FORD 20 3000 1 # ==== 동률순위=======
JONES 20 2975 2
SMITH 20 800 3
111 30 3000 1
222 30 3000 1
BLAKE 30 2850 2
ENAME DEPTNO SAL RANK
-------------------- ------ ----- -----
ALLEN 30 1600 3
TURNER 30 1500 4
MARTIN 30 1250 5
WARD 30 1250 5
JAMES 30 950 6
777 1
17 rows selected.
SQL>
Q5)
□ CUME_DIST() : Cumulative Distribution Funtion ,
: 주어진 그룹에 대한 상대적인 누적분포도 값을 반환
- PARTITION 나누어진 블럭별로 각 row를 ORDER BY 절에 명시된 순서대로 정렬한 후
그룹별 상대적인 위치 (누적된 분산정보)를 구한다.
- 상대적인 위치는 구하고자 하는 값보다 작거나 같은 값을 가진 ROW수를
그룹 내 전체 ROW수 로 나눈것을 의미한다. 결과 값의 범위는 0보다 크고 1보다 작거나 같다.
# 20번 사원의 이름, 봉급, 누적분산 정보를 조회하자.
SELECT ENAME, SAL, CUME_DIST() OVER (ORDER BY SAL)
FROM TEST_EMP
WHERE DEPTNO = 20;
SQL> SELECT ENAME, SAL, CUME_DIST() OVER(ORDER BY SAL)
2 FROM TEST_EMP
3 WHERE DEPTNO = 20;
ENAME SAL CUME_DIST()OVER(ORDERBYSAL)
-------------------- ---------- ---------------------------
SMITH 800 .2
JONES 2975 .4
333 3000 1
444 3000 1
FORD 3000 1
# TEST_EMP 테이블에 있는 사원의 이름, 봉급, 누적분산 정보 조회
SQL> SELECT ENAME, SAL, CUME_DIST() OVER(ORDER BY SAL)
2 FROM TEST_EMP;
ENAME SAL CUME_DIST()OVER(ORDERBYSAL)
-------------------- ----- ---------------------------
SMITH 800 .0588
JAMES 950 .1176
WARD 1250 .2353
MARTIN 1250 .2353
MILLER 1300 .2941
TURNER 1500 .3529
ALLEN 1600 .4118
CLARK 2450 .4706
BLAKE 2850 .5294
JONES 2975 .5882
FORD 3000 .8824
ENAME SAL CUME_DIST()OVER(ORDERBYSAL)
-------------------- ----- ---------------------------
444 3000 .8824
111 3000 .8824
222 3000 .8824
333 3000 .8824
KING 5000 .9412
777 1
17 rows selected.
Q6)
□ NTILE()
: 정확하게 떨어지지 않을 경우 근사치로 배분한 후 남는 값에 대해서 최초 PARTITION 부터 한개씩 배분한다.
📎 PARTITION 내에 100개의 ROW 가 있다.
4개의 BUCKET 값을 나누고 싶다. → NTILE(4)
👉 1개의 BUCKET 당 25개씩 ROW가 배정된다.
📎 PARTITION 내에 103개의 ROW 가 있다.
5개의 BUCKET 값을 나누고 싶다 → NTILE(5)
정확하게 떨어지지 않는 경우로, 근사치 배분 후 남는 값은 최초 PARTITION 으로 부터 한개 씩 배분 한다.
👉 1 (21), 2(21), 3(21), 4(20), 5(2)
# 사원의 봉급을 기준으로 4등급으로 분류하자.
SELECT ENAME, SAL, NTILE(4) OVER(ORDER BY SAL)
FROM EMP;
SQL> SELECT ENAME, SAL, NTILE(4) OVER(ORDER BY SAL)
2 FROM EMP;
ENAME SAL NTILE(4)OVER(ORDERBYSAL)
-------------------- ----- ------------------------
SMITH 800 1
JAMES 950 1
WARD 1250 1
MARTIN 1250 2
MILLER 1300 2
TURNER 1500 2
ALLEN 1600 3
CLARK 2450 3
BLAKE 2850 3
JONES 2975 4
FORD 3000 4
ENAME SAL NTILE(4)OVER(ORDERBYSAL)
-------------------- ----- ------------------------
KING 5000 4
12 rows selected.
Q7)
□ ROW_NUMBER()
: PARTITION 내에 ORDER BY 절에 의해 정렬된 순서로 유일한 값을 리턴한다.
ROWNUM과는 전혀 상관없다.
# 사원번호, 이름, 봉급, 입사일을 조회하는데 순번을 매기자 (ROW_NUMBER())
급여가 많은 순으로 같은 급여를 받는 경우 입사일이 빠른 사람부터 순번을 부여하자.
SELECT EMPNO, ENAME, SAL, HIREDATE,
ROW_NUMBER() OVER (ORDER BY SAL DESC, HIREDATE ASC) AS 순번
FROM TEST_EMP;
SQL> SELECT EMPNO, ENAME, SAL, HIREDATE,
2 ROW_NUMBER() OVER (ORDER BY SAL DESC, HIREDATE ASC) AS 순번
3 FROM TEST_EMP;
EMPNO ENAME SAL HIREDATE 순번
----- -------------------- ----- -------- -----
7839 KING 5000 81/11/17 1
7902 FORD 3000 81/12/03 2
333 333 3000 3
444 444 3000 4
222 222 3000 5
111 111 3000 6
7566 JONES 2975 81/04/02 7
7698 BLAKE 2850 81/05/01 8
7782 CLARK 2450 81/06/09 9
7499 ALLEN 1600 81/02/20 10
7844 TURNER 1500 81/09/08 11
EMPNO ENAME SAL HIREDATE 순번
----- -------------------- ----- -------- -----
7934 MILLER 1300 82/01/23 12
7521 WARD 1250 81/02/22 13
7654 MARTIN 1250 81/09/28 14
7900 JAMES 950 81/12/03 15
7369 SMITH 800 80/12/17 16
16 rows selected.
Q8)
□ Windowing ()
: 윈도우 집계함수 (sum, min, max, min) 는 ROW에 대한 집계 함수
□ REPORTING
: 한 집합 레벨에 대한 집계 값과 다른 집합 레벨에 대한 집계 값의 비교를 통해 분석하고자 하는 경우
ex) 사원의 급여와 해당 부서의 평균 급여를 비교할때, 사원의 급여를 제외한 부서의 평균 급여를 알고 싶다.
# 사원의 이름, 부서번호, 급여, 전체급여 합계, 부서별 합계를 리턴하자.
SELECT ENAME, DEPTNO, SAL,
SUM(SAL) OVER() "TOTAL_SUM",
SUM(SAL) OVER(PARTITION BY DEPTNO) "DEPT_SUM"
FROM TEST_EMP;
SQL> SELECT ENAME, DEPTNO, SAL,
2 SUM(SAL) OVER() "TOTAL_SUM",
3 SUM(SAL) OVER(PARTITION BY DEPTNO) "DEPT_SUM"
4 FROM TEST_EMP;
ENAME DEPTNO SAL TOTAL_SUM DEPT_SUM
-------------------- ------ ----- --------- --------
CLARK 10 2450 36925 8750
MILLER 10 1300 36925 8750
KING 10 5000 36925 8750
333 20 3000 36925 12775
SMITH 20 800 36925 12775
FORD 20 3000 36925 12775
444 20 3000 36925 12775
JONES 20 2975 36925 12775
222 30 3000 36925 15400
111 30 3000 36925 15400
JAMES 30 950 36925 15400
ENAME DEPTNO SAL TOTAL_SUM DEPT_SUM
-------------------- ------ ----- --------- --------
TURNER 30 1500 36925 15400
BLAKE 30 2850 36925 15400
MARTIN 30 1250 36925 15400
WARD 30 1250 36925 15400
ALLEN 30 1600 36925 15400
777 36925
17 rows selected.
Q9)
# 사원의 이름, 부서번호, 급여, 업무별 급여평균, 해당업무의 최대급여를 조회한다.
SELECT ENAME, DEPTNO, SAL,
AVG(SAL) OVER (PARTITION BY JOB) "AVG_SUM",
MAX(SAL) OVER (PARTITION BY JOB) "MAX_SUM"
FROM TEST_EMP;
SQL> SELECT ENAME, DEPTNO, SAL,
2 AVG(SAL) OVER (PARTITION BY JOB) "AVG_SUM",
3 MAX(SAL) OVER (PARTITION BY JOB) "MAX_SUM"
4 FROM TEST_EMP;
ENAME DEPTNO SAL AVG_SUM MAX_SUM
-------------------- ------ ----- ------- -------
FORD 20 3000 3000 3000
JAMES 30 950 1017 1300
SMITH 20 800 1017 1300
MILLER 10 1300 1017 1300
JONES 20 2975 2758 2975
BLAKE 30 2850 2758 2975
CLARK 10 2450 2758 2975
KING 10 5000 5000 5000
TURNER 30 1500 1400 1600
MARTIN 30 1250 1400 1600
WARD 30 1250 1400 1600
ENAME DEPTNO SAL AVG_SUM MAX_SUM
-------------------- ------ ----- ------- -------
ALLEN 30 1600 1400 1600
111 30 3000 3000 3000
222 30 3000 3000 3000
333 20 3000 3000 3000
444 20 3000 3000 3000
777 3000 3000
17 rows selected.
Q10)
: ROWS BETWEEN start_point AND end_point
→ start_point : 그룹별 시작점을 의미
UNBOUNDED PRECEDING, CURRENT ROW
value_expr PRECEDING or value_expr FOLLOWING 이 올 수 있다.
→ end_point : 그룹별 시작점을 의미
UNBOUNDED FOLLOWING, CURRENT ROW
value_expr PRECEDING or value_expr FOLLOWING 이 올 수 있다.
# 사원이름, 부서번호, 봉급합계를 3줄씩 더한결과, 누적합계를 구해보자.
SELECT ENAME, DEPTNO, SAL,
SUM(SAL) OVER (ORDER BY SAL ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) "SUM1",
SUM(SAL) OVER (ORDER BY SAL ROWS UNBOUNDED PRECEDING) "SUM 12"
FROM TEST_EMP;
SQL> SELECT ENAME, DEPTNO, SAL,
2 SUM(SAL) OVER(ORDER BY SAL ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) "SUM1",
3 SUM(SAL) OVER(ORDER BY SAL ROWS UNBOUNDED PRECEDING) "SUM12"
4 FROM TEST_EMP;
ENAME DEPTNO SAL SUM1 SUM12
-------------------- ------ ----- ----- -----
SMITH 20 800 1750 800
JAMES 30 950 3000 1750
WARD 30 1250 3450 3000
MARTIN 30 1250 3800 4250
MILLER 10 1300 4050 5550
TURNER 30 1500 4400 7050
ALLEN 30 1600 5550 8650
CLARK 10 2450 6900 11100
BLAKE 30 2850 8275 13950
JONES 20 2975 8825 16925
FORD 20 3000 8975 19925
ENAME DEPTNO SAL SUM1 SUM12
-------------------- ------ ----- ----- -----
444 20 3000 9000 22925
111 30 3000 9000 25925
222 30 3000 9000 28925
333 20 3000 11000 31925
KING 10 5000 8000 36925
777 5000 36925
17 rows selected.
Q11)
□ RATIO_TO_REPORT()
: 해당 구간에서 차지하는 비율을 리턴하는 함수
# 사원의 총 월급을 50000으로 증가했을 때,
기존 월급 비율로 적용했을 경우 각 사원은 얼마씩 받게되는지 확인해보자.
SELECT ENAME, SAL,
RATIO_TO_REPORT(SAL) OVER() AS "비율",
TRUNC(RATIO_TO_REPORT(SAL) OVER() * 50000) AS "받을 급여"
FROM TEST_EMP;
SQL> SELECT ENAME, SAL,
2 RATIO_TO_REPORT(SAL) OVER() AS "비율",
3 TRUNC (RATIO_TO_REPORT(SAL) OVER() *50000) AS "받을 급여"
4 FROM TEST_EMP;
ENAME SAL 비율 받을 급여
-------------------- ----- ----- ---------
SMITH 800 .0217 1083
ALLEN 1600 .0433 2166
WARD 1250 .0339 1692
JONES 2975 .0806 4028
MARTIN 1250 .0339 1692
BLAKE 2850 .0772 3859
CLARK 2450 .0664 3317
KING 5000 .1354 6770
TURNER 1500 .0406 2031
JAMES 950 .0257 1286
FORD 3000 .0812 4062
ENAME SAL 비율 받을 급여
-------------------- ----- ----- ---------
MILLER 1300 .0352 1760
111 3000 .0812 4062
222 3000 .0812 4062
333 3000 .0812 4062
444 3000 .0812 4062
777
17 rows selected.
LEAD/LAG FAMILY
: 특정 ROW가 속한 파티션 내에서 상대적 상/하 위치에 있는 특정 ROW의 컬럼값을 참조하거나
상호 비교할 때 사용하는 함수.
Q12)
□ LAG : 이전 로우의 값 반환
: 파티션 내에서 OFFSET 에 지정된 값 (1) 만큼 상대적으로 상황에 위치한 ROW
(오름차순 기준시 정렬 값 보다 작은 값을 갖는 로우, 내림차순 기준시 로우의 정렬값 보다 큰값을 갖는 로우) 를
참조하기 위해 사용된다.
# 사원이름, 부서번호, 봉급, 본인 이전의 봉급 값을 조회
SELECT ENAME, DEPTNO, SAL,
LAG(SAL,1,0) OVER (ORDER BY SAL) AS NET_SAL,
LAG(SAL,1,SAL) OVER (ORDER BY SAL) AS SAL2,
LAG(SAL,1,SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL) AS SAL3
FROM TEST_EMP;
# LAG 함수의 인자값중 2번째 값을 1로 할 경우 1번째 이전값 리턴
# LAG 함수의 인자값중 3번째 값을 0으로 할 경우 이전값이 없을 경우 0을 리턴
SQL> SELECT ENAME, DEPTNO, SAL,
2 LAG(SAL,1,0) OVER(ORDER BY SAL) AS NET_SAL,
3 LAG(SAL,1,SAL) OVER(ORDER BY SAL) AS SAL2,
4 LAG(SAL,1,SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL) AS SAL3
5 FROM TEST_EMP;
ENAME DEPTNO SAL NET_SAL SAL2 SAL3
-------------------- ------ ----- ------- ----- -----
SMITH 20 800 0 800 800
JAMES 30 950 800 800 950
MARTIN 30 1250 950 950 1250
WARD 30 1250 1250 1250 950
MILLER 10 1300 1250 1250 1300
TURNER 30 1500 1300 1300 1250
ALLEN 30 1600 1500 1500 1500
CLARK 10 2450 1600 1600 1300
BLAKE 30 2850 2450 2450 1600
JONES 20 2975 2850 2850 800
444 20 3000 2975 2975 3000
ENAME DEPTNO SAL NET_SAL SAL2 SAL3
-------------------- ------ ----- ------- ----- -----
FORD 20 3000 3000 3000 2975
333 20 3000 3000 3000 3000
111 30 3000 3000 3000 2850
222 30 3000 3000 3000 3000
KING 10 5000 3000 3000 2450
777 5000 5000
17 rows selected.
Q13)
□ LEAD () : 이후 로우의 값을 리턴 하는 함수 입니다
: 파티션 내에서 OFFSET 에 지정된 값 (1) 만큼 상대적으로 상황에 위치한 로우
(오름차순 기준시 정렬 값 보다 큰 값을 갖는 로우, 내림차순 기준시 로우의 정렬값 보다 작은값을 갖는 로우)을
참조하기 위해 사용된다.
# 사원이름, 부서번호, 봉급, 본인 이후의 봉급 값을 조회 LEAD
SELECT ENAME, DEPTNO, SAL,
LEAD(SAL,1,0) OVER (ORDER BY SAL) AS NET_SAL,
LEAD(SAL,1,SAL) OVER (ORDER BY SAL) AS SAL2,
LEAD(SAL,1,SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL) AS SAL3
FROM TEST_EMP;
# LEAD 함수의 인자값중 2번째 값을 1로 할 경우 1번째 이후 값 리턴
# LAG 함수의 인자값중 3번째 값을 0으로 할 경우 이후 값이 없을 경우 0을 리턴
SQL> SELECT ENAME, DEPTNO, SAL,
2 LEAD(SAL,1,0) OVER (ORDER BY SAL) AS NET_SAL,
3 LEAD(SAL,1,SAL) OVER (ORDER BY SAL) AS SAL2,
4 LEAD(SAL,1,SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL) AS SAL3
5 FROM TEST_EMP;
ENAME DEPTNO SAL NET_SAL SAL2 SAL3
-------------------- ------ ----- ------- ----- -----
SMITH 20 800 950 950 2975
JAMES 30 950 1250 1250 1250
MARTIN 30 1250 1250 1250 1500
WARD 30 1250 1300 1300 1250
MILLER 10 1300 1500 1500 2450
TURNER 30 1500 1600 1600 1600
ALLEN 30 1600 2450 2450 2850
CLARK 10 2450 2850 2850 5000
BLAKE 30 2850 2975 2975 3000
JONES 20 2975 3000 3000 3000
444 20 3000 3000 3000 3000
ENAME DEPTNO SAL NET_SAL SAL2 SAL3
-------------------- ------ ----- ------- ----- -----
FORD 20 3000 3000 3000 3000
333 20 3000 3000 3000 3000
111 30 3000 3000 3000 3000
222 30 3000 5000 5000 3000
KING 10 5000 5000
777 0
17 rows selected.
JOIN
: 데이터 베이스에서 여러 테이블의 데이터가 필요한 경우 사용한다.
- 어떤 테이블을 기준으로 다른 테이블에있는 ROW를 찾아오는 것
- 서로 독립적인 데이터들 간의 조인을 이용해서 필요한 정보를 참조하게 된다.
- 해당열에 존재하는 공통 값, 일반적으로 기본키 및 외래키 열을 조인 조건으로 사용하여
한 테이블의 행을 다른테이블의 행에 조인 할 수 있다.
- N개의 테이블을 조인하려면 최소 N-1개의 조인 조건이 필요하다.
만일 3개의 테이블을 조인하려면 최소 2개의 조인조건이 필요하다.
[형식] # 테이블 별칭 가능, 30자이내
SELECT TABLE1.COLUMN, TABLE2.COLUMN , , , ,
FROM TABLE1, TABLE2
WHERE TABLE1.COLUMN = TABLE2.COLUMN;
✔ JOIN = INNER JOIN = EQUIJOIN
: 두개의 테이블 간의 컬럼 값들이 정확하게 일치하는 경우 테이터를 리턴
✔ OUTER JOIN
: 주/종관계를 만들어서 주 테이블은 전체출력, 종테이블은 TRUE만 출력 한다.
✔ SELF JOIN
: 같은 테이블에 있는 행들을 JOIN하는데 사용한다.
✔ NON-EQUIJOIN
: 두 개의 테이블 간에 컬럼 값들이 정확하게 일치하지 않는 경우 사용한다.
Q14)
□ Cartesian
: 첫번 째 테이블의 모든 행이 두번째 모든 행에 조인
ex)
A = ROW (N개)
B = ROW (M개)
→ M * N
<조건없이 JOIN>
SELECT *
FROM EMP, DEPT;
SQL> SELECT *
2 FROM EMP, DEPT;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO
----- ---------- ----------- ----- -------- ----- ----- ------ ------
DNAME LOC
----------- --------------------------
7369 SMITH CLERK 7902 80/12/17 800 20 10
ACCOUNTING NEW YORK
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 10
ACCOUNTING NEW YORK
7521 WARD SALESMAN 7698 81/02/22 1250 500 30 10
ACCOUNTING NEW YORK
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO
----- ---------- ----------- ----- -------- ----- ----- ------ ------
DNAME LOC
----------- --------------------------
7566 JONES MANAGER 7839 81/04/02 2975 20 10
ACCOUNTING NEW YORK
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30 10
ACCOUNTING NEW YORK
7698 BLAKE MANAGER 7839 81/05/01 2850 30 10
ACCOUNTING NEW YORK
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO
----- ---------- ----------- ----- -------- ----- ----- ------ ------
DNAME LOC
----------- --------------------------
7782 CLARK MANAGER 7839 81/06/09 2450 10 10
ACCOUNTING NEW YORK
7839 KING PRESIDENT 81/11/17 5000 10 10
ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30 10
ACCOUNTING NEW YORK
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO
----- ---------- ----------- ----- -------- ----- ----- ------ ------
DNAME LOC
----------- --------------------------
7900 JAMES CLERK 7698 81/12/03 950 30 10
ACCOUNTING NEW YORK
7902 FORD ANALYST 7566 81/12/03 3000 20 10
ACCOUNTING NEW YORK
7934 MILLER CLERK 7782 82/01/23 1300 10 10
ACCOUNTING NEW YORK
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO
----- ---------- ----------- ----- -------- ----- ----- ------ ------
DNAME LOC
----------- --------------------------
7369 SMITH CLERK 7902 80/12/17 800 20 20
RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 20
RESEARCH DALLAS
7521 WARD SALESMAN 7698 81/02/22 1250 500 30 20
RESEARCH DALLAS
' ' '
48 rows selected.
Q15)
□ INNER JOIN (FALSE, NULL)을 제외, 맞는값만 나옴
# INNER JOIN 을 이용하여 사원테이블의 사원번호, 이름, 부서번호, 부서명 조회해보자.
<ANSI>
SELECT EMPNO, ENAME, DEPTNO, DNAME
FROM EMP JOIN DEPT USING(DEPTNO);
SQL> SELECT EMPNO, ENAME, DEPTNO, DNAME
2 FROM EMP JOIN DEPT USING(DEPTNO);
EMPNO ENAME DEPTNO DNAME
----- ---------- ------ -----------
7839 KING 10 ACCOUNTING
7782 CLARK 10 ACCOUNTING
7934 MILLER 10 ACCOUNTING
7902 FORD 20 RESEARCH
7369 SMITH 20 RESEARCH
7566 JONES 20 RESEARCH
7900 JAMES 30 SALES
7844 TURNER 30 SALES
7654 MARTIN 30 SALES
7521 WARD 30 SALES
7499 ALLEN 30 SALES
EMPNO ENAME DEPTNO DNAME
----- ---------- ------ -----------
7698 BLAKE 30 SALES
12 rows selected.
<ORACLE>
SELECT TABLE1.COLUMN, TABLE2.COLUMN, , , ,
FROM TABLE1, TABLE2
WHERE TABLE1.COLUMN = TABLE2.COLUMN;
SELECT E.EMPNO, E.ENAME, E.DEPTNO, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;
SQL> SELECT E.EMPNO, E.ENAME, E.DEPTNO, D.DNAME
2 FROM EMP E, DEPT D
3 WHERE E.DEPTNO = D.DEPTNO;
EMPNO ENAME DEPTNO DNAME
----- ---------- ------ -----------
7839 KING 10 ACCOUNTING
7782 CLARK 10 ACCOUNTING
7934 MILLER 10 ACCOUNTING
7902 FORD 20 RESEARCH
7369 SMITH 20 RESEARCH
7566 JONES 20 RESEARCH
7900 JAMES 30 SALES
7844 TURNER 30 SALES
7654 MARTIN 30 SALES
7521 WARD 30 SALES
7499 ALLEN 30 SALES
EMPNO ENAME DEPTNO DNAME
----- ---------- ------ -----------
7698 BLAKE 30 SALES
12 rows selected.
Q16)
# TEST_EMP 테이블에 부서 배치 받지 않은 사원을 추가해서 INNER JOIN 하자.
INSERT INTO TEST_EMP(ENAME, DEPTNO) VALUES(999, NULL);
<ANSI>
SELECT EMPNO, ENAME, DEPTNO, DNAME
FROM TEST_EMP JOIN DEPT USING(DEPTNO);
SQL> INSERT INTO TEST_EMP(ENAME, DEPTNO) VALUES(999,NULL);
1 row created.
## ↑ INNER JOIN은 컬럼값들이 정확하게 일치하는 경우만 데이터 리턴한다.
## 따라서 아래와 같이 JOIN 시, 999 값은 나오지 않는다.
SQL> SELECT EMPNO, ENAME, DEPTNO, DNAME
2 FROM TEST_EMP JOIN DEPT USING(DEPTNO);
EMPNO ENAME DEPTNO DNAME
----- ---------- ------ -----------
7782 CLARK 10 ACCOUNTING
7934 MILLER 10 ACCOUNTING
7839 KING 10 ACCOUNTING
7566 JONES 20 RESEARCH
7369 SMITH 20 RESEARCH
333 333 20 RESEARCH
444 444 20 RESEARCH
7902 FORD 20 RESEARCH
7844 TURNER 30 SALES
7900 JAMES 30 SALES
222 222 30 SALES
EMPNO ENAME DEPTNO DNAME
----- ---------- ------ -----------
7499 ALLEN 30 SALES
111 111 30 SALES
7521 WARD 30 SALES
7654 MARTIN 30 SALES
7698 BLAKE 30 SALES
16 rows selected.
<ORACLE>
SELECT T.EMPNO, T.ENAME, T.DEPTNO, D.DNAME
FROM TEST_EMP T, DEPT D
WHERE T.DEPTNO = D.DEPTNO;
SQL> SELECT T.EMPNO, T.ENAME, T.DEPTNO, D.DNAME
2 FROM TEST_EMP T, DEPT D
3 WHERE T.DEPTNO = D.DEPTNO;
EMPNO ENAME DEPTNO DNAME
----- ---------- ------ -----------
7782 CLARK 10 ACCOUNTING
7934 MILLER 10 ACCOUNTING
7839 KING 10 ACCOUNTING
7566 JONES 20 RESEARCH
7369 SMITH 20 RESEARCH
333 333 20 RESEARCH
444 444 20 RESEARCH
7902 FORD 20 RESEARCH
7844 TURNER 30 SALES
7900 JAMES 30 SALES
222 222 30 SALES
EMPNO ENAME DEPTNO DNAME
----- ---------- ------ -----------
7499 ALLEN 30 SALES
111 111 30 SALES
7521 WARD 30 SALES
7654 MARTIN 30 SALES
7698 BLAKE 30 SALES
16 rows selected.
Q17)
# SALESMAN 의 사원번호, 이름, 급여, 부서명, 근무지를 리턴한다.
SELECT TABLE1.COLUMN, TABLE2.COLUMN, , , ,
FROM TABLE1, TABLE2
WHERE TABLE1.COLUMN = TABLE2.COLUMN;
<ANSI>
SELECT EMPNO, ENAME, SAL, DNAME, LOC
FROM EMP JOIN DEPT USING(DEPTNO)
WHERE JOB = 'SALESMAN';
SQL> SELECT EMPNO, ENAME, SAL, DNAME, LOC
2 FROM EMP JOIN DEPT USING(DEPTNO)
3 WHERE JOB = 'SALESMAN';
EMPNO ENAME SAL DNAME LOC
----- ---------- ----- ----------- --------------------------
7499 ALLEN 1600 SALES CHICAGO
7521 WARD 1250 SALES CHICAGO
7654 MARTIN 1250 SALES CHICAGO
7844 TURNER 1500 SALES CHICAGO
<ORACLE>
SELECT E.EMPNO, E.ENAME, E.SAL, D.DNAME, D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO AND E.JOB = 'SALESMAN';
SQL> SELECT E.EMPNO, E.ENAME, E.SAL, D.DNAME, D.LOC
2 FROM EMP E, DEPT D
3 WHERE E.DEPTNO = D.DEPTNO AND E.JOB = 'SALESMAN';
EMPNO ENAME SAL DNAME LOC
----- ---------- ----- ----------- --------------------------
7499 ALLEN 1600 SALES CHICAGO
7521 WARD 1250 SALES CHICAGO
7654 MARTIN 1250 SALES CHICAGO
7844 TURNER 1500 SALES CHICAGO
Q18 )
□ OUTER JOIN
: 주종관계를 만들어서 주 테이블은 전체출력, 종테이블은 TRUE만 출력
# EMP 테이블(종)과 DEPT 테이블(주)에서
EMP 테이블에 있는 모든 자료를 사원번호, 이름, 직업,
DEPT 테이블의 부서번호 부서명을 조회하자.
<ANSI>
SELECT EMPNO, ENAME, JOB, DEPTNO, DNAME
FROM EMP OUTER JOIN DEPT USING(DEPTNO);
SQL> SELECT EMPNO, ENAME, JOB, DEPTNO, DNAME
2 FROM EMP OUTER JOIN DEPT USING(DEPTNO);
EMPNO ENAME JOB DEPTNO DNAME
----- ---------- ----------- ------ -----------
7839 KING PRESIDENT 10 ACCOUNTING
7782 CLARK MANAGER 10 ACCOUNTING
7934 MILLER CLERK 10 ACCOUNTING
7902 FORD ANALYST 20 RESEARCH
7369 SMITH CLERK 20 RESEARCH
7566 JONES MANAGER 20 RESEARCH
7900 JAMES CLERK 30 SALES
7844 TURNER SALESMAN 30 SALES
7654 MARTIN SALESMAN 30 SALES
7521 WARD SALESMAN 30 SALES
7499 ALLEN SALESMAN 30 SALES
EMPNO ENAME JOB DEPTNO DNAME
----- ---------- ----------- ------ -----------
7698 BLAKE MANAGER 30 SALES
12 rows selected.
SELECT EMPNO, ENAME, JOB, DEPTNO, DNAME
FROM EMP LEFT OUTER JOIN DEPT USING(DEPTNO);
주 종
SQL> SELECT EMPNO, ENAME, JOB, DEPTNO, DNAME
2 FROM EMP LEFT OUTER JOIN DEPT USING(DEPTNO);
EMPNO ENAME JOB DEPTNO DNAME
----- ---------- ----------- ------ -----------
7934 MILLER CLERK 10 ACCOUNTING
7839 KING PRESIDENT 10 ACCOUNTING
7782 CLARK MANAGER 10 ACCOUNTING
7902 FORD ANALYST 20 RESEARCH
7566 JONES MANAGER 20 RESEARCH
7369 SMITH CLERK 20 RESEARCH
7900 JAMES CLERK 30 SALES
7844 TURNER SALESMAN 30 SALES
7698 BLAKE MANAGER 30 SALES
7654 MARTIN SALESMAN 30 SALES
7521 WARD SALESMAN 30 SALES
EMPNO ENAME JOB DEPTNO DNAME
----- ---------- ----------- ------ -----------
7499 ALLEN SALESMAN 30 SALES
12 rows selected.
<ORACLE>
SELECT E.EMPNO, E.ENAME, E.JOB, D.DEPTNO, D.DNAME
FROM DEPT D, EMP E
WHERE D.DEPTNO = E.DEPTNO(+);
# LEFT OUTER JOIN // 왼쪽열을 기준으로 오른쪽 열의 데이터 존재 여부 상관없이 출력
SQL> SELECT E.EMPNO, E.ENAME, E.JOB, D.DEPTNO, D.DNAME
2 FROM DEPT D, EMP E
3 WHERE D.DEPTNO = E.DEPTNO(+);
EMPNO ENAME JOB DEPTNO DNAME
----- ---------- ----------- ------ -----------
7839 KING PRESIDENT 10 ACCOUNTING
7782 CLARK MANAGER 10 ACCOUNTING
7934 MILLER CLERK 10 ACCOUNTING
7902 FORD ANALYST 20 RESEARCH
7369 SMITH CLERK 20 RESEARCH
7566 JONES MANAGER 20 RESEARCH
7900 JAMES CLERK 30 SALES
7844 TURNER SALESMAN 30 SALES
7654 MARTIN SALESMAN 30 SALES
7521 WARD SALESMAN 30 SALES
7499 ALLEN SALESMAN 30 SALES
EMPNO ENAME JOB DEPTNO DNAME
----- ---------- ----------- ------ -----------
7698 BLAKE MANAGER 30 SALES
40 OPERATIONS
13 rows selected.
❔ LEFT/RIGHT OUTER JOIN
SELECT *
FROM X LEFT OUTER JOIN Y USING(S1); - 다 조회됨
SELECT *
FROM X RIGHT OUTER JOIN Y USING(S1); - 맞는 것만 조회됨
SQL> SELECT *
2 FROM X LEFT OUTER JOIN Y USING(S1);
S1 X2 Y2
---- ---- ----
YA
YB 1 1
C
SQL> SELECT *
2 FROM X RIGHT OUTER JOIN Y USING(S1);
S1 X2 Y2
---- ---- ----
YA
YB 1 1
Q19)
□ SELF JOIN
: ANCI 에는 SELF JOIN 이란 말이 없고, ORACLE 만 SELF JOIN이라는 말이 있음
# ○○ 사원의 관리자는 ○○ 이다.
<ORACLE>
SELECT WORKER.ENAME || '사원의 관리자는' || MANAGER.ENAME || '이다' AS "관리자 정보"
FROM EMP WORKER, EMP MANAGER
WHERE WORKER.MGR = MANAGER.EMPNO
SQL> SELECT WORKER.ENAME ||'사원의 관리자는' ||MANAGER.ENAME ||'이다' AS "관리자 정보"
2 FROM EMP WORKER, EMP MANAGER
3 WHERE WORKER.MGR = MANAGER.EMPNO;
관리자 정보
--------------------------------------------------------------------------------
FORD사원의 관리자는JONES이다
JAMES사원의 관리자는BLAKE이다
TURNER사원의 관리자는BLAKE이다
WARD사원의 관리자는BLAKE이다
MARTIN사원의 관리자는BLAKE이다
ALLEN사원의 관리자는BLAKE이다
MILLER사원의 관리자는CLARK이다
CLARK사원의 관리자는KING이다
JONES사원의 관리자는KING이다
BLAKE사원의 관리자는KING이다
SMITH사원의 관리자는FORD이다
11 rows selected.
SELECT WORKER.ENAME || '사원의 관리자는' || MANAGER.ENAME || '이다' AS "관리자 정보"
FROM EMP WORKER, EMP MANAGER
WHERE WORKER.MGR = MANAGER.EMPNO(+);
SQL> SELECT WORKER.ENAME || '사원의 관리자는' || MANAGER.ENAME || '이다' AS "관리자 정보"
2 FROM EMP WORKER, EMP MANAGER
3 WHERE WORKER.MGR = MANAGER.EMPNO(+); # LEFT(MGR) 의 모든것이 나옴
관리자 정보
--------------------------------------------------------------------------------
FORD사원의 관리자는JONES이다
JAMES사원의 관리자는BLAKE이다
TURNER사원의 관리자는BLAKE이다
MARTIN사원의 관리자는BLAKE이다
WARD사원의 관리자는BLAKE이다
ALLEN사원의 관리자는BLAKE이다
MILLER사원의 관리자는CLARK이다
CLARK사원의 관리자는KING이다
BLAKE사원의 관리자는KING이다
JONES사원의 관리자는KING이다
SMITH사원의 관리자는FORD이다
관리자 정보
--------------------------------------------------------------------------------
KING사원의 관리자는이다 # LEFT(MGR) 의 모든것이 나옴
<ANSI>
SELECT W.ENAME || '사원의 관리자는' || M.ENAME || '이다' AS "관리자 정보"
FROM EMP W LEFT OUTER JOIN EMP M ON (W.MGR = M.EMPNO); (칼럼이름이 다르면 ON)
SQL> SELECT W.ENAME || '사원의 관리자는' || M.ENAME || '이다' AS "관리자 정보"
2 FROM EMP W LEFT OUTER JOIN EMP M ON (W.MGR = M.EMPNO);
# JOIN ~ ON (조인 조건식)
관리자 정보
--------------------------------------------------------------------------------
FORD사원의 관리자는JONES이다
JAMES사원의 관리자는BLAKE이다
TURNER사원의 관리자는BLAKE이다
MARTIN사원의 관리자는BLAKE이다
WARD사원의 관리자는BLAKE이다
ALLEN사원의 관리자는BLAKE이다
MILLER사원의 관리자는CLARK이다
CLARK사원의 관리자는KING이다
BLAKE사원의 관리자는KING이다
JONES사원의 관리자는KING이다
SMITH사원의 관리자는FORD이다
관리자 정보
--------------------------------------------------------------------------------
KING사원의 관리자는이다
12 rows selected.
Q20)
□ NATURAL
: 조인 대상이 되는 두 테이블에 이름과 자료형 같은 열 찾은 후 그 열을 기준으로 등가조인 함
# 사원이름, 부서번호, 부서명을 조회하자.
<오류>
SELECT ENAME, DEPTNO, DNAME
FROM EMP NATURAL JOIN DEPT USING(DEPTNO);
# ↑ NATURAL 은 칼럼명을 명시하지 않음
<실행>
SELECT E.ENAME, DEPTNO, D.DNAME
FROM EMP E NATURAL JOIN DEPT D;
SQL> SELECT ENAME, DEPTNO, DNAME
2 FROM EMP NATURAL JOIN DEPT USING(DEPTNO);
FROM EMP NATURAL JOIN DEPT USING(DEPTNO)
*
ERROR at line 2:
ORA-00933: SQL command not properly ended
# ==============================================
SQL> SELECT E.ENAME, DEPTNO, D.DNAME
2 FROM EMP E NATURAL JOIN DEPT D;
ENAME DEPTNO DNAME
---------- ------ -----------
KING 10 ACCOUNTING
CLARK 10 ACCOUNTING
MILLER 10 ACCOUNTING
FORD 20 RESEARCH
SMITH 20 RESEARCH
JONES 20 RESEARCH
JAMES 30 SALES
TURNER 30 SALES
MARTIN 30 SALES
WARD 30 SALES
ALLEN 30 SALES
ENAME DEPTNO DNAME
---------- ------ -----------
BLAKE 30 SALES
12 rows selected.
Q21)
□ CROSS JOIN : 아무런 조건없이 집합테이블을 연결 , 카테시안곱
SELECT E.ENAME, D.DNAME
FROM EMP E CROSS JOIN DEPT D;
SQL> SELECT E.ENAME, D.DNAME
2 FROM EMP E CROSS JOIN DEPT D;
ENAME DNAME
---------- -----------
SMITH ACCOUNTING
ALLEN ACCOUNTING
WARD ACCOUNTING
JONES ACCOUNTING
MARTIN ACCOUNTING
BLAKE ACCOUNTING
CLARK ACCOUNTING
KING ACCOUNTING
TURNER ACCOUNTING
JAMES ACCOUNTING
FORD ACCOUNTING
ENAME DNAME
---------- -----------
MILLER ACCOUNTING
SMITH RESEARCH
ALLEN RESEARCH
WARD RESEARCH
JONES RESEARCH
MARTIN RESEARCH
BLAKE RESEARCH
CLARK RESEARCH
KING RESEARCH
TURNER RESEARCH
JAMES RESEARCH
ENAME DNAME
---------- -----------
FORD RESEARCH
MILLER RESEARCH
SMITH SALES
ALLEN SALES
WARD SALES
JONES SALES
MARTIN SALES
BLAKE SALES
CLARK SALES
KING SALES
TURNER SALES
ENAME DNAME
---------- -----------
JAMES SALES
FORD SALES
MILLER SALES
SMITH OPERATIONS
ALLEN OPERATIONS
WARD OPERATIONS
JONES OPERATIONS
MARTIN OPERATIONS
BLAKE OPERATIONS
CLARK OPERATIONS
KING OPERATIONS
ENAME DNAME
---------- -----------
TURNER OPERATIONS
JAMES OPERATIONS
FORD OPERATIONS
MILLER OPERATIONS
48 rows selected.
Q22)
□ NON-EQUI JOIN
# 사원번호, 이름, 직업, 봉급, 봉급의 등급을 출력하자.
<ORACLE>
SELECT EMPNO, ENAME, JOB, SAL, GRADE
FROM SALGRADE, EMP
WHERE SAL BETWEEN LOSAL AND HISAL;
SQL> SELECT EMPNO, ENAME, JOB, SAL, GRADE
2 FROM SALGRADE, EMP
3 WHERE SAL BETWEEN LOSAL AND HISAL;
EMPNO ENAME JOB SAL GRADE
----- ---------- ----------- ----- -----
7369 SMITH CLERK 800 1
7900 JAMES CLERK 950 1
7521 WARD SALESMAN 1250 2
7654 MARTIN SALESMAN 1250 2
7934 MILLER CLERK 1300 2
7844 TURNER SALESMAN 1500 3
7499 ALLEN SALESMAN 1600 3
7782 CLARK MANAGER 2450 4
7698 BLAKE MANAGER 2850 4
7566 JONES MANAGER 2975 4
7902 FORD ANALYST 3000 4
EMPNO ENAME JOB SAL GRADE
----- ---------- ----------- ----- -----
7839 KING PRESIDENT 5000 5
12 rows selected.
<ANSI>
SELECT ENAME, SAL, GRADE
FROM EMP JOIN SALGRADE ON (SAL BETWEEN LOSAL AND HISAL);
SQL> SELECT ENAME, SAL, GRADE
2 FROM EMP JOIN SALGRADE ON (SAL BETWEEN LOSAL AND HISAL);
ENAME SAL GRADE
---------- ----- -----
SMITH 800 1
JAMES 950 1
WARD 1250 2
MARTIN 1250 2
MILLER 1300 2
TURNER 1500 3
ALLEN 1600 3
CLARK 2450 4
BLAKE 2850 4
JONES 2975 4
FORD 3000 4
ENAME SAL GRADE
---------- ----- -----
KING 5000 5
12 rows selected.'｜Playdata_study > SQL.Oracle' 카테고리의 다른 글
| 210526_DB4 (SQL) / INSERT, UPDATE, DELETE, MARGE (0) | 2021.05.26 |
|---|---|
| 210525 _DB3 (SQL) / 집합(SET)연산, 서브쿼리 (0) | 2021.05.25 |
| 210521 _DB3 (SQL) (0) | 2021.05.22 |
| 210518_DB2 (SQL) (0) | 2021.05.19 |
| 210517_ DB 1 (SQL) (0) | 2021.05.17 |
댓글