"
--UNION, UNION ALL

SELECT ENAME, JOB
FROM EMP
UNION
SELECT ENAME, JOB
FROM EMP
;

SELECT ENAME, JOB
FROM EMP
UNION ALL
SELECT ENAME, JOB
FROM EMP
;

-- 중복 제거(DISTINCT) - 해당 값을 제거하는 것이 아니라 로우를 제거하기 때문에 효율이 떨어짐

SELECT DISTINCT JOB
FROM EMP
;

-- 굳이 안써도 동일한 결과 나오도록 할 수 있음. 하지만 GROUP BY 자체는 집계함수를 쓰기 위해 만들어진 것이므로 이걸 통해서 중복제거하는 것은 좋지 않음.

SELECT JOB
FROM EMP
GROUP BY JOB
;

-- ROW_NUMBER

SELECT ENAME, HIREDATE,
       ROW_NUMBER() OVER(ORDER BY HIREDATE DESC) AS RNUM
FROM EMP
;

SELECT ENAME, HIREDATE
FROM (SELECT ENAME, HIREDATE,
           ROW_NUMBER() OVER(ORDER BY HIREDATE DESC) AS RNUM
      FROM EMP)
WHERE RNUM BETWEEN 1 AND 10
;

-- ROWNUM : 가져온 데이터의 취득 순서(오라클 전용 함수)

SELECT ENAME, HIREDATE, ROWNUM
FROM EMP
ORDER BY HIREDATE DESC
;

SELECT ENAME, HIREDATE, ROWNUM
FROM(SELECT ENAME, HIREDATE
     FROM EMP
     ORDER BY HIREDATE DESC)
;

-- 부하직원이 가장 많은 상급자를 구하시오.

SELECT E.FIRST_NAME
FROM EMPLOYEES E INNER JOIN(SELECT MANAGER_ID, COUNT(*) AS CNT,
                                    RANK() OVER(ORDER BY COUNT(*) DESC) AS RNK
                            FROM EMPLOYEES
                            GROUP BY MANAGER_ID) A
                         ON E.EMPLOYEE_ID = A.MANAGER_ID
                        AND A.RNK = 1
;

-- 업무나 부서 변동이 있었던 사원들의 급여평균을 구하시오.

SELECT AVG(SALARY) AS AVG_SALARY
FROM EMPLOYEES
WHERE EMPLOYEE_ID IN(SELECT EMPLOYEE_ID
                       FROM JOB_HISTORY)
;

-- 사원수 1, 2등인 부서들의 급여평균을 구하고 그 평균의 차이를 구하시오.

SELECT ROUND(MAX(AVG_SALARY) - MIN(AVG_SALARY), 2) AS CHA
FROM(SELECT DEPARTMENT_ID, COUNT(*) AS CNT, AVG(SALARY) AS AVG_SALARY,
             RANK() OVER(ORDER BY COUNT(*) DESC) AS RNK
     FROM EMPLOYEES
     GROUP BY DEPARTMENT_ID)
WHERE RNK IN(1, 2)
;

SELECT A1.AVG_SALARY - A2.AVG_SALARY
FROM (SELECT DEPARTMENT_ID, AVG(SALARY) AS AVG_SALARY,
              RANK() OVER(ORDER BY COUNT(*) DESC) AS RNK
      FROM EMPLOYEES
      GROUP BY DEPARTMENT_ID) A1 INNER JOIN (SELECT DEPARTMENT_ID, AVG(SALARY) AS AVG_SALARY,
                                                     RANK() OVER(ORDER BY COUNT(*) DESC) AS RNK
                                             FROM EMPLOYEES
                                             GROUP BY DEPARTMENT_ID) A2
                                         ON A1.RNK = 1
                                        AND A2.RNK = 2
;

"