18. 서브쿼리(2)

1. 인라인 뷰
- from절 사용 서브쿼리
- 하나의 테이블처럼 사용

예제) emp에서 최소 급여자 출력

 

sol)

select *
  from emp e, (select deptno, min(sal) as min_sal
                 from emp
                group by deptno) i
 where e.deptno = i.deptno
   and e.sal = i.min_sal;

 

 

예제) PROFESSOR 에서 학과별 평균급여보다 낮은 급여를 받는 교수의 이름, 학과명, 급여를
     각 교수의 소속 학과의 평균급여와 함께 출력

 

sol)
select deptno, avg(pay) as avg_pay 
  from professor
 group by deptno;
 
select p.name, d.dname, p.pay, i.avg_pay
  from professor p, 
       department d,
      (select deptno, avg(pay) as avg_pay 
         from professor
        group by deptno) i
 where p.deptno = i.deptno
   and p.pay < i.avg_pay
   and p.deptno = d.deptno;

 

 

 

 

2. 스칼라 서브쿼리
- select절에 사용되는 서브쿼리
- 마치 하나의 컬럼처럼 특정 쿼리 결과를 출력하기 위해 사용
- 서브쿼리 결과가 각 행마다 하나의 값이 매칭되도록 출력 필요
- outer join을 명시하지 않아도 데이터 생략 발생하지 X


예제) 각 직원의 이름, 급여, 부서번호, 전체평균급여 출력

sol)

select ename, sal, deptno, avg(sal)   -- error
  from emp;

 

 

 

select ename, sal, deptno, 
       (select round(avg(sal)) from emp) as avg_sal
  from emp;

 

 

 

예제) 각 직원의 이름, 급여, 부서번호, 각 직원의 소속부서의 평균급여를 함께 출력
select ename, sal, deptno, 
       (select round(avg(sal)) 
          from emp
         group by deptno) as avg_sal  -- error(각 행마다 하나의 값이 매칭이 되어야 하는데
  from emp;                            -- 서브쿼리 결과가 여러개 행이 리턴되므로 에러 발생)

 

 

 

select ename, sal, deptno, 
       (select round(avg(sal)) 
          from emp e2
         where e1.deptno = e2.deptno) as avg_sal  
  from emp e1;
  

 

 

예제) student, professor 테이블을 사용하여
     각 학생의 이름, 학년, 지도교수이름 출력(단, 스칼라 서브쿼리로 풀이)

 

sol1)
select s.name, s.grade, p.name
  from student s, professor p
 where s.profno = p.profno(+);

 

 

 

sol2)
select s.name, s.grade, (select p.name
                           from professor p
                          where s.profno = p.profno) as 지도교수명
  from student s;