‘DB’ 수업을 듣는 학생 중 ‘A’ 이상을 받은 학생의 학번, 이름, 과명을 구하시오

select s.student_num
          , s.student_name
          , (                                   -- 스칼라서브쿼리
             select d.department_name
             from department d
             where d.department_num = s.department_no
            ) as ‘학과’
from (                                          -- 인라인뷰
      select  s.student_name
            , s.student_num
            , avg(g.grade) as score
            , s.department_no
      from    student s
      		, grade g
      where s.student_num = g.student_num
      and   g.class_nm = ‘DB’
      group by s.studnet_name, student_num, department_no
      ) s
where 1 = 1
and   s.score >= (                              -- 단일행 서브쿼리
                  select score
                  from   score_std
                  where  grade_nm = ‘a’
                  );

서브쿼리란?
다른 쿼리 내부에 포함된 SELECT 쿼리이다. 서브쿼리의 결과값을 통해 기능을 수행하는 영역은 메인쿼리(main query)이다.

서브쿼리의 종류

single row subquery

단일행을 조회하는 쿼리로 비교 연산자 =, >, < >=, <= 등을 사용한다.

select *
from   student
where  score >= (
                  select score
                  from score_std
                  where grade_nm = ‘a’
                );

multi row subquery

결과가 여러행인 경우 조회하는 쿼리로 all, any, in, any/some 등을 사용한다.

select *
from student
where student_num in (
                      select g.studnet_num
                      from   class
                      where  class_nm = ‘DB'
                      );

correlative subquery(연관서브쿼리)

메인쿼리에 제시된 테이블이 서브쿼리 내에 제시된 경우이다. 메인쿼리가 먼저 실행된 후, 서브쿼리가 실행된다.

select student_num
from student s
where height > (
                select avg(height)
                from   student
                where  department_no = s.department_no
                );

scalar subquery (스칼라 서브쿼리)

단일 컬럼을 반환하며 메인쿼리의 SELECT절에 이용된다.

select student_num
, (
   select d.department_name
   from   department d
   where  d.department_num = s.department_no
  ) as department_name
from student;

데이터 건수가 적은 경우, join보다 유리하다. 단일 컬럼을 반환하지 않으면 오류가 발생하며 결과값이 같은 경우가 많거나, 매번 동일한 결과값을 반환하면 캐싱을 통해 성능이 좋아진다.
조건 데이터가 변경되는 경우, 성능이 저하된다.
스칼라 서브쿼리는 쿼리 수행 횟수를 줄이기 위해 입력값과 출력값을 내부 캐시에 저장한 후, 해당하는 값이 있는 경우 출력값을 리턴하고 아닌 경우 재수행하기 때문이다.
성능이 저하된 경우 레프트 아우터 조인을 통해 성능을 향상 시킬 수 있다.

inline view

from절에 사용되는 서브쿼리이다. 뷰처럼 sql 실행 시에만 ‘임시적’으로 생성된다. top n 쿼리에서 많이 이용된다. with절로 변경이 가능하다. with 절은 오라클 옵티마이저가 쿼리를 인라인뷰나 임시 테이블로 여기기 때문이다.

• top n 쿼리 : 상위 n개의 데이터를 추출하는 쿼리

-- 컴퓨터공학과를 다니는 학생 중 키가 세번째로 큰 학생의 학번, 이름을 조회하시오.
select s.student_num
     , s.student_name
from  (
        select student_num
             , student_name
             , row_number() over (order by height) as seq
        from   student
        where  department_name = '컴퓨터공학과'
      ) s
where s.seq <= 3;