SQL VIEW

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

Syntax for creating a View,

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

As you may have understood by seeing the above SQL query, a view is created using data fetched from some other table(s). It's more like a temporary table created with data.

Creating a VIEW

Consider following Sale table,

SQL Query to Create a View from the above table will be,

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 and REPLACE seperately too, but using both together works better, as if any view with the specified name exists, this query will replace it with fresh data.

Displaying a VIEW

The syntax for displaying the data in a view is similar to fetching data from a table using a SELECT statement.

SELECT * FROM sale_view;

Force VIEW Creation

FORCE keyword is used while creating a view, forcefully. This keyword is used to create a 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;

NOTE: 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