SQLite Create View

Views are a great way to simplify complex SQL queries, and they can be used to hide sensitive information from users who should not have access to it. Creating a view does not actually create a new table; it simply creates a new way of looking at an existing table. Views are sometimes also referred to as “virtual tables”.

SQLite view is a virtual table created based on a select from a single table or several tables.
Basically SQLite view is created using the command CREATE VIEW AS followed by an SQL query.
The syntax to create a SQLite view is the same as in SQL Server. The syntax is as follows:

CREATE VIEW syntax

CREATE [ TEMP | TEMPORARY ] VIEW [ IF NOT EXISTS ] 
view_name 
AS
SQL query;

or

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

If TEMP or TEMPORARY is used when creating a view, then the created view will be visible only in the Database Connection in which it was created and will be automatically deleted when the database connection is closed.

In SQLite you cannot use the INSERT, DELETE and UPDATE statements to add, delete or modify data from a view. In conclusion, SQLite view is read-only.

Example

CREATE VIEW IF NOT EXISTS 
customers_orders_view
AS 
SELECT c.id, c.name, o.order_id, o.amount
FROM customers c, orders o
WHERE c.id=o.customer_id;

In the example above, a view called “customers_orders_view” is created from SQL query(our query consists of the join between the customers and orders table).

The created view will contain all initiated orders, for each order the customer’s name will be displayed.
Views can be used like regular tables, and they can be queried with SQL commands. For example, the following SQL query would return all rows from the “customers_orders_view” view:

SELECT * FROM customers_orders_view;

Basic example

CREATE VIEW 
report_orders_view
AS 
SELECT order_id, customer_id, amount 
FROM orders 
WHERE amount > 100;

The SQLite view called “report_orders_view” is created from the “orders” table. This view would include all rows from the original table where the amount is greater than 100.