SQL Views

SQL VIEW is a virtual table based on the result-set of an SQL statement.

Following is the general form to create SQL VIEW:

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Note - A VIEW always shows up-to-date data. The database engine recreates the data, using the VIEW's SQL statement, every time a user queries a view

SQL CREATE VIEW Example

If you have Northwind database, you can see that, by default, it has several views installed.

The VIEW "Current Product List" lists all the active products (products that are not discontinued) from the table "Products"

The VIEW is created with the following SQL Statement:

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

Now, we can query the view above as follows:

SELECT * FROM [Current Product List]

Another VIEW in the Northwind sample database selects every product in the table "Products" with a unit price higher than the average unit price

Let's look at the following SQL Statement:

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

Now, we can query the view above as follows:

SELECT * FROM [Products Above Average Price]

Another VIEW in the Northwind database calculates the total sale for each category in 1997

Note that this VIEW selects its data from the another VIEW called "Product Sales for 1997"

Let's look at the following SQL Statement:

CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT C_Name,Sum(ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY C_Name

Now We can query the VIEW above as follows:

SELECT * FROM [Category Sales For 1997]

We can also add a condition to the query. Now we want to see the total sale only for the "Mountain Dew" category:

SELECT * FROM [Category Sales For 1997]
WHERE C_Name='Mountain Dew'

Update SQL VIEW

You can update a VIEW.

The CREATE/REPLACE VIEW Syntax:

Following is the general form of the SQL CREATE or REPLACE VIEW:

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Now we want to add the column "Category" to the "Current Product List" VIEW. We will update the VIEW with the following SQL Statement:

CREATE VIEW [Current Product List] AS
SELECT P_ID,P_Name,Category
FROM Products
WHERE Discontinued=No

Drop SQL VIEW

You can drop or delete a VIEW with the DROP VIEW command

The DROP VIEW Syntax

Following is the general form of the SQL DROP VIEW:

DROP VIEW view_name

SQL Online Test