Free Courses

Views in SQL

Learn about Views in SQL

Updated - 17 May 202215 mins readPublished : 8 Aug 2021
Published : 8 Aug 2021
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 Usage/ Perks of Views in SQL then we will disscuss on 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”

Heard of the term “Database”? It is just like a collection of tables storing some meaningful data. All the tables are connected with some sort of relation. For this reason, only we can use a relational database.

Now, here comes a question, why create so many tables? why not just one? Doesn’t it become quite complicated?

Multiple Tables allow storing large amounts of data in a systematic manner, rather than repeating the same data in a single table. But also, 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 look closely at this issue, on one hand, we have the advantage of storing data in multiple tables but on other hand we have the problem of querying so scattered data. The solution to this problem brings “SQL Views” in picture.SQL Views have made managing data and tables quite easy for users.

SQL Views are basically virtual tables. They don’t exist in reality(database), hence doesn’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. We will discuss this later on, in the article.

Uses of Views(Perks)

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

  • Everyone wants to get things in a simple manner. With this idea in mind, 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.
  • Always like having free space in devices? Why not keep our databases a bit clean? Being a virtual table, views takes very little storage since the Database contains only the statements(definition) of a view and not the copy of all the data(tables) the view is creating. For example, even if we create multiple views, still it won’t take much space compared to a single real table in the database.
  • Moreover, 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, there must be some data that is 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 hide/show some data of table depending on requirement and security. With the help of conditions, we can hide some data for a particular person. (Also depends on SQL engine used)
  • Through Views, 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 on, you remember you have forgotten to add some rows which were required by the client. In such a situation you can easily use the update feature to add those rows in your views.
  • Views also maintain the 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 if the data which the 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. Creating views in SQL is simple as a pie.

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];

For your better understanding of the syntax here we have defined few keywords of SQL:

  • “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, which pertains to the selection of rows.

We will be learning more about the different types of operations in the Views with some cool Examples. Let’s take the below scenario to understand views in a simple manner.

At Scaler some people are working at different departments in a particular project, which is to publish the information of the courses which the company provides. 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 such results which 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. While explaining the operations and types of views we will be referring to these tables.

SCALER COURSES

Table 1(ScalerCourses)

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

AUTHOR DETAILS

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 above 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

Something new came to your mind? Want to add a new exciting field into the Database? Now coming to the above scenario.

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 need to be in mind while updating.

For example, here the Tech Team wants to update the CourseView, and add the CourseLanguage as a new field to this View. This can be done as follows.

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

Bingo! 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.

Here the field “Cost” is not present in the views we created.

Inserting Rows in a SQL View

The Scaler HR team did hire some teachers 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 to the view, by using the INSERT INTO statement.

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

To view the data after the above statements, it is as follows.

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

Now we have the extra row added to the existing view.

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.

Apart from inserting a view, they can also delete particular rows in a view, by using the DELETE FROM statement. The syntax follows as below.

DELETE FROM CourseView
WHERE Name = “Python Foundation”;

To view the data after the above statements, it is 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)

Changed your mind? Want to delete an existing 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 in an accurate and consistent manner. (This is an optional clause in the CREATE View statement) We will understand the working of this better via an example. Some points to take note of before moving ahead.

  • If the statement is specified in the create view then every row which is updated or inserted must conform with the definition of the view(the condition which was 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. “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, as follows:

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

The above statement inserted a row which 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 have mainly two types of views, namely System Defined View and User Defined View. Here we have divided the other views between these two and explained it in brief 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 the 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(ie 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 different groups and it returns data which is used by the SQL Database and Server. 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 comes which helps the user to administer the problems related to the SQL Server and DB.

dynamic management view in sql

2. User Defined View

These types of views are defined by Users. Moreover, there are two types of user defined views, namely: 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 – While on the other hand, if a view is created from more than one table, is called Complex View. Such views can contain group data. Moreover, in Complex View, all such operations of update, delete, insert are not possible. 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 View and Inline View.

In SQL Server, we have such different sorts of 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 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 some 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.

Do try using such views to improve your coding experience in SQL and practice similar problems to understand the syntax well.

Challenge Time!
quiz
quiz
Time to test your skills and win rewards! Note: Rewards will be credited after the next product update.
Free Courses by top Scaler instructors