There are three kinds of Views in SQL. The three views are Read-only, Force, and Updatable. Views real usage is to hide data. And you need to ensure base tables are present before you create a View.
You can call views as logical tables. The advantage of Views is you can show only some of the fields of base tables.
What is a View in SQL
CREATE OR replace VIEW invoice_summary AS
SELECT vendor_name count(*) AS invoice_count,
SUM(invoice_total) AS invoice_total_sum
FROM vendor
JOIN invoices
ON vendors.vendor_id*invoices.vendor_id
GROUP BY vendor_name;
#2: Force Views
CREATE FORCE VIEW products_list
AS
SELECT product_description,
product_price
FROM products;
- A view can be constructed with another view so it is called a nested view.
- You can create or replace an existing view
- A view can be created without having base tables. This is possible with the FORCE option.
The standard syntax for the view is as follows:
SELECT vendor_name count(*) AS invoice_count,
SUM(invoice_total) AS invoice_total_sum
FROM vendor
JOIN invoices
ON vendors.vendor_id*invoices.vendor_id
GROUP BY vendor_name;
Notes: You cannot update Read-only Views
AS
SELECT product_description,
product_price
FROM products;
Notes: Without base Table you can create a FORCE View.
#3: Updatable Views
A view can be updatable if a view follows certain rules.
A view when it is created for update purpose, you can give INSERT, UPDATE and DELETE operations.
A read-only view should contain WITH READ ONLY clause
While updating a view, it is possible to update only one base table at a time. When you created a view from more than one table, then it is not possible to update two tables at a time.
How to Manipulate Views
ALTER View
- CREATE OR REPLACE statement you can use to ALTER the View.
Drop View
- Drop view vendor_sw
Summary
A view with CHECK OPTION restricts to update. When condition satisfied it updates.