← Previous Next →


A view in SQL is a logical subset of data from one or more tables. View is used to restrict data access.

Creating a View:

Syntax:

CREATE or REPLACE view view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Example of Creating a View

Consider following Sale table,

oid order_name previous_balance customer
11 ord1 2000 Alex
12 ord2 1000 Adam
13 ord3 2000 Abhi
14 ord4 1000 Adam
15 ord5 2000 Alex

SQL Query to Create View

CREATE or REPLACE view sale_view as select * from Sale where customer = 'Alex';

The data fetched from select statement will be stored in another object called sale_view. We can use create seperately and replace too but using both together works better.


Displaying a View

Syntax of displaying a view is similar to fetching data from table using Select statement.

SELECT * from sale_view;

Force View Creation

force keyword is used while creating a view. This keyword force to create View even if the table does not exist. After creating a force View if we create the base table and enter values in it, the view will be automatically updated. Syntax for forced View is,

CREATE or REPLACE force  view view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Update a View

Update command for view is same as for tables. Syntax to Update a View is,

UPDATE view-name 
set value
WHERE condition;

If we update a view it also updates base table data automatically.


Read-Only View

We can create a view with read-only option to restrict access to the view. Syntax to create a view with Read-Only Access

CREATE or REPLACE force view view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition with read-only

The above syntax will create view for read-only purpose, we cannot Update or Insert data into read-only view. It will throw an error.


Types of View

There are two types of view,

  • Simple View
  • Complex View
Simple View Complex View
Created from one table Created from one or more table
Does not contain functions Contain functions
Does not contain groups of data Contains groups of data
← Previous Next →