- A VIEW in SQL is a logical subset of data from one or more tables.
- View can be used to restrict data access and also to represent the same data in multiple format.
- We can create a view with read-only option to restrict access to the view.
- Force option creates a view even if the table is not present.
- Views will not hold data of its own, instead, it retrieves the data from the base table/view (expect materialised view)
- Triggers can be created on views
Read only syntax :
CREATE or REPLACE FORCE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition WITH read-only;
All views in a database
select * from user_views;
Types of View :
Simple Views
- Simple view are based of single table.
- The records are not group. (No grouping are allowed in Simple View).
- Does not contain a distinct keyword or pseudocolumns or expression based column.
- DMLs are allowed in Simple Views
Complex Views
- Can have any numbers of tables joined
- Records can be grouped. Grouping functions like Min, Max, Count, Sum, Avg can be used
- Can contain “Distinct” or PSEDUDOCOUMNS and columns based expressions.
Materialized Views
- In materialised view, the data is actually stored in it(at a physical location) unlike View where the underlying query is executed when the view is called at the runtime.
- Even if the data from the main table is deleted the data from the materialised view is not deleted, you need to call a refresh to job manually to refresh the view.