뷰 (View)
- 전체 데이터 중에서 일부만 접근 할 수 있도록 제한하기 위한 가상 테이블 기법
- 데이터의 보안과 사용자의 편의성을 높이기 위해 사용
- 평소 데이터는 가지고 있지 않다가 쿼리가 들어오면 데이터를 가지고 온다.
- 인덱스는 원래 테이블(student)에 만든다.
만드는법
Create view 뷰이름
AS 서브쿼리
단순 뷰 생성
예제 : 학생 테이블에서 101번 학과 학생들의
학번, 이름, 학과 번호로 정의되는 단순 뷰를 생성 하여라.
SQL> CREATE VIEW v_stud_dept101(학번, 이름, 학과번호)
AS SELECT studno, name, deptno
FROM student
WHERE deptno = 101;
SQL> SELECT * FROM v_stud_dept101; → 뷰의 내용 조회
복합 뷰 생성
예제 : 학생 테이블과 부서 테이블을 조인하여 102번 학과 학생들의 학번, 이름, 학년, 학과 이름으로 정의되는 복합 뷰를 생성하여라.
SQL> CREATE VIEW v_stud_dept102(학번, 이름, 학년, 학과이름)
AS SELECT s.studno, s.name, s.grade, d.dname
FROM stduent s, department d
WHERE s.deptno=d.deptno
AND s.deptno=102;
함수를 이용한 뷰 생성
예제 : 교수 테이블에서 학과별 평균 급여와 총계로 정의되는 뷰를 생성하여라.
SQL> CREATE VIEW v_prof_avg_sal
AS SELECT deptno, SUM(sal) sum_sal, AVG(sal) avg_sal
FROM professor
GROUP BY deptno;
인라인 뷰 (inline view)
- FROM 절에 서브쿼리 사용하여 생성한 임시 뷰를 의미
만드는법
Select ~~~
FROM (서브쿼리) , 테이블
Where ~~~
예제 : 인라인 뷰를 사용하여 학과별로 학생들의 평균 키와 평균 몸무게,
학과 이름을 출력하세요.
SQL> SELECT dname, avg_height, avg_weight
FROM ( SELECT deptno, avg(height) avg_height,
avg(weight) avg_weight
FROM student
GROUP BY deptno) s, department d
WHERE s.deptno = d.deptno;
|
s |
|
|
|
deptno |
avg(height) |
avg(weight) |
|
~~~ |
~~~ |
~~~ |
|
~~ |
~~~ |
~~~ |
문제 : 사원테이블(TEMP)에서 사원의 연봉이 동일한 직급을 가진 사원의
평균연봉보다 많이 받는 사원의 사번,이름,연봉을 출력하세요.
SQL> select a.emp_id,a.emp_name, a.salary
from temp a, (select lev, avg(salary) avgsal
from temp
group by lev) b
where a.lev=b.lev
and a.salary > b.avgsal;
문제 : Temp,tdept 테이블에서 아래처럼emp_id,emp_name 컬럼을 출력하되
출력결과를 3-6번까지 줄의 데이터만 출력하세요.
NO EMP_ID EMP_NAME DEPT_NAME
------ ---------- ---------- ------------
3 19970201 박문수 총무
4 19930331 정도령 기술지원
5 19950303 이순신 H/W지원
6 19966102 지문덕 S/W지원
SQL> select rownum no,emp_id, emp_name from temp;
NO EMP_ID EMP_NAME
---------- ---------- --------------------
1 19970101 김길동
2 19960101 홍길동
3 19970201 박문수
4 19930331 정도령
5 19950303 이순신
6 19966102 지문덕
7 19930402 강감찬
8 19960303 설까치
9 19970112 연흥부
10 19960212 배뱅이
11 20000101 이태백
12 20000102 김설악
13 20000203 최오대
14 20000334 박지리
15 20000305 정북악
16 20006106 유도봉
17 20000407 윤주왕
18 20000308 강월악
19 20000119 장금강
20 20000210 나한라
20 rows selected.
SQL> select a.no, a.emp_id, a.emp_name, b.dept_name
from (select rownum no, emp_id, emp_name, dept_code
from temp) a, tdept b
where a.no between 3 and 6
and a.dept_code = b.dept_code;
뷰와 관련된 데이터 딕셔너리
USER_VIEWS 로 사용자가 생성한 모든 뷰를 볼 수 있다.
뷰의 삭제
예제 : 학생 테이블과 연관된 뷰를 삭제하여라.
SQL> DROP VIEW v_stud_dept101;
연습 문제
교수 , 학생, 부서 테이블을 참조하여 다음 질문에 답하세요.
1. 학생 테이블에서 학생 이름과 전화번호로 구성되는 뷰 (v_stud_addr)을 만드세요
2. 학생 이름과 학생이 소속한 학과 이름으로 구성된 뷰(v_stud_dept)를 만드세요
3. 101번 학과 학생들의 학생 이름과 지도 교수 이름을 가지는 뷰 (v_stud_prof)를
정의하세요. 아직 지도교수가 정해지지 않은 학생도 함께 출력하세요.
4. 각 학과에 소속되어 있는 교수 이름과 학생 이름으로 구성되는 뷰(v_all1)를
생성하세요. 출력 칼럼이름은 학과 이름, 학생 이름, 교수 이름입니다.
5. 학과별 평균 몸무게와 평균 키 정보를 가지는 뷰(v_dept_avg)를 생성하세요.
'Oracle > Oracle - SQL' 카테고리의 다른 글
| SQL정리 13장 사용자 권한 제어 (0) | 2012.01.12 |
|---|---|
| SQL정리 11장 Index (0) | 2012.01.11 |
| SQL정리 10장 제약조건 (Constraint) (0) | 2012.01.11 |
| SQL정리 9장 DDL (0) | 2012.01.10 |
| SQL정리 8장 DML (0) | 2012.01.10 |
