Everything has an expiration date
[Oracle] 20231025 [프로그램소스] - 20231025_01_scott 본문
[Oracle]/SQL (Program source)
[Oracle] 20231025 [프로그램소스] - 20231025_01_scott
Jelly-fish 2023. 10. 25. 17:53HAVING 절 - GROUP BY 를 통해 그룹화한 컬럼에 대한 조건을 명시하는 절. GROUP BY 절이 없더라도 단독 사용 가능.
20231025_01_scott.sql
SELECT USER
FROM DUAL;
--==>> SCOTT
--■■■ HAVING ■■■--
--○ EMP 테이블에서 부서번호가 20, 30인 부서를 대상으로
-- 부서의 총 급여가 10000 보다 적을 경우만 부서별 총 급여를 조회한다.
SELECT DEPTNO, SUM(SAL)
FROM EMP
WHERE 부서번호가 20, 30
GROUP BY 부서번호;
SELECT DEPTNO, SUM(SAL)
FROM EMP
WHERE DEPTNO IN (20, 30)
GROUP BY DEPTNO;
--==>>
/*
30 9400
20 10875
*/
SELECT DEPTNO, SUM(SAL)
FROM EMP
WHERE DEPTNO IN (20, 30)
GROUP BY DEPTNO;
-- 하나에 대한 조건이 아니라, 그룹으로 묶은 것들 각각엗대한 조건이므로 WHERE에 사용할 수 없다.
-- + 그룹에 대한 집계함수에 대한 조건은 HAVING을 통해 작성하도록 한다.
-- HAVING : 그룹에 대한 조건을 명시하는 절이다.
-- WHERE 절에는 그룹 각각에 대한 조건을 명시할 수 없으므로
-- 다음과 같은 에러가 발생했다.
SELECT DEPTNO, SUM(SAL)
FROM EMP
WHERE DEPTNO IN (20, 30) -- OR 로 구성 가능.
AND SUM(SAL) < 10000
GROUP BY DEPTNO;
--==>> 에러 발생
-- (ORA-00934: group function is not allowed here)
-- HAVING 절을 통해 그룹에 대한 조건을 명시하자,
-- 에러가 나지 않고 정상적으로 실행되는 것을 관찰 할 수가 있다.
SELECT DEPTNO, SUM(SAL)
FROM EMP
WHERE DEPTNO IN (20, 30) -- OR 로 구성 가능.
GROUP BY DEPTNO
HAVING SUM(SAL) < 10000; -- 그룹에 대한 조건
-- 모든 절을 사용할 때 SELECT 문의 PARSING 순서를 항상 생각하도록 하자.
-- 구문을 작성하는 위치를 항상 준수해야만 한다.
SELECT DEPTNO, SUM(SAL)
FROM EMP
WHERE DEPTNO IN (20, 30) -- OR 로 구성 가능.
GROUP BY DEPTNO
HAVING SUM(SAL) < 10000;
-- FROM EMP 로 하고, HAVING에 추가 조건을 적용하면...
-- 첫 실행시 EMP 전체를 메모리에 퍼올리지만
-- FROM WHERE DEPTNO IN (20, 30) 으로 하게 되면
-- WHERE 절에 속한 데이터들만 메모리에 퍼올리므로...
-- 첫 번째로 필요한 메모리들만 퍼올린 후에 후속 처리를 하는 것이 바람직 하므로
-- HAVING 조건에 WHERE 조건을 추가적으로 작성하게 되면 리소스를 더 크게 소모하므로...
-- 이렇게 작성하는 것은 바람직 하지 않다.
SELECT DEPTN, SUM(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING SUM(SAL) < 10000
AND DEPTNO IN (20, 30);
SELECT *
FROM EMP;
-- HAVING 절에 추가적으로 20, 30 부서만 가져오도록 조건을 쓰는 것은 바람직하지 않다.
-- 필요한 데이터들은 20, 30 부서에 해당하는 데이터들 뿐인데
-- 첫 실행시 EMP 테이블의 데이터들을 모두 가져오기 때문이다.
SELECT DEPTNO, SUM(SAL)
FROM EMP
WHERE DEPTNO IN (20, 30)
GROUP BY DEPTNO
HAVING SUM(SAL) < 10000;
SELECT *
FROM EMP
WHERE DEPTNO IN (20, 30);
-- WHERE 절에 먼저 조건을 20, 30 부서만 가져오도록 작성하면
-- 필요한 데이터들만 먼저 메모리에 퍼올리므로 리소스 소모가 감소한다.
-- ※ 총정리 ------------------------------------------------------------------
-- HAVING 내부에 20, 30 부서만 가져오는 조건을 모두 작성하는 것이 아니라,
-- WHERE 절에 1차적으로 필요한 데이터들을 구분하는 조건을 먼저 작성한 후에
-- HAVING 절로 추가적인 SUM(SAL) < 10000 조건만을 적용시키는 것이 바람직하다.
-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--■■■ 중첩 그룹함수 / 분석함수 ■■■--
--※ 그룹 함수는 2 LEVEL 까지 중첩해서 사용할 수 있다.
SELECT SUM(SAL)
FROM EMP
GROUP BY DEPTNO;
--==>>
/*
9400
10875
8750
*/
-- SUM() 집계함수를 다시 MAX() 집계함수로 감싸서
-- 최종 최댓값 총합을 구한 것을 확인할 수 있다.
SELECT MAX(SUM(SAL)) "COL1"
FROM EMP
GROUP BY DEPTNO;
--==>> 10875
-- RANK() / DENSE_RANK()
-- ORACLE 9i 부터 적용... (MSSQL 2005 부터 적용...)
-- 이전 버전에서 RANK, 석차 구하려면 어떻게 해야하나?
--> 하위 버전에서는 RANK() 나 DENSE_RANK() 를 사용할 수 없기 때문에
-- 이 함수를 활용하지 않는 다른 방법을 찾아야 한다.
-- 예를 들어... 급여 순위를 구하고자 한다면...
-- 해당 사원의 급여보다 더 큰 값이 몇 개인지 확인한 후
-- 확인한 숫자에 『+ 1』 을 추가로 연산해 주면...
-- 그 값이 곧 해당 사원의 등수가 된다.
SELECT ENAME, SAL
FROM EMP;
-- SMITH 의 SAL : 800
-- ALLEN 의 SAL : 1600
SELECT COUNT(*) + 1
FROM EMP
WHERE SAL > 800; -- SMITH 의 급여
--==>> 14 -- SMITH 의 급여 등수
SELECT COUNT(*) + 1
FROM EMP
WHERE SAL > 1600; -- ALLEN 의 급여
--==>> 7 -- ALLEN 의 급여 등수
-- 정체성은 서브쿼리...
-- 쿼리 안의 또다른 쿼리...
-- 서브 쿼리의 일종으로
-->> ◎ [INLINE VIEW] = 서브 쿼리가 외부 쿼리의 FROM 절에 위치하는 것.
--※ 서브 상관 쿼리(상관 서브 쿼리)
-- 메인 쿼리가 있는 테이블의 컬럼이
-- 서브 쿼리의 조건절(WHERE절, HAVING절) 에 사용되는 경우
-- 우리는 이 쿼리문을 서브 상관 쿼리(상관 서브 쿼리)라고 부른다.
SELECT ENAME "사원명", SAL "급여", 1 "급여등수"
FROM EMP;
SELECT ENAME "사원명", SAL "급여", (1) "급여등수"
FROM EMP;
SELECT ENAME "사원명", SAL "급여", (SELECT COUNT(*) + 1
FROM EMP
WHERE SAL > 800) "급여등수"
FROM EMP;
SELECT E.ENAME "사원명", E.SAL "급여", (SELECT COUNT(*) + 1
FROM EMP
WHERE SAL > E.SAL) "급여등수"
FROM EMP E
ORDER BY 3;
--==>>
/*
KING 5000 1
FORD 3000 2
SCOTT 3000 2
JONES 2975 4
BLAKE 2850 5
CLARK 2450 6
ALLEN 1600 7
TURNER 1500 8
MILLER 1300 9
WARD 1250 10
MARTIN 1250 10
ADAMS 1100 12
JAMES 950 13
SMITH 800 14
*/
-- 서브 상관 쿼리 내부에서 ("급여등수")
-- SAL > 800(SMITH의 SAL 값) 이었던 조건식을
-- 800 으로 고정된 상태가 아니라, SMITH부터 ~ KING 까지 모두 조회하고자 한다면
-- SAL 이 계속 변경되어야만 한다
-- 따라서, 외부의 FROM EMP 테이블의 EMP에 E라는 ALIAS를 붙여서
-- 서브 상관 커리 내부의 EMP 와, 외부 EMP 테이블을 구분지음으로써
-- E.SAL이 하나씩 변경될 때마다 등수를 비교하여 급여 등수를 확정지을 수 있도록
-- 처리한 것이다.
-- 즉, SELECT 문 안에는 서브 쿼리가 존재할 수 있고, 이를 서브 상관 쿼리라고 하며
-- 외부 메인 쿼리의 테이블과 같은 테이블을 서브 상관 쿼리에서 사용하고 있다면
-- ALIAS 를 사용하여 테이블을 구분지음으로써 원하는 처리를 할 수 있게 되는 것이다...
--○ EMP 테이블을 대상으로
-- 사원명, 급여, 부서번호, 부서내급여등수, 전체급여등수 항목을 조회한다.
-- 단, RANK() 함수를 사용하지 않고, 서브상관쿼리를 활용할 수 있도록 한다.
SELECT COUNT(*) + 1
FROM EMP
WHERE SAL > 800;
SELECT (SELECT COUNT(*) + 1
FROM EMP
WHERE SAL > E.SAL) "전체급여등수"
FROM EMP E;
SELECT E.ENAME "사원명", E.SAL "급여", E.DEPTNO "부서번호"
, (SELECT COUNT(*) + 1
FROM EMP
WHERE SAL > E.SAL) "전체급여등수"
FROM EMP E;
SELECT DEPTNO, SUM(SAL)
FROM EMP
GROUP BY DEPTNO;
SELECT DEPTNO
, SUM(SAL) "부서별급여"
FROM EMP
GROUP BY DEPTNO;
SELECT COUNT(*) + 1
FROM EMP
WHERE SAL > 800;
SELECT E.ENAME "사원명", E.SAL "급여", E.DEPTNO "부서번호"
, (SELECT COUNT(*) + 1
FROM EMP
WHERE SAL > E.SAL) "전체급여등수"
, (SELECT COUNT(*) + 1
FROM EMP
WHERE SAL > E.SAL
AND
DEPTNO = E.DEPTNO) "부서내급여등수"
FROM EMP E
ORDER BY 부서번호 ASC, 부서내급여등수 ASC, 전체급여등수 ASC;
SELECT (
SELECT COUNT(*) + 1
FROM
(
SELECT DEPTNO
, SUM(SAL) "부서별급여"
FROM EMP
GROUP BY DEPTNO
) A
WHERE T.부서별급여 < A.부서별급여
) "부서별급여순위"
, T.DEPTNO "부서번호"
, T.부서별급여
FROM
(
SELECT DEPTNO
, SUM(SAL) "부서별급여"
FROM EMP
GROUP BY DEPTNO
) T
ORDER BY 1 ASC;
SELECT COUNT(*) + 1
FROM EMP
WHERE SAL > 800; -- SMITH 의 급여
--==>> 14 -- SMITH 의 급여 등수 (→ 전체급여등수)
SELECT COUNT(*) + 1
FROM EMP
WHERE SAL > 800 -- SMITH 의 급여
AND DEPTNO = 20; -- SMITH 의 부서번호
--==>> 5 -- SMITH 의 급여 등수(→ 부서내급여등수)
SELECT ENAME "사원명", SAL "급여", DEPTNO "부서번호"
, (1) "부서내급여등수"
, (1) "전체급여등수"
FROM EMP;
-- SMITH의 부서내 급여등수, 전체급여등수
SELECT E.ENAME "사원명", E.SAL "급여", E.DEPTNO "부서번호"
, (SELECT COUNT(*) + 1
FROM EMP
WHERE SAL > E.SAL -- SMITH 의 급여
AND DEPTNO = E.DEPTNO) "부서내급여등수"
, (SELECT COUNT(*) + 1
FROM EMP
WHERE SAL > E.SAL) "전체급여등수"
FROM EMP E;
SELECT E.ENAME "사원명", E.SAL "급여", E.DEPTNO "부서번호"
, (SELECT COUNT(*) + 1
FROM EMP
WHERE SAL > E.SAL -- SMITH 의 급여
AND DEPTNO = E.DEPTNO) "부서내급여등수"
, (SELECT COUNT(*) + 1
FROM EMP
WHERE SAL > E.SAL) "전체급여등수"
FROM EMP E
ORDER BY 3, 5;
--==>>
/*
KING 5000 10 1 1
CLARK 2450 10 2 6
MILLER 1300 10 3 9
SCOTT 3000 20 1 2
FORD 3000 20 1 2
JONES 2975 20 3 4
ADAMS 1100 20 4 12
SMITH 800 20 5 14
BLAKE 2850 30 1 5
ALLEN 1600 30 2 7
TURNER 1500 30 3 8
MARTIN 1250 30 4 10
WARD 1250 30 4 10
JAMES 950 30 6 13
*/
SELECT *
FROM EMP
ORDER BY 5;
--○ EMP 테이블을 대상으로 다음과 같이 조회될 수 있도록 쿼리문을 구성한다.
/*
- 각 부서 내에서 입사일자별로 누적된 급여의 합
--------- ------------- -------------- ---------- ----------------------------
사원명 부서번호 입사일 급여 부서내입사별급여누적
--------- ------------- -------------- ---------- ----------------------------
SMITH 20 1980-12-17 800 800
JONES 20 1981-04-02 2975 3775
FORD 20 1981-12-03 3000 6775
------------------------------------------------------------------------------
*/
SELECT E.DEPTNO "부서번호"
, E.SAL "급여"
, E.HIREDATE "고용일자"
FROM
(
SELECT SAL
FROM EMP
) T
, EMP E
WHERE E.SAL = T.SAL
ORDER BY DEPTNO ASC, HIREDATE ASC;
SELECT E.ENAME "사원명", E.SAL "급여", E.DEPTNO "부서번호"
, (SELECT COUNT(*) + 1
FROM EMP
WHERE SAL > E.SAL -- SMITH 의 급여
AND DEPTNO = E.DEPTNO) "부서내급여등수"
, (SELECT COUNT(*) + 1
FROM EMP
WHERE SAL > E.SAL) "전체급여등수"
FROM EMP E
ORDER BY 3, 5;
SELECT E.DEPTNO
, E.SAL
, E.ENAME
, E.HIREDATE
, (SELECT SUM(SAL)
FROM EMP
WHERE HIREDATE <= E.HIREDATE
AND
DEPTNO = E.DEPTNO) "부서내입사별급여누적"
FROM EMP E
ORDER BY DEPTNO ASC, HIREDATE ASC;
SELECT E.DEPTNO
, E.SAL
, E.ENAME
, E.HIREDATE
, (SELECT SUM(SAL)
FROM EMP
WHERE HIREDATE <= E.HIREDATE
AND
DEPTNO = E.DEPTNO) "부서내입사별급여누적"
FROM EMP E
ORDER BY DEPTNO ASC, HIREDATE ASC;
SELECT (SELECT CASE WHEN HIREDATE = E.HIREDATE
THEN '라라'
WHEN HIREDATE < E.HIREDATE
THEN '루루'
ELSE '하하'
END
FROM EMP)
FROM EMP E;
SELECT EMP.ENAME "사원명", EMP.DEPTNO "부서번호", EMP.HIREDATE "입사일", EMP.SAL "급여"
, (1) "부서내입사별급여누적"
FROM EMP
ORDER BY 2, 3;
SELECT E1.ENAME "사원명", E1.DEPTNO "부서번호", E1.HIREDATE "입사일", E1.SAL "급여"
, (1) "부서내입사별급여누적"
FROM EMP E1
ORDER BY 2, 3;
SELECT E1.ENAME "사원명", E1.DEPTNO "부서번호", E1.HIREDATE "입사일", E1.SAL "급여"
, (
SELECT SUM(E2.SAL)
FROM EMP E2
WHERE E2.DEPTNO = E1.DEPTNO
) "부서내입사별급여누적"
FROM EMP E1
ORDER BY 2, 3;
SELECT E1.ENAME "사원명", E1.DEPTNO "부서번호", E1.HIREDATE "입사일", E1.SAL "급여"
, (
SELECT SUM(E2.SAL)
FROM EMP E2
WHERE E2.DEPTNO = E1.DEPTNO
AND
E2.HIREDATE <= E1.HIREDATE
) "부서내입사별급여누적"
FROM EMP E1
ORDER BY 2, 3;
---==>>
/*
사원명 부서번호 입사일 급여 부서내입사별급여누적
CLARK 10 1981-06-09 2450 2450
KING 10 1981-11-17 5000 7450
MILLER 10 1982-01-23 1300 8750
SMITH 20 1980-12-17 800 800
JONES 20 1981-04-02 2975 3775
FORD 20 1981-12-03 3000 6775
SCOTT 20 1987-07-13 3000 10875
ADAMS 20 1987-07-13 1100 10875
ALLEN 30 1981-02-20 1600 1600
WARD 30 1981-02-22 1250 2850
BLAKE 30 1981-05-01 2850 5700
TURNER 30 1981-09-08 1500 7200
MARTIN 30 1981-09-28 1250 8450
JAMES 30 1981-12-03 950 9400
*/
--○ EMP 테이블을 대상으로
-- 입사한 사원의 수가 가장 많았을 때의
-- 입사년월과 인원수를 조회할 수 있도록 쿼리문을 구성한다.
SELECT TO_CHAR(HIREDATE, 'YYYY-MM') "입사년월"
, COUNT(*) "인원수"
FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM')
HAVING COUNT(*)
IN
( SELECT MAX(T.인원수)
FROM
(
SELECT TO_CHAR(HIREDATE, 'YYYY-MM') "입사년월"
, COUNT(*) "인원수"
FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM')
) T
);
-- 최대 인원수 구하기
SELECT MAX(T.인원수)
FROM
(
SELECT TO_CHAR(HIREDATE, 'YYYY-MM') "입사년월"
, COUNT(*) "인원수"
FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM')
) T;
--○ EMP 테이블을 대상으로
-- 입사한 사원의 수가 가장 많았을 때의
-- 입사년월과 인원수를 조회할 수 있도록 쿼리문을 구성한다.
/*
----------- ----------
입사년월 인원수
----------- ----------
1981-12 2
1981-09 2
1981-02 2
1987-07 2
*/
SELECT ENAME, HIREDATE
FROM EMP
ORDER BY 2;
SELECT TO_CHAR(HIREDATE, 'YYYY-MM') "입사년월"
, COUNT(*) "인원수"
FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM');
--==>>
/*
1981-05 1
1981-12 2
1982-01 1
1981-09 2
1981-02 2
1981-11 1
1980-12 1
1981-04 1
1987-07 2
1981-06 1
*/
-- ERROR
-- 집계 함수는 WHERE 절에서 쓸 수 없으므로
-- 다음과 같이 쿼리문을 구성하면 에러가 발생한다.
SELECT TO_CHAR(HIREDATE, 'YYYY-MM') "입사년월"
, COUNT(*) "인원수"
FROM EMP
WHERE COUNT(*) = 2
GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM');
SELECT TO_CHAR(HIREDATE, 'YYYY-MM') "입사년월"
, COUNT(*) "인원수"
FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM')
HAVING COUNT(*) = 2;
--==>>
/*
1981-12 2
1981-09 2
1981-02 2
1987-07 2
*/
SELECT TO_CHAR(HIREDATE, 'YYYY-MM') "입사년월"
, COUNT(*) "인원수"
FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM')
HAVING COUNT(*) = (입사년월 기준 최대 인원);
SELECT TO_CHAR(HIREDATE, 'YYYY-MM') "입사년월"
, COUNT(*) "인원수"
FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM')
HAVING COUNT(*) = (입사년월 기준 최대 인원);
-- 입사년월 기준 최대 인원
SELECT COUNT(*)
FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM');
--==>>
/*
1
2
1
2
2
1
1
1
2
1
*/
-- SELECT 에서는 MAX(COUNT(*)) 가 가능하다!!
-- HAVING의 조건으로 사용했을 때는 문제가 있었는데...
SELECT MAX(COUNT(*))
FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM');
--==>> 2
--#1
--------------------------------------------------------------------------------
SELECT TO_CHAR(HIREDATE, 'YYYY-MM') "입사년월"
, COUNT(*) "인원수"
FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM')
HAVING COUNT(*) = (SELECT MAX(COUNT(*))
FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM'));
--==>>
/*
1981-12 2
1981-09 2
1981-02 2
1987-07 2
*/
--------------------------------------------------------------------------------
SELECT TO_CHAR(HIREDATE, 'YYYY-MM') "입사년월"
, COUNT(*) "인원수"
FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM');
SELECT T1.입사년월, T1.인원수
(
SELECT TO_CHAR(HIREDATE, 'YYYY-MM') "입사년월"
, COUNT(*) "인원수"
FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM')
) T1;
SELECT T1.입사년월, T1.인원수
FROM
(
SELECT TO_CHAR(HIREDATE, 'YYYY-MM') "입사년월"
, COUNT(*) "인원수"
FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM')
) T1;
SELECT T1.입사년월, T1.인원수
FROM
(
SELECT TO_CHAR(HIREDATE, 'YYYY-MM') "입사년월"
, COUNT(*) "인원수"
FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM')
) T1
WHERE T1.인원수 = (2);
--#2
--------------------------------------------------------------------------------
SELECT T1.입사년월, T1.인원수
FROM
(
SELECT TO_CHAR(HIREDATE, 'YYYY-MM') "입사년월"
, COUNT(*) "인원수"
FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM')
) T1
WHERE T1.인원수 = (
SELECT MAX(T2.인원수)
FROM
(
SELECT TO_CHAR(HIREDATE, 'YYYY-MM') "입사년월"
, COUNT(*) "인원수"
FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM')
) T2
)
ORDER BY 1;
--#3
--------------------------------------------------------------------------------
SELECT T1.입사년월, T1.인원수
FROM
(
SELECT TO_CHAR(HIREDATE, 'YYYY-MM') "입사년월"
, COUNT(*) "인원수"
FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM')
) T1
WHERE T1.인원수 = (SELECT MAX(COUNT(*))
FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM'))
ORDER BY 1;
--------------------------------------------------------------------------------
--■■■ ROW_NUMBER ■■■--
SELECT ENAME "사원명", SAL "급여", HIREDATE "입사일"
FROM EMP;
--==>>
/*
SMITH 800 1980-12-17
ALLEN 1600 1981-02-20
WARD 1250 1981-02-22
JONES 2975 1981-04-02
MARTIN 1250 1981-09-28
BLAKE 2850 1981-05-01
CLARK 2450 1981-06-09
SCOTT 3000 1987-07-13
KING 5000 1981-11-17
TURNER 1500 1981-09-08
ADAMS 1100 1987-07-13
JAMES 950 1981-12-03
FORD 3000 1981-12-03
MILLER 1300 1982-01-23
*/
SELECT ROW_NUMBER() OVER(ORDER BY SAL DESC) "관찰"
, ENAME "사원명", SAL "급여", HIREDATE "입사일"
FROM EMP;
--==>>
/*
관찰 사원명 급여 입사일
1 KING 5000 1981-11-17
2 FORD 3000 1981-12-03
3 SCOTT 3000 1987-07-13
4 JONES 2975 1981-04-02
5 BLAKE 2850 1981-05-01
6 CLARK 2450 1981-06-09
7 ALLEN 1600 1981-02-20
8 TURNER 1500 1981-09-08
9 MILLER 1300 1982-01-23
10 WARD 1250 1981-02-22
11 MARTIN 1250 1981-09-28
12 ADAMS 1100 1987-07-13
13 JAMES 950 1981-12-03
14 SMITH 800 1980-12-17
*/
SELECT ROW_NUMBER() OVER(ORDER BY SAL DESC) "관찰"
, ENAME "사원명", SAL "급여", HIREDATE "입사일"
FROM EMP
ORDER BY ENAME;
--==>>
/*
[정렬 순서가 달라지더라도 ROW_NUMBER의 결과가 달라지는 것이 아니라 ]
[고정된 값으로 유지된다.]
관찰 사원명 급여 입사일
12 ADAMS 1100 1987-07-13
7 ALLEN 1600 1981-02-20
5 BLAKE 2850 1981-05-01
6 CLARK 2450 1981-06-09
2 FORD 3000 1981-12-03
13 JAMES 950 1981-12-03
4 JONES 2975 1981-04-02
1 KING 5000 1981-11-17
11 MARTIN 1250 1981-09-28
9 MILLER 1300 1982-01-23
3 SCOTT 3000 1987-07-13
14 SMITH 800 1980-12-17
8 TURNER 1500 1981-09-08
10 WARD 1 250 1981-02-22
*/
SELECT ROW_NUMBER() OVER(ORDER BY ENAME) "관찰"
, ENAME "사원명", SAL "급여", HIREDATE "입사일"
FROM EMP
ORDER BY ENAME;
--==>>
/*
관찰 사원명 급여 입사일
1 ADAMS 1100 1987-07-13
2 ALLEN 1600 1981-02-20
3 BLAKE 2850 1981-05-01
4 CLARK 2450 1981-06-09
5 FORD 3000 1981-12-03
6 JAMES 950 1981-12-03
7 JONES 2975 1981-04-02
8 KING 5000 1981-11-17
9 MARTIN 1250 1981-09-28
10 MILLER 1300 1982-01-23
11 SCOTT 3000 1987-07-13
12 SMITH 800 1980-12-17
13 TURNER 1500 1981-09-08
14 WARD 1250 1981-02-22
*/
-- ROW_NUMBER() 는 관리를 위해 쓰는 정보가 아니다.
--*****[부가 설명 내용 정리]**********************************************************
-- 자동으로 번호를 발행해주는 장치 IDENTITY...
-- 주문번호 : 앞선 번호보다 1 증가하여... EX : (171 -> 172 -> ...)
-- 게시물 선택을 위해 구분하려면...
-- #1. 제목 : 동일제목이 여러 개일경우, 여러 개가 동시에 수정됨 (X)
-- #2. 발행날짜 : 동일한 날짜, 시간에 작성한 게시글이 동시에 수정됨 (X)
-- → 따라서, 어떤 특정한 레코드만 식별해내는 장치가 필요하다 - 식별자
-- 시퀀스를 식별자로 보는 것은 위험하다.
-- 이 번호가 왜 필요한가? (ROW_NUMBER()) 특정 데이터가 갖는 고유한 번호가 없으면
-- 그것을 정확하게 선택해서 수정하거나 삭제할 수 없으니까.
-- 식별번호를 보여주게 되면... 보안성 측면에서 위험하다.
-- 특정 게시물의 키 값(고유번호)를 알고 있으면.. 그것을 알고 있으면 데이터 수정이 가능하므로
-- 사용자들이 보는 고유번호와, 관리자가 식별해 내는 키 값을 서로 다르도록 구성해야 한다.
-- 식별번호를 노출했을 경우, 관리자가 게시물을 삭제 했을 때 그 식별번호가 통째로날아가기 때문에
-- 번호가 순차적이지 않아서, 필터링이 심하다는 인식을 사용자에게 줄 수 있다. (사용자의 게시판 신뢰도 하락)
-- 리스트상에서 보기 좋게...
--*********************************************************************************
--※ 게시판의 게시물 번호를 SEQUENCE 나 IDENTITY 를 사용하게 되면
-- 게시물을 삭제했을 경우... 삭제한 게시물의 자리에 다음 번호를 가진
-- 게시물이 등록되는 상황이 발생하게 된다.
-- 이는... 보안성 측면이나... 미관상... 바람직하지 않은 상태일 수 있기 때문에
-- ROW_NUMBER() 의 사용을 고려해 볼 수 있다.
-- 관리의 목적으로 사용할 때에는 SEQUENCE 나 IDENTITY 를 사용하지만,
-- 단순히 게시물을 목록화하여 사용자에게 리스트 형식으로 보여줄 때에는
-- 사용하지 않는 것이 바람직할 수 있다.
--○ SEQUENCE(시퀀수 : 주문번호)
-- → 사전적인 의미 : 1. (일련의) 연속적인 사건들 2. (사건, 행동 등의) 순서
--******************************************************************************
-- 여러 장을 미리 뽑아 놓고 그 여러장을 빠르게 번호표를 나눠줄 수 있는 것.
-- 번호표를 30개 정도 뽑았을 때, 사람이 10명밖에 없으면 20개를 버려야함...
-- 그럼 그 다음 사람은 31번을 뽑아야 함...
-- 캐시를 안 쓰게 되면 기다리는 사람들이 늘어나서 트래픽이 늘어난다.
-- 캐시를 쓰게 되면 많이 뽑아놓고 대기 인원 수만큼 더 많이 뽑게 되면 사이사이에 비는 영역들이 생길 수 있다.
--******************************************************************************
--○ 시퀀스 생성
CREATE SEQUENCE SEQ_BOARD -- 기본적인 시퀀스 생성 구문
START WITH 1 -- 시작값 설정
INCREMENT BY 1 -- 증가값 설정
NOMAXVALUE -- 최대값 설정
NOCACHE; -- 캐시 사용 안함 설정 옵션
--==>> Sequence SEQ_BOARD이(가) 생성되었습니다.
--○ 실습 테이블 생성 (테이블명 : TBL_BOARD)
CREATE TABLE TBL_BOARD -- TBL_BOARD 테이블 생성 구문 → 게시판 테이블
( NO NUMBER -- 게시물 번호 → Ⅹ
, TITLE VARCHAR2(50) -- 게시물 제목 → ○
, CONTENTS VARCHAR2(1000) -- 게시물 내용 → ○
, NAME VARCHAR2(10) -- 게시물 작성자 → △
, PW VARCHAR2(20) -- 게시물 패스워드 → △
, CREATED DATE DEFAULT SYSDATE -- 게시물 작성일 → Ⅹ
);
--==>> Table TBL_BOARD이(가) 생성되었습니다.
--○ 데이터 입력 → 게시판에 게시물 작성
-- 시퀀스명.NEXTVAL : 이전에 발행했던 번호보다 1 큰 수를 자동으로 발급해 주는 것.
-- 1씩 증가하면서 자동으로 번호표가 발급 됨.
INSERT INTO TBL_BOARD VALUES
(SEQ_BOARD.NEXTVAL, '아~~~ 힘들다', '10분만 쉬었다 올께요', '문정환', 'java006$', DEFAULT);
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_BOARD VALUES
(SEQ_BOARD.NEXTVAL, '아~ 졸리다', '10분만 자고 올께요', '정한울', 'java006$', DEFAULT);
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_BOARD VALUES
(SEQ_BOARD.NEXTVAL, '아~ 웃겨', '하루하루가 재미있어요', '노은하', 'java006$', DEFAULT);
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_BOARD VALUES
(SEQ_BOARD.NEXTVAL, '보고싶다', '범구가 너무너무 보고싶어요', '김수환', 'java006$', DEFAULT);
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_BOARD VALUES
(SEQ_BOARD.NEXTVAL, '배고파요', '점심을 먹었는데 배고파요', '김민지', 'java006$', DEFAULT);
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_BOARD VALUES
(SEQ_BOARD.NEXTVAL, '아직 남아있네요', '두 시간 반이나 남아있네요', '이윤수', 'java006$', DEFAULT);
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_BOARD VALUES
(SEQ_BOARD.NEXTVAL, '그만하고싶다', '그냥 넘어갈까....', '김호진', 'java006$', DEFAULT);
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_BOARD VALUES
(SEQ_BOARD.NEXTVAL, '아~ 배아파', 'ㅋㅋㅋㅋㅋ', '노은하', 'java006$', DEFAULT);
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_BOARD VALUES
(SEQ_BOARD.NEXTVAL, '소근소근', '궁시렁궁시렁', '이윤수', 'java006$', DEFAULT);
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_BOARD VALUES
(SEQ_BOARD.NEXTVAL, '모자라요', '아직 잠이 모자라요', '김동민', 'java006$', DEFAULT);
--==>> 1 행 이(가) 삽입되었습니다.
--○ 세선 셜정 변경
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
--==>> Session이(가) 변경되었습니다.
--○ 확인
SELECT *
FROM TBL_BOARD;
--==>>
/*
NO TITLE CONTENTS NAME PW CREATED
1 아~~~ 힘들다 10분만 쉬었다 올께요 문정환 java006$ 2023-10-25 15:24:46
2 아~ 졸리다 10분만 자고 올께요 정한울 java006$ 2023-10-25 15:28:10
3 아~ 웃겨 하루하루가 재미있어요 노은하 java006$ 2023-10-25 15:30:58
4 보고싶다 범구가 너무너무 보고싶어요 김수환 java006$ 2023-10-25 15:31:35
5 배고파요 점심을 먹었는데 배고파요 김민지 java006$ 2023-10-25 15:33:57
6 아직 남아있네요 두 시간 반이나 남아있네요 이윤수 java006$ 2023-10-25 15:35:19
7 그만하고싶다 그냥 넘어갈까.... 김호진 java006$ 2023-10-25 15:37:29
8 아~ 배아파 ㅋㅋㅋㅋㅋ 노은하 java006$ 2023-10-25 15:38:11
9 소근소근 궁시렁궁시렁 이윤수 java006$ 2023-10-25 15:38:44
10 모자라요 아직 잠이 모자라요 김동민 java006$ 2023-10-25 15:39:27
*/
--○ 커밋
COMMIT;
--==>> 커밋 완료.
--○ 게시물 삭제
DELETE
FROM TBL_BOARD
WHERE NO = 1;
--==>> 1 행 이(가) 삭제되었습니다.
DELETE
FROM TBL_BOARD
WHERE NO = 6;
--==>> 1 행 이(가) 삭제되었습니다.
DELETE
FROM TBL_BOARD
WHERE NO = 8;
--==>> 1 행 이(가) 삭제되었습니다.
DELETE
FROM TBL_BOARD
WHERE NO = 10;
--==>> 1 행 이(가) 삭제되었습니다.
--○ 확인
SELECT *
FROM TBL_BOARD;
--==>>
/*
NO TITLE CONTENTS NAME PW CREATED
2 아~ 졸리다 10분만 자고 올께요 정한울 java006$ 2023-10-25 15:28:10
3 아~ 웃겨 하루하루가 재미있어요 노은하 java006$ 2023-10-25 15:30:58
4 보고싶다 범구가 너무너무 보고싶어요 김수환 java006$ 2023-10-25 15:31:35
5 배고파요 점심을 먹었는데 배고파요 김민지 java006$ 2023-10-25 15:33:57
7 그만하고싶다 그냥 넘어갈까.... 김호진 java006$ 2023-10-25 15:37:29
9 소근소근 궁시렁궁시렁 이윤수 java006$ 2023-10-25 15:38:44
*/
--○ 게시물 작성
INSERT INTO TBL_BOARD VALUES
(SEQ_BOARD.NEXTVAL, '집중합시다', '저는 전혀 졸리지 않아요', '임하성', 'java006$', DEFAULT);
--==>> 1 행 이(가) 삽입되었습니다.
--○ 게시물 삭제
DELETE
FROM TBL_BOARD
WHERE NO = 7;
--==>> 1 행 이(가) 삭제되었습니다.
--○ 확인
SELECT *
FROM TBL_BOARD;
--==>>
/*
NO TITLE CONTENTS NAME PW CREATED
2 아~ 졸리다 10분만 자고 올께요 정한울 java006$ 2023-10-25 15:28:10
3 아~ 웃겨 하루하루가 재미있어요 노은하 java006$ 2023-10-25 15:30:58
4 보고싶다 범구가 너무너무 보고싶어요 김수환 java006$ 2023-10-25 15:31:35
5 배고파요 점심을 먹었는데 배고파요 김민지 java006$ 2023-10-25 15:33:57
9 소근소근 궁시렁궁시렁 이윤수 java006$ 2023-10-25 15:38:44
11 집중합시다 저는 전혀 졸리지 않아요 임하성 java006$ 2023-10-25 16:06:33
*/
--○ 커밋
COMMIT;
--==>> 커밋 완료.
--○ 게시판의 게시물 리스트 조회
SELECT NO "글번호", TITLE "제목", NAME "작성자", CREATED "작성일"
FROM TBL_BOARD;
--==>>
/*
글번호 제목 작성자 작성일
2 아~ 졸리다 정한울 2023-10-25 15:28:10
3 아~ 웃겨 노은하 2023-10-25 15:30:58
4 보고싶다 김수환 2023-10-25 15:31:35
5 배고파요 김민지 2023-10-25 15:33:57
9 소근소근 이윤수 2023-10-25 15:38:44
11 집중합시다 임하성 2023-10-25 16:06:33
*/
-- 작성일자를 기준으로 사용자에게 보여줄 수 있는 글번호
-- 데이터에 부여된 고유한 번호와는 다르다. (SEQUANCE)
SELECT ROW_NUMBER() OVER(ORDER BY CREATED) "글번호"
, TITLE "제목", NAME "작성자", CREATED "작성일"
FROM TBL_BOARD;
-- 이렇게 작성하면, 고유번호를 노출시키지도 않고
-- 중간 중간에 빠진 번호가 생기지도 않는다.
--==>>
/*
글번호 제목 작성자 작성일
1 아~ 졸리다 정한울 2023-10-25 15:28:10
2 아~ 웃겨 노은하 2023-10-25 15:30:58
3 보고싶다 김수환 2023-10-25 15:31:35
4 배고파요 김민지 2023-10-25 15:33:57
5 소근소근 이윤수 2023-10-25 15:38:44
6 집중합시다 임하성 2023-10-25 16:06:33
*/
-- 실제로는 작성일자가 가장 최신 순인 것이 부여되는 글 번호가 가장 크므로
-- 다음과 같이 수정할 수 있다.
SELECT ROW_NUMBER() OVER(ORDER BY CREATED) "글번호"
, TITLE "제목", NAME "작성자", CREATED "작성일"
FROM TBL_BOARD
ORDER BY 4 DESC;
--==>>
/*
글번호 제목 작성자 작성일
6 집중합시다 임하성 2023-10-25 16:06:33
5 소근소근 이윤수 2023-10-25 15:38:44
4 배고파요 김민지 2023-10-25 15:33:57
3 보고싶다 김수환 2023-10-25 15:31:35
2 아~ 웃겨 노은하 2023-10-25 15:30:58
1 아~ 졸리다 정한울 2023-10-25 15:28:10
*/
-- 오늘 쓴 게시물 : 연월일 시분초가 다 나온다
-- 어제 쓴 게시물 : 연월일만 나온다.
--------------------------------------------------------------------------------
-- 붙이거나 서로 어울리게 만들거나 할 때 조인이라는 문법을 사용하게 된다.
-- ORACLE 에서 사용하는 JOIN은...
--■■■ JOIN(조인) ■■■--
-- 1. SQL 1992 CODE
-- CROSS JOIN(상호 조인)은
-- 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인하는 기능이다.
-- 모든 결합 유형이 들어있는 JOIN 이다. 모든 유형을 다 적용해서 결합시키는 JOIN을
-- CROSS JOIN 이라고 한다.
--------------------------------------------------------------------------------
--○ CROSS JOIN
SELECT *
FROM EMP, DEPT;
--> 수학에서 말하는 데카르트 곱(CATERSIAN PRODUCT)
-- 두 테이블을 결합한 모든 경우의 수
-- 이것 때문에 테이블 수가 굉장히 많이 나왔던 것이다...
--
--SELECT *
--FROM EMP
-- CROSS JOIN
-- DEPT;
-- SMITH가 10번, 20번, 30번, 40번 부서들과 모두 결합되어 있는 것을 확인할 ㅜㅅ 있다.
-- 모든 결합 유형이 들어있는 CROSS JOIN 이다.
--==>>
/*
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 10 ACCOUNTING NEW YORK
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 10 ACCOUNTING NEW YORK
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 10 ACCOUNTING NEW YORK
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 10 ACCOUNTING NEW YORK
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 10 ACCOUNTING NEW YORK
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 10 ACCOUNTING NEW YORK
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 1987-07-13 00:00:00 3000 20 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 10 ACCOUNTING NEW YORK
7876 ADAMS CLERK 7788 1987-07-13 00:00:00 1100 20 10 ACCOUNTING NEW YORK
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 10 ACCOUNTING NEW YORK
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 10 ACCOUNTING NEW YORK
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 10 ACCOUNTING NEW YORK
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 20 RESEARCH DALLAS
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 20 RESEARCH DALLAS
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 20 RESEARCH DALLAS
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 20 RESEARCH DALLAS
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 1987-07-13 00:00:00 3000 20 20 RESEARCH DALLAS
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 20 RESEARCH DALLAS
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 20 RESEARCH DALLAS
7876 ADAMS CLERK 7788 1987-07-13 00:00:00 1100 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 20 RESEARCH DALLAS
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 30 SALES CHICAGO
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 30 SALES CHICAGO
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 30 SALES CHICAGO
7788 SCOTT ANALYST 7566 1987-07-13 00:00:00 3000 20 30 SALES CHICAGO
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 30 SALES CHICAGO
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 30 SALES CHICAGO
7876 ADAMS CLERK 7788 1987-07-13 00:00:00 1100 20 30 SALES CHICAGO
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 30 SALES CHICAGO
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 30 SALES CHICAGO
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 40 OPERATIONS BOSTON
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 40 OPERATIONS BOSTON
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 40 OPERATIONS BOSTON
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 40 OPERATIONS BOSTON
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 40 OPERATIONS BOSTON
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 40 OPERATIONS BOSTON
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 40 OPERATIONS BOSTON
7788 SCOTT ANALYST 7566 1987-07-13 00:00:00 3000 20 40 OPERATIONS BOSTON
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 40 OPERATIONS BOSTON
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 40 OPERATIONS BOSTON
7876 ADAMS CLERK 7788 1987-07-13 00:00:00 1100 20 40 OPERATIONS BOSTON
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 40 OPERATIONS BOSTON
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 40 OPERATIONS BOSTON
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 40 OPERATIONS BOSTON
*/
--------------------------------------------------------------------------------
--○ EQUI JOIN : 서로 정확히 일치하는 데이터들끼리 연결하여 결합시키는 결합 방법
-- 테이블을 결합시킬 때 조건이 있다. (WHERE)
SELECT *
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;
-- 부서번호가 일치하는 것들끼리만 테이블이 결합된 것을 확인할 수가 있다.
-- SMITH 20번 부서하고만 연결
-- ALLEN 30번 부서하고만 연결... 부서번호가 일치할 때만 테이블을 결합시키기 때문에.
--==>>
/*
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 10 ACCOUNTING NEW YORK
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 10 ACCOUNTING NEW YORK
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 20 RESEARCH DALLAS
7876 ADAMS CLERK 7788 1987-07-13 00:00:00 1100 20 20 RESEARCH DALLAS
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 1987-07-13 00:00:00 3000 20 20 RESEARCH DALLAS
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 30 SALES CHICAGO
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 30 SALES CHICAGO
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 30 SALES CHICAGO
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 30 SALES CHICAGO
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 30 SALES CHICAGO
*/
-- 별칭 사용
SELECT *
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;
--==>>
/*
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 10 ACCOUNTING NEW YORK
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 10 ACCOUNTING NEW YORK
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 20 RESEARCH DALLAS
7876 ADAMS CLERK 7788 1987-07-13 00:00:00 1100 20 20 RESEARCH DALLAS
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 1987-07-13 00:00:00 3000 20 20 RESEARCH DALLAS
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 30 SALES CHICAGO
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 30 SALES CHICAGO
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 30 SALES CHICAGO
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 30 SALES CHICAGO
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 30 SALES CHICAGO
*/
--------------------------------------------------------------------------------
SELECT *
FROM EMP;
SELECT *
FROM SALGRADE;
--○ NON EQUI JOIN : 범위 안에 적합한 것들끼리 연결시키는 결합 방법
SELECT *
FROM EMP, SALGRADE
WHERE EMP.SAL BETWEEN SALGRADE.LOSAL AND SALGRADE.HISAL;
-- 정확하게 일치하는 데이터가 없더라도 데이터 처리가 이루어진다
SELECT *
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;
--==>>
/*
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 1 700 1200
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 1 700 1200
7876 ADAMS CLERK 7788 1987-07-13 00:00:00 1100 20 1 700 1200
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 2 1201 1400
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 2 1201 1400
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 2 1201 1400
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 3 1401 2000
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 3 1401 2000
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 4 2001 3000
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 4 2001 3000
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 4 2001 3000
7788 SCOTT ANALYST 7566 1987-07-13 00:00:00 3000 20 4 2001 3000
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 4 2001 3000
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 5 3001 9999
*/
-- EQUI JOIN 시 (+) 를 활용한 결합 방법
SELECT *
FROM TBL_EMP;
SELECT *
FROM TBL_DEPT;
INSERT INTO TBL_DEPT VALUES(50, '개발부', '서울');
--==>> 1 행 이(가) 삽입되었습니다.
COMMIT;
--==>> 커밋 완료.
-- EQUI JOIN
SELECT *
FROM TBL_EMP E, TBL_DEPT D
WHERE E.DEPTNO = D.DEPTNO;
--> 총 14건의 데이터가 결합되어 조회된 상황
-- 즉, 부서번호를 갖지 못한 사원들(5) 모두 누락
-- 또한, 소속 사원을 갖지 못한 부서(2) 모두 누락
--(+) 가 붙어있지 않은 쪽이 필수
--(+) 가 붙어있는 쪽이 부가적인 옵션, 양념... 필수가 아님.
-- (+)가 붙지 않은 TBL_EMP 테이블이 필수적인 것이다!
-- TBL_EMP 테이블에 속한 데이터들은 (부서번호, 지역명)
-- TBL_EMP에 사용되지 않은 데이터더라도 필수적으로 조회되어야 한다.
SELECT *
FROM TBL_EMP E, TBL_DEPT D
WHERE E.DEPTNO = D.DEPTNO(+);
--> 총 19건의 데이터가 결합되어 조회된 상황
-- 소속 사원을 갖지 못한 부서(2) 누락 ----------------------(+)
-- 부서번호를 갖지 못한 사원들(5) 모두 조회
-- (+)가 붙지 않은 TBL_DEPT 테이블이 필수적인 것이다!
-- TBL_DEPT 테이블에 속한 데이터들은 (부서번호, 지역명)
-- TBL_EMP에 사용되지 않은 데이터더라도 필수적으로 조회되어야 한다.
SELECT *
FROM TBL_EMP E, TBL_DEPT D
WHERE E.DEPTNO(+) = D.DEPTNO;
--> 총 16건의 데이터가 결합되어 조회된 상황
-- 부서번호를 갖지 못한 사원들(5) 누락 ---------------------(+)
-- 소속 사원을 갖지 못한 부서(2) 모두 조회
--※ 『(+)』 가 없는 쪽 테이블의 데이터를 모두 메모리에 적재한 후 -- 기준
-- 『(+)』 가 있는 쪽 테이블의 데이터를 하나하나 확인하여 결합시키는 형태로 -- 추가(첨가)
-- JOIN이 이루어진다.
-- 이와 같은 이유로...
SELECT *
FROM TBL_EMP E, TBL_DEPT D
WHERE E.DEPTNO(+) = D.DEPTNO;
-- 이런 형식의 JOIN 은 존재하지 않는다.
-- 기준이 없는데 양쪽 다 첨가하겠다는 형식으로 사용하는 것은 불가능하다.
-- JOIN 이라는 키워드가 등장하면 99 코드이다.
-- JOIN 이라는 키워드 없이 여러 테이블이 결합되어 있으면 92 코드이다.
-- #결합하는 조건을 가시화시키기 위해 ON이라는 키워드가 등장하게 된 것이다.
-- WHERE 에 두 가지의 조건 (결합 조건, 조회 선택 조건) 이 있으면 두개를 구분하기 어려우므로
-- 2. SQL 1999 CODE → 『JOIN』 키워드 등장 → JOIN(결합)의 유형 명시
-- → 『ON』 키워드 등장 → 결합 조건은 WHERE 대신 ON
-- EQUI JOIN, NON EQUI JOIN의 구분이 없어진다.
-- CROSS JOIN
SELECT *
FROM EMP CROSS JOIN DEPT;
--○ INNER JOIN
SELECT *
FROM EMP INNER JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO; -- 테이블 결합 조건
-- 형태는 EQUI JOIN의 형태이다.
SELECT *
FROM EMP E INNER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO;
--INNER 생략 가능
SELECT *
FROM EMP E JOIN DEPT D
ON E.DEPTNO = D.DEPTNO;
-- INNER JOIN 에서 INNER 는 생략 가능
-- 92코드에서 (+)로 테이블을 결합했던 방식을
-- OUTER JOIN 으로 바꾼 것이다.
-- (+) 가 붙은 것들이 주목을 받게 되는데
-- 주인공을 방향성을 잡아서 보이도록 하자. 그러면 혼란이 좀 덜할 것이다.
--○ OUTER JOIN
-- 왼쪽에 위치한 테이블의 데이터들이 필수적으로 조회되어야 하므로
-- TBL_EMP LEFT OUTER 로 된 것이다.
-- 방향이 가리키는 것이 주인공이다.
-- 오른쪽에 (+)
SELECT *
FROM TBL_EMP E LEFT OUTER JOIN TBL_DEPT D
ON E.DEPTNO = D.DEPTNO;
-- 오른쪽에 위치한 테이블의 데이터들이 필수적으로 조회되어야 한다.
-- 왼쪽에 (+)
-- 지시하는 방향 자체가 주인공
SELECT *
FROM TBL_EMP E RIGHT OUTER JOIN TBL_DEPT D
ON E.DEPTNO = D.DEPTNO;
-- 왼쪽 오른쪽 모두가 다 주인공
-- 양쪽 모두가 다 주인공이므로 양쪽의 테이블 데이터들이 모두 조회되어야 한다.
SELECT *
FROM TBL_EMP E FULL OUTER JOIN TBL_DEPT D
ON E.DEPTNO = D.DEPTNO;
-- OUTER JOIN 에서 OUTER 는 생략 가능
SELECT *
FROM TBL_EMP E LEFT JOIN TBL_DEPT D
ON E.DEPTNO = D.DEPTNO;
SELECT *
FROM TBL_EMP E RIGHT JOIN TBL_DEPT D
ON E.DEPTNO = D.DEPTNO;
SELECT *
FROM TBL_EMP E FULL JOIN TBL_DEPT D
ON E.DEPTNO = D.DEPTNO;
-- 부서를 갖지 못한 사원들도 모두 조회
-- 사원을 갖지 못한 부서들도 모두 조회
-- 양쪽 테이블 모두를 조회하는 형식으로 조회 가능하다.
--※ 참고
-- 테이블을 FULL SCAN하여 모두 메모리에 퍼올린 후에 결합한다. (리소스 소모가 크다)
SELECT *
FROM TBL_EMP E JOIN TBL_DEPT D
ON E.DEPTNO = D.DEPTNO
AND E.JOB = 'CLERK';
-- ON 에 두 개의 조건을 주어서 테이블을 결합시켰다.
--> 이와 같은 방법으로 쿼리문을 구성해도
-- 조회 결과를 얻는 과정에는 문제가 없다.
--==>>
/*
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 10 ACCOUNTING NEW YORK
7876 ADAMS CLERK 7788 1987-07-13 00:00:00 1100 20 20 RESEARCH DALLAS
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 30 SALES CHICAGO
*/
-- ↑ 이렇게 조회하는 것 보다는....
-- ↓ 아래와 같이 조회하는 것이 더 올바르다.
-- ON에는 결합조건만 사용하고, WHERE 에만 조회 조건을 작성할 수 있도록 하는 것이 바람직하다.
-- WHERE의 조건에 해당하는 데이터들만 메모리에 퍼올린 후에 테이블을 결합한다. (리소스 소모가 적다.)
SELECT *
FROM TBL_EMP E JOIN TBL_DEPT D
ON E.DEPTNO = D.DEPTNO
WHERE E.JOB = 'CLERK';
--> 하지만 이와 같이 구성하여
-- 조회하는 것을 권장한다.
--==>>
/*
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 10 ACCOUNTING NEW YORK
7876 ADAMS CLERK 7788 1987-07-13 00:00:00 1100 20 20 RESEARCH DALLAS
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 30 SALES CHICAGO
*/
--○ EMP 테이블과 DEPT 테이블을 대상으로
-- 직종이 MANAGER 와 CLERK 인 사원들만
-- 부서번호, 부서명, 사원명, 직종명, 급여 항목을 조회한다.
SELECT *
FROM EMP;
SELECT *
FROM DEPT;
SELECT E.DEPTNO "부서번호", D.DNAME "부서명", E.ENAME "사원명"
, E.JOB "직종명", E.SAL "급여"
FROM EMP E
FULL OUTER JOIN
DEPT D
ON
E.DEPTNO = D.DEPTNO
WHERE E.JOB IN ('MANAGER', 'CLERK');
SELECT E.DEPTNO, D.DNAME, E.ENAME, E.JOB, E.SAL
FROM TBL_EMP E
LEFT JOIN
TBL_DEPT D
ON E.DEPTNO = D.DEPTNO
WHERE E.JOB = 'MANAGER'
OR E.JOB = 'CLERK';
-- 부서번호, 부서명, 사원명, 직종명, 급여
-- DEPTNO DNAME ENAME JOB SAL
-- E, D D E E E
SELECT 부서번호, 부서명, 사원명, 직종명, 급여
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;
SELECT DEPTNO, DENAME, ENAME, JOB, SAL
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;
--==>> 에러 발생
-- (ORA-00904: "DENAME": invalid identifier)
--> 두 테이블 간 중복되는 컬럼에 대한
-- 소속 테이블을 정해줘야(명시해줘야) 한다.
-- DEPTNO 가 두 테이블 모두 존재하는 COLUMN 이므로
-- 어떤 테이블에서 가져와야 하는지 알 수 없다.
-- 양쪽에 모두 존재하는 컬럼을 꺼내쓸 때는, 부모 테이블의 컬럼을 꺼내 써야 한다.
-- 부모 테이블? - 어떤 테이블이 부모 테이블이고, 어떤 테이블이 자식 테이블일까?
-- 연결고리 컬럼 DEPTNO 컬럼을 봤을 때...
-- DEPT 컬럼에는 10, 20, 30, 40 이 하나씩이지만
-- EMP 에는 10이 여러 개, 20이 여러 개...
-- 즉, 하나의 부모에 여러 자식이 있으므로 DEPT 테이블이 부모가 되는 것이다.