SQL Views

Introduction

“A functionality which might open doors to enhance coding experience”

A database is a collection of tables storing some meaningful data. All the tables are connected with some form of relation. But the fundamental question that arises here is why we need to create so many tables and not just a single table. Multiple Tables allow for storing large amounts of data in a systematic manner, rather than repeating the same data in a single table. But, we cannot deny the fact that if we want to query some data from this database, we need to bring all relevant tables together to make a new table.

So, if we observe this issue closely, we have the advantage of storing data in multiple tables on one side, but on the other side, we have the problem of querying so scattered data. The solution to this problem brings “SQL Views” into the picture. SQL Views have made managing data and tables relatively easy for users. Views are virtual tables, i.e., they don’t exist in reality in the database, hence don’t require any storage in a database. Virtual Tables also have rows and columns similar to a real table in a database. Such views are simply made by selecting data(fields) from one or more tables, present in the database, with some conditions for selecting rows of the table.

Use of Views in SQL

Views are nothing more than SQL statement, which allows users to do the following:

• Views were introduced to reduce the complexity of the multiple tables and deliver data in a simple manner. Views hide the complexity of the data in the database as they join and simplify multiple tables into a single virtual table, which is easier for a user to understand.
• Being a virtual table, views take very little storage since the database contains only a view's statements (definition) and not a copy of all the tables the view is creating. For example, even if we create multiple views, it still won’t take much space compared to a single real table in the database.
• Views provide security to the data, acting as a security mechanism. Let’s take a simple scenario, in an IT company, the engineer, the HR, and the manager might be using the same table for some information. But because of their different departments, some data must be irrelevant to HR but relevant to the manager. If so, shouldn’t there be a security mechanism that would hide irrelevant information from the table from HR? Yes, views allow us to mask/show some data of the table depending on requirements and security. With the help of conditions, we can hide some data for a particular person.
• We can easily update the rows in the virtual tables (views) as the DBMS translates our request through the views. For example, let’s assume that while you are working in a company, you made a view to query some data for a product to be visible on a website. Later, you remember you forgot to add some rows that the client required. You can easily use the update feature to add those rows to your views in such a situation.
• Views also maintain data integrity as it presents a consistent and accurate image of the data from the database even if the underlying source is restructured, renamed, or split. It can automatically check the data which a user or any other third party is trying to access meets the conditions mentioned in the views to maintain accuracy while displaying data.

SQL Creating a View

The Views in SQL are created with the CREATE VIEW syntax. Following is the basic syntax to create a VIEW in SQL:

To see the data in the view, we can query the view using the following SELECT statement:

• “CREATE VIEW” is used at the start of the code to initiate the view.
• “SELECT” is used to decide which columns to pick from the tables.
• With the help of the “FROM” term, we can select the tables from which columns(data) have to be picked.
• “Table1..table N” denotes the names of the tables. (Here, for example, we have “Scaler Courses” & “Author Details” as tables.)
• “WHERE” is used to define the condition pertaining to selecting rows.

We will learn more about the different types of operations in the views with some simple examples. Let’s take the below scenario to understand views thoroughly.

At Scaler, some people work in different departments on a particular project to publish information about the company's courses. To adhere to the particular data which needs to be shown at some time to the users, the technical team takes the help of the views in SQL to query the results they want. We will try to understand the whole article through the examples of these tables, namely SCALER COURSES & AUTHOR DETAILS, which are already in the database of the company.

Table 1 (ScalerCourses)

Table 2 (AuthorDetails)

Now let’s start by creating a view. Here the tech team will 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:

Isn’t it quite simple? Now let’s explore the different sorts of operations in views with some examples and code snippets.

SQL Updating a View

Suppose one of the team members at Scaler thought of adding the details of the language used during teaching of the courses to the students, for letting students from all parts of India select educational courses according to their ease. The team can easily add these things by updating the view.

A view can be easily updated with the CREATE OR REPLACE VIEW statement, but certain conditions must be considered while updating. For example, the Tech Team wants to update 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.

SQL Dropping a View

After some months, the team at Scaler thought of scrapping the virtual tables they created for displaying on the website. SQL Views allows them to delete a view using the DROP statement.

SYNTAX:

For Example:

And Boom, the existing view gets deleted.

Managing SQL Views

For managing the views, there are different aspects which were quite explained well in the above topics like:

• Creating Views
• Updating Views(Replacing Views)
• Inserting Rows
• Renaming Views
• Deleting Views(Drop Statement)
• Listing Views(By querying we can list all the views in the Database)

With the above operations, we can easily manage the views which we create and keep updating them from time to time for real-life use.

Types of Views in SQL

In SQL Server, we mainly have two types of views: System Defined View and User Defined View. Here we have divided the other views between these two and explained them briefly for your better understanding.

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 System Defined Views are always, accordingly, linked to some User-Defined databases. Moreover, 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. The syntax of this view starts with INFORMATION_SCHEMA, which is succeeded by the view name like INFORMATION_SCHEMA.[View Name].

Output:

All the rows and columns of the particular table (i.e., ScalerCourses here) which are selected are displayed. It displays the “detailed information” of that table.

• 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. The syntax starts with “sys”.

Output:

Just like the below table, this view would query out the info about the database.

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

Output:

After using this view, such output helps the user administer the problems related to the SQL Server and DB.

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.

Creating Views from Single Table

Let’s take the example of a “Simple View”. Here we took the data from a single table, namely “Scaler Courses” as done while creating a view previously.

The scenario of creating the view “Course View” was a perfect example of creating views from a Single Table.

The code snippet for View from Single Table is as follows:

Creating Views from Multiple Tables

Let’s now take the example of a “Complex View”. Here we create a view that takes data from two or more tables to give an output in a simplified manner. To understand this better, we will create a view by taking data from two tables, namely, “Scaler Course” & “Author Details,” which are mentioned earlier in the article.

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.

Do refer to the pictorial representation below for a better understanding.

The code for multiple views is as follows:

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

Output:

Read More:

• SQL and NoSQL.