08.서브쿼리

서브쿼리 (QUERY)

쿼리문 안에 쿼리

실무에서 많이 사용한다

 

평균 급여 조회

select round(avg(salary))
from employees;

ROUND(AVG(SALARY))
6462

 

평균 급여보다 적은 사원의 사번,first_name 출력하기

select first_name,employee_id
from employees
where salary<=round(avg(salary));

 

오류 발생:집계함수는 where에서 사용불가하다

ORA-00934: 그룹 함수는 허가되지 않습니다

 

서브쿼리를 적용

select first_name,employee_id
from employees
where  salary<(select round(avg(salary)) from employees);

EMPLOYEE_ID	FIRST_NAME
104	Bruce
105	David
106	Valli
107	Diana
115	Alexander
116	Shelli
117	Sigal
118	Guy
119	Karen
124	Kevin
125	july
126	Irene

select * location_id
from locations
where state_province is null;

LOCATION_ID
1000
1100
1300
2000
2300
2400

 

부서테이블에서 1400,1500,1600,1700의 지역번호를 가진 부서의 정보를 모두 조회하시오

select *
from departments
where location_id in (1400, 1500, 1600, 1700);

DEPARTMENT_ID	DEPARTMENT_NAME	MANAGER_ID	LOCATION_ID
60	IT	103	1400
50	Shipping	121	1500
10	Administration	200	1700
30	Purchasing	114	1700
90	Executive	100	1700
100	Finance	108	1700
110	Accounting	205	1700
120	Treasury		1700
130	Corporate Tax		1700
140	Control And Credit		1700
150	Shareholder Services		1700
160	Benefits		1700
170	Manufacturing		1700
180	Construction		1700
190	Contracting		1700
200	Operations		1700
210	IT Support		1700
220	NOC		1700
230	IT Helpdesk		1700
240	Government Sales		1700
250	Retail Sales		1700
260	Recruiting		1700
270	Payroll		1700

 

월급이 가장 적은 사원의 이름과 직책명을 출력하세요

select e.first_name, job.job_title
from employees e, jobs job
where e.salary = (select min(salary) from employees)
and e.job_id = job.job_id;

FIRST_NAME	JOB_TITLE
TJ	Stock Clerk

 

any,all 이용

select salary
from employees
where departmnet_id = 20;

SALARY
13000
6000

 

any

any 연산자는 모든 다중 값 중에서 하나만 참이면 모두 참이다

select employee_id, department_id, salary
from employees
where salary > (select salary
                from employees
                where department_id = 20);

오류발생

ORA-01427: 단일 행 하위 질의에 2개 이상의 행이 리턴되었습니다.

select salary
                from employees
                where department_id = 20

SALARY
13000
6000

 

select employee_id, department_id, salary
from employees
where salary > any(select salary
                from employees
                where department_id = 20);

EMPLOYEE_ID	DEPARTMENT_ID	SALARY
100	90	24000
101	90	17000
102	90	17000
145	80	14000
146	80	13500
201	20	13000
108	100	12008
205	110	12008
147	80	12000
168	80	11500
114	30	11000

 

아래와 같다

  select employee_id,department_id,salary
  from employees
  where salary > (select min(salary)
              from employees
              where department_id = 20);

 

all

all 연산자는 모든 다중 값이 참이면 참(결과값 모두 다 만족)

select employee_id, department_id, salary
from employees
where salary > all (select salary
                    from employees
                    where department_id = 20);

EMPLOYEE_ID	DEPARTMENT_ID	SALARY
146	80	13500
145	80	14000
102	90	17000
101	90	17000
100	90	24000

 

아래와 같다

 select employee_id,department_id,salary
  from employees
  where salary > (select min(salary)
              from employees
              where department_id = 20);

일치하는 값만 출력(in 연산자)

select employee_id,department_id,salary
from employees
where salary in (select salary
              from employees
              where department_id = 20);