Introduction to View in SQL
In SQL, a view is a virtual table based on the outcome-set of an statement.
A view contains rows and columns, just like an original table. The entries in a view are entries from one or more original tables in the database.
You can add functions, WHERE, and JOIN statements to a view and display the data as if the data were arriving from one single table.
CREATE VIEW Syntax
CREATE VIEW view_name AS
SELECT column1, column2, …
Note: A view always shows up-to-date data! The database storage recreates the data, using the view’s statement, every time a user queries a view.
SQL CREATE VIEW Examples
Here, creates a view that shows all customers from Brazil:
Example CREATE VIEW [Brazil Customers] AS SELECT CustomerName, ContactName FROM Customers WHERE Country = 'Brazil'; We can query the view above as follows: Example SELECT * FROM [Brazil Customers];
Widget not in any sidebars
Here, 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); We can query the view above as follows: Example SELECT * FROM [Products Above Average Price]; Here 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; Here 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'; Here the view is deleted with the DROP VIEW command. Syntax DROP VIEW view_name; Here SQL drops the "Brazil Customers" view: Example DROP VIEW [Brazil Customers];