3 Tricky Ideas on Working with SQL Views

A view can be read-only or updatable. You can maintain data security with views. Creating a view need base tables present in the database. Views are also called logical tables.

What is a View in SQL

• 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.

1). Read-Only Views

The standard syntax for the view is as follows:

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;

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

a). ALTER View

Using CREATE OR REPLACE statement you can ALTER the view already present.

b). Drop View

DROP VIEW vendors_sw

The view vendors_sw is deleted from the database.

Summary

A view with CHECK OPTION is surely helped you to satisfy a certain condition. Usually, this option will be used in updatable views.