Database : Basic SQL_Subquery & Join_QUIZ(2)

Subquery & Join_QUIZ

1. 모든 사원의 평균급여보다 많은 급여를 받고 성(last_name)에 'u'가 포함된 사원과 같은 부서에 근무하는 모든 사원의 사원 번호(employee_id)와 성, 급여를 표시하는 쿼리를 작성합니다.

풀이
(1)모든 사원의 평균급여보다 많은 급여를 받고 / 성(last_name)에 (2)'u'가 포함된 사원과 같은 부서에 근무하는 모든 사원의 사원 번호(employee_id)와 성, 급여를 표시하는 쿼리를 작성합니다.

select avg(salary) from employees;

(2)
select department_id
from employees
where last_name like '%u%';


정답 - 1과 2를 사용한 정답 쿼리문장)
select employee_id, last_name, salary
from   employees
where  salary > (select avg(salary) from employees)
and    department_id in(select department_id
                                     from employees
                                    where last_name like '%u%');

2. King(last_name)에게 보고하는 모든 사원의 성(last_name)과 급여(salary)를 표시하는 보고서를 작성합니다.

풀이
(1) : 일단, 보고하는 manager_id와 사원(employee_id)를 알아보아야 합니다.)

select employee_id, last_name, manager_id
from   employees
order by last_name;

정답) (1)을 사용하여 'King'이라는 사람의 사원id를 가진 메니저id를 통해 king의 아랫사람을 구할 수 있습니다. 즉, king에게 보고하는 아랫사람을 알 수 있고 king은 메니저가 되겠죠.

select last_name, salary
from   employees
where manager_id in (select  employee_id
                from   employees
                where  last_name = 'King');

3. 60번 부서의 사원보다 급여가 많은 모든 사원 리스트를 표시하는 보고서를 작성합니다.

풀이
(1) : 부서번호가 60번인 사원이 받는 급여를 알아보기.

select salary
from   employees
where  department_id = 60;


정답) (1)을 사용한 3번의 정답.

select *
from   employees
where  salary > any (select salary
                     from   employees
                     where  department_id = 60);

 

4. 사원 수가 가장 많은 부서의 부서 번호(department_id),부서이름(department_name) 및 근무하는 사원 수를 표시합니다.
(join+subquery)

풀이
(1) : 각각의 부서의 총 사원수를 먼저 구합니다.
select e.department_id, department_name, count(*)
from   departments d, employees e
where  d.department_id = e.department_id
group by e.department_id, department_name
order by e.department_id, department_name ;


(2) : 가장 많은 사원수를 가진 부서를 알아봅니다.
select MAX(count(*))
from  employees
group by department_id
order by department_id ;


정답) (1), (2)을 통해 가장 많은 부서를 알아봅니다.

select e.department_id, department_name, count(*)
from   departments d, employees e
where  d.department_id = e.department_id
group by e.department_id, department_name
having  count(*) = (select max(count(*))
        from  employees
        group by department_id)
order by e.department_id, department_name ;