SQLite Create and Drop view

Introduction

The SQLite Create View Tool allows users to visually create views. A view is simply a stored query that can be selected against similar to a table. Once created, views act more or less like read-only tables. The contents of base tables are persistent whereas the contents of views are dynamically generated when they are used. Technically a view is a virtual table. So we can list all views with a .tables command.

Syntax:

 CREATE [TEMP] VIEW view_name AS SELECT query_statement 

Parameters:

view_name - Name of the view.

query_statement - Select query.

Create View:

Here we are creating a table company.

 CREATE TABLE company( com_id text(4), com_name text(15), yr_tran integer(8,2)); sqlite> INSERT INTO company VALUES('COM1','Company1',145632); sqlite> INSERT INTO company VALUES('COM2','Company2',200032); sqlite> INSERT INTO company VALUES('COM3','Company3',95032); sqlite> select * from company; com_id com_name yr_tran ------------- ---------- ---------- COM1 Company1 145632 COM2 Company2 200032 COM3 Company3 95032 

Here is the SQL statement to create a simple view my_view on company table.

 CREATE VIEW my_view AS SELECT * FROM company;  

You can use the view as you use the base table. Here is the example.

 sqlite> SELECT * FROM my_view; com_id com_name yr_tran ------------- ---------- ---------- COM1 Company1 145632 COM2 Company2 200032 COM3 Company3 95032 

Here is another example

 CREATE VIEW my_view1 AS SELECT * FROM company WHERE yr_tran>100000;  

Now you can use the view as a base table for select query. Here is the statement.

 sqlite> SELECT * FROM my_view1; com_id com_name yr_tran ------------- ---------- ---------- COM1 Company1 145632 COM2 Company2 200032 sqlite> SELECT * FROM my_view1 ...> WHERE com_id='COM2'; com_id com_name yr_tran ------------- ---------- ---------- COM2 Company2 200032 

If you want to see the list of views in the database, you can use the table list command, see the following command-

 sqlite> .tables company hrdb.departments hrdb.jobs my_view hrdb.countries hrdb.employees hrdb.locations my_view1 hrdb.department hrdb.job_history hrdb.regions 

DROP View:

Syntax:

 DROP VIEW view_name; 

Example:

If you want to drop the view my_view1 from the databases, the following can be used.

 DROP VIEW my_view1;  

Previous: STRFTIME
Next: INNER JOIN