DbSchema | SQLite - How to Create Views?
Table of Contents
- Introduction
- Prerequisites
- What is a View?
- Usage of Views
- Advantages and Limitations of Using a View
- Creating a View in sqlite3
- Creating a View in DbSchema
- Conclusion
- References
Introduction
SQLite
is a C-library that provides a __disk-based database with zero __configuration
and no __server process requirement. This __article
explores a key feature of __SQLite and other SQL databases — the __View
. We’ll discuss the concept of views, their uses, advantages, and limitations. Additionally, we’ll provide a detailed guide on __creating views in __sqlite3
and __DbSchema`.
Prerequisites
Basic knowledge of __SQL is essential to understanding this article. Familiarity with __SQLite
and the basics of the __sqlite3 command-line utility and __DbSchema
would be helpful.
For installation and establishing connection you can read our article SQLite-How to create a database?
What is a View?
In SQL, a __View is a __virtual table
based on the __result-set of an SQL statement. It contains __rows
and __columns, just like a real table. The fields in a view are __fields
from one or more real tables in the database. A __view does not __physically
exist. It is a set of queries that, when __invoked, provide a tabular result similar to a regular database table. However, views don't store data — they __pull
from other tables.
Usage of Views
Complexity Hiding: Views can hide the __complexity
of data. A view can __encapsulate
complicated joins, __aggregations`, and calculations into a simpler table.Data Protection: Views can limit the __degree
to which the underlying database schema is __exposed
. They can also provide a level of __abstractionover your tables, providing a measure of __security
.Data Preparation: They can __transform
the data into a format that __application-specific code
can work with.
Advantages and Limitations of Using a View
Advantages:
Data security: Views __restrict
user access to the data because the view can display __selective columns
from the table.Complex queries can be __encapsulated` in a view for repeated usage.
Data from multiple tables can be __combined
and represented as one __entity
.
Limitations:
When a table is dropped, associated views become __irrelevant`.
You __can’t
create a view on temporary tables or associated __indexes
.In SQLite, you can’t use the __DROP VIEW IF EXISTS` statement.
Creating a View in sqlite3
Here’s how to create a view using sqlite3:
Step 1: Create and connect to your database:
sqlite3 TestDB.db
To know more about creating a database you can read our article SQLite-How to create a database?
Step 2: Create a sample table:
CREATE TABLE Employees(id INTEGER PRIMARY KEY, name TEXT, role TEXT);
To know more about creating a table you can read our article SQLite-How to create a Table?
Step 3: Insert some sample data:
INSERT INTO Employees(name, role) VALUES('John Doe', 'Manager');
INSERT INTO Employees(name, role) VALUES('Jane Smith', 'Developer');
INSERT INTO Employees(name, role) VALUES('David Gray', 'Developer');
INSERT INTO Employees(name, role) VALUES('Mia Brown', 'Sales');
INSERT INTO Employees(name, role) VALUES('Max Green', 'Developer');
Sample Database
__Employees` table:
ID | Name | _Role_ |
---|---|---|
__1` | John Doe | Manager |
__2` | Jane Smith | Developer |
__3` | David Gray | Developer |
__4` | Mia Brown | Sales |
__5` | Max Green | Developer |
Step 4: Create a view
Here we create a view that shows all developers:
CREATE VIEW Developers AS SELECT name FROM
Employees WHERE role='Developer';
Step 5: Use the view:
SELECT * FROM Developers;
Result After Executing the Query:
After executing a query on the __Developers` view, the result would be:
_Name_ |
---|
__Jane Smith` |
__David Gray` |
__Max Green` |
This output table shows that we have __three Developers in our company according to the __Employees
table: __Jane Smith, __David Gray
, and __Max Green`.
Creating a View in DbSchema
DbSchema
is a visual database designer that allows managing SQL and NoSQL databases using interactive diagrams.
Step 1: __Launch DbSchema and __connect
to your SQLite database.
Step 2: Right-click on the canvas and select __Create View`.
Step 3: Enter the __SQL statement` to define your view.
SELECT name FROM Employees WHERE role='Developer';
Step 4: Click __Apply` to create the view.
You can now use the view like any other table in your database.
Visually Manage SQLite using DbSchema
DbSchema is a __SQLite client and __visual designer
. DbSchema has a free Community Edition, which can be downloaded here.
Key Features of DbSchema:
Following are the key features of DbSchema which distinguish it from other database GUI tools.
Conclusion
Views
in SQLite and other SQL databases provide a powerful tool for __abstracting the underlying schema, improving __data security
, and simplifying complex queries. While they come with certain __limitations, their benefits far __outweigh
these in most use cases.