DATABASE - 9장 - 서브쿼리 연습문제

문제 1) 학생중에서 생년월일이 가장 빠른 학생의 학번,이름,생년월일을 출력하시오.

 

select studno 학생번호, name 이름, to_char(birthdate, 'MM/DD/YYYY') 가장빠른생년월일  
from student
where birthdate = (select min(birthdate)
                  from student);

 

 

문제 2) 학년별로 평균 체중이 가장 가벼운 학년과 그 학년의 평균 몸무게를 출력하시오.

 

select grade 학년, avg(weight) 평균몸무게
from student
group by grade
having avg(weight) = (select min(avg(weight))
                      from student 
                      group by grade);

 

문제 3) 학년별 최대 몸무게를 가지는 학생의 이름, 몸무게를 pairwise와 unpairwise 비교방법으로 각각 출력하시오

 

- PAIRWISE

select name, weight
from student
where (grade, weight) in (select grade, max(weight)
                          from student
                          group by grade);

3-1 PAIRWISE

- UNPAIRWISE

select name, weight
from student
where grade in (select grade
                from student
                group by grade)
and weight in (select max(weight)
               from student
               group by grade);

3-2 UNPAIRWISE

 

문제 4) 교수 테이블에서 평균 연봉보다 많이 받는 교수의 교수번호, 이름, 연봉을 출력하시오

            ( 연봉이 높은 순으로 정렬하시오)

 

nvl 사용하여 (급여+수당)*12 = 연봉 

select profno, name, (sal+nvl(comm, 0))*12 연봉
from professor
where (sal+nvl(comm, 0))*12 > (select avg(sal+nvl(comm, 0))*12
                               from professor)
order by 연봉 desc;

 

문제 5) 학과별로 최소 몸무게를 가진 학생을 출력하시오 ( 정렬 : deptno 오름차순)

select name, weight, deptno
from student
where weight in (select min(weight)
                 from student
                 group by deptno)
order by deptno;

 

문제 6) 학과별 평균 키보다 큰 학생의 이름,학번,키를 출력하시오

 

상호연관 서브쿼리를 사용해보자

select name, studno, height
from student s1
where height > (select avg(height)
                from student s2
                where s1.deptno = s2.deptno)
order by studno;

 

문제 7) 다음 2개의 sql문의 출력 결과가 다르다. 다른 이유를 설명하시오

 

1번 SQL 문

2번 SQL 문

 

이유 :  ANY 와 ALL 의 차이는 '최솟값' 과 '최댓값'의 차이이다 ( 'MIN , 'MAX')

           서브쿼리의 결과값이 여러개일 경우 ANY를 사용하면 그 중 최솟값과 비교하게 되며

           ALL 을 사용할 경우 최댓값과 비교하게 된다

           

최솟값 :240 / 최댓값 : 450

 위에 경우는 sal (월급) 의 값이 any 를 사용했을 경우 240보다 큰 교수들의 목록과 all을 사용하면 450 보다 큰 교수들의 목록이 각각 출력된다