How to create a view in Oracle

A view is a sql statement which can be stored in Oracle database as SQL statement. It never stored as data but as a sql statement.

Syntax to create a view

CREATE OR REPLACE VIEW VIEW_NAME AS
SELECT * FROM TABLE_NAME;

Syntax to see a view content

DESC VIEW_NAME

Syntax to delete a view

DROP VIEW VIEW_NAME

You can use a view as a table i.e. add where clause in view or use it in an inline view.

SELECT * FROM VIEW_NAME WHERE COLUMN_NAME = FIELD_VALUE

Example

Suppose you have a sql based on 2 table employee having 2 columns ecode, ename
and salary having 2 columns month_year, net_pay. Now the sql is to get ecode, ename,month_year
and sum of ney_pay. sql statement is given below.

select ee.ecode, ee.ename,sl.month_year, sum(sl.ney_pay) tot_net_pay
from employee ee,salary sl
where sl.emp_id = ee.emp_id
group by ee.ecode, ee.ename,sl.month_year,
order by 1,3

sl is the alias for table salary, ee is the alias for table employee, emp_id is primary key in employee
and reference key in salary. order by clause uses index of columns in select clause.

Now to create the view we write

CREATE OR REPLACE VIEW EMP_SAL_VIEW AS
select ee.ecode, ee.ename,sl.month_year, sum(sl.ney_pay) tot_net_pay
from employee ee,salary sl
where sl.emp_id = ee.emp_id
group by ee.ecode, ee.ename,sl.month_year,
order by 1,3

Now if you write a select query on this view it will be

SELECT ECODE,ENAME,MONTH_YEAR,NET_PAY FROM EMP_SAL_VIEW;

You can also use where clause in this view.

SELECT ECODE,ENAME,MONTH_YEAR,NET_PAY FROM EMP_SAL_VIEW

WHERE NET_PAY<=1000;