SQL Views

In SQL, a view is a virtual table. Unlike a regular table that stores data directly, a view is a stored query that presents data from one or more underlying tables as if it were a single table. It doesn't store data itself; it dynamically generates its results whenever it's queried.

Creating Views

Using CREATE VIEW

You create a view using the CREATE VIEW statement. This statement defines the structure and the underlying query for your view.

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

Example: Brazilian Customers View

This creates a view showing only customers from Brazil.

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

Querying the View

You can query a view like a regular table:

SELECT * FROM BrazilCustomers;
      

CustomerName | ContactName
-----------------------------
...           | ...
      

Example: Products Above Average Price

This creates a view showing products with a price higher than the average price.

CREATE VIEW ProductsAboveAveragePrice AS
SELECT ProductName, Price
FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products);
      

SELECT * FROM ProductsAboveAveragePrice;
      

ProductName | Price
--------------------
...          | ...
      

Updating a View

Using CREATE OR REPLACE VIEW

To modify an existing view, use CREATE OR REPLACE VIEW. This will replace the old view definition with the new one.

Example: Adding a City Column to the Brazil Customers View

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

Deleting a View

Using DROP VIEW

To delete a view, use the DROP VIEW statement.

DROP VIEW view_name;
      

Example: Dropping the Brazil Customers View

DROP VIEW BrazilCustomers;