Oracle 기본정리 - sub query1(where절에 select 넣어보기)

Oracle 기본정리 - where절에 select 넣어보기

-hr 계정의 테이블 사용

--우리회사의 평균 급여 이상을 받는 사원의 사원번호, 성, 급여를 출력 (급여는 많이 받는 사람순으로)
 
    select employee_id,last_name,salary
    from employees
    where salary >= (select avg(salary)
                  from employees e2)
    order by salary desc;
 
 
 
--성(lastname)에 u문자가 포함된 사원과 같은 부서에서 근무하는 사원의 사원번호, 성을 출력
 
    select * from employees;
    select employee_id, last_name, department_id
    from employees
    where department_id in (select department_id
                           from employees
                           where last_name like '%u%')
    order by department_id asc;
 
 
 
--king에게 보고하는 사원의 사원코드와 성, 급여를 출력
 
    --방법1
    select * from employees order by manager_id;
    select e1.employee_id, e1.last_name, e1.salary
    from employees e1, employees e2
    where e1.manager_id = e2.employee_id
    and upper(e2.last_name) = 'KING'
    and upper(e2.first_name) = 'STEVEN';
 
    --방법2
    select employee_id, last_name, salary
    from employees
    where manager_id in (select employee_id
                        from employees
                        where upper(last_name) = 'KING'
                        and upper(first_name) = 'STEVEN');
 
 
--부서 60의 사원들보다 급여가 많이 받는 사원들의 성, 부서명, 급여를 출력
 
    --방법1
 
    select e.last_name, d.department_name, e.salary
    from employees e inner join departments d
    on e.department_id = d.department_id
    where e.salary > (select max(salary)
                    from employees
                    where department_id=60);
    --방법2
    select e.last_name, d.department_name, e.salary
    from employees e inner join departments d
    on e.department_id = d.department_id
    where e.salary > all (select salary
                          from employees
                          where department_id=60);
 
 
--평균보다 많이 급여를 받고 성에 u가 포함된 사원이 있는 부서에서 근무하는 모든 사원의 사원번호, 성, 급여를 출력
 
    select *
    from employees
    where salary  > ( select avg(salary)
                            from employees )
    and department_id in (select department_id
                          from employees
                          where last_name like '%u%');
 
 
--각 부서에서 급여를 많이 받는 사원의 이름, 급여, 부서코드를 출력
 
    select  last_name,salary,department_id
    from employees e1
    where salary  = (select max(salary) --in은 사용하면 중복된다. 사용x
                    from employees e2
                    where e1.department_id = e2.department_id
                    group by department_id)
    order by department_id asc;
                     
 
 
--각 직책별 평균 급여보다 많이 받는 사원의 사원번호, 이름 , 입사일 출력
 
 
    select * from employees;
    select employee_id, last_name, hire_date
    from employees e1
    where salary >(select avg(salary)
                    from employees e2
                    where e1.job_id = e2.job_id
                    group by job_id);
 
       
 
--각 부서의 평균 급여보다 많이 받는 사원의 사원번호, 이름, 급여, 부서명, 직책 및 입사일 출력
 
    select e1.employee_id, e1.last_name, e1.salary, e1. department_id, j.job_title, e1.hire_date
    from employees e1 inner join departments d
    on e1.department_id = d.department_id
    inner join jobs j
    on e1.job_id = j.job_id
    where salary >(select avg(salary)
                    from employees e2
                    where e1.department_id = e2.department_id
                    group by department_id);