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:
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,
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.
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|