DAY 32 - ORACLE DB - 뷰(인라인) (2024.08.16)

[뷰 - 인라인(inline)개념]

: 별칭을 사용하는 서브쿼리 (일반적으로 from절에서 사용)

SELECT ~~ FROM 테이블 WHERE SELECT ~~~

SELECT ~~FROM SELECT ~~~

---

create or replace view v_view7("부서ID", "부서평균")
as select nvl(department_id, 5000), round( avg(salary), -3)
from employees
group by department_id
order by department_id asc;

---

select * from v_view7;

---

select 부서ID, 부서평균
from (select nvl(department_id, 5000) "부서ID", round( avg(salary), -3) "부서평균"
from employees
group by department_id
order by department_id asc);

---

select 부서명, 최대급여
from (select department_name "부서명", max(salary) "최대급여"
from employees
join departments using(department_id)
group by department_name);

---

select 이름, 부서명, 최대급여
from (select last_name "이름", department_name "부서명", max(salary) "최대급여"
from employees
join departments using(department_id)
group by last_name, department_name);

못하겠다~~~~~~

 

---

select department_id, max(salary)
from employees
group by department_id;

 

department_id로 물어봐도 된다는거를 캐치하기 !!

---

select 이름, 부서명, 최대급여
from (select last_name 이름, department_name 부서명, salary 최대급여
from employees
join departments using(department_id)
where department_id=10 and salary=4400);

---

select 이름, 부서명, 최대급여
from (select last_name 이름, department_name 부서명, salary 최대급여
      from employees
      join departments using(department_id)
      where (department_id, salary) in (select department_id, max(salary)
                                  from employees
                                  group by department_id)
      );

select 이름, 부서명, 최대급여
from (select last_name 이름, department_name 부서명, salary 최대급여
      from employees
      join departments using(department_id)
      where (department_id, salary) = any (select department_id, max(salary)
                                     from employees
                                     group by department_id)
      );

---

 

인라인 처리 안 한 것도 똑같다.

select last_name 이름, department_name 부서명, salary 최대급여
from employees
join departments using(department_id)
where (department_id, salary) in (select department_id, max(salary)
                                  from employees
                                  group by department_id);

 

group by는 내가 select한 애들만 가능하다.

partition by는 내가 select한애들 아니여도 가능하다.

 

select 이름, 부서명, 최대급여
from (select last_name "이름", department_name "부서명", max(salary) "최대급여"
      from employees
      join departments using(department_id)
      where salary=max(salary)
      );

---

[뷰 - Top N분석]

    Top N분석 : 조건에 맞는 최상위(최하위) 레코드를 N개 식별해야 하는 경우에 사용

           예) 최상위 소득자3명

                 최근 6개월동안 가장 많이 팔린 제품3가지

                 실적이 가장 좋은 영업사원 5명

 

    오라클에서 Top N분석원리

            - 원하는 순서대로 정렬

            - rownum 이라는 가상의 컬럼을 이용하여 순서대로 순번부여

            - 부여된 순번을 이용하여 필요한 수만큼 식별

            - rownum값으로 특정행을 선택할수 없음

                   (단, Result Set 1st 행(rownum=1)은 선택가능

---

select rownum, last_name, salary
from (select last_name, nvl(salary,0) salary from employees order by 2 desc)
where rownum<=3;

---

select rownum, last_name, salary
from (select last_name, nvl(salary,0)as salary from employees order by 2 desc)
where rownum=1;

 

---

select rownum, last_name, salary
from (select last_name, nvl(salary,0)as salary from employees order by 2 desc)
where rownum=2;

→ rownum=2이면 결과가 안 나온다. (특정 행은 사용할 수 없음)

---

• 방법1

select last_name, nvl(salary, 0) salary from employees order by salary desc;

 

---

가상테이블 뷰를 만든 것처럼 위에 나온 테이블의 이름을 tt로 하겠다.

(select last_name, nvl(salary, 0) salary from employees order by salary desc) tt

---

select rownum , ceil(rownum/3) page, tt.* from
(select last_name, nvl(salary, 0) as salary from employees order by salary desc) tt;

---

select * from
(select rownum , ceil(rownum/3) as page, tt.* from
(select last_name, nvl(salary, 0) as salary from employees order by salary desc) tt
) where page=2;

---

• 방법2

select last_name, nvl(salary,0) as salary from employees order by 2 desc;

---

(select rownum rn, tt.* from
(select last_name, nvl(salary,0) as salary from employees order by 2 desc)tt
);

---

select * from
(select rownum rn, tt.* from
(select last_name, nvl(salary,0) as salary from employees order by 2 desc)tt
) where rn>=4 and rn<=6;

---

select last_name 사원이름, department_name 부서명, salary 연봉 
from employees
join departments using(department_id)
order by salary;

select last_name 사원이름, department_name 부서명, salary 연봉 
from (select last_name, department_name, salary
      from employees
      join departments using(department_id)
      order by salary);

---

select last_name 사원이름, department_name 부서명, salary 연봉 
from (select last_name, department_name, salary*12+(salary*12*nvl(commission_pct, 0)) salary
      from employees
      join departments using(department_id)
      order by salary);

select last_name 사원이름, department_name 부서명, salary 연봉 
from (select last_name, department_name, salary*12+(salary*12*nvl(commission_pct,0)) salary
      from employees
      join departments using(department_id)
      order by salary)
where rownum <=5;

---

select last_name 사원이름, department_name 부서명, to_char(salary, 'L999,999,999') 연봉 
from (select last_name, department_name, salary*1300*12+(salary*12*nvl(commission_pct,0)) salary
      from employees
      join departments using(department_id)
      order by salary)
where rownum <=5;

---

[문제 1] 기말고사 과목별 평균이 높은 3과목을 검색하시오

             평균은 반올림하여 소수이하 둘째자리까지 출력하시오.
테이블 : COURSE, SCORE
컬럼 : ROWNUM, CNO, CNAME, RESULT

select cno, cname, avg(result)
from course
join score using(cno) 
group by cno, cname 
order by 3 desc;

select rownum, tt.* from
(select cno, cname, avg(result)
 from course
 join score using(cno) 
 group by cno, cname 
 order by 3 desc)tt
where rownum <=3;

---

[문제 2] 4.5 환산 평점이 높은 순위로 정렬했을 때 5~7순위의 학생을 검색하시오.
테이블 : STUDENT
컬럼 : RN, SNO, SNAME, AVR

select sno, sname, avg(avr)*4.5/4.0 평점
from student
group by sno, sname
order by 3 desc;

---

select *from(
select rownum rn, tt.* from
(select sno 학번, sname 이름, round(avg(avr)*4.5/4.0, 2) 평점
 from student
 group by sno, sname
 order by 3 desc) tt)
where rn between 5 and 7;

---

[문제 3] 기말고사 평균이 60점 이상인 학생의 정보를 검색하시오
         평균은 반올림하여 소수이하 둘째자리까지 출력하시오
테이블 : STUDENT, SCORE
컬럼 : SNO, SNAME, RESULT

select sno 학번, sname 이름, round(avg(result),2) 평균
from student
join score using(sno)
group by sno, sname;

---

select tt.* from
(select sno 학번, sname 이름, round(avg(result),2) 평균
from student
join score using(sno)
group by sno, sname) tt
where 평균>=60;

select sno 학번, sname 이름, round(avg(result),2) 평균
from student
join score using(sno)
group by sno, sname
having round(avg(result),2)>=60;

---

[문제 4] 기말고사 평균 성적이 핵 화학과목보다 우수한 과목의 과목명과 담당 교수명을 검색하시오
테이블 : COURSE, SCORE, PROFESSOR
컬럼 : CNO, CNAME, RESULT, PNO, PNAME

select cno 과목번호, 
       cname 과목이름, 
       avg(result) 평균, 
       pno 교수번호, 
       pname 교수이름
from professor
join course using(pno)
join score using(cno)
join student using(sno)
group by cno, cname, pno, pname;

---

select tt.* from
(select cno 과목번호, 
       cname 과목이름, 
       avg(result) 평균, 
       pno 교수번호, 
       pname 교수이름
from professor
join course using(pno)
join score using(cno)
join student using(sno)
group by cno, cname, pno, pname)tt
where 평균 > (select avg(result) 
             from course 
             join score using(cno) 
             where cname='핵화학');

select cno 과목번호, 
       cname 과목이름, 
       avg(result) 평균, 
       pno 교수번호, 
       pname 교수이름
from professor
join course using(pno)
join score using(cno)
join student using(sno)
group by cno, cname, pno, pname
having avg(result) > (select avg(result) 
                      from course 
                      join score using(cno) 
                      where cname='핵화학');

---