SQLite Views

  Total Views : 310
  Comments : 0
  Keywords : create view in sqlite with example, sqlite drop or alter view with example, sqlite query to create view if not exists with example

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 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

 

  • Whenever we have complex and commonly used queries then we can pack it as view and use it wherever we required in convenient way with 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 view in detailed manner.

Syntax of SQLite Create View

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.....

FROM table_name

WHERE [condition];

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.

SQLite Create Views Example 

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,

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 use 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.

 

sqliteSELECT 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

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 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.

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 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 “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.

 

sqliteDROP VIEW SalesTeam;

 

Error: no such view: SalesTeam

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

 

NEXT

SQLite Indexes  
 

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User