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;
CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;
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.
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;
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.