VIEW IN ORACLE WITH EXAMPLE

View:

It is a logical table based on one or more tables or views. views can be created based on a table called a BASE TABLE. Here Base Table nothing but  a view brought its data from the tables on which it is based these tables are called Base Tables.You can create Views same as any other table. You can query,insert,update,delete from views just like any other tables.It Will not hold any data or store data itself.That means views takes up no storage space. That means a view contains no data all the data it shows comes from the base tables.


Advantages of View:

1. It provides high security while sharing the data between users
2. USER-VIEWS hold the details of views(data dictionary table)
3. view can be used to make simple queries to retrieve the result of complicated quires
4. views provides groups of users access to according to their particular criteria.
5. one or more advantage is views are data abstraction since the end user not aware of all the data present in the database table

Where do we use these views in real time:

A view is simply a SQL Statement  that is stored in memory so that can be easily be re-used.Suppose we have emp and dept table to see empno,ename,sal,deptno,deptname we have to write a join query like below.

select e.empno,e.empname,e.sal,e.deptno,d.deptname from emp e,dept d where e.deptno=d.deptno;

So to see emp details every time and dept names we need to write long join query, to overcome this problem(again and again have to write join query)simply we can create view on this table by using a CREATE VIEW.

Syntax:

CREATE VIEW view-emp

As 
select emp-id from emp;

Here View-emp specifies the name of the view .As specifies the actions that will be performed by the view. select statement specifies that defines a view. The view may use the data contained in other views and tables.

Example:

CREATE VIEW CUSTOMER

AS

SELECT cust-id,comp-name,phone-number from customers;

Note:  Before crating view there must be existing table name CUSTOMER. Create it first next create view on that table(CUSTOMER).

Note that the view is a  query stored as an object. Here the data is derived from the columns of the base tables customers.

Now you use a view by querying the view like a table:

select * from customer;

The output of the SELECT statement is:

cust-id                     comp-name                     phone-number

123                               sunrise                             798987989

345                               northjoe                            34343433

567                               eastjone                            576567565

Like this we can create views by using Sql query. The main use to create view is serves as security mechanism. This ensures that the users are able to retrieve and modify the data only seen by them. Users can not see or remaining data in the underlying tables. And also simply the complex queries since complex queries can be stored in the form of views. From the views we extract data by using simple query.

Also check the post:  Joins in SQL  with Examples