SQL Views

SQL

SQL Views : SQL Views is a virtual table based on the result-set of an SQL statement

SQL CREATE VIEW Statement

  • In SQL, a view is a virtual table based on the result-set of an SQL statement.
  • A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
  • You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.

CREATE VIEW Syntax

CREATE VIEW view_name AS
SELECT column1, column2, …
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 Examples

  • If you have the Northwind database you can see that it has several views installed by default.
  • The view “Current Product List” lists all active products (products that are not discontinued) from the “Products” table. The view is created with the following SQL:

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

  • Then, we can query the view as follows:

SELECT * FROM [Current Product List];

  • Another view in the Northwind sample database selects every product in the “Products” table with a unit price 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 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 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 view above as follows:

SELECT * FROM [Category Sales For 1997];

  • We can also add a condition to the query. Let’s see the total sale only for the category “Beverages”:

SELECT * FROM [Category Sales For 1997]
WHERE CategoryName = ‘Beverages’;

SQL Updating a View

You can update a view by using the following syntax:

SQL CREATE OR REPLACE VIEW Syntax

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, …
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 OR REPLACE VIEW [Current Product List] AS
SELECT ProductID, ProductName, Category
FROM Products
WHERE Discontinued = No;

SQL Dropping a View

You can delete a view with the DROP VIEW command.

SQL DROP VIEW Syntax

DROP VIEW view_name;