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
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.
Example of 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 |
