a view is nothing more than a sqlite statement that is stored in the database with an associated name. it is actually a composition of a table in the form of a predefined sqlite query.

a view can contain all rows of a table or selected rows from one or more tables. a view can be created from one or many tables which depends on the written sqlite query to create a view.

views which are kind of virtual tables, allow the users to −

structure data in a way that users or classes of users find natural or intuitive.
restrict access to the data such that a user can only see limited data instead of a complete table.
summarize data from various tables, which can be used to generate reports.

sqlite views are read-only and thus you may not be able to execute a delete, insert or update statement on a view. however, you can create a trigger on a view that fires on an attempt to delete, insert, or update a view and do what you need in the body of the trigger.

creating views

sqlite views are created using the create view statement. sqlite views can be created from a single table, multiple tables, or another view.

following is the basic create view syntax.

create [temp | temporary] view view_name as
select column1, column2.....
from table_name
where [condition];

you can include multiple tables in your select statement in a similar way as you use them in a normal sql select query. if the optional temp or temporary keyword is present, the view will be created in the temp database.

example

consider company table with 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

following is an example to create a view from company table. this view will be used to have only a few columns from company table.

sqlite> create view company_view as
select id, name, age
from  company;

you can now query company_view in a similar way as you query an actual table. following is an 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 views

to drop a view, simply use the drop view statement with the view_name. the basic drop view syntax is as follows −

sqlite> drop view view_name;

the following command will delete company_view view, which we created in the last section.

sqlite> drop view company_view;

Previous Lesson

Next Lesson