SQL provides the concept of VIEW, which hides the complexity of the data and restricts unnecessary access to the database. It permits the users to access only a particular column rather than the whole data of the table.
The View in the Structured Query Language is considered as the virtual table, which depends on the result-set of the predefined SQL statement.
Like the SQL tables, Views also store data in rows and columns, but the rows do not have any physical existence in the database.
Any database administrator and user can easily create the View by selecting the columns from one or more database tables. They can also delete and update the views according to their needs.
A view can store either all the records of the table or a particular record from the table using the WHERE clause.
Create a SQL View
You can easily create a View in Structured Query Language by using the CREATE VIEW statement. You can create the View from a single table or multiple tables.
Syntax to Create View from Single Table
In the syntax, View_Name is the name of View you want to create in SQL. The SELECT command specifies the rows and columns of the table, and the WHERE clause is optional, which is used to select the particular record from the table.
Syntax to Create View from Multiple Tables
You can create a View from multiple tables by including the tables in the SELECT statement.
Example to Create a View from Single table
Let's consider the Student_Details table, which consists of Stu_ID, Stu_Name, Stu_Subject, and Stu_Marks columns. The data of the Student_Details is shown in the following table:
Suppose, you want to create a view with Stu_ID, Stu_Subject, and Stu_Marks of those students whose marks are greater than 85. For this issue, you have to type the following query:
Example to Create a View from Multiple tables
Let's consider two tables, Student_Details and Teacher_Details. The Student_Details table consists of Stu_ID, Stu_Name, Stu_Subject, and Stu_Marks columns. And, the Teacher_Details table consists of Teacher_ID, Teacher_Name, Teacher_Subject, Teacher_City columns. The data of the Student_Details and Teacher_Details is shown in the following two tables:
Suppose, you want to create a view with Stu_ID, Stu_Name, Teacher_ID, and Teacher_Subject columns from the Student_Details and Teacher_Details tables.
To display the data of Student_Teacher_View, you have to type the following SELECT query:
Update an SQL View
We can also modify existing data and insert the new record into the view in the Structured Query Language. A view in SQL can only be modified if the view follows the following conditions:
Syntax to Update a View
Example to Update a View
If we want to update the above Student_View and add the Stu_Name attribute from the Student table in the view, you have to type the following Replace query in SQL:
The above statement updates the existing Student_View and updates the data based on the SELECT statement.
Now, you can see the virtual table by typing the following query:
Insert the new row into the existing view
Just like the insertion process of database tables, we can also insert the record in the views. The following SQL INSERT statement is used to insert the new row or record in the view:
Example to Insert new record in the view
Suppose, you want to insert the record of a new student in the Student_View, then you have to write the following query in SQL:
Now, you can check that the new record is inserted in the Student_View or not by using the following query:
Delete the existing row from the view
Just like the deletion process of database tables, we can also delete the record from the views. The following SQL DELETE statement is used to delete the existing row or record from the view:
Example to Delete the record from the View
Suppose, you want to delete the record of those students from the Student_View whose Subject is Math, then you have to type the following SQL query:
Now, you can check that the record is removed from the Student_View or not by using the following query:
Drop a View
We can also delete the existing view from the database if it is no longer needed. The following SQL DROP statement is used to delete the view:
Example to Drop a View
Suppose, you want to delete the above Student_View, then you have to type the following query in the Structured Query Language: