[MSSQL] hr SQL 연습 문제 (13번~마지막 21번)

13번 문제

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

select e.first_name 직원이름, e.salary 급여, d.department_name 부서이름
from employees e, departments d
where d.department_name = 'Sales' and e.department_id  = d.department_id
and e.salary < (select avg(em.salary) from employees em where em.department_id = 100); 

14번 문제

--14. Employees 테이블에서 입사한달(hire_date)별로 인원수를 조회하시오.

select count(*) 인원수
from employees
group by month(hire_date);

15번 문제

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

select d.department_name 부서명, max(e.salary) 최대급여, min(e.salary) 최소급여, avg(e.salary) 평균급여
from employees e, departments d
where e.department_id = d.department_id
group by d.department_name
having avg(e.salary) > (select avg(de.salary) from employees de, departments dp where de.department_id = dp.department_id and dp.department_name='IT')
and avg(e.salary) < (select avg(de.salary) from employees de, departments dp where de.department_id = dp.department_id and dp.department_name='Sales');

16번 문제

--16. 각 부서별로 직원이 한명만 있는 부서만 조회하시오 .
--단 , 직원이 없는 부서에 대해서는 신생부서 라는 문자열이 출력되도록 하고
--출력결과는 다음과 같이 부서명이 내림차순 으로 정렬되어야한다

select isnull(d.department_name,'<none>') 부서명, count(e.department_id) 사원수
from departments d
right join employees e
on e.department_id = d.department_id
group by  d.department_name
having count(*) = 1
order by d.department_name desc;

---

17번 문제

--17. 부서별 입사월별 직원수를 출력하시오 .
--단 , 직원수가 5 명 이상인 부서만 출력되어야 하며 출력결과는 부서이름 순으로 한다

select d.department_name 부서명, month(e.hire_date) 월, count(*) 직원수
from employees e, departments d
group by d.department_name, month(e.hire_date)
having count(*) > 5
order by d.department_name, month(e.hire_date) asc;

🥵 매운 맛 18번 문제

--18. 국가(country_name) 별 도시(city)별 직원수를 조회하시오.
-- 단, 부서에 속해있지 않은 직원 이 있기 때문에 106명의 직원만 출력이 된다.
-- 부서정보가 없는 직원은 국가명과 도시명 대신에 ‘<부서없음>’이 출력되도록 하여 107명 모두 출력되게 한다.

• 내가 charset 변경을 초반 테이블 만들 때 변경을 안해줘서 <??>로 떠가지고 ㅋㅋ none으로 바꿨다.

select isnull(cl.country_name,'<none>') 국가명, isnull(cl.city,'<none>') 도시명, count(*) 직원수
from (select e.department_id e_id, d.location_id de_lo_id from employees e left join departments d on e.department_id = d.department_id) em
left join (select c.country_name, l.city, l.location_id l_id from countries c, locations l where c.country_id = l.country_id group by c.country_name, l.city, l.location_id) cl
on em.de_lo_id = cl.l_id
group by cl.country_name, cl.city
order by cl.country_name, cl.city;

다른 답

  SELECT 
       isnull(C.COUNTRY_NAME, '<none>') AS COUNTRY_NAME, isnull(L.CITY, '<none>') 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번 문제)

--19. 각 부서별 최대 급여자의 아이디(employee_id), 이름(first_name), 급여(salary)를 출력하시오.
-- 단, 최대 급여자가 속한 부서의 평균급여를 마지막으로 출력하여 평균급여와 비교할 수 있게 할 것.

select dep.department_name 부서명, e.employee_id 아이디, e.first_name 최대급여자이름, e.salary 최대급여, avgsalary.avgsal 평균급여
from employees e
inner join (select d.department_id, d.department_name from departments d group by d.department_id, d.department_name) dep
on dep.department_id = e.department_id
inner join (select de.department_id, de.department_name, max(em.salary) maxsal from departments de, employees em where em.department_id = de.department_id group by de.department_id, de.department_name) deem
on deem.department_id = e.department_id
inner join (select de.department_id, de.department_name, avg(em.salary) avgsal from departments de, employees em where em.department_id = de.department_id group by de.department_id, de.department_name ) avgsalary
on avgsalary.department_id = e.department_id
where deem.maxsal = e.salary
order by 1 asc;

😣 20번 문제 (완벽한 답이 아님!)

--20. 커미션(commission_pct)별 직원수를 조회하시오.
-- 커미션은 아래실행결과처럼 0.2, 0.25는 모두 .2로, 0.3, 0.35는 .3 형태로 출력되어야 한다.
-- 단, 커미션 정보가 없는 직원들도 있는 데 커미션이 없는 직원 그룹은 ‘<커미션 없음>’이 출력되게 한다.

with employees_archor as
(select convert(NUMERIC(13,1), commission_pct) AS cp , count(1) AS quantity 
from employees
group by commission_pct)
select (CASE WHEN ISNULL(cp, 0) = 0 THEN 0 ELSE cp END) as cpp, sum(quantity) 
from employees_archor
group by cp;

21번 문제

--21. 커미션(commission_pct)을 가장 많이 받은 상위 4명의 부서명(department_name),
-- 직원명 (first_name), 급여(salary), 커미션(commission_pct) 정보를 조회하시오.
-- 출력결과는 커미션 을 많이 받는 순서로 출력하되 동일한 커미션에 대해서는 급여가 높은 직원이 먼저 출력 되게 한다.

select top (4) d.department_name 부서명, e.first_name 직원명, e.salary 급여, e.commission_pct 커미션
from employees e, departments d
where d.department_id = e.department_id
order by e.commission_pct desc, e.salary desc;

참고한 사이트

• 출처 (문제와 답)
  https://kwanhwi0123.tistory.com/1

• 서브쿼리 내용 정리
  https://inpa.tistory.com/entry/MYSQL-%F0%9F%93%9A-%EC%84%9C%EB%B8%8C%EC%BF%BC%EB%A6%AC-%EC%A0%95%EB%A6%AC

접기펼치기 마크다운이 안되지만.. 올립니다!