SQLite is a self-contained, serverless, and open-source relational database management system. It is used for simplicity, efficiency, and portability, SQLite is widely employed in diverse applications, from embedded systems to mobile devices and large–scale software.
It is serverless, zero-configuration setup, and supports standard SQL making it a preferred choice for developers to perform operations and queries. It supports various platforms, allowing for easy implementation and reliable data storage and retrieval.
What is a View?
A VIEW is a composition of a table in the form of a predefined SQLite query. A view can contain all rows of a table or selected rows from one or more tables. A view can be created from one or many tables, which depends on the written SQLite query to create a view.
Views provide a way to encapsulate complex queries or frequently used filters, improving code readability and maintainability.
Views play a crucial role in Database management systems, offering several key advantages:
- Data Abstraction: Views provide a layer of abstraction, allowing users to interact with a subset of data without exposing the underlying complexities of the database schema. This enhances data security and simplifies user interactions.
- Simplified Querying: By encapsulating complex SQL queries into views, database administrators can simplify the querying process for end-users. This is particularly valuable when intricate joins, filters, or calculations are needed.
- Enhanced Security: Views enable the implementation of security measures by restricting access to specific columns or rows. Users can access views without direct access to the underlying tables, ensuring sensitive information remains protected.
- Logical Data Organization: Views help organize data logically by presenting it in a format that aligns with the user’s perspective or specific business requirements. This improves the overall usability of the database and facilitates a better understanding of the data structure.
The CREATE VIEW command assigns a name to a SELECT statement. Once the view is created, it can be used in the FROM clause of another SELECT in place of a table name. You cannot DELETE, INSERT, or UPDATE a view. Views are read-only in SQLite. Views are removed with the DROP VIEW command.
If the “TEMPORARY” keyword occurs between “CREATE” and “VIEW,” then the View that is created is only visible to the database connection that created it and is automatically deleted when the database connection is closed.
Syntax for Create View:
CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2, ..., columnn
FROM table_name
WHERE [condition];
Example of Create View
Consider a Students table with the following data:
Schema of students Table
After Inserting Some data into the Students table, the table looks:
Data in students Table
Following is an example to create a view from the Students table.
Example 1
Query:
Let’s create an view for some columns in the students table using some clauses.
CREATE VIEW high_scorers AS
SELECT name, percentage, city
FROM students
WHERE percentage > 80.0;
Output:
Create high_scorers view
Explanation: The above query creates a view naming high_scorers which have 3 columns, namely “name,” “percentage” and “city.” It includes the data only of students with more than 80 in the percentage column of the Students table.
We can check if the View is created successfully using the .tables command. In the above image we can see high_scorers and students as output of .tables command. high_scorers is visible in the output as we just created that view.
Query:
Let’s execute queries on high_scorers view similarly to how you query an actual table.
Output:
Output of high_scorers
Query:
Let’s findout the city in the high_scorers WHERE city name is City1.
Output:
Explanation: This query will display all the records where city is City1 (Students of City1) from the high_scorers view.
Temporary View
We haven’t used the “TEMPORARY” keyword, so We View will be saved even after closing the Database Connection.
Below is an example using the “TEMPORARY” keyword. The View created is only visible to the database connection that created it and is automatically deleted when the connection is closed.
Query:
CREATE TEMPORARY VIEW high_scorers AS
SELECT name, percentage, city
FROM students
WHERE percentage > 80.0;
Output:
Create Temporary view named high_scorers
Explanation: The above query will create a temporary view including “name“, “percentage” and “city” columns from students table. The VIEW will get deleted once the Database Connection is closed. We can perform all the operations as the standard View. The View is deleted once the Database connection is closed.
Drop a View
Syntax:
DROP VIEW view_name;
Let’s drop the previous view that we have created called high_scorers.
Query:
DROP VIEW high_scorers;
Output:
Drop/Delete high_scorers view
Explanation: The above query will delete the high_scorers.
Example 2
The “Inventory” is a table designed with SQLite, featuring a numerical primary key column named ‘id‘ and character data type attributes such as ‘name,’ ‘category,’ ‘description,’ ‘location,’ and ‘status.’
It stores information about various items, including their names, categories, descriptions, physical locations, and availability status.
Query:
CREATE TABLE Inventory
(
id INTEGER PRIMARY KEY,
name TEXT,
category TEXT,
description TEXT,
location TEXT,
status TEXT
);
After inserting some data into the Inventory table the table looks:
Data in Inventory Table
Create a View
Now we will make a View for Available items. This makes it easier to obtain a list of available items without repeating the same condition in every query.
Query:
Let’s make AvailableItems view, which will consist all the attributes of only the items that are looks Available in the status Column from Inventory table.
CREATE VIEW AvailableItems AS
SELECT id, name, category, description, location
FROM Inventory
WHERE status = 'Available';
Output:
Create AvailableItems View
Query:
Let’s execute the queries on the AvailableItems View.
Output:
Explanation: This Query fetch all the data from the AvailableItems View.
Query:
Let’s findout the location in the AvailableItems WHERE location is Store A.
Explanation: This Query fetch all the location Store A from the AvailableItems Views.
Query:
We can drop the View using the DROP VIEW Command. The below query will delete the AvailableItems View.
DROP VIEW AvailableItems;
Output:
Drop/Delete AvailableItems view
Conclusion
In SQLite, views provide a powerful mechanism for simplifying complex queries and enhancing the overall manageability of databases. Views offer a layer of abstraction that hides the complexity of underlying data structures by allowing users to encapsulate specific queries into virtual tables. This can be particularly useful for organizing and presenting data that aligns with clear business logic or reporting requirements.
As you explore the capabilities of views in SQLite, consider how they can be tailored to the unique requirements of your projects, providing an elegant solution to complex data access scenarios.