syntax and example of create views in oracle database query

A VIEW in Oracle SQL / PLSQL is like a virtual table, there is no physical existence of a view. A VIEW can be created using a SELECT statement by forming a valid SQL query.

Syntax for creation of a VIEW in Oracle SQL / PLSQL is :

CREATE VIEW view_name
AS
SELECT column(s)
FROM table_name(s)
WHERE condition(s);

Let’s take an example for understanding
Suppose have a table named ‘employee’ in the database as shown below.

Employee_Id	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Scenario 1:

Creating a VIEW

Suppose we want to create a view so that we can see the data only for those employees who are having ‘salary’ > 25000, we can achieve the same as:

CREATE VIEW employee_view
AS
SELECT *
FROM employee
WHERE salary > 25000;

The above query will create a view named as ‘employee_view’. If we query the ‘employee_view’ as:

SELECT *
FROM employee_view;

We will get the following result:

Employee_Id	Employee_Name	Salary	Department	Commission
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Here we can understand that we have created a view named ‘employee_view’ based on ‘employee’ table which shows us the records of employees having ‘salary’ > 25000 using CREATE VIEW and SELECT statement.

Scenario 2:

Updating an already existing view:

Syntax for updating a view is:

CREATE OR REPLACE VIEW view_name
AS
SELECT column(s)
FROM table(s)
WHERE condition(s);

Suppose we want to update the view named ‘employee_view’ created in the previous scenario so that the ‘employee_view’ can display the records of employees having ‘salary’ < 30000.

The same can be achieved as:

CREATE OR REPLACE VIEW employee_view
AS
SELECT *
FROM employee
WHERE salary < 30000;

The above statement will alter or change the definition of ‘employee_view’ so that it will show record of employees from ‘employee’ table having ‘salary’ < 30000.

If we query the ‘employee_view’ as:

SELECT *
FROM employee_view;

We will get the following result:

Employee_Id	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20

Here we can see that the view definition for ‘employee_view’ has been updated and the view is now displaying the records of employees having salary < 30000.

Scenario 3:

Deleting or dropping a view

The syntax for deleting or dropping view is

DROP VIEW view_name;

E.g.

DROP VIEW employee_view;

The above statement will delete or drop the definition of ‘employee_view’ from the database.

Important Points about VIEWS in SQL / PLSQL:

1. The data in the VIEWS can be updated if the user has the privileges for doing the same.
2. If the data in the VIEW is updated, it updates the data in the tables on which the VIEW  is based upon.
3. If the table (on which the view is based) is dropped or deleted from the database the definition if the view still remains i.e. the VIEW continues to exist, but if we query the view we will get a message that VIEW has errors.
4. If we create the base table again the VIEW become alright as it was before the deletion if the base table.