SQL view
View is a visual table.
This chapter explains how to create, update and delete views.
SQL CREATE VIEW statement
In SQL, a view is a visual table based on the result set of a SQL statement.
A view contains rows and columns, just like a real table. The fields in the view are fields from real tables in one or more databases.
You can add SQL functions, WHERE, and JOIN statements to views, and you can present data as if it came from a single table.
SQL CREATE VIEW syntax
SELECT column_name(s)
FROM table_name
WHERE condition
Note: The view always displays the latest data! Whenever a user queries a view, the database engine reconstructs the data by using the view's SQL statements.
SQL CREATE VIEW Example
Sample database Northwind has some views installed by default.
The view "Current Product List" will list all products in use (products that are not discontinued) from the "Products" table. This view is created using the following SQL:
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No
We can query the above view like this:
Another view of the Northwind sample database will select "Products" All products in the table whose unit price is higher than the average unit price:
SELECT ProductName,UnitPrice
FROM Products
WHERE UnitPrice>( SELECT AVG(UnitPrice) FROM Products)
We can query the above view like this:
Another view of the Northwind sample database calculates the total sales for each category in 1997. Note that this view selects data from another view named "Product Sales for 1997":
SELECT DISTINCT CategoryName,Sum( ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY CategoryName
We can query the above view like this:
We can also add conditions to the query. Now, we just need to view the total sales of the "Beverages" category:
WHERE CategoryName='Beverages'
SQL Update View
You can use the following syntax to update a view:
SQL CREATE OR REPLACE VIEW Syntax
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:
SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued=No
SQL Drop View
You can delete a view through the DROP VIEW command.
