-- DAY3 수업내용
-- 집계함수
-- ROLLUP 함수 : 그룹별로 중간 집계 처리를 하는 함수
-- GROUP BY 절에서만 사용하는 함수
-- 그룹별로 묶어진 값에 대한 중간 집계와 총 집계를 구할 때 사용한다.
-- 그룹별로 계산된 결과값들에 대한 총 집계가 자동으로 추가가 된다.
SELECT
JOB_CODE
,SUM(SALARY)
FROM
EMPLOYEE
GROUP BY ROLLUP (JOB_CODE)
ORDER BY 1;
-- CUBE 함수 : 그룹별 산출한 결과를 집계하는 함수이다.
SELECT
JOB_CODE
,SUM(SALARY)
FROM
EMPLOYEE
GROUP BY CUBE(JOB_CODE)
ORDER BY 1;
-- 인자로 전달한 그룹중에서 가장 먼저 지정한 그룹별 합계와 총 합계를 구하는 함수
SELECT
DEPT_CODE
, SUM(SALARY)
FROM
EMPLOYEE
GROUP BY ROLLUP(DEPT_CODE)
ORDER BY 1;
SELECT
DEPT_CODE
,JOB_CODE
, SUM(SALARY)
FROM
EMPLOYEE
GROUP BY ROLLUP(DEPT_CODE,JOB_CODE)
--GROUP BY ROLLUP(JOB_CODE,DEPT_CODE)
ORDER BY 1;
SELECT
DEPT_CODE
,JOB_CODE
,SUM(SALARY)
FROM
EMPLOYEE
GROUP BY CUBE(DEPT_CODE,JOB_CODE)
ORDER BY 1;
-- GROUPING 함수 : ROLLUP이나 CUBE에 의한 산출물이
-- 인자로 전달받은 컬럼 집합의 산출물이면 0을 반환하고
-- 아니면 1을 반환하는 함수
SELECT
DEPT_CODE
,JOB_CODE
,SUM(SALARY)
,GROUPING(DEPT_CODE) "부서별그룹묶인상태"
,GROUPING(JOB_CODE) "직급별그룹묶인상태"
FROM
EMPLOYEE
GROUP BY CUBE(DEPT_CODE, JOB_CODE)
ORDER BY DEPT_CODE;
SELECT
DEPT_CODE
,JOB_CODE
,SUM(SALARY)
,CASE WHEN GROUPING(DEPT_CODE) = 0
AND GROUPING(JOB_CODE) = 1
THEN '부서별합계'
WHEN GROUPING(DEPT_CODE) = 1
AND GROUPING(JOB_CODE) = 0
THEN '직급별합계'
WHEN GROUPING(DEPT_CODE) = 0
AND GROUPING(JOB_CODE) = 0
THEN '그룹별합계'
ELSE '총합계'
END AS 구분
FROM
EMPLOYEE
GROUP BY CUBE(DEPT_CODE, JOB_CODE)
ORDER BY DEPT_CODE;
-- SET OPERATION(집합연산)
-- UNION : 여러 개의 쿼리 결과를 하나로 합치는 연산이다.
-- 중복된 영역을 제외하여 하나로 합친다.
SELECT
EMP_ID
,EMP_NAME
,DEPT_CODE
,SALARY
FROM
EMPLOYEE
WHERE DEPT_CODE = 'D5'
UNION
SELECT
EMP_ID
,EMP_NAME
,DEPT_CODE
,SALARY
FROM
EMPLOYEE
WHERE
SALARY > 3000000;
-- UNIONALL : 여러 개의 쿼리 결과를 하나로 합치는 연산자
-- UNION과의 차이점은 중복 영역을 모두 포함시킨다.
SELECT
EMP_ID
,EMP_NAME
,DEPT_CODE
,SALARY
FROM
EMPLOYEE
WHERE DEPT_CODE = 'D5'
UNION ALL
SELECT
EMP_ID
,EMP_NAME
,DEPT_CODE
,SALARY
FROM
EMPLOYEE
WHERE
SALARY > 3000000;
-- INTERSECT : 여러개의 SELECT한 결과에서 공통부분만 결과로 추출
-- 수학에서의 교집합과 비슷하다.
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5'
INTERSECT
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY > 3000000;
-- MINUS : 선행 SELECT 결과에서 다음 SELECT 결과와 겹치는 부분을 제외한
-- 나머지 부분만 추출, 수학에서 차집합과 비슷하다.
SELECT
EMP_ID
,EMP_NAME
,DEPT_CODE
,SALARY
FROM
EMPLOYEE
WHERE
DEPT_CODE = 'D5'
MINUS
SELECT
EMP_ID
,EMP_NAME
,DEPT_CODE
,SALARY
FROM
EMPLOYEE
WHERE SALARY > 3000000;
SELECT * FROM EMPLOYEE;
SELECT * FROM DEPARTMENT;
/*
<JOIN>
하나 이상의 테이블에서 데이터를 조회하기 위해 사용하고 수행 결과는 하나의 RESULT SET으로 나온다.
관계형 데이터베이스에서 SQL을 이용해 테이블간 '관계'를 맺는 방법
관계형 데이터베이스는 최소한의 데이터를 테이블에 담고 있어(중복을 최소화해서)
원하는 정보를 테이블에서 조회하혀면 한 개 이상의 테이블에서 데이터를 읽어와야 되는 경우가 많다.
이때, 무작정 데이터를 가져오는게 아닌 테이블간 연결고리로 관계가 맺어진 데이터를 추출해야 한다.
JOIN은 크게 오라클 전용 구문과 ANSI구문으로 나뉜다.
ANSI는 미국 국립 표준 협회(Americal National Standards Institue)를 뜻한다.
ANSI 구문 같은 경우 오라클, DBMS 외의 다른 DBMS에서 똑같이 사용된다는 장점이 있다.
오라클 전용 구문 | SQL : 1999표준(ANSI) 구문
-------------------------------------------------------------------------
등가 조인 | 내부조인(INNER JOIN) --> JOIN USING / ON
| 자연 조인(NATURAL JOIN) --> JOIN USING 구문과 비슷
--------------------------------------------------------------------------
포괄 조인 | 왼쪽 외부 조인(LEFT OUTER JOIN)
(LEFT OUTER) | 오른쪽 외부 조인(RIGHT OUTER JOIN)
(RIGHT OUTER) | 전체 외부 조인(FULL OUTER JOIN, 오라클 구문으로는 사용 못함)
*/
-- 오라클 전용 구문
-- FROM절에 ','로 구분하여 합치게 될 테이블명을 기술하고
-- WHERE절에 합치기에 사용할 컬럼명을 명시한다.
-- 연결에 사용할 두 컬럼명이 다른 경우
SELECT EMP_ID, EMP_NAME, DEPT_CODE, DEPT_TITLE
FROM EMPLOYEE, DEPARTMENT
WHERE DEPT_CODE = DEPT_ID;
-- 연결에 사용할 두 컬럼명이 같은 경우
SELECT EMP_ID, EMP_NAME, EMPLOYEE.JOB_CODE, JOB_NAME
FROM EMPLOYEE, JOB
WHERE EMPLOYEE.JOB_CODE = JOB.JOB_CODE;
-- 테이블에 별칭 사용
SELECT EMP_ID, EMP_NAME, E.JOB_CODE, JOB_NAME
FROM EMPLOYEE E, JOB J
WHERE E.JOB_CODE = J.JOB_CODE;
-- ANSI 표준 구문
-- 연결에 사용할 컬럼명이 같은 경우 USING(컬럼명)을 사용한다.
SELECT EMP_ID, EMP_NAME, JOB_CODE, JOB_NAME
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE);
-- 컬럼명이 같은 경우에도 ON()을 사용할 수 있다.
SELECT EMP_ID, EMP_NAME, E.JOB_CODE, JOB_NAME
FROM EMPLOYEE E
JOIN JOB J ON(E.JOB_CODE = J.JOB_CODE);
-- 연결에 사용할 컬럼명이 다른 경우 ON()을 사용
SELECT EMP_ID,EMP_NAME,DEPT_CODE,DEPT_TITLE
FROM EMPLOYEE
JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID);
-- 부서 테이블과 지역테이블을 조인하여 테이블에 모든 데이터를 조회하세요
-- ANSI표준
SELECT
*
FROM DEPARTMENT
JOIN LOCATION ON(LOCATION_ID = LOCAL_CODE);
-- 오라클 전용
SELECT
*
FROM DEPARTMENT D, LOCATION L
WHERE D.LOCATION_ID = L.LOCAL_CODE;
-- 조인은 기본이 EQUAL JOIN
-- 연결되는 컬럼의 값이 일치하는 행들만 조인됨
-- 일치하는 값이 없는 행은 조인에서 제외되는 것을 INNER JOIN이라고 한다.
-- OUTER JOIN : 두 테이블의 지정하는 컬럼 값이 일치하지 않는 행도 조인에 포함을 시킨다.
-- 반드시 OUTER JOIN임을 명시해야 한다.
-- 1. LEFT OUTER JOIN : 합치기에 사용한 두 테이블 중 왼쪽편에 기술된 테이블의 행의 수를 기준으로 JOIN
-- 2. RIGHT OUTER JOIN : 함치기에 사용한 두 테이블 중 오른편에 기술된 테이블의 행의 수를 기준으로 JOIN
-- 3. FULL OUTER JOIN : 합치기에 사용한 두 테이블이 가진 모든 행의 결과를 포함하여 JOIN
SELECT EMP_NAME, DEPT_TITLE
FROM EMPLOYEE
JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID);
-- LEFT OUTER JOIN
-- ANSI 표준
SELECT EMP_NAME, DEPT_TITLE
FROM EMPLOYEE
-- LEFT OUTER JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID);
LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID);
-- 오라클 전용 구문
SELECT EMP_NAME, DEPT_TITLE
FROM EMPLOYEE,DEPARTMENT
WHERE DEPT_CODE = DEPT_ID(+);
-- RIGHT OUTER JOIN
-- ANSI 표준
SELECT EMP_NAME,DEPT_TITLE
FROM EMPLOYEE
RIGHT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID);
--오라클 전용 구문
SELECT EMP_NAME, DEPT_TITLE
FROM EMPLOYEE, DEPARTMENT
WHERE DEPT_CODE(+) = DEPT_ID;
-- FULL OUTER JOIN
-- ANSI 표준
SELECT EMP_NAME, DEPT_TITLE
FROM EMPLOYEE
FULL JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID);
-- 오라클 전용구문
-- 오라클 전용 구문으로는 FULL OUTER JOIN을 하지 못한다.
SELECT EMP_NAME, DEPT_TITLE
FROM EMPLOYEE, DEPARTMENT
WHERE DEPT_CODE = DEPT_ID;
-- CROSS JOIN : 카테이션곱이라고도 한다.
-- 조인되는 테이블의 각 행들이 모두 매핑된 데이터가 검색되는 방법
SELECT EMP_NAME,DEPT_TITLE
FROM EMPLOYEE
CROSS JOIN DEPARTMENT;
-- NON EQUAL JOIN
-- 지정한 컬럼의 값이 일치하는 경우가 아닌, 값의 범위에 포함되는 행들을 연결하는 방식
-- ANSI 표준
SELECT EMP_NAME, SALARY, E.SAL_LEVEL, S.SAL_LEVEL
FROM EMPLOYEE E
JOIN SAL_GRADE S ON (SALARY BETWEEN MIN_SAL AND MAX_SAL);
-- 오라클 전용
SELECT EMP_NAME, SALARY, E.SAL_LEVEL, S.SAL_LEVEL
FROM EMPLOYEE E, SAL_GRADE S
WHERE SALARY BETWEEN MIN_SAL AND MAX_SAL;
-- SELF JOIN : 같은 테이블을 조인하는 경우
-- 자기 자신과 조인을 맺는 것
-- 오라클 전용
SELECT
E1.EMP_ID
, E1.EMP_NAME 사원이름
, E1.DEPT_CODE
, E1.MANAGER_ID
, E2.EMP_NAME 관리자이름
FROM EMPLOYEE E1, EMPLOYEE E2
WHERE E1.MANAGER_ID = E2.EMP_ID;
-- ANSI 표준
SELECT
E1.EMP_ID
, E1.EMP_NAME 사원이름
, E1.DEPT_CODE
, E1.MANAGER_ID
, E2.EMP_NAME 관리자이름
FROM EMPLOYEE E1
JOIN EMPLOYEE E2
ON(E1.MANAGER_ID = E2.EMP_ID);
--다중 JOIN : N개의 테이블을 조회할 때 사용
-- ANSI
-- 조인 순서 중요함
SELECT EMP_ID, EMP_NAME, DEPT_CODE, DEPT_TITLE, LOCAL_NAME
FROM EMPLOYEE
JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE);
-- 오라클 전용
-- 조인 순서 상관없음
SELECT EMP_ID, EMP_NAME, DEPT_CODE, DEPT_TITLE, LOCAL_NAME
FROM EMPLOYEE, DEPARTMENT, LOCATION
WHERE DEPT_CODE = DEPT_ID
AND LOCATION_ID = LOCAL_CODE
AND EMP_ID = 200;
-- 직급이 대리이면서 아시아 지역에 근무하는 직원을 조회
-- 사번, 이름, 직급명, 부서명, 근무지역명, 급여를 조회하세요
-- ANSI 표준
SELECT EMP_ID, EMP_NAME, JOB_NAME, DEPT_TITLE, LOCAL_NAME, SALARY
FROM EMPLOYEE E
JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE)
JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE)
WHERE JOB_NAME = '대리'
-- AND LOCAL_NAME = 'ASIA1';
AND LOCAL_NAME LIKE 'ASIA%';
-- 오라클 전용
SELECT EMP_ID, EMP_NAME, JOB_NAME, DEPT_TITLE, LOCAL_NAME, SALARY
FROM EMPLOYEE E, JOB J, DEPARTMENT, LOCATION
WHERE E.JOB_CODE = J.JOB_CODE
AND DEPT_CODE = DEPT_ID
AND LOCATION_ID = LOCAL_CODE
AND JOB_NAME = '대리'
AND LOCAL_NAME LIKE 'ASIA%';
/*
SUBQUERY(서브쿼리)
- 하나의 SQL문 안에 포함된 또다른 SQL문
- 메인쿼리(기존쿼리)를 위해 보조 역할을 하는 쿼리문
*/
-- 간단한 서브쿼리 예시1
-- 부서코드가 노옹철 사원과 같은 부서의 직원 명단을 조회해 보자
-- 1) 사원명이 노옹철인 사람의 부서코드 조회
SELECT
DEPT_CODE
FROM
EMPLOYEE
WHERE EMP_NAME = '노옹철'; --> D9 부서사람인 것을 알아냄
-- 2) 부서코드가 D9인 직원을 조회
SELECT
EMP_NAME
FROM
EMPLOYEE
WHERE
DEPT_CODE = 'D9';
-- > 위의 2개의 단계를 하나의 쿼리로 변경
SELECT
EMP_NAME
FROM
EMPLOYEE
WHERE
DEPT_CODE = (SELECT
DEPT_CODE
FROM
EMPLOYEE
WHERE EMP_NAME = '노옹철');
-- 간단한 서브쿼리 예시 2
-- 전 직원의 평균 급여보다 많은 급여를 받고 있는 직원의
-- 사번, 이름, 직급코드, 급여 조회
-- 1) 전 직원의 평균 급여 조회
SELECT AVG(SALARY)
FROM EMPLOYEE;
-- 2) 직원들 중 급여가 3047663원 이상인 사원들의 사번, 이름, 직급코드, 급여 조회
SELECT EMP_ID, EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY >= 3047663;
--> 위의 2단계를 하나의 쿼리로 수정
SELECT EMP_ID, EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY >= (SELECT AVG(SALARY)
FROM EMPLOYEE);
-- JOIN 연습문제
-- 1. 주민번호가 70년대 생이면서 성별이 여자이고,
-- 성이 전씨인 직원들의 사원명,주민번호,부서명,직급명을 조회하시오.
-- ANSI 표준
SELECT * FROM EMPLOYEE;
-- 오라클 전용
SELECT EMP_NAME, EMP_NO, DEPT_TITLE,JOB_NAME
FROM EMPLOYEE
WHERE EMP_NO = 70
-- 2. 가장 나이가 적은 직원의 사번, 사원명,나이,부서명, 직급명을 조회하시오.
-- ANSI표준
-- 오라클 전용
-- 3. 이름에 '형'자가 들어가는 직원들의 사번,사원명, 부서명을 조회하시오.
-- ANSI 표준
-- 오라클 전용
-- 4. 해외영업팀에 근무하는 사원명,직급명,부서코드,부서명을 조회하시오.
-- ANSI표준
-- 오라클전용
-- 5. 보너스포인트를 받는 직원들의 사원명,보너스포인트,부서명,근무지역명을 조회하시오.
-- ANSI 표준
-- 오라클 전용
댓글