[SQL] 복잡한 쿼리를 단순하게 2 - VIEW

문제1. 위 예제

create or replace view dept_avg
as
 select deptno, round(avg(sal)) avgsal
 from emp
 group by deptno;
 
select * from dept_avg;

문제2. emp 와 dept_avg 뷰를 조인해서 이름과 부서번호, 월급, 자기가 속한 부서번호의 평균월급을 출력하시오

select e.ename, e.deptno, e.sal, da.avgsal
 from emp e, dept_avg da
 where e.deptno = da.deptno;

문제3. 위의 결과를 다시 출력하는데 자기의 월급이 자기가 속한 부서번호의 평균월급보다 더 큰 사원들만 출력하시오

select e.ename, e.deptno, e.sal, da.avgsal
 from emp e, dept_avg da
 where e.deptno = da.deptno and e.sal > da.avgsal;

문제4. emp21 테이블에서 이름, 나이, 통신사, 통신사별 평균나이를 출력하시오

select ename, age, telecom, round(avg(age) over (partition by telecom)) 통신사_평균나이
 from emp21;

문제5. 위의 결과를 뷰로 생성하시오. 뷰 이름은 emp21_telecom

create or replace view emp21_telecom
as
 select ename, age, telecom, round(avg(age) over (partition by telecom)) 통신사_평균나이
  from emp21;
  
select * from emp21_telecom;

문제6. emp21 테이블에서 자기의 나이가 자기가 속한 통신사의 평균나이 보다 큰 학생들만 출력하시오

 select * 
 from emp21_telecom
 where age > 통신사_평균나이;

문제. emp21 테이블에서 각각 통신사별로 나이가 가장 많은 학생들 즉 통신사별 나이순위가 1등인 학생들의 이름과 나이와 통신사를 출력하시오(view를 만들어 쿼리 또는 from 절의 서브쿼리를 이용)

view 사용

create or replace view emp21_rank
as
 select ename, age, telecom, rank() over (partition by telecom
 										  order by age desc) 순위
  from emp21;
  
  
select * from emp21_rank;


select *
 from emp21_rank
 where 순위 = 1;

서브쿼리 사용

select ename, age, telecom, 순위
  from ( select ename, age, telecom, rank() over (partition by telecom 
  												  order by age desc) 순위
          from emp21 )
  where 순위 = 1;