Example of Create View

Consider a Students table with the following data:

After Inserting Some data into the Students table, the table looks:

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:

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:

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:

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.

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.