SQLite - SPLessons

SQLite Views

Chapter 16

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

SQLite Views

SQLite Views

shape Description

SQLite Views are database objects that can be created on a table to improve the performance of the database. A view has unique look on data from one or more tables. It can organize data in  unique order, focus or hide some data.

A view comprises of a stored query accessible as a fundamental table composed of the outcome set. Beside standard tables a perspective does not be shaped as a part of the physical schema. It is a virtual table, dynamic in the database.

View is a stored query, and it can be attributed like a table.

SQLite Views object is mainly classified into:

Create view

shape Description

Once SQLite Views are created from base table, all DML operations can be performed on that view which effects its base table. This kind of view is called Simple View.

Create any number of columns in a table. The system privileges are required to create own schema, and can execute any object privilege on object type.

shape Syntax


Create [TEMP|TEMPORARY] VIEW view_name AS SELECT column1,column2…columnN from table _name where

[condition];

table_name => Any accurate table.

column => The columns that inserted in the table.

Condition => The condition to get the specific result set . 

shape Examples

By viewing the below example, the concepts of simple SQLite views understands easily.

sqlite> create table cabs(cab_id int,name varchar2(20),age int,address varchar2(20),salary int);
sqlite> insert into cabs values(1,'David',27,'Texas',10000);
sqlite> insert into cabs values(2,'Mark',26,'South hall',9000);
sqlite> insert into cabs values(3,'Pollard',29,'Houston',11000);
sqlite> insert into cabs values(4,'Allen',32,'Norway',5000);
sqlite> insert into cabs values(5,'Teddy',30,'Edmold',6000);
sqlite> select * from cabs;
1|David|27|Texas|10000
2|Mark|26|South hall|9000
3|Pollard|29|Houston|11000
4|Allen|32|Norway|5000
5|Teddy|30|Edmold|6000
sqlite> create view cabs_view as select cab_id,name,age from cabs;
sqlite> select * from cabs_view;
1|David|27
2|Mark|26
3|Pollard|29
4|Allen|32
5|Teddy|30

In the above example, a simple table cab has been created and by applying view operation to that table, like inserting some condition on column name (i.e.,create view cab_view as select cab_id, name, age from cabs then it will display only those column values from the table cabs).

Alter view

shape Description

Once SQLite Views are created from base table, all alter operations can be performed on that view which effects its base table. The alter view proclamation is like the create view proclamation aside from create is replaced with alter keyword.

shape Syntax


Alter view table <table_name>as select from where condition;

Table_name => Any accurate table.

Condition => The condition to get the specific result set.

shape Examples


By viewing the below example, the concepts of Alter view can be understand easily.

sqlite> select * from cabs;
1|David|27|Texas|10000
2|Mark|26|South hall|9000
3|Pollard|29|Houston|11000
4|Allen|32|Norway|5000
5|Teddy|30|Edmold|6000
sqlite> alter view experience as select name from cabs where age>29;
4|Allen|32|Norway|5000
5|Teddy|30|Edmold|6000

In the above example, cabs table has been created and then alter the table by applying view operations to that table, like adding some condition on column values using where clause.(The column name age have been alter by using where clause,i.e,age<29 will be show in the result set.

Drop view

shape Description

Drop table will drop the table. In the same way drop view will drop the permission of view table, and there is no such table exist in the Databases.

shape Syntax

Drop  view table <table_name>;

table_name =>The accurate table stored in the database.

shape Examples

sqlite> create table cabs(cab_id int,name varchar2(20),age int,rate int,salary int);
sqlite> insert into cabs values(1001,'Prince',25,700000,12000);
sqlite> insert into cabs values(1002,'James',26,750000,12500);
sqlite> insert into cabs values(1003,'Mike',27,850000,13500);
sqlite> select * from cabs;
1001|Prince|25|700000|12000
1002|James|26|750000|12500
1003|Mike|27|850000|13500
sqlite> alter view experience as select name from cabs where age>26;
1003|Mike|27|850000|13500
sqlite> drop table cabs;
sqlite> select * from cabs;
Error: no such table: cabs
sqlite> select * from experience;
Error: no such table: experience

In the above example, the table name cabs have been dropped from the database, and cannot perform any operations on cars related to experiences.

Summary

shape Key Points

  • SQLite Views -View is a database object.
  • Create view – DML operations can be performed in create view.
  • Alter view – DML operation can be performed in alter view.
  • Drop view – drop view will drop a table.