Views in SQL

quiz
Challenge Inside! : Find out where you stand! Try quiz, solve problems & win rewards!

Overview

A View in SQL is simply a virtual table created based on a result set of another SQL statement. Views were introduced to reduce the complexity of multiple tables and deliver data in a simple manner. Views help us maintain data integrity and provide security to the data, thus acting as a security mechanism.

Scope

  • In this article, we will see the usage/ perks of Views in SQL.
  • After that, we will discuss how to do various operations on a View (update, insert, delete).
  • Then we go through different types of views and how to create views from single and multiple tables.

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 that why do we need to create so many tables and not just a single table. Multiple Tables allow 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.

Uses of Views (Perks)

Views are nothing more than a 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 of 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.

Creating a View in SQL

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

CREATE VIEW view_name AS  
SELECT column1, column2...column N 
FROM table1, table2...table N 
WHERE condition;

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

SELECT  * FROM [view_name];
  • “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 understanding 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)

SNoNameDurationCourseLanguageCost(Rs)
1Python Foundation3-4 monthsEnglish1500
2Django5 monthsEnglish1000
3C++4-5 monthsHindi500
4Interview Preparation6 monthsEnglish1800
5Node Js6 monthsHindi2500

Table 2 (AuthorDetails)

SNoNameRating
1Anshuman5
2Ravi4
3Raman4.5
4Yash5
5Jatin4.5

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.

CREATE VIEW CourseView AS
SELECT Name, Duration
FROM ScalerCourses
WHERE Cost < 2000;

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

SELECT * FROM CourseView;

The output of the above query:

NameDuration
Python Foundation3-4 months
Django5 months
C++4-5months
Interview Preparation6 months

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

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.

CREATE OR REPLACE VIEW CourseView AS
SELECT Name, Duration, CourseLanguage
FROM ScalerCourses
WHERE Cost < 2000;

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

SELECT * FROM CourseView;

Output for the above statements is as follows:

NameDurationCourse Language
Python Foundation3-4 monthsEnglish
Django5 monthsEnglish
C++4-5monthsHindi
Interview Preparation6 monthsEnglish

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

Note: Here we have updated the view by keeping the condition that “Cost” is below 2000. It is possible to keep the condition (WHERE statement) of a particular view based on a field that is present in the original table in the database but not in the views we created.

Inserting Rows in a SQL View

The Scaler HR team did hire some teachers in the past month. Now looking into the demand for some courses by the students, they thought of adding a new course to the virtual table. For updating the view with a new row, they can update the view by inserting new rows into the view, by using the INSERT INTO statement.

INSERT INTO CourseView(Name, Duration)
VALUES(“Java”, “4 months”);

The following will be the data stored in our view after executing the above query.

NameDuration
Python Foundation3-4 months
Django5 months
C++4-5months
Interview Preparation6 months
Java4 months

Deleting Rows Into a View

At regular intervals of the educational courses, feedback was taken from the students. While taking those feedback into account, the team at Scaler wanted to take down a particular course. To tackle this issue, they used one of the functionalities of the views in SQL. A row can be deleted from the view using the DELETE FROM statement. The following is the syntax to remove a row from the view.

DELETE FROM CourseView
WHERE Name = “Python Foundation”;

The data after executing the above query looks as follows.

NameDuration
Django5 months
C++4-5months
Interview Preparation6 months
Java4 months

Bingo! One of the rows got deleted from the view.

Dropping Views(Deleting 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:

DROP VIEW view_name;

For Example:

DROP VIEW CourseView;

And Boom, the existing view gets deleted.

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

  • If the statement is specified in the create view, then every row updated or inserted must conform with the view's definition (the condition specified).
  • An error message will be shown if WITH CHECK OPTION is in the CREATE Statement, and the update does not match the condition in the Create statement.

For example, the team at Scaler is sharing its “Views” codes to some other third party for handling the time-to-time updates needed in the views. They don’t want any other third-party employee to update anything apart from what is mentioned in the condition. They want certain restrictions to be imposed while updating the view. Using “WITH CHECK OPTION,” they can set the conditions which are within the WHERE clause of the SELECT statement.

CREATE VIEW CourseView AS
SELECT Name, Duration
FROM ScalerCourses
WHERE Cost < 2000

WITH CHECK OPTION;

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

INSERT INTO CourseView(Name, Duration, Cost)
VALUES(“Ruby”, “7 months”, 3000);

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.

Error Code: 1369. CHECK OPTION failed 'classicmodels.CourseView'

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].
SELECT * FROM INFORMATION_SCHEMA.CourseView
where TABLE_NAME='ScalerCourses'

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”.
select * from sys.databases

Output:

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

catalog view in sql

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

dynamic management view in sql

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.

creating view in sql

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

CREATE VIEW CourseView AS
SELECT Name, Duration
FROM ScalerCourses
WHERE Cost < 2000;

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.

multiple view in sql

The code for multiple views is as follows:

CREATE VIEW CourseView AS
SELECT ScalerCourses.Name, ScalerCourses.Duration, AuthorDetails.Author
FROM ScalerCourses, AuthorDetails
WHERE Cost < 2000;

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

SELECT * FROM CourseView;

Output:

multiple table views in sql

Conclusion

  • Learned a lot of things in one go? Don’t worry, go through the examples, scenarios, and images to clear your concept well. Most of the concepts were aided by examples to convey the idea lucidly.
  • This article elaborately describes Views in SQL with proper codes for better understanding.
  • We have covered different operations of views to types of views. We also even learned how to create Views from Single and Multiple Tables as well as the perks of using views.
  • Try using such views to improve your coding experience in SQL and practice similar problems to understand the syntax well.
Free Courses by top Scaler instructors
certificate icon
Certificates
SQL Tutorial
This program includes modules that cover the basics to advance constructs of SQL Tutorial. The highly interactive and curated modules are designed to help you become a master of this language.'
If you’re a learning enthusiast, this is for you.
Module Certificate
Criteria
Upon successful completion of all the modules in the hub, you will be eligible for a certificate.
You need to sign in, in the beginning, to track your progress and get your certificate.