*oracle 데이터베이스 자료인 hr 스키마를 활용해서 문제 풀이
SQL Server(MSSQL 구문으로 작성)
--1. 직책 (Job 이 Sales Manager 인 사원들의 입사년도와 입사년도 (hire_ date)별 평균 급여를 출력하시오 .
-- 출력 시 년도를 기준으로 오름차순 정렬하시오.
select year(e.hire_date) 입사년도, avg(e.salary) 평균급여 from employees e, jobs j where j.job_title = 'Sales Manager' and e.job_id = j.job_id group by hire_date order by e.hire_date asc;
-- 2. 각 도시 (city) 에 있는 모든 부서 직원들의 평균급여를 조회하고자 한다 .
-- 평균급여가 가장 낮은 도시부터 도시명 (city) 과 평균연봉 , 해당 도시의 직원수를 출력하시오 .
-- 단 , 도시에 근 무하는 직원이 10 명 이상인 곳은 제외하고 조회하시오
select l.city 도시명, avg(e.salary * 12) 평균연봉, count(d.department_id) 직원수 --- 또는 count(*) from locations l, employees e, departments d where l.location_id = d.location_id and e.department_id = d.department_id group by l.city having count(d.department_id) < 10 --- l.city order by avg(e.salary) asc;
--3. ‘Public Accountant’의 직책(job_title)으로 과거에 근무한 적이 있는 모든 사원의 사번과 이름을 출력하시오.
-- (현재 ‘Public Accountant’의 직책(job_title)으로 근무하는 사원은 고려 하지 않는다.)
-- 이름은 first_name(이름), last_name(성)을 아래의 실행결과와 같이 출력한다.
select jh.employee_id 사번, e.last_name + ' ' + e.first_name 이름 from employees e, job_history jh, jobs j where e.employee_id = jh.employee_id and jh.job_id = j.job_id and j.job_title='Public Accountant';
--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; ---department where 로 연결 안해도 됨
--5. 2007년에 입사(hire_date)한 직원들의 사번(employee_id), 이름(first_name), 성(last_name),
-- 부서명(department_name)을 조회합니다.
-- 이때, 부서에 배치되지 않은 직원의 경우, ‘’로 출력하시오.
select e.employee_id 사번, e.first_name 이름, e.last_name 성, isnull(d.department_name,'<Not Assigned>') 부서명 from employees e left join departments d on e.department_id = d.department_id;
--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.salary between 9000 and 10000 and e.job_id = j.job_id and j.job_title = 'Sales Representative';
--7. 부서별로 가장 적은 급여를 받고 있는 직원의 이름, 부서이름, 급여를 출력하시오.
-- 이름은 last_name만 출력하며, 부서이름으로 오름차순 정렬하고,
-- 부서가 같은 경우 이름을 기준 으로 오름차순 정렬하여 출력합니다.
select e.last_name, a.* from employees e, (select d.department_name, min(e.salary) min_salary from departments d, employees e where e.department_id = d.department_id group by d.department_name) a where e.salary = a.min_salary order by 1, 2 asc;
--8. EMPLOYEES 테이블에서 급여를 많이 받는 순서대로 조회했을 때 결과처럼 6번째부터 10 번째까지
-- 5명의 last_name, first_name, salary를 조회하는 sql문장을 작성하시오.
select a.last_name 성, a.first_name 이름, a.salary 급여 from (select ROW_NUMBER() OVER(order by em.salary desc) as row#, em.last_name, em.first_name, em.salary from employees em) a where a.row# between 6 and 10;
--- top 이용하여 중간 데이터 가져오기 select top 5 e.last_name 성, e.first_name 이름, e.salary 급여 from employees e where e.salary not in (select top 5 e.salary from employees e order by e.salary desc) ---앞에서 5행 날리고 order by e.salary desc;
내 기준 어려웠던 문제
--9. 사원의 부서가 속한 도시(city)가 ‘Seattle’인 사원의 이름, 해당 사원의 매니저 이름, 사원 의 부서이름을 출력하시오.
-- 이때 사원의 매니저가 없을 경우 ‘<없음>’이라고 출력하시오. 이름은 last_name만 출력하며,
-- 사원의 이름을 오름차순으로 정렬하시오.
select e.e_name 사원이름, isnull(e.m_name,'<none>') 매니저이름, d.department_name 부서이름 from (select a.last_name e_name, b.last_name 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 d.location_id = l.location_id and e.department_id = d.department_id;
--10. 각 업무(job) 별로 연봉(salary)의 총합을 구하고자 한다. 연봉 총합이 가장 높은 업무부터
-- 업무명(job_title)과 연봉 총합을 조회하시오. 단 연봉총합이 30,000보다 큰 업무만 출력하시오.
select j.job_title 업무명, sum(e.salary) 연봉총합 from employees e, jobs j 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, jobs j, departments d, locations l where l.city = 'Seattle' and l.location_id = d.location_id and d.department_id = e.department_id and e.job_id = j.job_id order by e.employee_id asc;
--12. 2001~20003년사이에 입사한 직원의 이름(first_name), 입사일(hire_date), 관리자사번 (employee_id),
-- 관리자 이름(fist_name)을 조회합니다. 단, 관리자가 없는 사원정보도 출력 결과에 포함시켜 출력한다.
select em.e_name 직원이름, em.e_hire_date 입사일, em.m_id 관리자사번, em.m_first 관리자이름 from (select e.first_name e_name, e.hire_date e_hire_date, m.employee_id m_id, isnull(m.first_name,'<none>') m_first from employees e left join employees m on e.manager_id = m.employee_id where year(e.hire_date) = 1987) em;
--- 다른 답
SELECT
E.FIRST_NAME, E.hire_date AS HIRE_DATE, E.MANAGER_ID, M.FIRST_NAME
FROM EMPLOYEES E LEFT JOIN EMPLOYEES M ON
E.MANAGER_ID = M.EMPLOYEE_ID WHERE YEAR(E.HIRE_DATE) = 1987
;
