오라클 | 서브쿼리 ( select절, from절, where절 )

 1. select 절 서브쿼리 : select절에 (select, from, where) 넣기 , 하나의 열처럼사용, 데이터의 양이많을수록 속도 느려짐
 2. from절 서브쿼리 : 가장많이사용 , 하나의 테이블처럼사용, 복잡한 집계·정렬·필터링을 조합하는 데 유리함
 3. where 절 서브퀄 : 가장많이사용

 

 

*** select 절 서브쿼리 연습문제

-- 1. 사원 테이블에서 모든 사원의 이름과 해당 사원의 부서명을 함께 출력하라.

       select FIRST_NAME,

               ( select  DEPARTMENT_NAME 

                 from departments d 
                 where d.DEPARTMENT_ID = e. DEPARTMENT_ID  

                 ) as 부서명
        from employees e 

 

employees  테이블

 

 

departments 테이블

 

 

1번 조회된 결과

 

 

 

-- 2. 각 사원의 이름, 급여, 그리고 전체 사원 중에서 급여 순위(rank)를 구하라.
        select FIRST_NAME, salary,

              ( select  COUNT(*)+1   -- COUNT(*) : 나보다 급여가 높은 사람 수
                  FROM employees e2  -- 비교할 전체 직원
                 WHERE e2.salary > e1.salary

               ) as 순위     
       from employees e1 --  현재 보고 있는 직원
       order by salary desc

 

2번 조회된 결과

 

 

 

 

** from절 서브쿼리
  1 . 부서별 평균 급여를 구하라. (서브쿼리로 부서별 평균 급여를 만든 뒤 그 결과를 조회)
select  *
from ( select department_id, round(avg(salary),2) as 평균급여 
        from employees 
        group by department_id
        )

 

1번 조회된 결과

 

 

2. 최근 1년간 주문 건수만을 계산하여 고객명과 함께 보여줘라. (Hint: 서브쿼리에서 1년간 주문만 필터링)
select count(*) 건수 , a.customer_name
from customers a
join ( select * 
       from ORDERS 
       where order_Date between to_date('24/08/01','RR/MM/DD')
       and to_date('25/08/01','RR/MM/DD')
       ) b
on a.customer_id= b.customer_id
group by a.customer_name
       

orders 테이블

 

 

customers  테이블

 

 

2번 조회된 결과

 

 

 

     3. 총 매출 상위 3개 카테고리를 출력하라

        select c.category_name as 카테고리
           from categories c
        join ( select category_id, sum(quantity)
                 from sales
                 group by category_id
                order by sum(quantity) desc
              FETCH FIRST 3 ROWS ONLY
               ) s
on c.category_id= s.category_id

 

 

categories 테이블

SALES 테이블

 

 

3번 조회된 결과

 

 

 

      4. 사원 중에서 각 부서 내에서 급여가 가장 높은 사원만 출력하라.
        select e1.first_name, e1.salary
          from employees e1
      join
            ( select department_id, max(salary) as max_salary
               from employees
               group by department_id
             )e2 -- 부서별 최고 급여
   on e2.department_id=e1.department_id
   and e1.salary= e2.max_salary

 

4번 조회된 결과

 

 

      5.  전체 제품 판매량 대비 카테고리별 비율을 출력하라. (Hint: 전체 합계를 구하는 서브쿼리와 카테고리 집계를 join)
        select s.*
          from categories 
   join 

         ( select category_id, sum(quantity)as 합계, round((sum(quantity)/ count(*))/100,3) as 비율 
             from sales
            group by category_id        
          ) s
    on s.category_id= c.category_id

 

 

5번 조회된 결과

 

 

 

** where절 서브쿼리

1. 평균 급여보다 높은 급여를 받는 사원을 출력하라.

select first_name, salary
from employees
where salary >  all ( select avg(salary)
                         from employees

 

 

1번 조회된 결과

 

 

2. 한 번도 주문을 하지 않은 고객을 출력하라.

select o.customer_id
from orders o
left join customers c
    on o.customer_id = c.customer_id
where c.customer_id is null

 

2번 조회된 결과

 

### 추가적인 내용

- 그룹함수 ( count, sum, avg, max, min )
 : 여러행의 데이터를 입력받아 하나의 값을 출력

 

- where 절 서브쿼리

 1) 단일값 서브쿼리 ( =, <>, <, <=, >, >= )

 2) 다중값 서브쿼리 ( ANY, SOME, ALL )

 3) 다중행 서브쿼리 ( exists )

 

 

 

# 서브쿼리 참고 사이트

https://suy379.tistory.com/106

SQL 서브쿼리(Sub Query) 예제 - select절, from절, where절

 

 

https://ohjinn.tistory.com/89

where절 서브쿼리