[Oracle] 다중열 서브쿼리, scalar subquery

다중열 서브쿼리

쌍비교

select *
from employees
where (manager_id, department_id) in (select manager_id, department_id
                                      from employees
                                      where first_name = 'John');

비쌍비교

select *
from employees
where manager_id in (select manager_id
                     from employees
                     where first_name = 'John')
and department_id in (select department_id
                      from employees
                      where first_name = 'John'); --결과값이 쌍비교와 다름.

	쌍비교 결과값	비쌍비교 결과값
100 10	100 10	100 10
200 20	200 20	300 10
300 30	300 30	200 20
300 10		300 30

 

---

[문제76] commission_pct null이 아닌 사원들의 department_id, salary 일치하는 사원들의 정보를 출력해주세요.

select *
from employees
where (department_id, salary) in (select department_id, salary
                                  from employees
                                  where commission_pct is not null);

[문제77] location_id가 1700 위치에 있는 사원들의 salary, commission_pct가 일치하는 사원들의 정보를 출력해주세요.

select *
from employees
where (salary, nvl(commission_pct,0)) in (select e.salary, nvl(e.commission_pct,0)
                                          from employees e, departments d
                                          where e.department_id=d.department_id
                                          and location_id = 1700);

---

scalar subquery

: 한 행에서 정확히 하나의 열값만 반환하는 쿼리. 일컬럼, 단일값만 리턴해야함

-동일한 입력값이 들어오면 수행 횟수를 최소화 할 수 있는 로직을 구형. io를 최소화

-query execution cache 기능이 수행된다. >> 성능향상

-key값이 없는 데이터가 입력되면 null값으로 리턴한다.(outer join처럼 결과가 출력됨)

-select절, order by절에 사용 가능

select employee_id, department_id, (select department_name
                                    from departments
                                    where department_id=e.department_id)
from employees e order by 2;

---

[문제78] 부서이름별 급여의 총액, 평균을 구하세요.

1) 일반적인 형식

select d.department_name, sum(e.salary), avg(e.salary)
from employees e, departments d
where e.department_id=d.department_id
group by d.department_name;

2) inline view 이용

select d.department_name, e.sum_sal, e.avg_sal
from (select department_id, sum(salary) sum_sal, avg(salary) avg_sal
	from employees
    group by department_id) e, departments d
where e.department_id=d.department_id;

3) scalar subquery 이용

select d.department_name, (select sum(salary)
			from employees
       			where department_id= d.department_id) sum_sal,
       			(select avg(salary)
       			from employees
       			where department_id= d.department_id) avg_sal        
from departments d;

[문제79] 사원들의 last_name, salary, grade_level을 출력해주세요.

1) 조인

select e.last_name, e.salary, j.grade_level
from employees e, job_grades j
where e.salary between j.lowest_sal and j.highest_sal;

2) scalar subquery

select last_name, salary, (select grade_level
                           from job_grades
                           where e.salary between lowest_sal and highest_sal) "level"
from employees e;

[문제80] 사원들의 employee_id, last_name을 출력을 하는데 단 department_name을 기준으로 정렬해주세요.

select employee_id, last_name
from employees e
order by (select department_name
          from departments
          where e.department_id = department_id);

[문제81] 아래 화면과 같이 출력해주세요.
년도            SA_REP               SH_CLERK             ST_CLERK
-------- -------------------- -------------------- --------------------
2001                   ￦0원                ￦0원                ￦0원
2002                   ￦0원                ￦0원                ￦0원
2003                   ￦0원                ￦0원            ￦7,100원
2004              ￦39,500원            ￦8,200원            ￦3,300원
2005              ￦74,800원           ￦15,400원           ￦18,100원
2006              ￦59,100원           ￦21,900원           ￦15,900원
2007              ￦38,200원           ￦13,400원            ￦6,900원
2008              ￦38,900원            ￦5,400원            ￦4,400원

①
select 년도, to_char(nvl(SA_REP,0),'l999,999')||'원' SA_REP,
             to_char(nvl(SH_CLERK,0),'l999,999')||'원' SH_CLERK,
             to_char(nvl(ST_CLERK,0),'l999,999')||'원' ST_CLERK
from (select to_char(hire_date,'yyyy') 년도, job_id, salary
      from employees)
pivot(sum(salary) for job_id in ('SA_REP' SA_REP,'SH_CLERK' SH_CLERK,'ST_CLERK' ST_CLERK))
order by 1;
②
select to_char(hire_date,'yyyy') 년도,
       to_char(nvl(sum(decode(job_id,'SA_REP',salary)),0),'L999,999')||'원' SA_REP,
       to_char(nvl(sum(decode(job_id,'SH_CLERK',salary)),0),'L999,999')||'원' SH_CLERK,
       to_char(nvl(sum(decode(job_id,'ST_CLERK',salary)),0),'L999,999')||'원' ST_CLERK
from employees
group by to_char(hire_date,'yyyy')
order by 1;