The SQL views are used for querying the data. There is no need of data storage in views unlike the tables. The SQL views also do not need any space in the disk as they do not contain any data.
The SQL views are created by assigning a name to a select statement. Then you store this query for later use for others. And then these can be used for querying the data directly. The views are also used to restrict the use of the data or data access is restricted. The syntax for creating a view in SQL is given below:
CREATE or REPLACE view view-name AS
In the above syntax, a view is created as the column-name that is assigned to the select statement and from the table “table-name” when meeting a specified condition.
Suppose you have the following table named student:
As you can see that the table we used is a large table. Now we will use SQL views to select some data and store in some other object. To do this use the following query:
Create or replace view Student-view as select * from Student where marks >= 60;
This query will extract all the data of those students that have marks greater than and equal to 60 and will store them in another object (view) that is named student-view. In this way we can easily access our data. The views are created to handle complexity of the tables.
The SQL view can be viewed using the select statement. The syntax for this is as follows:
Select * from Student-view;
As the name tells that a force view is created forcefully that is when a table does not exist and we make a view then this will be the force view. The force keyword is used to create force view. When the table is created and the user enters records in it then the force view will be automatically updated. The syntax of force view is given below:
CREATE or REPLACE force view view-name ASSELECT column-nameFROM table-nameWHERE condition It can be seen in the above syntax that a keyword force is used to create the force view.
A view can be updated just like the tables are updated. The syntax to update a view is given below:
When the view is updated the table will also update.
A view can also be created with the read only option. The view is created in the read only option so that to restrict the access of the view. The user will not be able to do editing or cannot update the view when it is in read only mode. The syntax of the read only view is as follows:
Create or replace force view view-name as
Where condition with read only;
The above syntax is the same as a simple creation of a view the only difference is that we used a keyword read only at the end of the syntax to make the view read only so that the user cannot be able to edit, update or modify the view. When a user try to insert any data or try to update, edit the ready only view then an error will be thrown.
By dropping a view we mean to delete or remove a view definition from the database. The syntax to delete or drop a view from the database is as follows:
Drop view <view name>
When a view is deleted or dropped the data in the table is not affected. You can also create a new view with the same name if any of the view is dropped or deleted. Suppose you have a created a view named Student-view and now you want to drop then use the following statement to do this:
DROP view Student-view;
This query will delete the view student-view without affecting the data in the base table. Now a new view can also be created with this name.
In the SQL there are two types of views:
- Simple view
- Complex view
The simple view is created from only one single table and does not have any functions. The DML that is data manipulation language can be performed through the simple view. A simple view also does not have any groups of data.
The complex view is created from than one table and also has functions. The DML that is data manipulation language cannot be performed through the complex view. A complex view contains groups of data.