Views are logical tables of data extracted from existing tables. It can be quired just like a table, but does not require disk space. Like a regular table, a view consists of rows and columns, but it does not store its own data. Instead, it pulls data from its underlying base tables every time it is queried, ensuring the data is always up-to-date.
A view is a virtual table that is created from the result set of a SELECT query. It does not store data itself but rather stores the definition of the query. When a view is queried, the underlying SELECT statement is executed to retrieve the data from the base table. Views behave like tables in terms of querying, allowing us to SELECT from them. A view can contain all rows of a table or selected rows from one or more tables. Views are read-only meaning we cannot perform INSERT, UPDATE or DELETE operations directly on a view.
Key characteristics of SQLite Views:
Read-only: In SQLite, views are read-only by default. You cannot directly execute INSERT, UPDATE, or DELETE statements on a view.
No stored data: A view does not consume storage space for its own data. It only stores its definition—the SELECT query.
Dynamic content: The contents of a view are generated dynamically each time it is accessed, so it always reflects the current state of its base tables.
Advantages of using Views
Data abstraction: They provide a layer of abstraction that hides the underlying database schema from the end user or application. This improves code readability and maintainability.
Enhanced security: Views can restrict access to sensitive information by limiting which columns or rows are visible. Users can be granted access to the view without having direct access to the base tables.
Creating a View / How to create a view
A view is created using the CREATE VIEW statement, followed by the view’s name, the AS keyword and then the SELECT statement that defines the view’s data.
Syntax:
CREATE VIEW view_name AS SELECT column1, column2, …… FROM table_name;
Example:
CREATE VIEW STD_VIEW AS SELECT roll, name, address FROM student;
Display View
Once created, a view can be queried just like a regular table. The most common way to display the data contained within a view is to use a SELECT statement, just as one would with a table.
Syntax:
SELECT * FROM view_name;
SELECT column_name FROM view_name WHERE condition;
Example:
SELECT * FROM std_view;
SELECT * FROM std_view WHERE name= “Raja”;
Deleting a View / Dropping a View
To delete an existing view, simply use the DROP VIEW statement with the view_name.
Syntax:
DROP VIEW view_name;
Example:
DROP VIEW std_view;