오라클 11g hr계정 연습문제

--오라클 11g hr계정 연습문제
 
--1. 직책(Job Title)이 Sales Manager인 사원들의 입사년도와 입사년도(hire_date)별 평균 급여를 출력하시오. 
--   출력 시 년도를 기준으로 오름차순 정렬하시오. 
SELECT 
       TO_CHAR(e.HIRE_DATE, 'YYYY') AS HIRE_DATE, AVG(e.salary) AS AVG
  FROM EMPLOYEES e, JOBS j
 WHERE e.JOB_ID = j.JOB_ID AND j.JOB_TITLE = 'Sales Manager' 
 GROUP BY TO_CHAR(e.HIRE_DATE,'YYYY')
 ORDER BY TO_CHAR(e.HIRE_DATE,'YYYY') ASC
;
 

--2. 각 도시(city)에 있는 모든 부서 직원들의 평균급여를 조회하고자 한다. 
--   평균급여가 가장 낮은 도시부터 도시명(city)과 평균연봉, 해당 도시의 직원수를 출력하시오. 
--   단, 도시에 근 무하는 직원이 10명 이상인 곳은 제외하고 조회하시오.
SELECT 
       l.CITY , AVG(e.SALARY) , COUNT(*)
  FROM EMPLOYEES e, LOCATIONS l, DEPARTMENTS d
 WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID AND d.LOCATION_ID = l.LOCATION_ID
 GROUP BY l.CITY
HAVING COUNT(*) < 10
 ORDER BY AVG(e.SALARY) ASC
;
  

--3. ‘Public  Accountant’의 직책(job_title)으로 과거에 근무한 적이 있는 모든 사원의 사번과 이름을 출력하시오. 
--   (현재 ‘Public Accountant’의 직책(job_title)으로 근무하는 사원은 고려 하지 않는다.)      
--   이름은 first_name, last_name을 아래의 실행결과와 같이 출력한다.
SELECT 
       e.EMPLOYEE_ID , CONCAT(CONCAT(e.FIRST_NAME,' '), e.LAST_NAME) 
  FROM EMPLOYEES e, JOBS j, JOB_HISTORY h
 WHERE j.JOB_TITLE = 'Public Accountant' AND h.JOB_ID = j.JOB_ID AND e.EMPLOYEE_ID = h.EMPLOYEE_ID
;
 

--4. 자신의 매니저보다 연봉(salary)를 많이 받는 직원들의 성(last_name)과 연봉(salary)를 출 력하시오. 
SELECT
       e.LAST_NAME, e.SALARY
  FROM EMPLOYEES e, EMPLOYEES m
 WHERE e.MANAGER_ID = m.EMPLOYEE_ID AND e.SALARY > m.SALARY   
;      
 

--5. 2007년에 입사(hire_date)한 직원들의 사번(employee_id), 이름(first_name), 성(last_name), 
--   부서명(department_name)을 조회합니다.  
--   이때, 부서에 배치되지 않은 직원의 경우, ‘<Not Assigned>’로 출력하시오. 
SELECT 
       e.EMPLOYEE_ID , e.LAST_NAME , e.FIRST_NAME , NVL(d.DEPARTMENT_NAME, '<Not Assigned>')
  FROM EMPLOYEES e
  LEFT JOIN DEPARTMENTS d
  ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
  WHERE TO_CHAR(e.HIRE_DATE, 'YYYY') = '2007'
  ORDER BY e.EMPLOYEE_ID ASC
;  

 
--6. 업무명(job_title)이 ‘Sales Representative’인 직원 중에서 연봉(salary)이 9,000이상, 10,000 이하인 
--   직원들의 이름(first_name), 성(last_name)과 연봉(salary)를 출력하시오
SELECT 
       e.FIRST_NAME, e.LAST_NAME, e.SALARY
  FROM EMPLOYEES e, JOBS j
  WHERE e.JOB_ID = j.JOB_ID AND e.SALARY BETWEEN 9000 AND 10000 AND j.JOB_TITLE = 'Sales Representative'
;
 

--7. 부서별로 가장 적은 급여를 받고 있는 직원의 이름, 부서이름, 급여를 출력하시오. 
--   이름은 last_name만 출력하며, 부서이름으로 오름차순 정렬하고, 
--   부서가 같은 경우 이름을 기준 으로 오름차순 정렬하여 출력합니다. 
SELECT 
       E.LAST_NAME, A.*
  FROM EMPLOYEES E, 
      (
      SELECT 
             d.DEPARTMENT_NAME, MIN(e.SALARY) AS MIN_SALARY
        FROM EMPLOYEES e, DEPARTMENTS d 
       WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID 
       GROUP BY d.DEPARTMENT_NAME
      ) A 
 WHERE E.SALARY = A.MIN_SALARY
 ORDER BY A.DEPARTMENT_NAME ASC, E.LAST_NAME ASC
;
 

--8. EMPLOYEES 테이블에서 급여를 많이 받는 순서대로 조회했을 때 결과처럼 6번째부터 10 번째까지 
--   5명의 last_name, first_name, salary를 조회하는 sql문장을 작성하시오.
SELECT 
       * 
  FROM
       (
       SELECT 
               RANK() OVER (ORDER BY TRUNC(SALARY, -1) DESC) AS RANKING, LAST_NAME, 
               FIRST_NAME, TRUNC(SALARY, -1) AS SALARY
          FROM EMPLOYEES 
         ORDER BY SALARY DESC
       )
  WHERE RANKING 
  BETWEEN 6 AND 10
 ;
 

--9. 사원의 부서가 속한 도시(city)가 ‘Seattle’인 사원의 이름, 해당 사원의 매니저 이름, 사원 의 부서이름을 출력하시오. 
--   이때 사원의 매니저가 없을 경우 ‘<없음>’이라고 출력하시오. 이름은 last_name만 출력하며, 
--   사원의 이름을 오름차순으로 정렬하시오. 
SELECT 
       E.E_NAME, NVL(E.M_NAME, '<없음>'), D.DEPARTMENT_NAME
  FROM 
      (
       SELECT 
              A.LAST_NAME AS E_NAME, B.LAST_NAME AS M_NAME, A.DEPARTMENT_ID 
         FROM EMPLOYEES A 
         LEFT JOIN EMPLOYEES B 
           ON A.MANAGER_ID = B.EMPLOYEE_ID
      ) E, DEPARTMENTS D, LOCATIONS L
 WHERE L.CITY = 'Seattle' AND E.DEPARTMENT_ID = D.DEPARTMENT_ID AND D.LOCATION_ID = L.LOCATION_ID
 ORDER BY E.E_NAME ASC
;  

 
--10. 각 업무(job) 별로 연봉(salary)의 총합을 구하고자 한다. 연봉 총합이 가장 높은 업무부터 
--    업무명(job_title)과 연봉 총합을 조회하시오. 단 연봉총합이 30,000보다 큰 업무만 출력하시오. 
SELECT J.JOB_TITLE, SUM(E.SALARY)
  FROM JOBS J, EMPLOYEES E
 WHERE E.JOB_ID = J.JOB_ID
 GROUP BY J.JOB_TITLE
 HAVING SUM(E.SALARY) > 30000
 ORDER BY SUM(E.SALARY) DESC
;
 

--11. 각 사원(employee)에 대해서 사번(employee_id), 이름(first_name), 업무명(job_title), 
--    부서 명(department_name)을 조회하시오. 
--    단 도시명(city)이 ‘Seattle’인 지역(location)의 부서 (department)에 근무하는 직원을 사원번호 오름차순순으로 출력하시오.
SELECT 
       E.EMPLOYEE_ID, E.FIRST_NAME, J.JOB_TITLE, D.DEPARTMENT_NAME
  FROM EMPLOYEES E, DEPARTMENTS D, LOCATIONS L, JOBS J
 WHERE L.CITY = 'Seattle' AND J.JOB_ID = E.JOB_ID AND E.DEPARTMENT_ID = D.DEPARTMENT_ID 
       AND D.LOCATION_ID = L.LOCATION_ID
 ORDER BY E.EMPLOYEE_ID ASC
;
 

--12. 2001~20003년사이에 입사한 직원의 이름(first_name), 입사일(hire_date), 관리자사번 (employee_id), 
--    관리자 이름(fist_name)을 조회합니다. 단, 관리자가 없는 사원정보도 출력 결과에 포함시켜 출력한다.
SELECT 
       E.FIRST_NAME, TO_CHAR(E.HIRE_DATE, 'YYYY-MM-DD AM HH12:MI:SS') AS HIRE_DATE, E.MANAGER_ID, M.FIRST_NAME
  FROM EMPLOYEES E LEFT JOIN EMPLOYEES M ON
  E.MANAGER_ID = M.EMPLOYEE_ID WHERE TO_CHAR(E.HIRE_DATE, 'YYYY') BETWEEN '2001' AND '2003'
;
 

--13. ‘Sales’ 부서에 속한 직원의 이름(first_name), 급여(salary), 부서이름(department_name)을 조회하시오. 
--    단, 급여는 100번 부서의 평균보다 적게 받는 직원 정보만 출력되어야 한다. 
SELECT 
        E.FIRST_NAME, E.SALARY, D.DEPARTMENT_NAME 
  FROM EMPLOYEES E, DEPARTMENTS D
 WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND D.DEPARTMENT_NAME = 'Sales'
   AND E.SALARY < (
                   SELECT 
                          AVG(SALARY) 
                     FROM EMPLOYEES 
                    WHERE DEPARTMENT_ID = 100
                  )
; 
 

--14. Employees 테이블에서 입사한달(hire_date)별로 인원수를 조회하시오.
SELECT 
       CONCAT(TO_CHAR(E.HIRE_DATE, 'MM'), '월') AS HIRE_DATE, COUNT(*)
  FROM EMPLOYEES E
  GROUP BY TO_CHAR(E.HIRE_DATE, 'MM')
  ORDER BY TO_CHAR(E.HIRE_DATE, 'MM') ASC
;  
  

--15. 부서별 직원들의 최대, 최소, 평균급여를 조회하되, 
--    평균급여가 ‘IT’ 부서의 평균급여보다 많고, ‘Sales’ 부서의 평균보다 적은 부서 정보만 출력하시오. 
SELECT 
       D.DEPARTMENT_NAME, MAX(E.SALARY), MIN(E.SALARY), TRUNC(AVG(E.SALARY), 0)
  FROM EMPLOYEES E, DEPARTMENTS D
 WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
 GROUP BY D.DEPARTMENT_NAME
HAVING TRUNC(AVG(E.SALARY), 0) > (
                                  SELECT 
                                         TRUNC(AVG(E.SALARY), 0)
                                    FROM EMPLOYEES E, DEPARTMENTS D
                                   WHERE D.DEPARTMENT_NAME = 'IT' AND D.DEPARTMENT_ID = E.DEPARTMENT_ID
                                   )
   AND  TRUNC(AVG(E.SALARY), 0) < (
                                   SELECT 
                                          TRUNC(AVG(E.SALARY), 0)
                                     FROM EMPLOYEES E, DEPARTMENTS D
                                    WHERE D.DEPARTMENT_NAME = 'Sales' AND D.DEPARTMENT_ID = E.DEPARTMENT_ID)
;

 
--16. 각 부서별로 직원이 한명만 있는 부서만 조회하시오. 
--    단, 직원이 없는 부서에 대해서는 ‘<신생부서>’라는 문자열이 출력되도록 하고,
--    출력결과는 다음과 같이 부서명이 내림차순 으로 정렬되어야한다. 
SELECT 
       NVL(D.DEPARTMENT_NAME, '<신생부서>') AS DEPARTMENT_NAME , COUNT(*)
  FROM EMPLOYEES E LEFT JOIN DEPARTMENTS D
  ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
 GROUP BY D.DEPARTMENT_NAME
HAVING COUNT(*) = 1
 ORDER BY D.DEPARTMENT_NAME DESC
;
 

--17. 부서별 입사월별 직원수를 출력하시오. 
--    단, 직원수가 5명 이상인 부서만 출력되어야 하며 출력결과는 부서이름 순으로 한다.
SELECT 
       D.DEPARTMENT_NAME, TO_CHAR(E.HIRE_DATE, 'MON', 'NLS_DATE_LANGUAGE=ENGLISH') AS HIRE_DATE, COUNT(*)
  FROM DEPARTMENTS D, EMPLOYEES E
 WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID 
 GROUP BY D.DEPARTMENT_NAME, TO_CHAR(E.HIRE_DATE, 'MON', 'NLS_DATE_LANGUAGE=ENGLISH')
HAVING COUNT (*) >= 5
 ORDER BY D.DEPARTMENT_NAME ASC;
 

--18. 국가(country_name) 별 도시(city)별 직원수를 조회하시오. 
--    단, 부서에 속해있지 않은 직원 이 있기 때문에 106명의 직원만 출력이 된다. 
--    부서정보가 없는 직원은 국가명과 도시명 대신에 ‘<부서없음>’이 출력되도록 하여 107명 모두 출력되게 한다.
SELECT 
       NVL(C.COUNTRY_NAME, '<부서없음>') AS COUNTRY_NAME, NVL(L.CITY, '<부서없음>') AS CITY, COUNT(*)
  FROM EMPLOYEES E LEFT JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID 
                   LEFT JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID
                   LEFT JOIN COUNTRIES C ON L.COUNTRY_ID = C.COUNTRY_ID 
 GROUP BY COUNTRY_NAME, CITY
 ORDER BY COUNTRY_NAME ASC, CITY ASC
;  

 
--19. 각 부서별 최대 급여자의 아이디(employee_id), 이름(first_name), 급여(salary)를 출력하시오. 
--    단, 최대 급여자가 속한 부서의 평균급여를 마지막으로 출력하여 평균급여와 비교할 수 있게 할 것.
SELECT 
       E.EMPLOYEE_ID, E.FIRST_NAME, A.SALARY, A.AVG
  FROM 
       EMPLOYEES E, (
                      SELECT 
                             E.DEPARTMENT_ID, MAX(E.SALARY) AS SALARY, TRUNC(AVG(E.SALARY),2) AS AVG
                        FROM 
                             EMPLOYEES E, DEPARTMENTS D
                       WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
                       GROUP BY E.DEPARTMENT_ID
                    ) A
 WHERE E.SALARY = A.SALARY AND E.DEPARTMENT_ID = A.DEPARTMENT_ID
 ORDER BY E.EMPLOYEE_ID ASC
;
 

--20. 커미션(commission_pct)별 직원수를 조회하시오. 
--    커미션은 아래실행결과처럼 0.2, 0.25는 모두 .2로, 0.3, 0.35는 .3 형태로 출력되어야 한다. 
--    단, 커미션 정보가 없는 직원들도 있는 데 커미션이 없는 직원 그룹은 ‘<커미션 없음>’이 출력되게 한다.
SELECT 
       NVL(TO_CHAR(TRUNC(E.COMMISSION_PCT,1)), '<커미션없음>') AS COMMISSION, COUNT(*)
  FROM 
       EMPLOYEES E 
 GROUP BY NVL(TO_CHAR(TRUNC(E.COMMISSION_PCT,1)), '<커미션없음>')
 ORDER BY NVL(TO_CHAR(TRUNC(E.COMMISSION_PCT,1)), '<커미션없음>') DESC
;
 

--21. 커미션(commission_pct)을 가장 많이 받은 상위 4명의 부서명(department_name), 
--    직원명 (first_name), 급여(salary), 커미션(commission_pct) 정보를 조회하시오. 
--    출력결과는 커미션 을 많이 받는 순서로 출력하되 동일한 커미션에 대해서는 급여가 높은 직원이 먼저 출력 되게 한다.
SELECT 
       * 
  FROM
       (
        SELECT 
               D.DEPARTMENT_NAME, E.FIRST_NAME, E.SALARY, E.COMMISSION_PCT
          FROM 
               EMPLOYEES E, DEPARTMENTS D
         WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
         ORDER BY E.COMMISSION_PCT DESC NULLS LAST, E.SALARY DESC
       )
 WHERE ROWNUM <= 4
;