DbSchema | SQLite - How to Create Views?

Sqlite • 08-Jun-2023

by Malik Umair Sajjad

SQLite alt >

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