SQLite - VIEWS

The SQLite VIEW statement is used to create a SQLite VIEW which is a virtual table created based on the SQLite statement. A view contains rows and columns just like a normal table. All SQLite functions, WHERE, HAVING and JOINs statements can be used to create a SQLite VIEW. The SQLite VIEW statement can be used to create, update or delete a view.

Syntax

The syntax for using VIEW statement in SQLite is given below:

/* Create a view */
CREATE VIEW [IF NOT EXISTS] view_name AS
SELECT column1, column2, column3, ...
FROM table_name
WHERE condition(s);

/* Drop a view */
DROP VIEW view_name;

IF NOT EXISTS is an optional parameter that conditionally creates view only if it does not exist on the database. If a view is created which already exists, it will raise an error.

Example:

Consider a database table called Employee with the following records:

CREATE VIEW

The below mentioned SQLite statement is used to create a view on Employee table which contains all records of employee whose salary is greater than 2800.

CREATE VIEW Employee_Salary_GT_2800 AS
SELECT * FROM Employee WHERE Salary > 2800;

Query the VIEW

After creating the VIEW, it can be used as mentioned below:

SELECT * FROM Employee_Salary_GT_2800;

This will produce the result as shown below:

DROP VIEW

The DROP VIEW is used to delete a view.

DROP VIEW Employee_Salary_GT_2800;

ALTER OR UPDATE VIEW

In SQLite, a view can not be altered or updated. However, this can be achieved by first deleting the view and creating the view with updated content.

The below statement is used to update the above view which contains all records of employee whose salary is greater than 2800 and age is greater than 25.

/* Deleting the old view */
DROP VIEW Employee_Salary_GT_2800;

/* Creating a new view */
CREATE VIEW Employee_Salary_GT_2800 AS
SELECT * FROM Employee WHERE Salary > 2800 AND AGE > 25;

After updating the VIEW, the following query can be used to see its content:

SELECT * FROM Employee_Salary_GT_2800;

This will produce the result as shown below: