[MSSQL] hr SQL 연습2 100문제 (21~50번)

prana·2022년 12월 2일
mssqlsql
0

MSSQL

목록 보기
6/6
  • 중급 이상의 난이도를 원한다면 30번부터 풀어도 좋을 것 같다.
    use hr;

21~30번 문제

21번 문제

-- 21. 모든 사원들의 사원번호, 부서명, 직무명, 도시명을 조회하라.

select e.employee_id 사원번호, d.department_name 부서명, j.job_title 직무명, l.city 도시명
from employees e, departments d, jobs j, locations l
where e.department_id = d.department_id
and d.location_id = l.location_id 
and j.job_id = e.job_id;

--- 부서id가 없는 경우엔?

22번 문제

-- 22. 10번, 20번, 30번 부서에서 근무하는 사원들의 모든 정보를 조회하라.

select *
from employees e
where e.department_id in (10, 20, 30);

23번 문제

-- 23. 6인 미만의 사원이 근무하는 부서의 이름을 조회하라.

select d.department_name 부서명
from employees e, departments d
where e.department_id = d.department_id
group by d.department_name
having count(e.department_id) < 6;

24번 문제

-- 24. 4인 미만의 사원이 근무하는 부서의 평균 연봉과 부서명을 조회하라.

select avg(e.salary) 평균연봉, d.department_name 부서명
from employees e, departments d
where e.department_id = d.department_id 
group by d.department_name
having count(e.department_id) < 4;

25번 문제

-- 25. IT 부서의 연봉 총합을 조회하라.

select sum(e.salary) 연봉총합
from employees e, departments d
where e.department_id = d.department_id 
and d.department_name = 'IT';

26번 문제

-- 26. 대륙명(REGIONS)별 도시의 개수를 조회하라.

select r.region_name 대륙명, count(l.city) 도시수
from locations l, regions r, countries c
where l.country_id = c.country_id 
and r.region_id = c.region_id
group by r.region_name;

27번 문제

-- 27. 도시명 별 부서의 개수를 조회하라.

select l.city 도시명, count(d.department_id) 부서수
from departments d, locations l
where l.location_id = d.location_id
group by l.city 
order by l.city asc;

28번 문제

-- 28. 부서가 존재하지 않는 도시를 조회하라.

select l.city
from locations l
where l.location_id not in (select d.location_id from departments d);

29번 문제

-- 29. 사원이 존재하지 않는 국가를 조회하라.

select c.country_name
from countries c
where c.country_id not in 
(select l.country_id from locations l,departments d, employees e
where l.location_id = d.location_id 
and d.department_id = e.department_id);

30번 문제

-- 30. 150번 사원보다 빨리 입사한 사원 중 가장 최신에 입사한 사원의 모든 정보를 조회하라

select top(1) e.* 
from employees e, employees em
where e.hire_date < em.hire_date
and em.employee_id = 150
order by e.hire_date desc;

31~40번 문제

31번 문제

-- 31. 직무를 전환한 이력이 있는 사원의 모든 정보를 조회하라.

select * 
from employees e
where e.employee_id in 
(select jh.employee_id 
from job_history jh, employees e 
where e.employee_id = jh.employee_id)

32번 문제

-- 32. 부서장으로 있는 사원의 모든 정보와 부서명을 조회하라.

select em.*, d.department_name
from (select e.* from employees e 
      left join employees m 
      on e.manager_id = m.employee_id ) em, departments d
where em.department_id = d.department_id 
and em.manager_id is null

👻시간 조금 잡아먹은 33번 문제

-- 33. 다른 사원의 상사인 사원의 모든 정보를 조회하라.

select * 
from employees e
where e.employee_id in (select m.manager_id from employees m)
order by employee_id;
  • 다른 답
SELECT  DISTINCT MANAGER.*
FROM    EMPLOYEES MANAGER
        , EMPLOYEES E
WHERE   MANAGER.EMPLOYEE_ID = E.MANAGER_ID
ORDER   BY MANAGER.EMPLOYEE_ID;

34번 문제

-- 34. 이름(FIRST_NAME)이 C로 시작하고 s로 끝나는 사원의 부서명, 직무명, 도시명, 국가명, 대륙명을 조회하라.

select d.department_name 부서명, j.job_title 직무명, l.city 도시명, c.country_name 국가명, r.region_name 대륙명 
from (select e.first_name ef_name, e.department_id ed_id, e.job_id ej_id, e.employee_id e_id from employees e where e.first_name like 'C%S') em, 
departments d, locations l, countries c, regions r, jobs j
where d.department_id = em.ed_id 
and d.location_id = l.location_id 
and l.country_id = c.country_id 
and r.region_id = c.region_id
and j.job_id = em.ej_id
  • 다시 푼 답
select d.department_name 부서명, j.job_title 직무명, l.city 도시명, c.country_name 국가명, r.region_name 대륙명 
from employees e, departments d, locations l, countries c, regions r, jobs j
where d.department_id = e.department_id 
and d.location_id = l.location_id 
and l.country_id = c.country_id 
and r.region_id = c.region_id
and j.job_id = e.job_id
and e.first_name like 'C%S';

35번 문제

-- 35. 직무를 전환한 이력이 없는 사원의 모든 정보를 조회하라.

select e.*
from employees e
left join job_history jh
on e.employee_id = jh.employee_id
where jh.employee_id is null

36번 문제

-- 36. 150번 사원보다 늦게 입사한 사원 중 150번 사원보다 더 많은 연봉을 받는 사원을 조회하라.

select e.* 
from employees e, employees em
where e.hire_date > em.hire_date and em.employee_id = 150 and e.salary > em.salary

37번 문제

-- 37. 자신의 상사가 자신 보다 늦게 입사한 사원의 모든 정보를 조회하라.

처음끝
select e.*
from employees e, employees m
where e.manager_id = m.employee_id 
and e.hire_date < m.hire_date

38번 문제

-- 38. 100번 사원을 직속 상사로 두고 있는 사원들의 모든 정보를 조회하라.

select * 
from employees e
where e.manager_id = 100;

😡아직 못 푼 39번 문제

-- 39. 100번 사원을 상사로 두고 있는 모든 사원들의 모든 정보를 조회하라. (어려움)

💢 4시간 걸린 39-1번 문제

-- 39-1. 112번 사원의 상사들을 모두 조회해라

WITH TEMP AS (
SELECT * , 0 AS LEVEL
FROM employees E WHERE E.employee_id = 112
UNION ALL
SELECT R.* , A.LEVEL +1
FROM TEMP A, EMPLOYEES R
WHERE A.manager_id = R.employee_id
)
SELECT * FROM TEMP
ORDER BY temp.LEVEL desc;
  • 오라클의 level 개념이 없어서, with 문을 쓴다는 것을 알게 됐다.

39-2번 문제

-- 39-2. 150번 사원의 모든 상사들의 이름과 부서명을 조회하라

WITH TEMP AS (
SELECT * , 0 AS LEVEL
FROM employees E WHERE E.employee_id = 150
UNION ALL
SELECT R.* , A.LEVEL +1
FROM TEMP A, EMPLOYEES R
WHERE A.manager_id = R.employee_id
)
SELECT temp.first_name 이름, d.department_name 부서명 FROM TEMP, departments d
where temp.department_id = d.department_id
ORDER BY temp.LEVEL desc;

40번 문제

-- 40. 사원수가 가장 많은 도시의 이름을 조회하라.

select l.city 도시명
from locations l, (select top(1) d.department_name d_name, d.location_id d_id , count(e.employee_id) counteid
from departments d, employees e
where d.department_id = e.department_id
group by d.department_name, d.location_id
order by counteid desc) de
where l.location_id = de.d_id

41~50번 문제

💯 39번부터 어려운 문제 많음

41번 문제

-- 41. 직무의 종류가 가장 많은 부서의 이름을 조회하라.

select top 1 d.department_name, d.department_id
from departments d, 
(select top 1 e.department_id ed_id, count(*) ct
from employees e, jobs j 
where e.job_id = j.job_id 
group by e.department_id
order by count(*) desc) ct
where d.department_id = ct.ed_id
--order by d.department_name asc;

💦 42번 문제 (헷갈림)

-- 42. 담당 직무의 최대 연봉을 받고 있는 사원들의 모든 정보를 조회하라.

select maxem.j_title, em.*
from employees em,
(select j.job_title j_title, max(e.salary) maxsal
from jobs j, employees e
where e.job_id = j.job_id
group by j.job_title) maxem
where em.salary = maxem.maxsal 
order by maxem.j_title asc, em.salary asc;

43번 문제

-- 43. 담당 직무의 최소 연봉을 받고 있는 사원들의 모든 정보를 조회하라.

select minem.j_title, em.*
from employees em,
(select j.job_title j_title, min(e.salary) minsal
from jobs j, employees e
where e.job_id = j.job_id
group by j.job_title) minem
where em.salary = minem.minsal
order by minem.j_title asc, em.salary asc;

44번 문제

-- 44. 부서와 직무별 최대연봉, 최소연봉, 사원수를 조회하라.

select d.department_name, j.job_title, max(e.salary) 최대연봉, min(e.salary) 최소연봉, count(e.employee_id) 사원수
from employees e, departments d, jobs j
where e.department_id = d.department_id 
and e.job_id = j.job_id
group by d.department_name, j.job_title

45번 문제

-- 45. 사원수가 가장 많은 도시에서 근무하는 모든 사원들의 연봉 총합을 조회하라.

select sum(e.salary) 연봉총합
from employees e, departments d, locations l, jobs j
where e.department_id = d.department_id
and l.location_id = d.location_id
and j.job_id = e.job_id
and l.city = (select l.city
from locations l,(
select top 1 d.location_id, count(e.employee_id) cnt
from departments d, employees e
where e.department_id = d.department_id
group by d.location_id
order by cnt desc) ed
where ed.location_id = l.location_id);

46번 문제

-- 46. 사원수가 가장 많은 도시에서 근무하는 모든 사원들의 부서별 및 직무별 평균 연봉을 조회하라.

select d.department_name 부서명, j.job_title 직무명, avg(e.salary) 평균연봉
from employees e, departments d, locations l, jobs j
where e.department_id = d.department_id
and l.location_id = d.location_id
and j.job_id = e.job_id
and l.city = (select l.city
from locations l,(
select top 1 d.location_id, count(e.employee_id) cnt
from departments d, employees e
where e.department_id = d.department_id
group by d.location_id
order by cnt desc) ed
where ed.location_id = l.location_id)
group by d.department_name, j.job_title;

47번 문제

-- 47. 입사일이 가장 오래된 사원을 조회하라.

select top(1) *
from employees e
order by e.hire_date asc;

48번 문제

-- 48. 입사일이 가장 최근인 사원을 조회하라.

select top(1) *
from employees e
order by e.hire_date desc;

49번 문제

-- 49. 가장 최근에 입사한 사원과 가장 오래전에 입사한 사원의 일차를 계산해 조회하라.

select 
datediff(day,(select top 1 e.hire_date from employees e order by e.hire_date asc),
(select top 1 e.hire_date
from employees e
order by e.hire_date desc
)) as 일차;

50번 문제

-- 50. 가장 최근에 입사한 사원과 가장 오래전에 입사한 사원의 시간차를 계산해 조회하라

select 
datediff(hour,(select top 1 e.hire_date
from employees e
order by e.hire_date asc),
(select top 1 e.hire_date
from employees e
order by e.hire_date desc
)) as 시간차;
profile
prana
이전 포스트

[MSSQL] hr SQL 연습2 100문제 (1~20번)

0개의 댓글