Intro to Database (10) : View, Top, Index, Sequence

2019-07-10

• View : 한 개 이상의 테이블이나 뷰로 만들어진 가상 테이블로 데이터 자체를 포함하지는 않는다. 
  • Simple View - 단일 테이블로 만듬
  • Join View - 2개 이상의 테이블로 만들어짐
  • Inline View - from 절에 테이블 대신 select 절이 들어감 
• TOP-N Query : 상위 몇개의 레코드만 추출 
• Index : 각 레코드에 빠르게 접근하기 위한 스키마 객체
• Sequence : 자동으로 기본키를 연속된 정수로 만들어주는 데이터베이스 객체 

실습(1) 

--dept 20 name, salary*12;
create or replace view emp_view
as select ename, sal*12 annual_salary
from emp where deptno=20;

--컴퓨터정보인 학생을 이용하여 뷰만들기
create or replace view V_student
as select * from student
where  stu_dept= '컴퓨터정보';

--enrol, subject, 테이블에서 과목이름, 과목번호, 학생법호, 성적을 보여주는 뷰 만들기
create or replace view v_enrol
as select sub_name, e.sub_no, stu_no, enr_grade
from enrol e, subject s
where e.sub_no=s.sub_no;
--학과별 평균신장보다 큰 학생들의 학번, 이름, 신장을 검색
create or replace view v_student1
as select stu_no, stu_name, stu_height from student a,
(select stu_dept, round(avg(stu_height)) avg_height from student group by stu_dept) b
where a.stu_dept=b.stu_dept 
and stu_height>b.avg_height;

--학생테이블의 상위 3개 레코드
select * from student where rownum<=3;

--학생테이블에서 신장이 큰 상위 5명의 학번, 이름 ,신장을 검색
select stu_no, stu_name, stu_height from 
(select * from student where stu_height is not null order by stu_height desc )
where rownum<=5;

--학생이름으로 인덱스 생성, 중복된 값을 사용할 수 있음
create index i_stu_name on student(stu_name);

--학번과 이름을 합쳐서 인덱스 생성, 중복된 값을 사용할 수 이씀
create index i_stu_no_name on student(stu_no, stu_name);

--유일한 값으로 인덱스 생성, 중복값을 사용할 수 없음
create unique index i_stu_name on student(stu_name);

--함수나 수식을 이용하여 인젝스를 생성
create index i_stu_weight on student(stu_weight-5);

--인덱스 검색
select * from user_indexes where table_name = 'STUDENT';

--인덱스 삭제
drop index i_stu_weight;

--Login as your account and complete the problems
--20. 사원테이블에서 20번부서 사원들로 이루어진 뷰 (v_emp20)를 생성하고 데이터검색
create or replace view v_emp20
as select * from emp where deptno='20';

--22. 사원번호, 이름, 부서이름을 가지는 뷰 (v_emp_dept)를 생성하고 데이터를 검색
create or replace view v_emp_dept
as select empno, ename, dname from emp e, dept d where e.deptno=d.deptno;

--24. 부서별 사원중 가장 최근에 입사한 사원보다 먼저 입사한 사원의 정보를 검색
select * from emp s1 ,
(select deptno, max(hiredate) max_hiredate from emp group by deptno) s2 where s1.deptno=s2.deptno
and s1.hiredate < s2.max_hiredate; 

--25. 가장 최근에 입사한 5명의 사원번호, 이름, 입사일을 검색
select empno, ename, hiredate from
(select * from emp order by hiredate desc) where rownum<=5;

--26. 급여와 커미션을 합한 금액으로 상위 7명의 사원번호, 이름검색
select empno, ename from
(select empno,ename,nvl(sal+comm,sal) total from emp order by total desc)
where rownum<=7;

--27. 부서별 평균급여가 가장 큰 부서 2개의 부서이름 검색
select dname from dept d,
(select e.deptno, round(avg(sal)) avg_sal from emp e
group by deptno order by avg_sal desc) s
where d.deptno=s.deptno and rownum<=2; 

select dname from (select deptno from emp natural join dept 
group by deptno order by avg(sal) desc) 
join dept using(deptno) where rownum <=2;

--28. 사원직무별 급여 표준편차가 큰 상위2개의 사원직무 검색
select job from 
(select job, round(stddev(sal)) stddev_sal from emp group by job order by 2 desc)