1.
업무별 사원 중 업무 표준 급여 평균값 이하인 사원들의 급여를 20%인상하여
--해당사원들의 변경 전/후의 급여를 출력하시오.
--업무 표준 급여 평균 : 표준 최소급여 + 표준 최대급여 / 2
SELECT J.JOB_ID, E.FIRST_NAME, (MIN_SALARY + MAX_SALARY)/2,E.SALARY AS AVG_SAL,
CASE
WHEN E.SALARY <= (MIN_SALARY + MAX_SALARY)/2
THEN E.SALARY * 1.2
ELSE E.SALARY
END RE_SAL
FROM JOBS J INNER JOIN EMPLOYEES E ON J.JOB_ID = E.JOB_ID
;
--업무 표준 급여 평균값 이하인 사원의 급여
SELECT (MIN_SALARY + MAX_SALARY) /2
FROM JOBS
;
-- 업무별 사원
SELECT E.JOB_ID
FROM EMPLOYEES E
GROUP BY E.JOB_ID
;
--정리
SELECT E.FIRST_NAME,E.SALARY*1.2, E.SALARY
FROM
(SELECT JOB_ID, (MIN_SALARY + MAX_SALARY) /2 AS AVG_SAL
FROM JOBS)A INNER JOIN EMPLOYEES E ON A.JOB_ID = E.JOB_ID --업무별
WHERE E.SALARY <= A.AVG_SAL
A 테이블 :업무 표준 급여 평균값 이하인 사원의 급여
E 테이블 : 업무별 사원
;
급여의 평균값이상일때 사원의 도시별로 지역을 구하시오.
________________________________________________________________________________
11명 문제
1.이직을 가장 많이 한사람의 현재 이름 직업 급여 근무도시 급여순위를 출력하시오.
SELECT E.EMPLOYEE_ID,E.FIRST_NAME,E.JOB_ID,E.SALARY,L.CITY,A.RNK
FROM
(SELECT E.EMPLOYEE_ID,E.DEPARTMENT_ID,
RANK() OVER(ORDER BY COUNT(*) DESC ) AS RNK
FROM JOB_HISTORY J INNER JOIN EMPLOYEES E
ON J.EMPLOYEE_ID = E.EMPLOYEE_ID
GROUP BY E.EMPLOYEE_ID,E.DEPARTMENT_ID)A INNER JOIN EMPLOYEES E ON A.EMPLOYEE_ID = E.EMPLOYEE_ID
INNER JOIN DEPARTMENTS D ON A.DEPARTMENT_ID = D.DEPARTMENT_ID
INNER JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID
WHERE A.RNK =1
2.
--옥스포드의 세일즈 부서 커미션을 지급하고 커미션을 포함한 급여를 구하고
--전체 사원의 변경 전/후 급여를 출력하라. (출력: 이름,변경전 급여,변경 후 급여)
SELECT DISTINCT(B.FIRST_NAME), B.SALARY AS 변경전급여, A.SAL AS 변경후급여
FROM
(SELECT C.EMPLOYEE_ID, C.FIRST_NAME, C.SALARY + (C.SALARY * NVL(COMMISSION_PCT,0)) AS SAL
FROM LOCATIONS A INNER JOIN DEPARTMENTS B ON A.LOCATION_ID = B.LOCATION_ID
INNER JOIN EMPLOYEES C ON B.DEPARTMENT_ID = C.DEPARTMENT_ID
WHERE A.CITY = 'Oxford' AND B.DEPARTMENT_NAME = 'Sales')
;
3.--대륙별로 최고급여를 받는 사람과 최저급여를 받는 사람의 차이를 구하고, 그 차이값이 가장 큰 대륙의 연봉 탑 사원의 정보를 출력하시오.
--출력: 대륙, 도시, 부서, 이름, 급여
--대륙별로 최고급여를 받는 사람과 최저급여를 받는 사람의 차이
SELECT REGION_NAME, MAX(E.SALARY)-MIN(E.SALARY)
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
INNER JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID
INNER JOIN COUNTRIES C ON C.COUNTRY_ID = L.COUNTRY_ID
INNER JOIN REGIONS R ON R.REGION_ID = C.REGION_ID
GROUP BY R.REGION_NAME
;
-- 그 차이값이 가장 큰 대륙의 연봉 탑 사원의 정보를 출력하시오.
SELECT *
FROM
(SELECT A.REGION_NAME,B.FIRST_NAME,B.SALARY, B.SALARY*12 AS YEAR_SAL, RANK() OVER(ORDER BY B.SALARY*12 DESC) AS Y_SAL
FROM
(SELECT REGION_NAME, MAX(E.SALARY)-MIN(E.SALARY) AS EACH
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
INNER JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID
INNER JOIN COUNTRIES C ON C.COUNTRY_ID = L.COUNTRY_ID
INNER JOIN REGIONS R ON R.REGION_ID = C.REGION_ID
GROUP BY R.REGION_NAME)A INNER JOIN EMPLOYEES B ON 1=1
WHERE REGION_NAME = 'Americas') C
WHERE C.Y_SAL =1
4.-- 유럽에 근무하는 사원의 급여를 20% 인상하고 유럽인 급여 평균을 내어, 평균 보다 유럽에 근무하는 사원을 제외한 사원들의 급여가 더 크면 20% 삭감 시킨 후,
-- 전체 부서별 급여 합계를 구하라.
SELECT SUM(CASE WHEN E.SALARY > E1.AVG_EURO AND R.REGION_NAME != 'Europe'
THEN E.SALARY * 0.8
ELSE E.SALARY
END) AS RE_SAL, D.DEPARTMENT_NAME
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
INNER JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID
INNER JOIN COUNTRIES C ON C.COUNTRY_ID = L.COUNTRY_ID
INNER JOIN REGIONS R ON R.REGION_ID = C.REGION_ID
INNER JOIN (SELECT AVG(E.SALARY *1.2) AS AVG_EURO
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
INNER JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID
INNER JOIN COUNTRIES C ON C.COUNTRY_ID = L.COUNTRY_ID
INNER JOIN REGIONS R ON R.REGION_ID = C.REGION_ID
WHERE R.REGION_NAME = 'Europe') E1
ON 1=1
GROUP BY D.DEPARTMENT_NAME
;
5.-- 직업별 급여 평균 최대값의 직업을 구하고 해당 직업의 직업이름을 출력하시오.
SELECT JOB_TITLE
FROM JOBS
WHERE JOB_ID =
( SELECT JOB_ID
FROM EMPLOYEES
GROUP BY JOB_ID
HAVING AVG(SALARY) =
(SELECT MAX(AVG(SALARY))
FROM EMPLOYEES
GROUP BY JOB_ID))
;
6.--LIKE 사용없이 FIRAST_NAME에 A가 2개 들어가고 2분기에 입사한 사원들중 급여가 5000 이하인 사람중 커미션이 NULL인사람의 급여를 50% 올려주세요
--출력:사원이름.입사일.인상된급여
SELECT E.FIRST_NAME, E.HIRE_DATE, E.SALARY*1.5 AS RE_SAL
FROM EMPLOYEES E
WHERE TO_CHAR(HIRE_DATE, 'Q')=2
AND INSTR(UPPER(E.FIRST_NAME),'A',1,2)>0
AND E.SALARY <= 5000
AND COMMISSION_PCT IS NULL
AND NOT INSTR(UPPER(E.FIRST_NAME), 'A', 3, 2) >= 1
7--이직을 가장많이한 사원들이 근무하는지역을 구하고, 그지역이 속해있는 대륙을 구하세요
--이직을 가장많이한 사원 근무하는 지역을 구하고
SELECT E.FIRST_NAME,L.CITY
FROM
(SELECT EMPLOYEE_ID,COUNT(*),RANK() OVER(ORDER BY COUNT(*) DESC) RNK
FROM JOB_HISTORY
GROUP BY EMPLOYEE_ID)A INNER JOIN EMPLOYEES E ON A.EMPLOYEE_ID = E.EMPLOYEE_ID
INNER JOIN DEPARTMENTS D ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
INNER JOIN LOCATIONS L ON L.LOCATION_ID = D.LOCATION_ID
WHERE A.RNK =1
;
--그지역이 속해있는 대륙을 구하세요
SELECT DISTINCT R.REGION_NAME
FROM
(SELECT EMPLOYEE_ID,COUNT(*),RANK() OVER(ORDER BY COUNT(*) DESC) RNK
FROM JOB_HISTORY
GROUP BY EMPLOYEE_ID)A INNER JOIN EMPLOYEES E ON A.EMPLOYEE_ID = E.EMPLOYEE_ID
INNER JOIN DEPARTMENTS D ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
INNER JOIN LOCATIONS L ON L.LOCATION_ID = D.LOCATION_ID
INNER JOIN COUNTRIES C ON L.COUNTRY_ID = C.COUNTRY_ID
INNER JOIN REGIONS R ON R.REGION_ID = C.REGION_ID
WHERE A.RNK =1
;
8/*도시가 시애틀인 사원들이 출퇴근이 힘들어하여 급여를 70%를 인상해주기로 했다.
하지만 이에 반발을 느낀 옥스포드의 사원들이 항의를 하여 이곳 또한 70%의 급여를 인상하였다.
이들의 목록을 순위로 매겨 출력하시오.
출력내용 : 이름 , 부서이름,상세주소(지역주소코드 나라등과 같은 모든 정보),급여별순위,급여
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME, L.*,
CASE WHEN L.CITY = 'Seattle'
THEN E.SALARY *1.7
WHEN L.CITY = 'Oxford'
THEN E.SALARY * 1.7
ELSE E.SALARY
END AS RE_SAL,
RANK () OVER(ORDER BY CASE WHEN L.CITY = 'Seattle'
THEN E.SALARY *1.7
WHEN L.CITY = 'Oxford'
THEN E.SALARY * 1.7
ELSE E.SALARY
END DESC) AS RNK
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
INNER JOIN LOCATIONS L ON L.LOCATION_ID = D.LOCATION_ID;
;
*/
9--도시별 급여를 가장 많이 받는 사람을 구하고 그 사람들 중 06년도 입사자를 구하시오.
SELECT *
FROM(
SELECT RANK() OVER(PARTITION BY L.CITY ORDER BY E.SALARY DESC) AS RNK,
E.FIRST_NAME,E.HIRE_DATE,E.SALARY, L.CITY
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
INNER JOIN LOCATIONS L
ON D.LOCATION_ID = L.LOCATION_ID
)
WHERE RNK = 1 AND TO_CHAR(HIRE_DATE,'YY') = 06
10--입사일이 02년인 사원들의 급여 평균을 구하여 02년입사자 급여평균 보다 높으면 300%인상하여 사원의 전체순위를 구하시오.
--입사일이 02년인 사원들의 급여 평균을 구하여,
SELECT AVG(E.SALARY) AS AVG_SAL
FROM EMPLOYEES E
WHERE TO_CHAR(HIRE_DATE,'YY') =02;
--02년 입사자 급여평균 보다 높으면 300%인상하여 사원의 전체순위를 구하시오.
SELECT D.FIRST_NAME,D.EMPLOYEE_ID,D.SALARY,D.SALARY*4 AS RE_SAL,
RANK() OVER(ORDER BY D.SALARY*4 DESC) AS RNK
FROM
(SELECT AVG(E.SALARY) AS AVG_SAL
FROM EMPLOYEES E
WHERE TO_CHAR(HIRE_DATE,'YY') =02)A
INNER
JOIN EMPLOYEES D ON D.SALARY > A.AVG_SAL
;
11.
사원 중 05년도에 입사한 사원의 평균 급여를 구하고 그 구한 평균값을 90%삭감 후 04년도에 입사한 사원의 급여의 총 합을 구하고 그 값을 80%삭감
후 서로의 값을 더하여 총 사원의 급여보다 낮을 시 50% 인상한 값을 출력하라.(반올림 하세용)
--사원 중 05년도에 입사한 사원의 평균 급여
--SELECT AVG(E.SALARY) * 0.1 AS AVG_SAL
--FROM EMPLOYEES E
--WHERE TO_CHAR(HIRE_DATE, 'YY') = 05
--;
--
---- 04년도에 입사한 사원의 급여의 총 합을 구하고 그 값을 80%삭감후
--
--SELECT SUM(E.SALARY)*0.2 AS SUM_SAL
--FROM EMPLOYEES E
--WHERE TO_CHAR(HIRE_DATE, 'YY') = 04;
-- 서로의 값을 더하여 총 사원의 급여보다 낮을 시 50% 인상한 값을 출력하라.
SELECT ABS(A.AVG_SAL + B.SUM_SAL) AS EACH
FROM
(SELECT ROUND(AVG(E.SALARY)) * 0.1 AS AVG_SAL
FROM EMPLOYEES E
WHERE TO_CHAR(HIRE_DATE, 'YY') = 05)A
INNER
JOIN
(SELECT SUM(E.SALARY)*0.2 AS SUM_SAL
FROM EMPLOYEES E
WHERE TO_CHAR(HIRE_DATE, 'YY') = 04)B ON 1=1;
--총 사원의 급여보다 낮을 시 50% 인상한 값을 출력하라
SELECT C.EACH,E.EMPLOYEE_ID,E.FIRST_NAME,E.LAST_NAME,E.SALARY,E.SALARY*1.5 AS RE_SAL
FROM
(SELECT ABS(A.AVG_SAL + B.SUM_SAL) AS EACH
FROM
(SELECT ROUND(AVG(E.SALARY)) * 0.1 AS AVG_SAL
FROM EMPLOYEES E
WHERE TO_CHAR(HIRE_DATE, 'YY') = 05)A
INNER
JOIN
(SELECT SUM(E.SALARY)*0.2 AS SUM_SAL
FROM EMPLOYEES E
WHERE TO_CHAR(HIRE_DATE, 'YY') = 04)B ON 1=1)C INNER JOIN EMPLOYEES E ON C.EACH <E.SALARY
