6장 연습문제 풀이

7.
찾는 항목:평균급여보다 많은 급여를 받고, 이름에 u가 포함된 사원과 같은 부서
에서 근무하는 사원의 사번, 이름, 급여를 찾아라.
-> 평균급여가 뭐냐?-->서브쿼리(결과가 1건)
select avg(salary) from employees
-> 이름에 u가 포함된 사원의 부서번호가 뭐냐?-->서브쿼리(결과가 여러건)
select department_id from employees where last_name like '%u%'
메인쿼리
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%');

6.
찾는 항목:부서이름이 Executive인 부서의 부서번호, 사원의 이름, 업무코드
-> 부서이름이 Executive인 부서번호가 뭐냐?-->서브쿼리(결과가 1건)
select department_id from departments
where department_name = 'Executive'
메인쿼리
select department_id, last_name, job_id from employees
where department_id in (select department_id from departments
where department_name = 'Executive');

5.
찾는 항목:King의 부하직원의 이름과 급여를 찾아라.
->King의 사번이 뭐냐?-->서브쿼리(결과가 여러건)
select employee_id from employees where last_name = 'King'
메인쿼리
select last_name, salary from employees
where manager_id in 
(select employee_id from employees where last_name = 'King' );

4.
찾는 항목:부서위치번호가 1700인 부서에 근무하는 사원의 이름,부서번호,업무코드
->부서위치번호가 1700인 부서의 부서번호가 뭐냐?-->서브쿼리(결과가 여러건)
select department_id from employees
where location_id = 1700
메인쿼리
select last_name, department_id, job_id from employees
where department_id in (select department_id from departments
where location_id = 1700);

3.
찾는 항목:이름에 u가 포함된 사원과 같은 부서의 사원번호,사원이름
->이름에 u가 포함된 사원의 부서번호가 뭐냐?-->서브쿼리(결과가 여러건)
select department_id from employees
where last_name like '%u%';
메인쿼리:
select employee_id, last_name from employees
where department_id in (select department_id from employees 
where last_name like '%u%');

2.
찾는 항목:평균급여보다 많은 사원의 사번, 이름을 찾아라.
정렬:급여를 기준으로 오름차순(작은값->큰값)
-> 평균급여가 뭐냐?-->서브쿼리 select avg(salary) from employees
메인쿼리
select employee_id, last_name, salary from employees
where salary > (select avg(salary) from employees)
order by salary;

1.
찾는 항목:Zlotkey와 동일한 부서에 속한 사원의 이름과 입사일
-> Zlotkey의 부서번호가 뭐냐?-->서브쿼리
-> select department_id from employees where last_name = 'Zlotkey'
메인쿼리: 
select last_name, hire_date from employees
where department_id = 
(select department_id from employees where last_name = 'Zlotkey')
and last_name <> 'Zlotkey';