Views in SQL and Create View in SQL

Views in SQL play an essential role in the context of a Database Management System. Today in this Tutorial, we will learn about Views’ concepts in SQL and create View in SQL.

We will learn about some important operations to be performed on Views like create View in SQL, insert, delete, drop on View with practical implementation in this Tutorial.

We will also see the performance tuning with SQL Server dynamic management views in this Tutorial.

Most of the time, questions on views in SQL are asked in a technical interview. So I would like to suggest every computer science student prepare this topic very well along with SQL Queries.

After reading this Tutorial, students will be able to execute various operations related to views in SQL.

Frequently Asked Questions

By the end of these views in the SQL server tutorial, students will be able to answer the following questions

• What are Views in SQL?
• What is the use of views in SQL?
• How To create View in SQL server?
• How To Create View from Multiple Tables?
• How To delete a View from Database?
• Can we update a view in SQL?
• How To delete data from View?
• How do I display a view in SQL?
• How To insert data into View?
• What is the use of View in SQL?

So let’s start with an introduction of View in SQL

What are Views in SQL?

• We can also create a virtual table using Structure Query Language. The View is nothing but a virtual table.
• It means View does not exist physically as a table exists in the Database. View SQL database contains the rows and columns as similar to the actual table.
• The View’s Attributes are derived from the other real table, which exists in the Database and maintains the DBMS Characteristics. This real table is known as Master Table.
• A view or Virtual Table may contain either all the rows of the master table or only some set of rows of the master table.
• In this view tutorial, we will learn about the View in more detail, such as creating the View, deleting the View, and updating the View.

To understand the concepts of SQL views, students should have sound knowledge of basic concepts of the Relational Database Management System.

What is use of Views in SQL?

What is the use of Views in SQL?

Views in SQL are fundamental. There is the following use of views in SQL.

• If we use the views in SQL, then we can protect our actual Database. Views provide security to our actual Database because views are created from a master table that is accurate and physically exists, so instead of using the real table, we can use views to perform database operations.
• Views help in maintaining the consistency and integrity of the Database.
• We can restrict the user to see the specific rows of data in a real Database.

How To Create View in SQL?

Syntax of creating the View in SQL is given below –

• Create View View_name As Select Colum1, Clolum2,….FROM Table_name Where Condition

Creating View From A single table

An example to create View using SQL is given below

• Create View Customers_View As Select name, Address From Customers Where Cust_id <5

The output of this query is shown in the following figure.

The output of this query is shown in the following figure.

In this example, we are creating a view named as Customers_View. This View contains the tuples from the Customers table. Only those tuples are taken from the customer’s table in which the value of the Cust_id attribute is less than 5.

• If we want to display the data of the Customers_View, then we will use the following command.

Select * from Customers_View

The output is as shown in the above figure.

How to Create View From Multiple Table?

Basic knowledge of ER Diagram is necessary because tables or relations are made from er diagram.

We can also create a view from multiple tables. A view can be created from multiple tables using the following SQL query. If we want to create a View using SQL from multiple tables, we have to use all the tables’ names in the select statement.

Example

Create View Student_View as select Student. name, Student.address, Stdmarks.marks from Student, Stdmarks where Student.name=Stdmarks.name;

• To display the data of View, we use the following command

Select * from Student_View;

The output is shown in the following figure.

How to Update a View ?

Like a table, we can also update a view. Some conditions needed to be satisfied to update a view. If any one of the following conditions is not satisfied, then SQL does not allow us to update the View. Let us see what these conditions are –

• A select statement that is used to create the View should not consist of the GROUP BY clause.
• Distinct keywords should not be used inside the select statement.
• All the attribute values should be NOT NULL in the View.
• A nested query should not be used to create a view.
• An important condition to update a view that View should not be created using multiple View. It should be created from a single table.

If we want to change the address of the customer whose name is Shyam, then we can update this using the following SQL Query

update Customers_View set address=’Lucknow’ where name =’ shyam’;

The output of this update operation is as shown in the following image.

How to Alter the view ?

• If we want to replace the View or we want to add or remove some more fields to the View, then we can do this by using the following syntax in SQL.

Create or Replace View View_name As select attribute1, attribute2 from table_name Where condition ;

For example, if we want to add the age field to the created View, then we can do this using the following SQL query.

Create view Student_View as select Student.name , Student.address, Student.age, Stdmarks.marks from Student, Stdmarks where Student.name=Stdmarks.name;

We use the following command to display the data of the View.

Select * from View_name;

How to Insert Rows in View?

We can not include the data into a View until and unless we have included all not null column in the View. As shown in the following image, when we tried to insert a row into Customer_View, It displays an error that the underlying table does not have a default value.

So conditions for Updating a View are also applicable on insert operation.

After including all not null, we can insert the rows in a table in the same way we can also insert the rows in the table.

How to Delete Data from View?

As we delete rows in the table in the same way, we delete data from a table. We can delete either all data from the View or. To delete the rows from View, we simply use the delete statement.

Syntax

delete from View_name where condition;

Example 

Delete from Customers_View Where id=3;

The output of this query is as shown in the following image.

How To Delete or drop a view in SQL ?

• When we don’t need the View anymore after its use, then we simply delete the View. In SQL, we use the following command to drop a view.

Syntax

DROP View View_Name;

Example

Drop View Student_View;

The output is shown in the following image

This command deletes the View named as Student_View.

What is the Use of Views in SQL?

There are the following advantages of views in SQL, or we use View due to following reasons

Restricting Database

A view provides security to the original table in the Database. It restricts access to the original tale

Simplify the Commands

Generally, to select the data from multiple users should have the knowledge of JOIN SQL queries but View allow the uses to select the data from multiple tables without knowledge of join.

Store Complex Queries

Views in SQL can also be used to store complex queries.

Rename Operation

We can also use the View to rename the column without affecting the original table.

Hide Complexity of Join

View hides the complexity of join operations. Different joint operations have been explained in the Relational Algebra Tutorial.

Note: Student Preparing for GATE(CS/IT) and UGC NET Exam can also Practice these  SQL Queries Based GATE Questions

Conclusion and Summary

In this Tutorial, we have explained important concepts of View in SQL. We have explained each query with Practical Implementation, and it is the result. We have discussed the advantages of SQL views, types of views in SQL, and use of views in SQL.

I hope that this Tutorial will be helpful to the students in understanding the concepts of View, and they will be able to implement the view operations in SQL.

Previous Tutorial – Normalization in DBMS

Next Tutorial – Transaction Processing Concepts