DBMS SQL Oracle Database SubQuery(서브쿼리)

 

 

 SubQuery(서브쿼리)

: select문 안에 select가 포함되어 있는 SQL문

 

select문 안에 필드, 테이블, where절, having절

 

 

서브쿼리의 종류

단일행 서브쿼리 : 결과가 하나인 서브쿼리

다중행 서브쿼리 : 결과가 여러개인 서브쿼리

다중열 서브쿼리 : 결과가 하나 이상의 컬럼인 경우

 

select first_name, last_name, department_id from employees;

select department_id,department_name from departments;

 

select first_name, last_name, employees.department_id, department_name

from employees, departments

where employees.department_id = departments.department_id;

 

select first_name, last_name, department_id, 
(

select department_name from departments dep

where dep.department_id = emp.department_id
) 

dname
from employees emp;

 

ex1. 

sales부서보다 평균 연봉이 높은 부서

 

//STEP1

select avg(salary) from employees where department_id = 80;

 

//STEP2

select department_id, avg(salary)

from employees

group by department_id

having avg(salary) >= 8955.88235;

=>

select department_id, avg(salary)

from employees

group by department_id

having avg(salary) >= (select avg(salary) from employees where department_id = 80);

 

ex2.

매니저를 제외한 사원들 중에

평균 연봉 보다 많은 월급을 받는 사람의

이름 부서명 급여를 조회

 

 //STEP1 평균 연봉

select avg(salary) from employees 

 

// 매니저 아이디가 있는 값 찾기

select manager_id from departments where manager_id is not null;

 

=>

select * from employees e
where e.employee_id not in(select manager_id from departments where manager_id is not null) 
and e.salary >= (select avg(salary) from employees);

select e.first_name,e.last_name,e.salary from employees e
where e.employee_id not in(select manager_id from departments where manager_id is not null) 
and e.salary >= (select avg(salary) from employees);

select e.first_name,e.last_name,d.department_name,e.salary from employees e,departments d
where e.employee_id not in(select manager_id from departments where manager_id is not null) 
and e.salary >= (select avg(salary) from employees) and e.department_id = d.department_id;

select rownum rn,e.* from
(select first_name,last_name from employees) e;