[데이터베이스] 21일차 - non-equi join, sub-query, 계층형 쿼리(hierarchy)

데이터베이스 9일차

 

1. non-equi join : 조인 조건이 = 이 아닌것

select * from salgrade;
select ename, sal, grade
from emp e
  join salgrade s on e.sal between s.losal and s.hisal;

  

2.  sub-query

 

(1) 단일행 서브쿼리

-- 평균 급여보다 더 많이 받는 직원들의 이름과 급여 출력
select first_name, salary
from employees
where salary >= (select avg(salary) from employees);



-- 1. Neena보다 먼저 입사한 직원들의 이름과 입사일 출력
select first_name, hire_date
from employees
where hire_date < (
  select hire_date from employees where first_name = 'Neena');



-- 2. Shanta 와 같은 직업을 가진 사원들의 이름과 직업 직업명 출력
select first_name, e.job_id, job_title
from employees e
  left join jobs j on e.job_id = j.job_id
where e.job_id = 
(select job_id from employees where first_name = 'Shanta');



-- 3. Diana 와 같은 부서에서 근무하는 사람들의 이름과 부서번호 부서명 출력
select first_name, department_id, department_name
from employees 
  join departments using(department_id)
where first_name != 'Diana' and department_id =
(select department_id from employees where first_name = 'Diana');

 

 

(2) 복수행서브쿼리(where절에서 연산자가 달라진다)
 

** 복수행 서브쿼리를 위한 연산자

• all : 모든 결과값
• any : 어느 결과값 하나
• in  : 결과값 중 하나
• exists : 존재여부

 

-- 60번 부서 사람들 월급보다 많이 받는 사람들을 출력
select *
from employees
where salary > all(select salary from employees where department_id = 60);


-- 80번부서 사람들과 같은 월급을 가진 사람들을 출력하세요
select *
from employees
where department_id != 80 and 
salary not in (select distinct salary -- !=any는 없다.
from employees
where department_id = 80);



-- exist(존재여부)
select *
from employees
where exists(select first_name from employees where salary > 20000);

 

 

 

(2-1) 복수컬럼 서브쿼리

-- 80번 부서 사람들과 salary와 hire_date가 같은 사람
select salary, hire_date from employees where department_id = 80;

select first_name, salary, hire_date, department_id
from employees
where (salary, hire_date) in
(select salary, hire_date from employees where department_id = 80);

 

 

 

 

(2-2) 상호관련 서브쿼리(상관 서브쿼리)

-- join 을 쓰지 않고(못쓰는 경우) 부서명을 가져오기
select first_name, 
(select department_name 
from departments 
where department_id = e.department_id)
from employees e;



-- 80번 부서 사원들의 월급과 같은 월급을 받는 사람들의
-- 이름, 부서명, 직업명, 월급을 출력하시오
select first_name 이름, 
(select department_name from departments 
where department_id = e.department_id) 부서명,
(select job_title from jobs where job_id = e.job_id) 직업명, 
salary 월급
from employees e
where department_id != 80 and salary in 
(select salary from employees where department_id = 80)
order by 4 desc;

 

 

 

 

(2-3) HAVING절 서브쿼리

select department_id, count(*)
from employees
group by department_id
having count(*) >= 5;


-- 부서별 월급합계의 평균보다 높은 월급합계를 가지는 부서의
-- 부서명과 월급합계를 출력하시오.
select avg(salary) from employees;  --6461.83

select avg(sum(salary))     -- 57618
from employees
group by department_id;



-- 어렵게 만들기 from 서브쿼리, where 서브쿼리
select *
from (
  select sum(salary) s, department_id
  from employees
  group by department_id
)
where s > (
  select avg(sum(salary))
  from employees
  group by department_id
);



-- HAVING 서브쿼리로 쉽게 만들기
select sum(salary), department_id, 
  (select department_name from departments 
  where department_id = e.department_id) 부서명
from employees e
group by department_id
having sum(salary) > (
  select avg(sum(salary))
  from employees
  group by department_id
);

 

 

 

 

3. 계층형 쿼리 ( hierarchy )
    - Root(제일 꼭대기), Branch(중간노드 - 말단이 아닌 node), leaf(말단 node)
    - pseudo column : 실제 존재하지 않지만 특정한 의도를 가지고 사용하는 컬럼

select first_name, employee_id, manager_id, 
level, sys_connect_by_path(first_name, '/'), connect_by_isleaf, -- pseudo column
connect_by_root first_name

from employees
connect by manager_id = prior employee_id
start with employee_id = 100;