w3resource
SQLite Tutorial

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;

Many of our valued users post comments along with piece of code. Disqus may, false positively, consider those piece of code as Bad or Starnge syntax and send those comments to spam automatically. Even if that happens, we will make sure those useful comments are taken out of spam and approved. It may take a couple of days for that though, but any useful comment will be brought to public view for sure. We regret if that happend to any user. You may write us directly regarding this to - w3resource[at]yahoo[dot]com.