[Oracle] 서브쿼리

Oracle

[Oracle] 서브쿼리

eunnys 2024. 2. 13. 11:38

*** 서브쿼리 (Sub Query)

- 쿼리 문장 안에 다른 쿼리가 포함된 것

- 서브쿼리, 메인쿼리

1) 메인쿼리

- 실행의 결과가 조회되는 쿼리

- 서브쿼리에 의해 실행된 쿼리문의 결과에 의해 ㅔ인쿼리가 실행된다

2) 서브쿼리

- 메인쿼리의 안쪽에 위치한 쿼리

- 서브쿼리의 실행 결과가 메인쿼리의 조건이나 결과로 사용된다

- 서브쿼리에서는 ORDER BY절 사용 불가

- 서브쿼리의 사용 위치

* WHERE절 : 값의 결과가 1개로 나오는 결과일 경우 (여러개도 가능)

* HAVING절 : 메인쿼리에서 그룹행 함수를 사용하고 그룹에 대한 조건을 서브쿼리로 사용가능

* FROM절

* SELECT절

* INSERT 명령의 INTO절

* UPDATE 명령의 SET절

- 서브쿼리의 종류

단일행 서브쿼리 : 서브쿼리의 결과가 하나만 나오는 경우

복수행 서브쿼리 : 서브쿼리의 결과가 하나 이상 나오는 경우

WHERE절의 IN 연산자와 사용

IN, ANY, ALL, EXISTS 등

-- [문제 1] 사원번호가 109번인 사원의 급여보다 급여가 많은 사원의 이름과 담당업무 조회

-- 1) 일반쿼리로 2번 나눠 처리한 경우

select salary

from employees

where employee_id = 109; -- 9000

select first_name, job_id

from employees

where salary > 9000;

-- 2) 서브쿼리로 처리한 경우 (단일행 서브쿼리)

select first_name, job_id

from employees

where salary > (select salary

from employees

where employee_id = 109);

-- [문제 2] 162번 사원의 급여와 동일한 급여를 받는 사원의 명단을 조회 (이름, 급여, 부서번호)

select first_name, salary, department_id

from employees

where salary = (select salary

from employees

where employee_id = 162);

-- [문제 3] from절에서 사용

select employee_id, first_name, salary, department_id

from employees

where department_id = 50;

select tbl.* from

(select employee_id, first_name, salary, department_id

from employees

where department_id = 50) tbl

where salary > 4000;

** 일련번호 rownum (동적으로 붙여주는 순서번호)

select rownum, first_name

from employees

where department_id = 80;

-- 일련번호를 기준으로 조건 걸기(무조건 1번부터 조회 가능)

select rownum, tbl.* from

(select employee_id, first_name, salary, department_id

from employees

where department_id = 50) tbl

where

salary > 3000

and

rownum between 1 and 10;

-- 동적으로 사용되지 않도록 안쪽에 사용

select tbl.* from

(select rownum as RNO, employee_id, first_name, salary, department_id

from employees

where department_id = 50) tbl

where

salary > 3000

and

RNO between 11 and 20;

-- [문제 4] 사원번호가 101인 사원의 담당업무가 같은 사원들의 목록을 조회 (사원번호, 이름, 담당업무)

select employee_id, first_name, job_id

from employees

where job_id = (select job_id

from employees

where employee_id = 101);

-- [문제 5] 03년도 이전에 입사한 직원의 일련번호화 이름, 급여, 입사일, 부서번호를 조회

-- 이중 3번째로 급여가 많은 직원의 명단을 재조회

-- 3중 서브쿼리

select * from

(select rownum rno, temp.* from

(select first_name, salary, hire_date, department_id

from employees where hire_date < '03/01/01'

order by salary desc

) temp

)tmp

where rno = 3;

-- [문제 6] 이름이 'Bruce', 'Daniel'이라는 직원의 성을 조회

select first_name, last_name

from employees

where first_name in ('Bruce', 'Daniel');

-- 2개의 데이터를 나란히 출력하기

select

(select last_name from employees where first_name = 'Bruce') as "Bruce의 성",

(select last_name from employees where first_name = 'Daniel') as "Daniel의 성"

from dual;

** 복수행 서브쿼리

-- 1) in 연산자와 함께한 복수행 서브쿼리

-- [문제 7] in 연산자와 함께 30번 부서의 직급과 동일한 직급이 다른 팀에도 있는지 조회

select * from employees

where job_id in (select distinct job_id

from employees

where department_id = 30);

** ANY / ALL 연산자

> ANY, < ANY, >= ANY, <= ANY, != ANY, = ANY

-- 2) 5800 가장 작은값, 8200 가장 큰값

select salary, job_id

from employees

where job_id = 'ST_MAN'

order by 1;

-- > any : 서브쿼리에서 추출된 어느 값보다 큰 값이어야 하므로 --> 결국, 가장 작은값보다 큰 값이 조회된다.

select first_name, salary

from employees

where salary > any (select salary

from employees

where job_id = 'ST_MAN');

-- > all : 서브쿼리에서 추출된 어느 값보다 큰값이 메인쿼리에서 사용되므로 가장 큰값보다 더 큰 값이 조회된다.

select first_name, salary

from employees

where salary > all (select salary

from employees

where job_id = 'ST_MAN');

-- [문제 8] 'ST_MAN' 직군의 급여 중에서 가장 적게 받는 급여보다 더 적게 받는 가람의 이름, 급여를 조회

select first_name, salary

from employees

where salary < any(select salary

from employees

where job_id = 'ST_MAN');

-- [문제 9] 직군이 'REP'인 직원들이 받는 급여와 동일한 급여를 받는 직원의 사원번호, 이름, 직급, 급여를 조회

select employee_id, first_name, job_id, salary

from employees

where salary = any (select salary

from employees

where job_id like '%REP');

** EXISTS : 서브쿼리에서 질의한 결과에 만족하는 행이 존재하면 메인쿼리 실행

select department_name

from departments

where exists (select department_id

from employees

where department_id = 80);

-- [문제 10] 성이 'Chen'인 직원이 존재하면 그 직원의 부서명, 이름, 성, 급여

select d.department_name, e.first_name, e.last_name, e.salary

from employees e join departments d

on e.department_id = d.department_id

where exists (select first_name, last_name

from employees

where last_name = 'Chen')

and last_name = 'Chen';

-- [문제] 입사년도별 입사인원이 가장 적은 해의 정보를 출력

select min(입사인원) from

(

select to_char(hire_date, 'YYYY') as 입사년도, count(*) as 입사인원

from employees

group by to_char(hire_date, 'YYYY')

order by 1

) temp ;

[연습문제]

-- 1. 80번 부서의 급여의 평균, 최고, 최저, 인원수를 구하시오.

select avg(salary), max(salary), min(salary), count(*)

from employees

where department_id = 80;

-- 2. 각 부서별 급여의 평균, 최고, 최저, 인원수를 구하시오.

select avg(salary), max(salary), min(salary), count(*)

from employees

group by department_id;

-- 3. 각 부서별 평균 급여, 전체 급여, 최저 급여를 구하여 평균 급여가 많은 순으로 조회

-- 단, 부서번호거 null이면 출력하지 않는다

select avg(salary), sum(salary), min(salary)

from employees

group by department_id

order by 1 desc;

-- 4. 각 부서별 같은 업무를 하는 사람의 인원수를 구하여 부서번호, 업무명, 인원수를 출력

select department_id, job_id, count(*)

from employees

group by department_id, job_id;

-- 5. 같은 업무를 하는 사람의 수가 4명 이상인 업무와 인원수를 출력

select job_id, count(*)

from employees

group by department_id, job_id

having count(*) >= 4;