SQL View
A view is a visualized table.
This chapter explains how to create, update, and delete views.
SQL CREATE VIEW statement
In SQL, a visual table that is based on the result set of SQL statements.
Views contain rows and columns, just like real tables. Fields in a view are fields in a real table in one or more databases.
You can add SQL functions, where to add them, and connect statements to the view, or you can render data as if it were from a single table.
SQL CREATE VIEW syntax
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
Note: Views always show the latest data! Whenever a user queries a view, the database engine rebuilds the data using the view's SQL statement.
SQL CREATE VIEW instance
The sample database Northwind has some views installed by default.
The Current Product List view lists all products in use (products that have not been discontinued) 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 this view above like this:
SELECT * FROM [Current Product List]
Another view of the Northwind sample database picks products in the Products table where the unit price is higher than 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 this view above like this:
SELECT * FROM [Products Above Average Price]
Another view of the Northwind sample database calculates the total number of sales per category in 1997. Note that this view picks up data from another view called 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 this view above like this:
SELECT * FROM [Category Sales For 1997]
We can also add conditions to the query. Now, we just need to look at the total number of sales for the "Beverages" category:
SELECT * FROM [Category Sales For 1997]
WHERE CategoryName='Beverages'
SQL updates the view
You can update the view using the following syntax:
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'll update the view with the following SQL:
CREATE OR REPLACE VIEW [Current Product List] AS
SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued=No
SQL undo view
You can delete the view through the DROP VIEW command.
SQL DROP VIEW syntax
DROP VIEW view_name