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.
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 like 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 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 updated data based on SQLite query statements.
Mostly SQLite views are useful in following scenarios
Now we will see how to create view in detailed manner.
Generally in SQLite we will use CREATE VIEW command to create a VIEW. Following is the syntax of creating view using CREATE VIEW command.
CREATE [TEMP | TEMPORARY] VIEW [IF NOT EXISTS] view_name AS
SELECT column1, column2.....
If you observe above syntax we used CREATE VIEW statement to create new view with SELECT statement.
TEMP or TEMPORARY – The TEMP or TEMPORARY option is useful to create temporary VIEW which is specific to current database connection. The VIEW which is created with 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 which is already exists.
Select Statement – The defined SELECT statements will be packed as view.
Now we will see how to create view using select statements with example for that we need create two tables dept_master and emp_master and need to insert some data.
To create and insert some data in dept_master table use following query.
CREATE TABLE dept_master
(dept_id INTEGER PRIMARY KEY AUTOINCREMENT,
INSERT INTO dept_master(dept_name)
Once we create and insert data use following query to see the data of dept_master table.
sqlite> SELECT * FROM dep_master;
3 Quality Control
Same way use following queries to create and insert some data in emp_master table.
CREATE TABLE emp_master
(emp_id INTEGER PRIMARY KEY AUTOINCREMENT,
INSERT INTO emp_master
values (2,'Shweta','Jariwala', 19300,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 are belongs to Sales Department and we will name view as SalesTeam.
CREATE VIEW SalesTeam AS
WHERE dept_id IN
(SELECT dept_id FROM dept_master
If you observe above CREATE VIEW example we are creating new view “SalesTeam” to access the result set of SELECT statements. Now we will see how our newly created VIEW will return data using 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 above result it returned only 3 columns because while creating VIEW we used only three columns in Select statement.
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 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.
Following is the example to remove “SalesTeam” view that we created in current database.
DROP VIEW IF EXISTS SalesTeam;
The above query will drop or delete “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.