create [or replace] [force | noforce ] view 뷰이름 [(alias [,alias,.....)] as 서브쿼리
[with check option [constraint 제약조건이름]]
[with read only [constraint 제약조건이름]]

create or replace view v_view1
as select employee_id, last_name, salary, department_id from employees
where department_id=90;

select * from v_view2;
update v_view2 set salary=12000 where employee_id=103;
select * from v_view2; ← 103사원이 빠졌음(범위를 벗어남)

create or replace view v_view5
as select employee_id, last_name, job_id
from employees
where job_id='IT_PROG'
with read only;

select * from v_view5;
delete from v_view5;

create or replace view v_view6
as select employee_id, last_name, email, hire_date, job_id
from employees
where job_id='IT_PROG'
with check option;

select * from v_view6;

insert into v_view6(employee_id, last_name, email, hire_date, job_id)
values(500,'kim','candy','2004-01-01','Sales'); 
→ 에러:with check option제약조건에 위배

update v_view6 set job_id='Sales' where employee_id=103;
→ 에러:with check option제약조건에 위배

insert into v_view6(employee_id, last_name, email, hire_date, job_id)
values(500,'kim','candy','2004-01-01','IT_PROG');

select * from v_view6;

TableName : bookshop

isbn   varchar2(10)   기본키(제약조건명:PISBN)  ISBN
title    varchar2(50) 널값 허용X (제약조건명:CTIT) 책제목
author  varchar2(50)  저자 
price   number    	  금액
company varchar2(30)  출판사
-------------------------------------------------------
is001 자바3일완성      김자바 25000  	야메루출판사
pa002 JSP달인되기      이달인 28000  	 공갈닷컴
or003 오라클무작정따라하기  박따라 23500  야메루출판사
-------------------------------------------------------

TableName : bookorder

idx  number    		primary key  일련번호     
isbn varchar2(10)	FKISBN  bookshop의 isbn의 자식키
qty  number     	수량

-----------------------------------
시퀀스명 : idx_seq  증가값: 1 시작값 1
-----------------------------------
1     is001   2
2     or003   3
3     pa002   5
4     is001   3
5     or003  10

↑ 시퀀스객체 이용

create or replace view v_view7("부서ID", "부서평균")
as select nvl(department_id, 5000),
      round( avg(salary), -3)
      from employees
      group by department_id
      order by department_id asc;
      
select * from v_view7;

select 부서ID, 부서평균
from (select nvl(department_id, 5000) "부서ID", 
      round( avg(salary), -3) "부서평균"
      from employees
      group by department_id
      order by department_id asc);

select rownum, last_name, salary
from (select last_name, nvl(salary,0)as salary from employees order by 2 desc)  
먼저수행된다.
where rownum<=3;

select rownum, last_name, salary
from (select last_name, nvl(salary,0)as salary from employees order by 2 desc)
where rownum=1;  ← rownum=2는 error (특정행은 사용할 수 없음)

select * from (select rownum , ceil(rownum/3) as page, tt.*   
	두번째로 수행          							tt에있는 모든항목을 가져와라         
from (select last_name, nvl(salary,0)as salary from employees order by salary desc)tt
	첫번째로수행													가져온 결과물을 tt라고명시
) where page=2;
	세번째로 수행

※
select * from
(select rownum rn, tt.* from 
두번째로 수행                     tt에있는 모든 항목을 가져와라     
(select last_name, nvl(salary,0)as salary from employees order by 2 desc)tt
첫번째로수행                                            가져온 결과물을 tt라고명시
) where rn>=4 and rn<=6;
원하는 값 아무거나 가져올 수 있다.