중첩 하위 질의

- SQL은 중첩된 하위 질의 메커니즘을 제공함

- 하위질의는 다른 질의 안에 중첩된 select-from-where 표현

- 하위 질의를 where절에서 중첩 시켜 집합의 멤버십을 테스트하고, 집합 비교, 집합의 원소 개수를 결정하는데 사용

예) 바깥 row 하나씩 가져와서 안쪽 목록에 있는지 확인

- 2009년 가을 학기와 2010년 봄 학기에 둘 다 있는 수업을 구하라

  select distinct course_id

  from section

  where semester = ’Fall’ and year= 2009 and

           course_id in (select course_id

                             from section

                             where semester = ’Spring’ and year= 2010);

- 2009년 가을 학기에는 있지만 2010년 봄학기에는 없는 수업을 구하라

  select distinct course_id

  from section

  where semester = ’Fall’ and year= 2009 and

           course_id not in (select course_id

                                  from section

                                  where semester = ’Spring’ and year= 2010);

- ID 10101의 교수가 가르치는 수업 분반을 수강하는 학생의 수를 구하라

  select count (distinct ID)

  from takes

  where (course_id, sec_id, semester, year) in

                         (select course_id, sec_id, semester, year

                          from teaches

                          where teaches.ID= 10101);

 

# from 절의 하위 질의

- SQL 은 from 절에서 하위 질의 표현을 허용함

- having 절 사용할 필요 없음

- as 절로 릴레이션 이름 붙임

- 평균 급여가 $42000 이상인 학과의 교수들의 평균 급여를 구하라

  select dept_name, avg_salary

  from (select dept_name, avg (salary) as avg_salary

          from instructor

          group by dept_name)

  where avg_salary > 42000;

 

  select dept_name, avg_salary

  from (select dept_name, avg (salary)

          from instructor

          group by dept_name) as dept_avg (dept_name, avg_salary)

  where avg_salary > 42000;

 

# lateral 절

  select name, salary, avg_salary

  from instructor I1, lateral (select avg(salary) as avg_salary

                                    from instructor I2

                                    where I2.dept_name= I1.dept_name);

 

# with 절 – 바로 다음 sql에만 유효

- with 절은 임시 릴레이션을 정의함

- 가장 많은 예산을 가진 학과를 구하라

with max_budget (value) as

          (select max(budget)

           from department)

select dept_name, budget

from department, max_budget

where department.budget = max_budget.value;

- 학과의 총 급여가 평균 학과의 총 급여보다 많은 모든 학과를 구하라

with dept _total (dept_name, value) as

           (select dept_name, sum(salary)

            from instructor

            group by dept_name),

      dept_total_avg(value) as

            (select avg(value)

             from dept_total)

select dept_name

from dept_total, dept_total_avg

where dept_total.value >= dept_total_avg.value;

 

# 스칼라 하위 질의

- 모든 학과와 그 학과별의 교수들의 수를 함께 나열하라. 신설학과는 instructor에 없음.

  select dept_name,

           (select count(*)

            from instructor

            where department.dept_name = instructor.dept_name)

            as num_instructors

  from department;