SQL VIEW (View)

Views are visual tables.

This chapter explains how to create, update, and delete views.

SQL CREATE VIEW Statement

What is a view?

In SQL, a view is a visual table based on the result set of an SQL statement.

Views contain rows and columns, just like a real table. The fields in the view are from the real fields of one or more tables in the database. We can add SQL functions, WHERE, and JOIN statements to the view, and we can also submit data as if it comes from a single table.

Note:The design and structure of the database are not affected by functions, WHERE, or JOIN statements within the view.

SQL CREATE VIEW Syntax

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Note:Views always display the most recent data. Every time a user queries a view, the database engine rebuilds the data using SQL statements.

SQL CREATE VIEW Example

Views can be used from within a query, within a stored procedure, or from another view. By adding functions, joins, etc., to the view, we can precisely submit the data we want to the user.

The Northwind sample database has some views that are installed by default. The view "Current Product List" lists all products in use from the Products table. This view is created using the following SQL:

CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No

We can query the above view:

SELECT * FROM [Current Product List]

Another view from the Northwind sample database selects all products from the Products table with unit prices above the average unit price:

CREATE VIEW [Products Above Average Price] AS
SELECT ProductName,UnitPrice
FROM Products
WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)

We can query the above view like this:

SELECT * FROM [Products Above Average Price]

Another view instance from the Northwind database calculates the total sales of each type in 1997. Please note that this view retrieves data from another view named "Product Sales for 1997":

CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName, Sum(ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY CategoryName

We can query the above view like this:

SELECT * FROM [Category Sales For 1997]

We can also add conditions to the query. Now, we just need to view the total sales of all 'Beverages' categories:

SELECT * FROM [Category Sales For 1997]
WHERE CategoryName='Beverages'

SQL Update View

You can use the following syntax to update the view:

SQL CREATE OR REPLACE VIEW Syntax
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Now, we want to add the 'Category' column to the 'Current Product List' view. We will update the view with the following SQL:

CREATE VIEW [Current Product List] AS
SELECT ProductID, ProductName, Category
FROM Products
WHERE Discontinued=No

SQL Revoke View

You can delete a view using the DROP VIEW command.

SQL DROP VIEW Syntax

DROP VIEW view_name