Database 기본4

--ROLLUP, CUBE : 그룹함수를 사용하면 각 그룹별 합계구하고, 총계도 같이 출력됨(롤업은 마지막, 큐브는 처음)
SELECT NVL(DEPT_CODE,'총계'), SUM(SALARY)
FROM EMPLOYEE
WHERE DEPT_CODE IS NOT NULL
GROUP BY ROLLUP(DEPT_CODE);
 
-- 직책 별 인원 수와 총 인원 수를 구하시오
SELECT NVL(JOB_CODE, '총계'), COUNT(*)
FROM EMPLOYEE
WHERE JOB_CODE IS NOT NULL
GROUP BY ROLLUP (JOB_CODE);
 
-- 직책 별 인원 수와 총 인원 수를 구하시오(4명 이상인 부서만)
SELECT NVL(JOB_CODE, '총계'), COUNT(*)
FROM EMPLOYEE
GROUP BY ROLLUP (JOB_CODE)
HAVING COUNT(*)>=4;
 
-- ROLLUP, CUBE도 1개 이상의 컬럼을 설정
SELECT DEPT_CODE, JOB_CODE, SUM(SALARY)
FROM EMPLOYEE
WHERE DEPT_CODE IS NOT NULL
GROUP BY ROLLUP (DEPT_CODE, JOB_CODE);
 
-- GROUPING() : 집계된것이 ROLLUP, CUBE 로 된 것인지 확인하는 함수
-- 롤업큐브로 집계된거면 1 반환, 아니면 0
 
SELECT DEPT_CODE, COUNT(*), GROUPING(DEPT_CODE)
FROM EMPLOYEE
WHERE DEPT_CODE IS NOT NULL
GROUP BY ROLLUP (DEPT_CODE);
 
-- CUBE 2개 컬럼에서 GROUPING() 사용
SELECT DEPT_CODE, JOB_CODE, COUNT(*),
    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)=1 AND GROUPING(JOB_CODE)=1 THEN '총합계'
        ELSE '그룹별합계'
    END AS 구분
FROM EMPLOYEE
WHERE DEPT_CODE IS NOT NULL
GROUP BY CUBE(DEPT_CODE, JOB_CODE)
ORDER BY 1;
 
-- UNION : 2개 이상의 SELECT문의 합집합 (컬럼 수, 종류가 맞아야 한다).. 교집합은 INTERSECT
SELECT EMP_ID, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE='D5'
UNION
SELECT EMP_ID, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY>=3000000;
 
-- MINUS : 차집합.... 1번째 셀렉트문 - 1,2의 교집합
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 '개인' AS DIV, NVL(DEPT_CODE,'인턴'), SALARY
FROM EMPLOYEE
UNION
SELECT '부서', NVL(DEPT_CODE,'인턴'), SUM(SALARY)
FROM EMPLOYEE
UNION
SELECT '부서평균', NVL(DEPT_CODE, '인턴'), FLOOR(AVG(SALARY))
FROM EMPLOYEE
GROUP BY DEPT_CODE;
 
-- GROUPING SETS() : GROUP BY 절에 여러개의 그룹 조건을 기술할 수 있다.
 
-- JOIN
--송중기 부서명 출력하기
--ORACLE 구문
SELECT EMP_NAME, DEPT_TITLE
FROM EMPLOYEE, DEPARTMENT
WHERE DEPT_CODE=DEPT_ID AND EMP_NAME='송종기';
--ANSI 구문
SELECT EMP_NAME, DEPT_TITLE
FROM EMPLOYEE JOIN DEPARTMENT ON DEPT_CODE=DEPT_ID
WHERE EMP_NAME='송종기';
-- 지역명, 국가명 출력
SELECT LOCAL_NAME, NATIONAL_NAME
FROM LOCATION JOIN NATIONAL ON LOCATION.NATIONAL_CODE=NATIONAL.NATIONAL_CODE;
-- 부서명과 지역명 출력
SELECT DEPT_TITLE, LOCAL_NAME
FROM DEPARTMENT JOIN LOCATION ON LOCATION_ID=LOCAL_CODE;
-- INNER JOIN : 2개 이상 테이블 조인할 때 일치하는 값이 없는 행은 조인에서 제외됨(JOIN은 이게 디폴트)
-- OUTER JOIN : 일치하지 않는 값도 포함이 된다.
 
--  CROSS JOIN : 모든 ROW 다 매칭시켜서 테이블 구성
SELECT EMP_ID, DEPT_CODE, EMP_NAME, DEPT_TITLE
FROM EMPLOYEE CROSS JOIN DEPARTMENT
ORDER BY 1;
 
-- SELF JOIN : 서로 같
SELECT EMP_ID, MANAGER_ID
FROM EMPLOYEE;
 
-- 매니저가 있는 사원의 이름 및 매니저 이름 조회
SELECT E.EMP_NAME AS 사원명, M.EMP_NAME AS 매니저명
FROM EMPLOYEE E JOIN EMPLOYEE M ON E.MANAGER_ID=M.EMP_ID;
 
-- 매니저가 있는 사원은 매니저의 EMP_ID, 이름 출력
-- ㄴ 없으면 EMP_ID 000, 이름 없음 출력
SELECT E.EMP_NAME, NVL(M.EMP_ID,'000'), NVL(M.EMP_NAME,'없음')
FROM EMPLOYEE E
LEFT JOIN EMPLOYEE M ON E.MANAGER_ID=M.EMP_ID;
 
-- 다중 JOIN : 2개 이상의 테이블을 조인하는 것
 
-- 사원명, 부서명, 근무하고 있는 지역명 조회
SELECT EMP_NAME, DEPT_TITLE, LOCAL_NAME
FROM EMPLOYEE
JOIN DEPARTMENT ON (DEPT_CODE=DEPT_ID)
JOIN LOCATION ON (LOCATION_ID=LOCAL_CODE);