YouTip

# SQLite View A View is simply a stored SQLite statement stored in the database with a related name. A View is actually a composition of a table in the form of a predefined SQLite query. A View can contain all rows from a table or selected rows from one or more tables. A View can be created from one or more tables, depending on the SQLite query used to create the View. A View is a virtual table that allows a user to: * Find and present data in a more natural or intuitive way for users or user groups. * Restrict data access so that users see only limited data instead of the complete table. * Summarize data from various tables to generate reports. SQLite Views are read-only and thus may not contain DELETE, INSERT, or UPDATE statements. However, you can create a trigger on a view to perform DELETE, INSERT, or UPDATE actions on the view, with the actions defined in the trigger's body. ## Creating a View SQLite Views are created using the **CREATE VIEW** statement. SQLite Views can be created from a single table, multiple tables, or other views. The basic syntax for CREATE VIEW is as follows: CREATE [TEMP | TEMPORARY] VIEW view_name AS SELECT column1, column2..... FROM table_name WHERE ; You can include multiple tables in your SELECT statement in a very similar way as you do in the normal SQL SELECT query. If the optional TEMP or TEMPORARY keyword is used, the view will be created in the temporary database. ## Example Assume the COMPANY table has the following records: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Now, here is an example to create a view from the COMPANY table. The view will select only a few columns from the COMPANY table: sqlite> CREATE VIEW COMPANY_VIEW AS SELECT ID, NAME, AGE FROM COMPANY; Now, you can query the COMPANY_VIEW in a similar way as you query an actual table. Here is the example: sqlite> SELECT * FROM COMPANY_VIEW; This will produce the following result: ID NAME AGE ---------- ---------- ---------- 1 Paul 32 2 Allen 25 3 Teddy 23 4 Mark 25 5 David 27 6 Kim 22 7 James 24 ## Dropping a View To drop a view, you simply use the DROP VIEW statement with the **view_name**. The basic syntax for DROP VIEW is as follows: sqlite> DROP VIEW view_name; The following command will delete the COMPANY_VIEW view we created earlier: sqlite> DROP VIEW COMPANY_VIEW;