18.SQL 연습

실습 1

각 사원(employee)에 대해서 사번(employee_id), 이름(first_name), 부서명(department_name), 매니저(manager)의 이름(first_name)을 조회하시오.

SELECT E.EMPLOYEE_ID, E.FIRST_NAME, D.DEPARTMENT_NAME, M.FIRST_NAME
FROM EMPLOYEES E LEFT OUTER JOIN DEPARTMENTS D
	ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
	LEFT OUTER JOIN EMPLOYEES M
	ON E.MANAGER_ID = M.EMPLOYEE_ID

실습 2

자신의(EMPLOYEE_ID = 118) 매니저보다 채용일(hire_date)이 빠른 사원의 사번(employee_id), 성(last_name)과 채용일(hire_date)을 조회하세요.

SELECT EMPLOYEE_ID, LAST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE < (SELECT M.HIRE_DATE
					FROM EMPLOYEES E JOIN EMPLOYEES M
					ON E.MANAGER_ID = M.EMPLOYEE_ID
					WHERE E.EMPLOYEE_ID = 118);

실습 3

자신의(EMPLOYEE_ID = 118) 부서 평균 급여보다 연봉이 많은 사원의 사번(EMPLOYEE_ID), 성(LAST_NAME)과 연봉(SALARY)을 조회하세요.

SELECT EMPLOYEE_ID, LAST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > (SELECT AVG(SALARY)
				FROM EMPLOYEES
				WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID
										FROM EMPLOYEES
										WHERE EMPLOYEE_ID=118))

실습 4

입사일이 2005년 이전 직원들의 부서, 이름, 입사일, 부서 주소를 다음과 같이 출력 출력시 입사일 빠른 순으로 정렬하시오.
- 부서 이름 입사일 주소

SELECT D.DEPARTMENT_NAME AS "부서", E.FIRST_NAME || ' ' || E.LAST_NAME AS "이름", E.HIRE_DATE AS "입사일", L.STREET_ADDRESS || ' ' || L.CITY || ' ' || C.COUNTRY_NAME || ' ' || R.REGION_NAME AS "주소"
FROM EMPLOYEES E LEFT OUTER JOIN DEPARTMENTS D
	ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
	LEFT OUTER JOIN LOCATIONS L
	ON D.LOCATION_ID = L.LOCATION_ID
	LEFT OUTER JOIN COUNTRIES C
	ON L.COUNTRY_ID = C.COUNTRY_ID
	LEFT OUTER JOIN REGIONS R
	ON C.REGION_ID = R.REGION_ID
WHERE E.HIRE_DATE < '05/01/01'
ORDER BY E.HIRE_DATE
/

실습 5

직책(JOB TITLE)이 SALES MANAGER인 사원들의 입사년도와 입사년도(HIRE_DATE)별 평균 급여를 출력하시오
- 출력 시 년도를 기준으로 오름차순 정렬하시오.

SELECT TO_CHAR(HIRE_DATE, 'YYYY') AS "HIRE DATE", AVG(SALARY) AS AVG
FROM EMPLOYEES E JOIN JOBS JO
	ON E.JOB_ID = JO.JOB_ID
WHERE JOB_TITLE = 'Sales Manager'
GROUP BY TO_CHAR(HIRE_DATE, 'YYYY')
ORDER BY "HIRE DATE";

실습 6

각 도시(City)에 있는 모든 부서 직원들의 평균 급여를 조회하고자 한다.
- 평균급여가 가장 낮은 도시부터 도시명(city)과 평균연봉, 해당 도시의 직원수를 출력하시오.
- 단, 도시에 근무하는 직원이 최소 3명 이상 근무하는 지역만 조회하시오.

SELECT L.CITY, AVG(SALARY), COUNT(*)
FROM EMPLOYEES E JOIN DEPARTMENTS D
	ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
	JOIN LOCATIONS L
	ON D.LOCATION_ID = L.LOCATION_ID
GROUP BY L.CITY
HAVING COUNT(*) >2
ORDER BY AVG(E.SALARY);

실습 7

‘Public Accountant’의 직책(job_title)으로 과거에 근무한 적이 있는 모든 사원의 사번과 이름을 출력하시오.
- (현재 ‘Public Accountant’의 직책(job_title)으로 근무하는 사원은 고려 하지 않는다.)
- 다음과 같이 출력
  - 사번 이름 시작일 종료일

SELECT E.EMPLOYEE_ID AS "사번", FIRST_NAME || ' ' || LAST_NAME AS "이름", START_DATE "시작일", END_DATE "종료일"
FROM EMPLOYEES E JOIN JOB_HISTORY JOH
	ON E.EMPLOYEE_ID = JOH.EMPLOYEE_ID
	JOIN JOBS JO
	ON JOH.JOB_ID = JO.JOB_ID
WHERE JO.JOB_TITLE = 'Public Accountant';

실습 8

2007년 입사(HIRE_DATE)한 직원들의 사번(EMPLOYEE_ID), 이름(FIRST_NAME), 성(LAST_NAME), 부서명(DEPARTMENT_NAME)을 조회함
- 이때, 부서에 배치되지 않은 직원의경우, ‘'로 출력하시오.

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, NVL(DEPARTMENT_NAME, '<Not Assigned>') AS DEPT_NAME
FROM EMPLOYEES E LEFT OUTER JOIN DEPARTMENTS D
	ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
WHERE TO_CHAR(HIRE_DATE, 'YYYY') = '2007';

실습 9

부서별로 가장 적은 급여를 받고 있는 직원의 이름, 부서이름, 급여를 출력하시오.
- 이름은 LAST_NAME만 출력하며, 부서이름으로 오름차순 정렬하고,
- 부서가 같은 경우 이름을 기준으로 오름차순 정렬하여 출력합니다.

SELECT E.LAST_NAME, ED.DEPARTMENT_NAME, ED.SALARY
FROM EMPLOYEES E JOIN (SELECT E.DEPARTMENT_ID, DEPARTMENT_NAME, MIN(SALARY) AS SALARY
			FROM EMPLOYEES E JOIN DEPARTMENTS D
			ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
			GROUP BY E.DEPARTMENT_ID, DEPARTMENT_NAME) ED
	ON E.DEPARTMENT_ID = ED.DEPARTMENT_ID
WHERE E.SALARY = ED.SALARY
ORDER BY ED.DEPARTMENT_NAME, E.LAST_NAM;

SELECT E.LAST_NAME, ED.DEPARTMENT_NAME, ED.SALARY
FROM EMPLOYEES E JOIN (SELECT E.DEPARTMENT_ID, DEPARTMENT_NAME, MIN(SALARY) AS SALARY
			FROM EMPLOYEES E JOIN DEPARTMENTS D
			ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
			GROUP BY E.DEPARTMENT_ID, DEPARTMENT_NAME) ED
	ON E.DEPARTMENT_ID = ED.DEPARTMENT_ID AND E.SALARY = ED.SALARY
ORDER BY ED.DEPARTMENT_NAME, E.LAST_NAME;

실습 10

사원의 부서가 속한 도시(CITY)가 ‘Seattle’인 사원의 이름, 해당 사원의 매니저 이름, 사원의 부서이름을 출력하시오.
- 이때 사원의 매니저가 없을 경우 <없음>이라고 출력하시오. 이름은 LAST_NAME만 출력하며
- 사원의 이름을 오름차순으로 정렬하시오.

SELECT E.LAST_NAME, NVL(M.LAST_NAME, '없음'), D.DEPARTMENT_NAME
FROM EMPLOYEES E JOIN DEPARTMENTS D
	ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
	JOIN LOCATIONS L
	ON D.LOCATION_ID = L.LOCATION_ID AND L.CITY = 'Seattle'
	LEFT OUTER JOIN EMPLOYEES M
	ON E.MANAGER_ID = M.EMPLOYEE_ID
ORDER BY E.LAST_NAME;

SELECT E.E_NAME, NVL(E.M_NAME, '<없음>') AS LNAME, D.DEPARTMENT_NAME
FROM (SELECT A.LAST_NAME AS E_NAME, B.LAST_NAME AS M_NAME, A.DEPARTMENT_ID
     FROM EMPLOYEES A LEFT OUTER JOIN EMPLOYEES B
     ON A.MANAGER_ID = B.EMPLOYEE_ID
     ) E JOIN DEPARTMENTS D
     ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
     JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID
WHERE L.CITY = 'Seattle'
ORDER BY E.E_NAME;

실습 11

각 업무(JOB) 별로 연봉(SALARY)의 총합을 구하고자 한다. 연봉 총합이 가장 높은 업무부터 업무명(JOB_TITLE)과 연봉 총합을 내림차순으로 조회하시오. 단 연봉총합이 30,000 보다 큰 업무만 출력하시오.

SELECT JOB_TITLE, SUM(SALARY) TOTAL_SAL
FROM EMPLOYEES E JOIN JOBS J
	ON E.JOB_ID = J.JOB_ID
GROUP BY JOB_TITLE
HAVING SUM(SALARY) > 30000
ORDER BY TOTAL_SAL DESC;

‘Sales’ 부서에 속한 직원의 이름(FIRST_NAME), 급여(SALARY), 부서이름(DEPARTMENT_NAME)을 조회하시오.
- 단, 급여는 100번 부서의 평균보다 적게 받는 직원 정보만 출력되어야 한다.

SELECT FIRST_NAME, SALARY, DEPARTMENT_NAME
FROM EMPLOYEES E JOIN DEPARTMENTS D
	ON E.DEPARTMENT_ID = D.DEPARTMENT_ID AND D.DEPARTMENT_NAME='Sales'
WHERE SALARY < (SELECT AVG(SALARY)
               	FROM EMPLOYEES
               	WHERE DEPARTMENT_ID = 100);

SELECT E.RIST_NAME, E.SALARY, D.DEPARTMENT_NAME
FROM EMPLOYEES E JOIN DEPARTMENTS D
	ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
	CROSS JOIN (SELECT AVG(SALARY) AS SAL
               	FROM EMPLOYEES
               	WHERE DEPARTMENT_ID = 100) SAL100
WHERE D.DEPARTMENT_NAME = 'Sales'
AND E.SALARY < SAL100.SAL

실습 12

부서별 직원들의 최대, 최소, 평균급여(소수점 이하 절사)를 조회하되, 평균 급여가 ‘IT’ 부서의 평균급여보다 많고, ‘Sales’ 부서의 평균보다 적은 부서 정보만 출력하시오.

SELECT DEPARTMENT_NAME, MAX(SALARY), MIN(SALARY), TRUNC(AVG(SALARY)) AVG
FROM EMPLOYEES E JOIN DEPARTMENTS D
	ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY DEPARTMENT_NAME
HAVING TRUNC(AVG(SALARY)) > (SELECT AVG(SALARY)
             FROM EMPLOYEES E JOIN DEPARTMENTS D
             ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
             WHERE DEPARTMENT_NAME='IT')
        AND
        TRUNC(AVG(SALARY)) < (SELECT AVG(SALARY)
             FROM EMPLOYEES E JOIN DEPARTMENTS D
             ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
             WHERE DEPARTMENT_NAME='Sales');

SELECT DEPARTMENT_NAME, MAX(SALARY), MIN(SALARY), TRUNC(AVG(SALARY)) AVG
FROM EMPLOYEES E JOIN DEPARTMENTS D
	ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY DEPARTMENT_NAME
HAVING TRUNC(AVG(SALARY)) > (SELECT TRUNC(AVG(SALARY))
             FROM EMPLOYEES E JOIN DEPARTMENTS D
             ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
             WHERE DEPARTMENT_NAME='IT')
        AND
        TRUNC(AVG(SALARY)) < (SELECT TRUNC(AVG(SALARY))
             FROM EMPLOYEES E JOIN DEPARTMENTS D
             ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
             WHERE DEPARTMENT_NAME='Sales');