THE WORLD'S LARGEST WEB DEVELOPER SITE
HTMLCSSJAVASCRIPTSQLPHPBOOTSTRAPJQUERYANGULARXML
 

SQL Views


We use make, refresh, and erase a view.


SQL CREATE VIEW Statement

In SQL, a view is a virtual table dependent on the outcome set of a SQL statement.

A see contains lines and sections, much the same as a genuine table. The fields in a view are fields from at least one genuine tables in the database.

You can include SQL capacities, WHERE, and JOIN proclamations to a view and present the information as though the information were originating from one single table.

SQL CREATE VIEW Syntax

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

SQL CREATE VIEW Examples

If you have the Northwind database you can see that it has a few perspectives introduced by default.

The see "Current Product List" records every single dynamic item (items that are most certainly not stopped) from the "Products" table. The view is made with the accompanying SQL:

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

We can inquiry the view above as follows:

SELECT * FROM [Current Product List]

Another see in the Northwind test database chooses each item in the "Products" table with a unit cost higher than the normal unit price:

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

We can inquiry the view above as follows:

SELECT * FROM [Products Above Average Price]

Another see in the Northwind database computes the complete deal for every classification in 1997. Note that this view chooses its information from another view called "Selling an item for 2018":

Make VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales
FROM [Selling an item for 2018]
Gathering BY CategoryName

We can question the view above as follows:

SELECT * FROM [Selling an item for 2018]

We can likewise add a condition to the inquiry. Presently we need to see the absolute deal just for the classification "Beverages":

SELECT * FROM [Selling an item for 2018]
WHERE CategoryName='average'

SQL Updating a View

You can refresh a view by utilizing the accompanying syntax:

SQL CREATE OR REPLACE VIEW Syntax

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

Now we need to include the "Category" section to the "Current Product List" see. We will refresh the view with the accompanying SQL:

Make OR REPLACE VIEW [Current Product List] AS
SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued=No

SQL Dropping a View

You can erase a view with the DROP VIEW command.

SQL DROP VIEW Syntax

DROP VIEW view_name