How to Create a View in SQL and Its Uses

We all know that SQL is a Structured Query Language used for storing, updating, manipulating and retrieving data from databases or tables. We can simply say that it is used to communicate with a database. Views in Structured Query Language (SQL) are simply a virtual table which is formed based on results obtained from the SQL Query can be created using both single tables and also with multiple tables.

Scope of the Article:

• In this article we will discuss the views, Views can be created and used as virtual tables.
• Views can also be updated, deleted, renamed, and inserted in rows.
• We will discuss the Creation of views with a single table and multiple tables.
• We will know the different types of views and discuss the uses of Views in SQL.

Introduction:

As said before, views in SQL are something like a virtual table or we can say it is a logical table which means views don't exist in reality, so not require any space in the database as we said views are tables, Views also have rows and columns same as the original table.

Views can be created using the tables in the database, we can use one table or more than one table to create the views depending on the condition we want to make the views.

We can specify which rows and which columns should be present in the views, which also depends on the condition we specify.

We can store the data in many/multiple tables, but the problem here is retrieving data, which needs the specification of multiple tables. We use these views to avoid using multiple tables and save space in the database.

These views manage data very easily from relevant tables quietly.

Now let us see the creation of Views.

Creation of views in SQL:

Views in SQL can be created using the keyword CREATE VIEW as the syntax.

Syntax to create a view in SQL:

CREATE VIEW viewname AS

SELECT column_1, column_2, .....column_n

FROM table_1, table_2, .....table_n

WHERE some_condition;

To retrieve data or to view data present in the created view use the SELECT statement:

SELECT * FROM viewname;

Let us discuss the syntax to create a view in brief.

→ Firstly, we start with CREATE VIEW which is the keyword to start the creation of view then we have to keep a name to the view we are creating (viewname).

→ Next, Select the particular columns from particular tables and then use some conditions. To define them use the WHERE clause.

Let us discuss the example to create a view from the employee table.

Table name: employees

Creation of view from employees’ table,

CREATE VIEW view1 AS

SELECT *

FROM employees;

We created a view that has the same data as the employee’s table, let us check the view using the

SELECT statement:

SELECT * FROM view1;

The result would be:

We can create a view with selected columns as well for example,

Creation of view from employees’ table,

CREATE VIEW view2 AS

SELECT Employee_name, Age

FROM employees;

Let us check the view using the SELECT statement:

SELECT * FROM view2;

The result would be:

Creation of view can also be done using WITH CHECK OPTION,

Use of WITH CHECK OPTION is to ensure update, and insert statements satisfy the conditions in the view creation statement.

Example of creating same view view2 with WITH CHECK OPTION,

CREATE VIEW view3 AS

SELECT Employee_name, Age

FROM employees

WHERE Age IS NOT NULL

WITH CHECK OPTION;

Here as per the query written, it checks whether Age is null or not and returns Age which is not null i.e., ignores the rows which have Age as NULL.

Till now we saw the creation view with a single table, Let us now see the creation of a view with multiple tables.

Views can be created using multiple tables, simply using multiple tables in the select statement.

Example:

Table1 students

Table2 marks

Creation of view from students’ table and marks table,

CREATE VIEW marks_view AS

SELECT students.Student_Name, students.Address, marks.Marks

FROM students, marks

WHERE students.Student_Name = marks.Student_Name;

Let us check the view using the SELECT statement:

SELECT * FROM marks_view;

The result would be,

For managing views, we have different aspects,

• Creating views
• Updating views
• Deleting views
• Renaming the views
• Inserting rows and more operations.

With these operations, we can easily manage the views.

Types of views:

System Defined View

1. Information Schema View
2. Catalog View
3. Dynamic Management View

User Defined View

1. Simple View

2. Complex View

Let us know about the uses of view,

Uses of Views in SQL:

• Views in SQL are used to reduce the usage of tables.

• A Proper database should contain views for -

  1. Restricting data access

  2. Storing Complex queries

  3. Hiding the data complexity occurred due to usage of multiple tables.

  4. Renaming Columns

• Views in SQL allow the user to select required information from multiple tables without using the join operation.

• Views in SQL provide additional Security by restricting access.

• Views hide the complexity which comes from usage of multiple tables.

• Views have the capability to store the large queries.

• Renaming columns mean without affecting the original table used to create a view, we can rename columns in view which can be done by specifying in the SELECT statement.

• We can create different views by using the same table for different reasons or for different users.

• Views in SQL use ZERO Space in the database, so that our database is free of space, it doesn't copy the data into the database.

• It can easily update, delete the rows from the view which is called a virtual table.

• Views help in improving performance and to partition data.