WEB DEVELOPER SITE
HTMLCSSJAVASCRIPTSQLPHPBOOTSTRAPJQUERYANGULARXML
 

SQL Views



Views are visual tables.

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.

The view contains rows and columns, just like a real table. The fields in a view are the fields from a real table in one or more databases.

You can add SQL functions, WHERE, and JOIN statements to the view, and you can render the data as if it came 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 reconstructs the data by using the SQL statement of the view.


SQL CREATE VIEW instance

Sample database Northwind has some views installed by default.

View "Current Product List" will be removed from "Products" table List all products in use (not discontinued products). 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 like this:

SELECT * FROM [Current Product List]

Another view of the Northwind sample database selects all products in the "Products" table whose 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 the above view like this:

SELECT * FROM [Products Above Average Price]

Another view of the Northwind sample database calculates the total sales for each category in 1997. Note that this view picks 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 the above view like this:

SELECT * FROM [Category Sales For 1997]

We can also add conditions to the query. For now, we just need to look at the total sales for the "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 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 Server

ALTER VIEW [ schema_name . ] view_name [ ( column [ ,...n ] ) ] 
[ WITH <view_attribute> [ ,...n ] ] 
AS select_statement 
[ WITH CHECK OPTION ] [ ; ]

<view_attribute> ::= 
{ 
    [ ENCRYPTION ]
    [ SCHEMABINDING ]
    [ VIEW_METADATA ]     
} 
  • schema_name: The name of the schema to which the view belongs.
  • view_name: The view to change.
  • column: The names of one or more columns (separated by commas) that will be part of the specified view.


SQL undo view

You can delete a view with the DROP VIEW command.

SQL DROP VIEW System

DROP VIEW view_name