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