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.
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
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.
The syntax for displaying the data in a view is similar to fetching data from a table using a
SELECT * FROM sale_view;
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 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.
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.
There are two types of 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|