Latest web development tutorials

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

CREATE VIEW view_name AS
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:

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

We can query the view above like this:

SELECT * FROM [Current Product List]

Another view of the Northwind sample database will select "Products" table, all higher than the average unit price unit price of products:

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

We can query the view above like this:

SELECT * FROM [Products Above Average Price]

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:

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 view above like this:

SELECT * FROM [Category Sales For 1997]

We can also add a condition to the query. Now, we just need to see the total number of sales "Beverages" category:

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 "Current Product List" view to add "Category" column. 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 revocation view

You can delete a view by DROP VIEW command.

SQL DROP VIEW Syntax

DROP VIEW view_name