view

View is virtual table or logical table based on one or more tables or views

Syntax :create or replace view as select from ;
To create a view in your own schema, we must have the create view system privileges
grant create view to scott;
Types of view

Simple view

A view create only one table is called simple view
For ex:-
Create or replace view v_emp as select empno,ename,job,sal,deptno from emp where deptno=20;
View is came from one base table we have possible to update, delete insert the rows in to the view With restrictions only.
Restrictions on DML operations for view:
1) If a view is define by a query that contains Setoperations(UNION UNION ALL,INTERSECT,MINUS) or Distinct operator,Group by or having clauses or function, then
Rows cannot be inserted into,updated in, or delete from the base tables using the view.
2) If a view is define with or connect by clauses a row cannot be inserted into,updated in, or delete from the base tables using the view
3) If the view contain Rownum pseudocolumn,not null and default column using an expression such as decode(deptno ,10,”sales”) then rows cannot be inserted into,updated in, or delete
4) from the base tables using the view

object modifiable
user_updatable_colums shows all columns in all tables and views in the uses schema that are modifiable
dba_updatable_columns
all_updatable_views

Forced view:
View Create as non-existent table but view cannot be executed we call such a view with errors
For ex:
create force view v_emp1 as select *from emp1;
output
SQL Command: create force
Failed: Warning: execution completed with warning
 
Complex view
Aview create based on multiple tables is called as complex view
Forex:
CREATE OR REPLACE VIEW Emp_detl AS
SELECT e.empno,e.ename,e.job,d.deptno,d.dname FROM emp e, dept d WHERE e.deptno = d.deptno;


Materalized view
materialized view is the view that is physically present in the database since when  we are working with various databases running in different system

before create materalized view we need privileges form system user or sys


grant query rewrite to scott;
grant create materialized view to scott;
alter session set query_rewrite_enabled = true;

create  materialized view m_dept1  as select d.deptno,d.dname,d.loc from dept d;
insert into dept values(61,'SALES1','Delhi');
commit; 
 select *from m_dept1

DEPTNO   DNAME                  LOC 
10               ACCOUNTING     NEW YORK
20               RESEARCH           DALLAS
30               SALES                   CHICAGO
40               OPERATIONS       BOSTON 

exec  dbms_mview.refresh('m_dept1','C');


DEPTNO   DNAME                  LOC 
61               SALES1                  Delhi
10               ACCOUNTING     NEW YORK
20               RESEARCH           DALLAS
30               SALES                   CHICAGO
40               OPERATIONS       BOSTON

select *from m_dept1

update dept set dname='OPERATIONS3' where deptno=40

exec  dbms_mview.refresh('m_dept1','C');

DEPTNO   DNAME                  LOC 
61               SALES1                  Delhi
10               ACCOUNTING     NEW YORK
20               RESEARCH           DALLAS
30               SALES                   CHICAGO
40               OPERATIONS3       BOSTON

delete from dept  where deptno=61
exec  dbms_mview.refresh('m_dept1','C');

DEPTNO   DNAME                  LOC 

10               ACCOUNTING     NEW YORK
20               RESEARCH           DALLAS
30               SALES                   CHICAGO
40               OPERATIONS3       BOSTON

or
 AUTO referesh when u'r commit

create materialized view mv_test  refresh complete on commit
 as  select deptno,dname,loc from dept;
 insert into dept values(71,'Accounts','Hyd');

DEPTNO   DNAME                  LOC 
71               Accounts                  Hyd
61               SALES1                  Delhi
10               ACCOUNTING     NEW YORK
20               RESEARCH           DALLAS
30               SALES                   CHICAGO
40               OPERATIONS3       BOSTON

Inline view 
 It is a subquery with an alias ,that can be used within sql statement ,it is not a schema object
 (A named subquery in the from clause of the main query is an example of an inline view)
 For ex:- select e.ename,e.sal,e.deptno,e1.max_sal from emp e,(select deptno,max(sal) max_sal from  emp   group by deptno)e1 where e.deptno=e1.deptno