DB 03/13

VIEW(가상의 테이블) : 원하는 정보들로만 구성된 조회 목적의 테이블

--기본 형식

CREATE or REPLACE view 테이블 이름 (컬럼에 대한 별칭 혹은 컬럼 이름) AS
SELECT

-- 부서테이블로 부터 사번,입사일,직종,이름만을 조회하는 view를 생성

CREATE or REPLACE view MYEMP (empno, hire, job, name) AS
SELECT employee_id, hire_date, job_id, first_name
FROM employees;

--만들어진 view 조회

SELECT * FROM MYEMP;

--MYEMP로부터 이름이 Julia인 사원을 '홍길동'으로 변경

UPDATE MYEMP SET name = '홍길동'
WHERE name = 'Julia'
commit;

--MYEMP에 정보를 추가해보자
--MYEMP에 정보를 추가하려면 employees테이블에 있는 모든 컬럼에 대한 정보가
   insert 되어야 하므로, 아래의 문장처럼 VALUES에 일부의 데이터만 추가되어 있는 경우 insert가 불가

insert into MYEMP values(108,'03/13/2023',IT_PROG','박상수')  -- 오류
commit;

--별칭 및 employees의 컬럼을 지정을 하지 않고 * 을 사용했을때

CREATE or REPLACE view MYEMP AS  -- (컬럼에 대한 별칭 혹은 컬럼 이름) 이 없으면 원래 employees에 있는 컬럼 이름으로 나온다.
SELECT *       -- employee_id, hire_date, job_id, first_name 컬럼을 지정하지 않고 * 을 사용하면 전체 컬럼의 정보를 가져온다.
FROM employees;

-- 사원테이블의 모든 정보 (s.*)에 full_name을 추가한 VIEW

CREATE or REPLACE view MYEMP AS
SELECT s.*, concat(first_name,last_name) full_name -- employees테이블이 s의 모든 컬럼과, full_name이라는 새로운 컬럼을 만들고 MYEMP VIEW 생성
FROM (SELECT * FROM employees) s;  -- employees 의 테이블에 대한 별칭 s 지정

--VIEW 삭제하기

DROP VIEW myemp;

--성적확인을 위한 테이블 생성

1. 시퀀스 생성

CREATE SEQUENCE SEQ_SCORE_NO;

2. 성적관리 테이블 생성

CREATE table SCORE (
no NUMBER(3) primary key,
kor NUMBER(3) not null,
eng NUMBER(3) not null,
math NUMBER(3) not null
);

3.성적관리 테이블에 값 추가

INSERT into SCORE values( SEQ_SCORE_NO.nextVal, 100, 70, 100 );
INSERT into SCORE values( SEQ_SCORE_NO.nextVal, 92, 40, 40 );
INSERT into SCORE values( SEQ_SCORE_NO.nextVal, 80, 60, 40 );
commit;

4. Score 테이블에 총점과 평균을 추가한 view를 생성

RANK() OVER

CREATE or REPLACE VIEW SCORE_view AS
SELECT s.* , ( kor + eng + math ) total, round((kor+eng+math)/3, 1) avg, 
         RANK() OVER (order by (kor+eng+math) DESC ) rank  -- RANK() OVER 기본형식, 순위를 매기고 값을 지정함
FROM ( select * from score) s;
ORDER BY no;