SQLite Views

Here we will learn what are the views in SQLite, how to create views IF NOT EXISTS in SQLite, and how to delete/drop views IF EXISTS in SQLite with examples.

SQLite Views

In SQLite view is nothing but a virtual table which is created by SQLite statements by joining one or more tables. 

 

Generally, in SQLite views will return rows and columns same as a normal table but those are the result set of joining multiple SQLite statements. The SQLite views like read-only tables that means it won’t allow to perform any operations like INSERT, UPDATE, and DELETE on views.

 

In SQLite views will not store the data like tables in memory it will store only SQLite query statements in memory that means it will store only the definition into memory.

 

In simple words, we can say that view is a way to pack SQLite query statements into one named object and whenever we request view it will update data based on SQLite query statements.

 

Mostly SQLite views are useful in the following scenarios

 

  • Whenever we have complex and commonly used queries then we can pack it as a view and use it wherever we required in a convenient way with a simple view name instead of using complex queries every time.
  • We can easily perform our custom operations on view based on our requirements.

Now we will see how to create a view in a detailed manner.

Syntax of SQLite Create View

Generally in SQLite, we will use the CREATE VIEW command to create a VIEW. Following is the syntax of creating a view using the CREATE VIEW command.

 

CREATE [TEMP | TEMPORARY] VIEW [IF NOT EXISTS] view_name AS

SELECT column1, column2.....

FROM table_name

WHERE [condition];

If you observe the above syntax we used CREATE VIEW statement to create a new view with the SELECT statement. 

 

TEMP or TEMPORARY – The TEMP or TEMPORARY option is useful to create temporary VIEW which is specific to the current database connection. The VIEW which is created with a TEMP or TEMPORARY option will be deleted automatically whenever the current database connection is closed.

 

IF NOT EXISTS – This option will help us to prevent creating views that already exist.

 

Select Statement – The defined SELECT statements will be packed as view.

SQLite Create Views Example 

Now we will see how to create a view using select statements with example for that we need to create two tables dept_master and emp_master and need to insert some data.

 

To create and insert some data in the dept_master table use the following query.

 

CREATE TABLE dept_master

(dept_id INTEGER PRIMARY KEY AUTOINCREMENT,

dept_name TEXT);

 

INSERT INTO dept_master(dept_name)

VALUES('Admin'),

('Sales'),

('Quality Control'),

('Marketing');

Once we create and insert data use following query to see the data of dept_master table.

 

sqlite> SELECT * FROM dep_master;

 

dept_id     dept_name

----------  ----------

1           Admin

2           Sales

3           Quality Control

4           Marketing

Same way, the following queries to create and insert some data in emp_master table.

 

CREATE TABLE emp_master

(emp_id INTEGER PRIMARY KEY AUTOINCREMENT,

first_name TEXT,

last_name TEXT,

salary NUMERIC,

dept_id INTEGER);

 

INSERT INTO emp_master

values (2,'Shweta','Jariwala', 19300,2),

(3,'Vinay','Jariwala', 35100,3),

(4,'Jagruti','Viras', 9500,2),

(5,'Shweta','Rana',12000,3),

(6,'sonal','Menpara', 13000,1),

(7,'Yamini','Patel', 10000,2),

(8,'Khyati','Shah', 500000,3),

(9,'Shwets','Jariwala',19400,2);

Now run the following query to check records of emp_master table.

 

sqlite> SELECT * FROM emp_master;

 

emp_id      first_name  last_name   salary      dept_id

----------  ----------  ----------  ----------  ----------

2           Shweta      Jariwala    19300       2

3           Vinay       Jariwala    35100       3

4           Jagruti     Viras       9500        2

5           Shweta      Rana        12000       3

6           Sonal       Menpara     13000       1

7           Yamini      Patel       10000       2

8           Khyati      Shah        50000       3

9           Shwets      Jariwala    19400       2

12          Sonal       Menpara     20000       4

Here we will create new VIEW for the employees who belong to the Sales Department and we will name the view as SalesTeam.

 

CREATE VIEW SalesTeam AS

SELECT emp_id,first_name,last_name

FROM emp_master

WHERE dept_id IN

(SELECT dept_id FROM dept_master

WHERE dept_name='Sales');

If you observe above CREATE VIEW example we are creating a new view “SalesTeam” to access the result set of SELECT statements. Now we will see how our newly created VIEW will return data using the following query.

 

SELECT * FROM SalesTeam;

When we run above query we will get the result like as shown below.

 

emp_id      first_name  last_name

----------  ----------  ----------

2           Shweta      Jariwala

4           Jagruti     Viras

7           Yamini      Patel

9           Shwets      Jariwala

If you observe the above result it returned only 3 columns because while creating VIEW we used only three columns in Select statement.

SQLite Drop or Remove Views

Generally in SQLite to remove or delete a view from the database we use the DROP VIEW statement like as shown below.

 

DROP VIEW [IF EXISTS] view_name;

The above syntax will remove or delete VIEWS from database.

 

IF EXISTS – Its good practice to use IF EXISTS in the DROP VIEW query because it will prevent errors in case if we try to delete or remove not existing VIEW. 

 

view_name – Its name of the view which we want to remove from database.

Example to Drop VIEW in SQLite

Following is the example to remove the “SalesTeam” view that we created in current database.

 

DROP VIEW IF EXISTS SalesTeam;

The above query will drop or delete the “SalesTeam” view. Now if we try to drop the same view without IF EXISTS then it will throw error like as shown below.

 

sqlite> DROP VIEW SalesTeam;

 

Error: no such view: SalesTeam

This is how we can use VIEWS in SQLite based on our requirements. 

Previous
 
Next