SQL - VIEWS

A database view is a searchable object in a database that is defined by a query. Though a view doesn’t store data, some refer to a views as “virtual tables”. A view can combine data from two or more table using joins, and also just contain a subset of information.

1. It won't allow to storing the data into the object.
2. It help us to hide some columns in tables.
3. It allows us to simplify complex queries.
4. It help limits data access to specific users.
5. It provides extra security layer.
6. Restrict the access of a original data in table.
   

	SQL> grant create view to public;
	Grant succeeded.

	conn / as sysdba
	Connected.

	SQL> create user demo identified by demo;
	User created.

	SQL> grant connect,resource,unlimited tablespace to demo;
	Grant succeeded.

	SQL> show user;
	USER is "SYS"

	SQL> conn scott/tiger
	Connected.

	SQL> grant select,update,delete,insert on scott.emp to demo;
	Grant succeeded.

	create view sh as select * from emp where deptno=20;
	View created.

  
  create or replace view sh as select a.empno,a.ename,b.dname from scott.emp a,scott.dept b;
  View created.

	select view_name,text,read_only from user_views;

	VIEW_NAME			TEXT                            	 		R
	----------	------------------------------------------------------------	-------------
	SH		select a.empno,a.ename,b.dname from scott.emp a,scott.dept b    	N

	create or replace view r1 as select * from emp where deptno=30
	with read only;
	View created.
  

	SQL> select view_name,text,read_only from user_views;

	create or replace force view s1 as select * from raj;
	Warning: View created with compilation errors.

	SQL> create table raj(id int);
	Table created.

	insert into raj values(11);
	1 row created.

	SQL> insert into raj values(12);
	1 row created.

	
	


	SQL> select view_name,text,read_only from user_views;
	

	drop view sh;
	View dropped.