Views

A view is a virtual table based on the result-set of a stored 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 to, and update fields in, a table through a view, provided that the view meets certain criteria.

Views are used to:

Creating Views

You can create a view using the CREATE VIEW statement. The syntax is as follows:

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

Example: Creating a simple view

This example creates a view named CustomerContactInfo that shows the CustomerID, FirstName, LastName, and EmailAddress for all customers.

CREATE VIEW CustomerContactInfo AS
SELECT CustomerID, FirstName, LastName, EmailAddress
FROM Customers
WHERE EmailAddress IS NOT NULL;

Once created, you can query the view like a regular table:

SELECT * FROM CustomerContactInfo WHERE LastName = 'Smith';

Modifying Views

To modify an existing view, you can use the ALTER VIEW statement. This statement allows you to change the definition of the view without dropping and recreating it.

ALTER VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE new_condition;

Example: Modifying a view

Let's say we want to add the PhoneNumber to our CustomerContactInfo view.

ALTER VIEW CustomerContactInfo AS
SELECT CustomerID, FirstName, LastName, EmailAddress, PhoneNumber
FROM Customers
WHERE EmailAddress IS NOT NULL;
Note: Some database systems might not support ALTER VIEW directly. In such cases, you would typically drop the view using DROP VIEW and then recreate it with the new definition.

Deleting Views

To remove a view from the database, use the DROP VIEW statement.

DROP VIEW view_name;

Example: Deleting a view

DROP VIEW CustomerContactInfo;

Permissions

Managing permissions on views is crucial for data security. You can grant or revoke specific privileges on views, allowing users to select data from them without having direct access to the underlying tables.

Granting Permissions

Use the GRANT statement to give users or roles permission to access a view.

GRANT SELECT ON view_name TO user_or_role;

Revoking Permissions

Use the REVOKE statement to remove access permissions.

REVOKE SELECT ON view_name FROM user_or_role;
Tip: Granting permissions on views is a common practice to enforce a security model where users interact with data through predefined interfaces rather than directly querying base tables.

Views offer a powerful way to manage and present data in SQL databases, enhancing both usability and security.