/*- 10_view.sql -*/

/*
 * # view
 * - 물리적인 테이블을 이용한 논리적인 가상 테이블.
 * - 실질적인 데이터를 저장하고 있지는 않더라도 사용자는 마지 테이블을 사용하는 것과 동일
 *   view를 사용가능.
 * - view는 기본 테이블에 대한 하나의 쿼리문, 실제 테이블에 저장된 데이터를 view를 통해서 조회가능.
 * - view 정의
 *   CREATE VIEW view_name [(alias, ...)]
 *   AS
 *   SUBQUERY;
 */
 
-- # VIEW 생성 권한 부여 ( SYSTEM )
--
GRANT CREATE VIEW TO SCOTT;

/*
 * # 단순뷰                  복합뷰
 * - 하나의 테이블로 생성.      여러개의 테이블로 생성.
 * - 그룹 함수 사용 가능.      그룹 함수 사용 불가.
 * - DML 사용 가능.          DML 사용 불가.
 */

-- 연습용 테이블
CREATE TABLE DEPT_COPY AS SELECT * FROM DEPT;
CREATE TABLE EMP_COPY AS SELECT * FROM EMP;

SELECT * FROM DEPT_COPY;
SELECT * FROM EMP_COPY;

-- 부서번호 30번에 소속된 사원의 사원번호, 사원이름, 부서번호 조회.
SELECT EMPNO, ENAME, DEPTNO FROM EMP_COPY WHERE DEPTNO=30;

-- 부서번호 30번에 소속된 사원의 사원번호, 사원이름, 부서번호 확인하는 select문을 하나의 VIEW로 정의.
CREATE VIEW EMP_DEPTNO_30 AS SELECT EMPNO, ENAME, DEPTNO FROM EMP_COPY WHERE DEPTNO=30;

SELECT * FROM EMP_DEPTNO_30;

-- EMP_DEPTNO_30 VIEW 를 사용해서 데이터 추가
INSERT INTO EMP_DEPTNO_30 VALUES (7002,'GPT_3.5',30 );
SELECT * FROM EMP_DEPTNO_30;
SELECT * FROM EMP_COPY;

-- 부서별 급여 총액과 평균을 구하는 VIEW
CREATE VIEW EMP_SAL_VIEW AS SELECT DEPTNO, SUM(SAL) "SAL SUM", TRUNC(AVG(SAL), 1) "SAL AVG" FROM EMP_COPY GROUP BY DEPTNO;
SELECT * FROM EMP_SAL_VIEW;
DROP VIEW EMP_SAL_VIEW;

/*
 * # 복합뷰
 */ 
 
-- 사원 테이블과 부서 테이블을 조인한 복합뷰 생성.
-- 사원번호, 이름, 급여, 부서번호, 부서명, 지역명 출력
CREATE VIEW EMP_DEPT_VIEW AS SELECT E.EMPNO, E.ENAME, E.SAL, E.DEPTNO, D.DNAME, D.LOC FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO ORDER BY EMPNO DESC;

SELECT * FROM EMP_DEPT_VIEW;
DROP VIEW EMP_DEPT_VIEW;

/* 
 * # VIEW 수정
 * - CREATE OR REPLACE VIEW 를 사용해서 VIEW를 수정.
 *   이때, 존재하지 않는 VIEW라면 새로운 VIEW를 생성하고, 기존에 존재하는 VIEW이면 내용변경.
 */

--EMP_DEPTNO_30 VIEW에 급여, 커미션 추가
CREATE OR REPLACE VIEW EMP_DEPTNO_30 
AS SELECT EMPNO, ENAME, SAL, COMM, DEPTNO
FROM EMP_COPY
WHERE DEPTNO=30;

SELECT * FROM EMP_DEPTNO_30;

---------------------------------------------------------------------------------------------------------------------------------------------------------

/*
 * # ROWNUM
 * - 조회된 값에 번호를 부여할 때 사용.
 */
 
SELECT ROWNUM, EMPNO, ENAME, HIREDATE FROM EMP;

SELECT ROWNUM, EMPNO, ENAME, HIREDATE FROM EMP ORDER BY HIREDATE;

-- 입사일을 기준으로 오름차순 정렬한 VIEW 생성
CREATE OR REPLACE VIEW HIREDATE_VIEW AS SELECT EMPNO, ENAME, HIREDATE FROM EMP ORDER BY HIREDATE;

SELECT * FROM HIREDATE_VIEW;
 

-- 입사일이 빠른 사람 순서로 5명 조회
SELECT ROWNUM, EMPNO, ENAME, HIREDATE FROM HIREDATE_VIEW WHERE ROWNUM<=5;

/*
 * # 인라인 뷰
 * - 메인쿼리의 SELECT문의 FROM 절 내부에 사용된 서브쿼리.
 */

SELECT ROWNUM, EMPNO, ENAME, HIREDATE FROM (SELECT EMPNO,ENAME,HIREDATE FROM EMP ORDER BY HIREDATE) WHERE ROWNUM<=5;

SELECT * FROM DEPT_COPY;
/* QUIZ */
-- 각 부서별 최대 급여와 최소 급여를 출력하는 'SAL_MAX_MIN' VIEW 를 생성하세요
CREATE OR REPLACE VIEW SAL_MAX_MIN AS SELECT D.DNAME "부서명", MAX(E.SAL) "최대 급여", MIN(E.SAL) "최소 급여" FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO GROUP BY D.DNAME;
SELECT * FROM SAL_MAX_MIN;
-- 인라인 뷰를 사용해서 급여를 많이 많는 순서대로 7명을 출력하세요
SELECT ROWNUM, EMPNO, ENAME, SAL FROM (SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL DESC) WHERE ROWNUM<=7;