SQL CREATE VIEW, REPLACE VIEW, DROP VIEW statement
A view is a visual table.
This chapter explains how to create, update, and delete views.
SQL CREATE VIEW statement
In SQL, a view is based on the result set of SQL statements visualization table.
View contains rows and columns, just like a real table. The field is the view from one or more databases in real fields in the table.
You can add to the view SQL functions, WHERE and JOIN statements to be present the data as if the data is from a single table the same.
SQL CREATE VIEW Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
NOTE: The view always show the latest data!Whenever a user queries view, the database engine uses an SQL statement to rebuild the view data.
SQL CREATE VIEW Examples
Northwind sample database has some of the default installation view.
View "Current Product List" lists all products in use (not discontinued product) from the "Products" table. This view is created using the following SQL:
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No
We can query the view above like this:
Another view of the Northwind sample database will select "Products" table, all higher than the average unit price unit price of products:
SELECT ProductName,UnitPrice
FROM Products
WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)
We can query the view above like this:
Another view of the Northwind sample database will be calculated in 1997. Total sales in each category. View Please note that this view is from another named "Product Sales for 1997" in which selected data:
SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY CategoryName
We can query the view above like this:
We can also add a condition to the query. Now, we just need to see the total number of sales "Beverages" category:
WHERE CategoryName='Beverages'
SQL update view
You can use the following syntax to update the view:
SQL CREATE OR REPLACE VIEW Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
Now we want to "Current Product List" view to add "Category" column. We will update the view with the following SQL:
SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued=No
SQL revocation view
You can delete a view by DROP VIEW command.