3 SQL Query Examples to Create Views Quickly

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

Notes: You cannot update Read-only Views


#2: Force Views

CREATE FORCE VIEW products_list
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.

Comments

Popular Posts

Hyperledger Fabric: 20 Real Interview Questions

7 AWS Interview Questions asked in Infosys, TCS

How to Fix Python Syntax Errors Quickly

Python 'getsizeof' Command the Real Purpose

Blue Prism complete tutorials download now

Python Dictionary Vs List With Examples

How to Use the ps Command in Linux

Vi Editor to Quit use Esc and Colon

5 HBase Vs. RDBMS Top Functional Differences

The Growth of Machine Learning till TensorFlow