SQL문법(View)

1. View의 개념 가. 1개 이상의 테이블을 이용하여 가상의 테이블을 구성하는 것 나. 데이터의 보안과 사용자 편의성을 높이기 위해 생성한다. 2. 뷰의 종류 가. 단순 뷰 : join이 없이 생성된 뷰 나. 복합 뷰 : join이 포함된 뷰 라. 인라인 뷰 : from절에 서브쿼리를 사용하여 생성된 임시 뷰 3. 연습문제 가. 사원테이블(TEMP)에서 사원의 연봉이 동일한 직급을 가진 사원의 평균연봉보다 많이 받는 사원의 사번,이름,연봉을 출력하세요.

select a.emp_id, a.emp_name, a.salary, a.lev
from temp a, (select lev, avg(salary) salary
        from temp
        group by lev) b
where a.salary > b.salary and a.lev=b.lev;

    EMP_ID EMP_NAME       SALARY LEV
---------- ---------- ---------- ----
  19960101 홍길동       72000000 과장
  19930331 정도령       70000000 차장
  19950303 이순신       56000000 대리

나. Temp,tdept 테이블에서 아래처럼 emp_id, emp_name 컬럼을 출력하되 출력결과를 3~6번까지 줄의 데이터만 출력하세요.

select no, emp_id, emp_name, dept_name
from (select rownum no, a.emp_id, a.emp_name, b.dept_name
    from temp a, tdept b
    where a.dept_code=b.dept_code)
where no between 3 and 6;

        NO     EMP_ID EMP_NAME   DEPT_NAME
---------- ---------- ---------- --------------------
         3   19960101 홍길동     재무
         4   20000102 김설악     재무
         5   19970201 박문수     총무
         6   20000203 최오대     총무

다. 학생 테이블에서 학생 이름과 전화번호로 구성되는 뷰 (v_stud_addr)을 만드세요

CREATE VIEW v_stud_addr
AS SELECT name, tel FROM student;

라. 학생 이름과 학생이 소속한 학과 이름으로 구성된 뷰(v_stud_dept)를 만드세요

CREATE VIEW v_stud_dept
AS SELECT a.name, b.dname
   FROM student a, department b
   WHERE a.deptno=b.deptno;

마. 101번 학과 학생들의 학생 이름과 지도 교수 이름을 가지는 뷰 (v_stud_prof)를 정의하세요. 아직 지도교수가 정해지지 않은 학생도 함께 출력하세요.

CREATE VIEW v_stud_prof
AS SELECT s.name "학생이름", p.name "지도교수이름"
   FROM student s, professor p
   WHERE s.profno=p.profno(+);

바. 각 학과에 소속되어 있는 교수 이름과 학생 이름으로 구성되는 뷰(v_all1)를 생성하세요. 출력 칼럼이름은 학과 이름, 학생 이름, 교수 이름입니다.

CREATE VIEW v_all1
AS SELECT d.dname "학과이름", s.name "학생이름", p.name "교수이름"
   FROM department d, student s, professor p
   WHERE d.deptno=s.deptno AND s.profno=p.profno;

사. 학과별 평균 몸무게와 평균 키 정보를 가지는 뷰(v_dept_avg)를 생성하세요.

CREATE VIEW v_dept_avg
AS SELECT d.dname, AVG(s.weight) s_weight, AVG(s.height) s_height
   FROM department d, student s
   WHERE d.deptno=s.deptno
   GROUP BY d.dname;