DbSchema | SQLite - How to Create Views?

Publish on DbSchema Blog >>>

Table of Contents

1. Introduction
2. Prerequisites
3. What is a View?
4. Usage of Views
5. Advantages and Limitations of Using a View
6. Creating a View in sqlite3
7. Creating a View in DbSchema
8. Conclusion
9. 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 __abstraction over 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.

References

1. SQLite Documentation
2. DbSchema Documentation
3. SQLite Tutorial