SQL Views:




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:

 

SYNTAX for creating a view

CREATE or REPLACE view view-name AS

SELECT column-name

FROM table-name

WHERE condition

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.

 

EXAMPLE of creating a view

Suppose you have the following table named student:

STUD_ID NAME DEPT MONFEE SUBJECT_ID Subject Marks
100 Tim Marketing 1000 204 English 56
100 Faddy Marketing 1000 204 English 56
140 Jones Accounting 1200 204 English 56
110 John Info. System 1100 204 English 56
110 James Info. System 1100 204 English 56
100 Tim Marketing 1000 230 Math 78
100 Faddy Marketing 1000 230 Math 78
140 Jones Accounting 1200 230 Math 78
110 John Info. System 1100 230 Math 78
110 James Info. System 1100 230 Math 78
100 Tim Marketing 1000 144 Computer 89
100 Faddy Marketing 1000 144 Computer 89
140 Jones Accounting 1200 144 Computer 89
110 John Info. System 1100 144 Computer 89
150 Stuart Marketing 1000 144 Computer 89
100 Tim Marketing 1000 230 Math 84
100 Faddy Marketing 1000 230 Math 84
140 Jones Accounting 1200 230 Math 84
110 John Info. System 1100 230 Math 84
110 James Info. System 1100 230 Math 84
100 Tim Marketing 1000 204 English 68
100 Faddy Marketing 1000 204 English 68
140 Jones Accounting 1200 204 English 68
110 John Info. System 1100 204 English 68
110 James Info. System 1100 204 English 68

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.




EXAMPLE of displaying a view

The SQL view can be viewed using the select statement. The syntax for this is as follows:

Select * from Student-view;

 

Force view creation

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:

SYNTAX

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.

 

Update a view

A view can be updated just like the tables are updated. The syntax to update a view is given below:

SYNTAX

Update view-name

Set value

Where condition;

When the view is updated the table will also update.

 

Read-only view

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

Select column-name

From table-name

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.

 

Dropping a view

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.

 

Types of views

In the SQL there are two types of views:

  1. Simple view
  2. Complex view

Simple 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.




Complex view

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.