117 SQL Views

The page explains how to use SQL statements to create, update, and delete views in a database:

SQL Views are virtual tables based on the result of an SQL query. They act like real tables, but the data they present comes from one or more actual tables.
Creating a View:
Use the CREATE VIEW statement with AS SELECT ... to define a view. For example, you can create a view to display all customers from a specific country by writing:
```
CREATE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName
FROM Customers
WHERE Country = 'Brazil';
```
Querying a View:
You can use a regular SELECT * FROM [view_name]; to access the data in a view.
Updating a View:
The CREATE OR REPLACE VIEW statement allows you to update an existing view (such as adding new fields or changing filter conditions).
Dropping a View:
Remove a view by using DROP VIEW view_name;
Note: Views always show the latest data because the database engine regenerates them at query time.
The page provides syntax and multiple examples for each statement.

Summary:
SQL views are tools for presenting, customizing, and simplifying access to data without altering the underlying tables. They can be created, updated, and dropped with the corresponding SQL commands, and always present up-to-date results from the base tables.[1]

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 statements and functions to a view and present the data as if the data were coming from one single table.

A view is created with the CREATE VIEW statement.

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 view, every time a user queries it.

SQL CREATE VIEW Examples

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

CREATE VIEW [Brazil Customers] AS  
SELECT CustomerName, ContactName  
FROM Customers  
WHERE Country = 'Brazil';

We can query the view above as follows:

SELECT * FROM [Brazil Customers];

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

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:

SELECT * FROM [Products Above Average Price];

SQL Updating a View

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

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:

CREATE OR REPLACE VIEW [Brazil Customers] AS  
SELECT CustomerName, ContactName, City  
FROM Customers  
WHERE Country = 'Brazil';

SQL Dropping a View

A view is deleted with the DROP VIEW statement.

SQL DROP VIEW Syntax

DROP VIEW view_name;

The following SQL drops the "Brazil Customers" view:

DROP VIEW [Brazil Customers];