What is a 'view' and what is it used for?

A view is a virtual table created based on a SELECT query on one or more underlying tables.

Uses of views:

• Simplifying complex queries: Hides the complexity of joins and other operations, presenting data in a more convenient form.
• Restricting data access: Allows showing users only the columns and rows they have access rights to, without giving direct access to the underlying tables.
• Reusability: Once a view is defined, it can be used in different queries, avoiding rewriting complex logic.
• Maintaining backward compatibility: If the structure of underlying tables changes, the view can be modified to preserve the previous data access interface for applications.

Views do not store data themselves; they only define how data from underlying tables should be presented. When querying a view, the DBMS actually executes the underlying query defined for that view.

Example of creating a view:

-- Creating a simple view
CREATE VIEW active_users AS
SELECT user_id, username, email
FROM users
WHERE status = 'active';

Example of using a view:

-- Querying the view
SELECT *
FROM active_users
WHERE username LIKE 'A%';