Views In SQL

In this article, we will learn how to create, read, update and delete a View using SQL query.

Based on the result-set of a SQL statement a View is a virtual table.

Just like a real table, also a View contains rows and columns. The fields in View are fields from single or multiple real tables in the database.

We can add WHERE, JOIN statements and SQL functions to a View.

 

View – Create:

The CREATE VIEW statement is used to create a new view in a database.

Syntax

CREATE VIEW VIEW_NAME AS
SELECT column1, column2, ...columnN
FROM TABLE_NAME 
WHERE condition;

Example

The subsequent statement would create a view named “Popular Articles” that contains all Articles with more than 100 views:

CREATE VIEW [Popular Articles] AS
SELECT * FROM Article
WHERE Views > 100;

 

View – Read:

To see the data in the View, we can query the View in the same manner as we query a table using the SELECT statement.

Please check How To Write Select Query In SQL, to get a brief description of the SELECT statement.

Syntax

SELECT * FROM VIEW_NAME;

Example

The subsequent statement would display data of all available fields in the “Popular Articles” view.

SELECT * FROM [Popular Articles];

 

View – Update:

The SQL UPDATE statement is used to update a View, just like we update tables.

Please check How To Write Update Query In SQL, to get a brief description of the UPDATE statement.

Syntax

UPDATE VIEW_NAME
SET column1 = value1, column2 = value2, ...columnN = ...valueN;

Example

The subsequent statement would update all records in the “Popular Articles” view with a single field named “Title)”:

UPDATE [Popular Articles] 
SET Title = 'Introduction';

 

View – Delete:

The DROP VIEW statement is used to delete a View.

Syntax

DROP VIEW VIEW_NAME;

Example

The subsequent statement would delete the existing view named “Popular Articles”:

DROP VIEW [Popular Articles];

 

Also, check How To Create Auto Increment Field Using Query In SQL