Oracle Quiz2

select 문으로 join, 서브쿼리 문제

1. 사원의 사번, 이름, 급여, 매니저번호, 매니저이름, 매니저급여를 출력하시오.

select e.employee_id,e.first_name,e.salary,e.manager_id,m.first_name,m.salary

from employees e join employees m on e.manager_id=m.employee_id

2. 자신의 매니저급여보다 많은 급여를 받는 사원의 사번, 이름, 급여를 출력하시오.

select e.employee_id,e.first_name,e.salary

from employees e left join employees m on e.manager_id=m.employee_id

where e.salary>m.salary

3. 매니저사번, 이름을 출력하시오. 매니저가 중복되지 않도록 한다

select employee_id,first_name

from employees

where employee_id in (select manager_id from employees group by manager_id)

order by employee_id

select employee_id,first_name

from employees

where employee_id in (select distinct manager_id from employees)

order by employee_id

select distinct m.employee_id,m.first_name

from employees e join employees m on e.manager_id=m.employee_id

group by m.employee_id,m.first_name

4. 매니저역할을 하지 않는 사원의 사번, 이름을 출력하시오.

select employee_id,first_name

from employees

where employee_id not in (select distinct manager_id from employees where manager_id is not null)

order by employee_id

5. 도시별 도시명, 근무사원수를 출력하시오. 단 근무하지 않는 도시도 출력한다 

select city,count(d.department_id)

from employees e join departments d on e.department_id=d.department_id

join locations l on d.location_id=l.location_id

group by city,d.department_id

6. 매니저가 [이름 last_name 'King'이고 first_name이 'Steven']인 사원의 이름과 급여를 출력하시오.

select first_name,last_name,salary

from employees

where manager_id=(select employee_id from employees where last_name='King' and first_name='Steven')

7. 부서명이'Sales'인 부서의 평균급여보다 많은 급여를 받는 'Sales'부서의 사원들을 출력하시오

select e.employee_id 

from employees e join departments d on e.department_id=d.department_id 

where d.department_name='Sales'

and salary>(select avg(salary) from employees e join departments d on e.department_id=d.department_id 

                where d.department_name='Sales')

//서브쿼리에서 메인에 있는 별칭을 사용함으로써 상호연관성 서브 쿼리 형성

//되도록 상호연관성은 사용x 능률별로임

select e.employee_id 

from employees e join departments d on e.department_id=d.department_id 

where d.department_name='Sales'

and salary>(select avg(salary) from employees where department_id=e.department_id)

8. last_name이 'S'로 시작하는 사원들의 부서명을 출력하시오.

select department_name

from departments

where department_id in (select department_id from employees where last_name like 'S%')