[데이터베이스] 중첩하위질의, 집합비교

# 3.8 중첩하위질의 (nested subquery)

select-from-where 에서 모두 중첩된 하위 질의 가능하다

 

# in접속사 

집합 멤버십을 테스트. 반대로는 not in 집합 멤버십의 부재

바깥 sql이 안쪽 sql에 들어있는 것들만 출력

 

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

in 접속사 이용해 중첩질의

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년 봄에는 없는 수업을 구하라

외부 sql맞고, 내부 sql 아닌 것

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)

 

# in과 not in연산자들은 열거형 집합에서도 사용할 수 있다.

 

- Mozart도 Einstein도 아닌 교수를 선택하는 질의

select distinct name
from instructor
where name not in ('Mozart', 'Einstein')

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

(sql에서는 where 콜롬명 한개만 가능)

select count(distinct ID)
from takes
where course_id in (select course_id
					from teaches
					where teaches.ID = 10101);

 

takes의 행은 primary key로 작동

# 3.8.2 집합비교

# >some (>any 도 결과 같음)

하나 이상보다 큰

 

# =some은 in과 동일

 

-생물학과의 적어도 한 교수보다도 급여가 많은 모든 교수들의 이름을 구하라

select name
from instructor
where salary > some (select salary
		     from instructor
		     where dept_name = 'Biology')

=같은코드=

select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = 'Biology'

 

5 not in (0,5) = False // 5가 (0,5)에 들어있으므로 

5 != some (0,5) = True // 5가 0(값들중 한 값)과 같지 않기 때문에

 

5 > some (0,5,6) = true

5 < some (0,5) = false

5 = some (0,5) = true

5 <> some (0,5) = true

 

(= some)은 in 과 같음

(<> some) 은 not in 과 같지 않음

 

 

#all

select *
from instructor
where salary > all (select salary
			from instructor
			where dept_name = 'Biology')

select *
from instructor
where dept_name = 'Biology'

 

생물학과의 교수들중 봉급의 최댓값은 77036이므로

모든 교수들중 봉급이 77036이상인 교수들만 출력

 

 

 

 

 

 

 

 

 

 

5 > all (0,5,6) = false

5 < all (6,10) = true

5 = all (4,5) = false

5 <> all (4,6) = true

 

(<> all) 은 not in 과 같음

(= all) 은 in 과 같지 않음

 

 

# exist

그 사실이 있냐없냐만 중요

 

- 2009년 가을학기 S 와 2010년 봄학기 T에 모두 진행된 강좌들 (smallDB)

select course_id
from section as S
where semester = 'Fall' and year = 2009 and
	exists( select *
		from section as T
		where semester = 'Spring' and year = 2010
		and S.course_id=T.course_id)

CS-101

 

# not exist

하위 질의의 결과물에 투플이 존재하지 않음을 테스트할 수 있다.

 

- 생물학과에서 제공하는 모든 수업을 수강하는 학생을 구하라

밖의 select는 각각의 학생을 갖고, 학생이 수강하는 모든 수업들의 집합이 생물학과의 모든 수업의 집합을 포함하는지 테스트

select distinct S.id, S.name
from student as S
where not exists ( (select course_id
			from course
			where dept_name = 'Biology')
			except
			(select T.course_id
			from takes as T
			where S.id = T.id) )

해당 조건에 맞는 결과들이 있으므로 출력이 안됨

exists로 변경시 출력됨 

 

# unique (sql 지원안됨)

하위 질의가 중복된 투플을 가지지 않으면 true

 

- 2009년에 많아야 한 번 제공된 모든 수업을 구하라

select T.course_id
from course as T
where unique (select R.course_id
			 from section as R
			 where T.course_id = R.course_id
				and R.year = 2009)

--> where 1 = count(*) 형식으로 unique대체해서 사용 가능

위 코드와 같은 코드 (실행가능한코드)

select T.course_id
from course as T
where 1 = (select count(R.course_id)
			from section as R
			where T.course_id = R.course_id and
			R.year = 2009)

- 2009년 적어도 두 번 제공되는 모든 수업을 구하라

select T.course_id
from course as T
where not unique (select count(R.course_id)
				  from section as R
				  where T.course_id = R.course_id and
						R.year = 2009)

select T.course_id
from course as T
where 1< (select count(R.course_id)
				  from section as R
				  where T.course_id = R.course_id and
						R.year = 2009)

 

# 3.8.5 From 절의 하위 질의

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

select dept_name, avg_salary
from (select dept_name, avg(salary) as avg_salary
	  from instructor
	  group by dept_name) as dept_avg(dept_name, avg_salary)
where avg_salary>42000;

 

- 각 학과의 총 급여의 최대값을 구하라

select max(tot_salary) as max_tot_salary
from (select dept_name, sum(salary)
	  from instructor
	  group by dept_name) as dept_total(dept_name, tot_salary)