-- 동일 부서에서 업무가 최종적으로 변경된 사원이 있는
-- 부서 사원들의 급여와 성과급을 포함한 지불금액의 순위를 구하시오.(강사님)
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME, E.SALARY + (E.SALARY * NVL(E.COMMISSION_PCT, 0)) AS RE_SAL,
RANK() OVER(PARTITION BY E.DEPARTMENT_ID
ORDER BY E.SALARY + (E.SALARY * NVL(E.COMMISSION_PCT, 0)) DESC) AS RNK
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
INNER JOIN (SELECT JH.EMPLOYEE_ID, JH.DEPARTMENT_ID,
COUNT(*) AS CNT, COUNT(E.EMPLOYEE_ID) AS ECNT
FROM JOB_HISTORY JH LEFT OUTER JOIN EMPLOYEES E
ON JH.EMPLOYEE_ID = E.EMPLOYEE_ID
AND JH.DEPARTMENT_ID = E.DEPARTMENT_ID
AND ((JH.START_DATE = E.HIRE_DATE AND JH.JOB_ID != E.JOB_ID)
OR JH.START_DATE != E.HIRE_DATE)
GROUP BY JH.EMPLOYEE_ID, JH.DEPARTMENT_ID) A
ON E.DEPARTMENT_ID = A.DEPARTMENT_ID
AND A.CNT = A.ECNT
IOT2 정상락
2018.03.09. 18:23
-- 동일 부서에서 업무가 최종적으로 변경된 사원이 있는 부서의 급여와 성과급을 포함한 지불금액 순위를 구하시오.
-- INNER JOIN써서 한거
SELECT E.FIRST_NAME,D.DEPARTMENT_NAME,
CASE WHEN E.COMMISSION_PCT IS NULL
THEN E.SALARY
ELSE E.SALARY+E.SALARY*E.COMMISSION_PCT
END AS CH_SAL,
RANK()OVER(PARTITION BY E.DEPARTMENT_ID ORDER BY CASE WHEN E.COMMISSION_PCT IS NULL
THEN E.SALARY
ELSE E.SALARY+E.SALARY*E.COMMISSION_PCT
END DESC) AS RNK
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID=D.DEPARTMENT_ID
WHERE E.DEPARTMENT_ID IN
(SELECT A.DEPARTMENT_ID
FROM
(SELECT A.EMPLOYEE_ID,A.JOB_ID,A.DEPARTMENT_ID
FROM
(SELECT JH.EMPLOYEE_ID,JH.START_DATE,E.DEPARTMENT_ID,JH.JOB_ID,
RANK()OVER(PARTITION BY JH.EMPLOYEE_ID ORDER BY START_DATE ASC) AS RNK
FROM JOB_HISTORY JH INNER JOIN EMPLOYEES E ON E.EMPLOYEE_ID=JH.EMPLOYEE_ID
WHERE JH.DEPARTMENT_ID=E.DEPARTMENT_ID)A
WHERE A.RNK=1)A
INNER JOIN
EMPLOYEES E ON A.EMPLOYEE_ID=E.EMPLOYEE_ID AND A.JOB_ID!=E.JOB_ID)
;
--OUTER JOIN 쓴거
SELECT E.FIRST_NAME,D.DEPARTMENT_NAME,
CASE WHEN E.COMMISSION_PCT IS NULL
THEN E.SALARY
ELSE E.SALARY+E.SALARY*E.COMMISSION_PCT
END AS CH_SAL,
RANK()OVER(PARTITION BY E.DEPARTMENT_ID ORDER BY CASE WHEN E.COMMISSION_PCT IS NULL
THEN E.SALARY
ELSE E.SALARY+E.SALARY*E.COMMISSION_PCT
END DESC) AS RNK
FROM EMPLOYEES E INNER JOIN
(SELECT A.DEPARTMENT_ID
FROM
(SELECT NVL(E.EMPLOYEE_ID,0)AS ACE,JH.DEPARTMENT_ID
FROM JOB_HISTORY JH LEFT OUTER JOIN EMPLOYEES E
ON E.EMPLOYEE_ID=JH.EMPLOYEE_ID AND E.JOB_ID!=JH.JOB_ID AND E.DEPARTMENT_ID=JH.DEPARTMENT_ID)A
WHERE A.ACE NOT IN
(SELECT NVL(E.EMPLOYEE_ID,0)
FROM (SELECT EMPLOYEE_ID,JOB_ID,DEPARTMENT_ID
FROM
(SELECT EMPLOYEE_ID,JOB_ID,START_DATE,DEPARTMENT_ID,
RANK()OVER(PARTITION BY EMPLOYEE_ID ORDER BY START_DATE) AS RNK
FROM JOB_HISTORY)A
WHERE A.RNK=1) JH
LEFT OUTER JOIN EMPLOYEES E
ON E.EMPLOYEE_ID=JH.EMPLOYEE_ID
AND E.JOB_ID=JH.JOB_ID
AND E.DEPARTMENT_ID=JH.DEPARTMENT_ID))A
ON E.DEPARTMENT_ID=A.DEPARTMENT_ID
INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID=D.DEPARTMENT_ID
;
--INNER JOIN을 LEFT JOIN으로 바꾼거
SELECT E.FIRST_NAME,D.DEPARTMENT_NAME,
CASE WHEN E.COMMISSION_PCT IS NULL
THEN E.SALARY
ELSE E.SALARY+E.SALARY*E.COMMISSION_PCT
END AS CH_SAL,
RANK()OVER(PARTITION BY E.DEPARTMENT_ID ORDER BY CASE WHEN E.COMMISSION_PCT IS NULL
THEN E.SALARY
ELSE E.SALARY+E.SALARY*E.COMMISSION_PCT
END DESC) AS RNK
FROM EMPLOYEES E LEFT OUTER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID=D.DEPARTMENT_ID
WHERE E.DEPARTMENT_ID IN
(SELECT A.DEPARTMENT_ID
FROM
(SELECT A.EMPLOYEE_ID,A.JOB_ID,A.DEPARTMENT_ID
FROM
(SELECT JH.EMPLOYEE_ID,JH.START_DATE,E.DEPARTMENT_ID,JH.JOB_ID,
RANK()OVER(PARTITION BY JH.EMPLOYEE_ID ORDER BY START_DATE ASC) AS RNK
FROM JOB_HISTORY JH LEFT OUTER JOIN EMPLOYEES E ON E.EMPLOYEE_ID=JH.EMPLOYEE_ID
WHERE JH.DEPARTMENT_ID=E.DEPARTMENT_ID)A
WHERE A.RNK=1)A
INNER JOIN
EMPLOYEES E ON A.EMPLOYEE_ID=E.EMPLOYEE_ID AND A.JOB_ID!=E.JOB_ID)
;
--INNER JOIN 3번 쓴거
SELECT E.FIRST_NAME,D.DEPARTMENT_NAME,
CASE WHEN E.COMMISSION_PCT IS NULL
THEN E.SALARY
ELSE E.SALARY+E.SALARY*E.COMMISSION_PCT
END AS CH_SAL,
RANK()OVER(PARTITION BY E.DEPARTMENT_ID ORDER BY CASE WHEN E.COMMISSION_PCT IS NULL
THEN E.SALARY
ELSE E.SALARY+E.SALARY*E.COMMISSION_PCT
END DESC) AS RNK
FROM EMPLOYEES E
INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID=D.DEPARTMENT_ID
WHERE E.DEPARTMENT_ID IN
(SELECT A.DEPARTMENT_ID
FROM (SELECT E.EMPLOYEE_ID,E.DEPARTMENT_ID
FROM JOB_HISTORY JH INNER JOIN EMPLOYEES E ON E.EMPLOYEE_ID=JH.EMPLOYEE_ID
WHERE JH.DEPARTMENT_ID=E.DEPARTMENT_ID)A
WHERE A.EMPLOYEE_ID !=
(SELECT JH.EMPLOYEE_ID
FROM JOB_HISTORY JH INNER JOIN EMPLOYEES E
ON E.EMPLOYEE_ID=JH.EMPLOYEE_ID AND E.HIRE_DATE=JH.START_DATE
AND E.JOB_ID=JH.JOB_ID
WHERE JH.DEPARTMENT_ID=E.DEPARTMENT_ID))
;
IoT2강원준
2018.03.10. 14:36
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME, E.SALARY + NVL(E.SALARY * E.COMMISSION_PCT, 0) AS C_SAL,
RANK() OVER(PARTITION BY E.DEPARTMENT_ID ORDER BY E.SALARY + NVL(E.SALARY * E.COMMISSION_PCT, 0) DESC) AS RNK
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
WHERE E.DEPARTMENT_ID IN (SELECT JH.DEPARTMENT_ID
FROM EMPLOYEES E INNER JOIN JOB_HISTORY JH
ON E.EMPLOYEE_ID = JH.EMPLOYEE_ID AND E.DEPARTMENT_ID = JH.DEPARTMENT_ID
INNER JOIN (SELECT JH.EMPLOYEE_ID
FROM EMPLOYEES E LEFT OUTER JOIN JOB_HISTORY JH
ON E.EMPLOYEE_ID = JH.EMPLOYEE_ID AND E.DEPARTMENT_ID = JH.DEPARTMENT_ID AND
JH.START_DATE = E.HIRE_DATE AND JH.JOB_ID = E.JOB_ID) E1
ON E1.EMPLOYEE_ID != E.EMPLOYEE_ID)
;
