THE WORLD'S LARGEST WEB DEVELOPER SITE

SQL Tutorial

SQL HOME SQL Intro SQL Syntax SQL Select SQL Select Distinct SQL Where SQL And, Or, Not SQL Order By SQL Insert Into SQL Null Values SQL Update SQL Delete SQL Select Top SQL Min and Max SQL Count, Avg, Sum SQL Like SQL Wildcards SQL In SQL Between SQL Aliases SQL Joins SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL Self Join SQL Union SQL Group By SQL Having SQL Exists SQL Any, All SQL Select Into SQL Insert Into Select SQL Case SQL Null Functions SQL Stored Procedures SQL Comments

SQL Database

SQL Create DB SQL Drop DB SQL Backup DB SQL Create Table SQL Drop Table SQL Alter Table SQL Constraints SQL Not Null SQL Unique SQL Primary Key SQL Foreign Key SQL Check SQL Default SQL Index SQL Auto Increment SQL Dates SQL Views SQL Injection SQL Hosting

SQL References

SQL Keywords MySQL Functions SQL Server Functions MS Access Functions SQL Operators SQL Data Types SQL Quick Ref

SQL Examples

SQL Examples SQL Quiz SQL Exercises SQL Certificate

SQL Views


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

The following SQL creates a view that shows all customers from Brazil:

Example

CREATE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName
FROM Customers
WHERE Country = "Brazil";
Try it Yourself »

We can query the view above as follows:

Example

SELECT * FROM [Brazil Customers];
Try it Yourself »

The following SQL creates a view that selects every product in the "Products" table with a price higher than the average price:

Example

CREATE VIEW [Products Above Average Price] AS
SELECT ProductName, Price
FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products);
Try it Yourself »

We can query the view above as follows:

Example

SELECT * FROM [Products Above Average Price];
Try it Yourself »


SQL Updating a View

A view can be updated with the CREATE OR REPLACE VIEW command.

SQL CREATE OR REPLACE VIEW Syntax

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

The following SQL adds the "City" column to the "Brazil Customers" view:

Example

CREATE OR REPLACE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName, City
FROM Customers
WHERE Country = "Brazil";
Try it Yourself »

SQL Dropping a View

A view is deleted with the DROP VIEW command.

SQL DROP VIEW Syntax

DROP VIEW view_name;

The following SQL drops the "Brazil Customers" view:

Example

DROP VIEW [Brazil Customers];
Try it Yourself »