SQL [13] - SUB QUERY 서브 쿼리 (단일행, 다중행)

<SUB QUERY>
select 구문 내부에 select를 사용하는 것입니다.

(1) 단일행 서브쿼리 : 서브쿼리의 결과가 1개인 경우.
사용 가능 연산자 : = , > , < , >= , <= 

 

-- Quiz, emp 테이블에서 'scott'보다 급여가 많은 사람의 이름과 급여를 출력하기

 

1단계 : scott의 급여를 select로 구하기 (단일행 서브쿼리)

select sal from emp where ename = 'SCOTT';

처음부터 조건으로 '3000보다 급여가 큰 사람'으로 나오면 '>= 3000'을 where 조건에 넣어주면 좋겠지만,

그렇지 않을 경우는 '>= (select sal from emp where ename = 'SCOTT')'를 where 조건에 넣어줘야 합니다.

 

2단계 : 위의 구문을 where 조건에 이용하여 위의 데이터 조회하기 (서브쿼리식은 괄호로 묶어줘야합니다.)

select ename, sal

from emp

where sal >= (select sal from emp where ename = 'SCOTT')

;

 

 

-- Quiz. 김진욱 학생보다 높은 학년의 이름, 학년, 학과번호, 학과명 출력하기

 

1단계 : 김진욱 학생의 학년을 출력하기 (단일행 서브쿼리)

select grade from student where name = '김진욱';

 

2단계 : 위의 구문을 where 조건에 이용하여 위의 데이터 조회하기 (서브쿼리식은 괄호로 묶어줘야합니다.)

다만, 해당 문제는 이름,학년,학과번호는 student 테이블 / 학과명은 department 테이블에 존재하는 데이터이기 때문에,  조인을 사용해줘야합니다. (등가조인을 이용합니다.)

select s.name, s.grade, s.deptno1, d.dname

from student s, department d

where s.deptno1 = d.deptno and

s.grade > (select grade from student where name = '김진욱')

;

-- Quiz 김진욱 학생과 같은 과 학생의 이름, 학년, 학과번호, 학과명 출력하기 (오라클, ANSI)

-- 오라클
select s.name, s.grade, s.deptno1, d.dname
from student s, department d
where deptno1 = (select deptno1 from student where name = '김진욱') and s.deptno1 = d.deptno
;

 

-- ANSI
select s.name, s.grade, s.deptno1, d.dname
from student s join department d
on s.deptno1 = d.deptno
where s.deptno1 = (select deptno1 from student where name = '김진욱')
;

 

(2) 다중행 서브쿼리 : 서브쿼리의 결과가 여러개인 경우. 
사용 불가능 연산자 : = , > , < , >= , <=

사용 가능 연산자 : in, any, all 
> any : 조회된 서브쿼리의 결과 중 한개만 일치. > min(값) 변환 가능
> all : 조회된 서브쿼리의 결과 중 한개만 일치. > max(값) 변환 가능
< any : 조회된 서브쿼리의 결과 중 한개만 일치. < max(값) 변환 가능
< all : 조회된 서브쿼리의 결과 중 한개만 일치. < min(값) 변환 가능

 

-- Quiz. emp2 테이블과 dept2 테이블을 참조하여 근무 지역이 서울 지사인 모든 사원의 사번, 이름, 부서번호 조회하기

1단계 : 근무지역이 서울지사인 dcode를 dept2에서 서브쿼리 조회하기 (4개의 행, 즉 다중 행 출력된다.)

select dcode from dept2 where area='서울지사'

2단계 : 위의 다중행 서브쿼리를 조건으로 이용하여 조회식 쿼리 작성하기
select empno, name, deptno
from emp2 
where deptno in (select dcode from dept2 where area='서울지사');

select empno, name, deptno
from emp2 
where deptno = (select dcode from dept2 where area='서울지사');

** 만약 다중행 서브쿼리에 =를 넣으면 에러가 납니다.

 

-- Quiz. 교수테이블에서 학과별로 입사일이 가장 오래된 교수의 교수번호, 이름, 학과명을 출력하기.

-- 학과 이름으로 정렬하기
select profno, name, deptno
from professor
where (deptno, hiredate) in (select deptno, min(hiredate) from professor group by deptno )
;