Views in SQL

Views in SQL are virtual tables created by querying data from one or more real tables in a database. They provide a powerful way to simplify complex queries, enhance data security, and tailor data presentation to specific user needs. By encapsulating SQL queries, views enable users to work with data more efficiently and transparently, without altering the database structure.

SQL Creating Views

Following is the basic syntax to create a VIEWS in SQL:

To access the data within the view, execute the following SELECT statement:

We will try to understand the whole article through the examples of these tables, namely ScalerCourses & AuthorDetails.

Table 1 (ScalerCourses)

Table 2 (AuthorDetails)

Creating View for Single table

Let's create a view named “CourseView” from the table “ScalerCourses” (Table 1) for querying some specific course details for the students, which are below the cost of Rs 2000, to display on the website.

We can see the following data by querying the view as follows:

The output of the above query:

Creating View for Multiple

Let's take the scenario that the team at Scaler wants to show the author of each course in the same virtual table they are creating via views. The details of the author can be taken from a different table(Author Details), already present in the database.

The code for multiple views is as follows:

We can see the above data, by querying the view as follows:

Output:

Listing ALL Views

There are multiple ways to enumerate all views in a database, with methods varying by the specific SQL database management system in use. Here, we'll discuss two universally applicable methods: leveraging the SHOW FULL TABLES command and querying the information_schema.views table.

Method 1: Utilizing SHOW FULL TABLES

This method is particularly useful. It allows users to quickly identify all views alongside tables in the database:

Replace your_database_name with the name of your database. This command filters the list to include only views, excluding regular tables.

Method 2: Querying the information_schema.views

For a more cross-compatible approach, applicable in MySQL, PostgreSQL, SQL Server, and other RDBMS, the information_schema.views table can be queried. This schema contains information about all views within the database:

This query provides a broad look at all views, their schemas, and a preview of their definitions. Adjust 'specific_schema_name' to target a specific schema or remove the WHERE clause to list views from all schemas.

Deleting Views

VIEWS in sql allows them to delete a view using the DROP statement.

SYNTAX:

For Example:

And Boom, the existing view gets deleted.

Updating Views

Views in sql can be easily updated with the CREATE OR REPLACE VIEW statement, but certain conditions must be considered while updating. For example, Updating the CourseView and add the CourseLanguage as a new field to this View.

Now, if we want to look at the data in CourseView, we can query as follows.

Output for the above statements is as follows:

As we can see from the above output, the column of Course Language got added to the views.

With Check Option in SQL

The “WITH CHECK OPTION” clause is quite a useful statement for views. It helps in updating views accurately and consistently. (This is an optional clause in the CREATE View statement) We will understand the working of this better via an example. Here are some points to take note of before moving ahead.

For example, Using “WITH CHECK OPTION,” you can set the conditions which are within the WHERE clause of the SELECT statement.

Now, if anyone inserts a new value to the view, it can be done as follows:

The above statement inserted a row that makes the condition in the WHERE clause (Cost<2000) not true. This did not follow the condition mentioned, so the following error message will be shown.

Uses of a View in SQL

Views in SQL simplify working with databases by acting like shortcuts to complex data. Here's how they help:

• Simplify Data: Combine data from several tables into one easy-to-understand view, making complex data look simple.
• Save Space: Views only store the "instructions" to see data, not the data itself, so they use very little storage.
• Boost Security: Control what data people can see, protecting sensitive information by showing only what's needed.
• Easy Updates: Change data through views without directly altering tables, making data management flexible.
• Keep Data Consistent: Even if the underlying tables change, views help ensure that the data you see remains accurate and reliable.

In short, views make data easier to handle, save space, enhance security, allow for straightforward updates, and keep information consistent.

Types of Views in SQL

In SQL Server, we mainly have two types of views: System Defined View and User Defined View.

1. System Defined View

These are pre-established views that exist in the Master Database of SQL Server. These views act as templates for data and tables. The main motive of these views is to provide details about the particular databases and their functions.

System-defined views are divided into three types of view, namely, Information Schema, Catalog View, and Dynamic Management View, which will be discussed as follows.

• Information Schema View – In the SQL Server, we have around 20 different schema views. Such views are used to display the information of a database, such as the tables.

• Catalog View – Catalog views were introduced in 2005 in SQL Server. They are categorized in various groups and it returns data that the SQL Database and Server use. These views provide an efficient way to present, obtain and transform data.

• Dynamic Management View- These views were also introduced in 2005. In these views, the administrator obtains the details of the current state of the SQL server to better diagnose it and tune the SQL Server for optimal performance. Also, there are two subtypes, namely Server Scoped and Database Scoped.

2. User Defined View

The users define these types of views. Moreover, there are two types of user-defined views: Simple View and Complex View.

• Simple View – Such views are created based on a single table. In simple views, all such operations of update, delete, etc., are possible. Creating a View from a single table is discussed below for your further understanding.
• Complex View – On the other hand, if a view is created from more than one table, it is called a complex view. Such views can contain group data. Moreover, in a complex view, all such operations of update, delete and insert are impossible. Creating views from multiple tables is discussed later on in this article for further clarity on the above topic.

Also, there are other types of views, such as Materialized Views and Inline Views.

In SQL Server, we have different views to manage the data and database well. These views reduce the complexity by sorting the data into a single table, thus saving a lot of time.