Javatpoint Logo
Javatpoint Logo

Views in SQL

  • Views in SQL are considered as a virtual table. A view also contains rows and columns.
  • To create the view, we can select the fields from one or more tables present in the database.
  • A view can either have specific rows based on certain condition or all the rows of a table.

Advantages of View:

  1. Complexity: Views help to reduce the complexity. Different views can be created on the same base table for different users.
  2. Security: It increases the security by excluding the sensitive information from the view.
  3. Query Simplicity: It helps to simplify commands from the user. A view can draw data from several different tables and present it as a single table.
  4. Consistency: A view can present a consistent, unchanged image of the structure of the database. Views can be used to rename the columns without affecting the base table.
  5. Data Integrity: If data is accessed and entered through a view, the DBMS can automatically check the data to ensure that it meets the specified integrity constraints.
  6. Storage Capacity: Views take very little space to store the data.
  7. Logical Data Independence: View can make the application and database tables to a certain extent independent.

Disadvantages of View:

The DML statements which can be performed on a view created using single base table have certain restrictions are:

  1. You cannot INSERT if the base table has any not null column that do not appear in view.
  2. You cannot INSERT or UPDATE if any of the column referenced in the INSERT or UPDATE contains group functions or columns defined by expression.
  3. You can't execute INSERT, UPDATE, DELETE statements on a view if with read only option is enabled.
  4. You can't be created view on temporary tables.
  5. You cannot INSERT, UPDATE, DELETE if the view contains group functions GROUP BY, DISTINCT or a reference to a psuedocolumn rownum.
  6. You can't pass parameters to the SQL server views.
  7. You can't associate rules and defaults with views.

Sample table:


1 Stephan Delhi
2 Kathrin Noida
3 David Ghaziabad
4 Alina Gurugram


1 Stephan 97 19
2 Kathrin 86 21
3 David 74 18
4 Alina 90 20
5 John 96 18

1. Creating view

A view can be created using the CREATE VIEW statement. We can create a view from a single table or multiple tables.


2. Creating View from a single table

In this example, we create a View named DetailsView from the table Student_Detail.


Just like table query, we can query the view to view the data.


Stephan Delhi
Kathrin Noida
David Ghaziabad

3. Creating View from multiple tables

View from multiple tables can be created by simply include multiple tables in the SELECT statement.

In the given example, a view is created named MarksView from two tables Student_Detail and Student_Marks.


To display data of View MarksView:

Stephan Delhi 97
Kathrin Noida 86
David Ghaziabad 74
Alina Gurugram 90

4. Deleting View

A view can be deleted using the Drop View statement.



If we want to delete the View MarksView, we can do this as:

Significance of Views:

Views are highly significant, as they can provide advantages over tasks. Views can represent a subset of data contained in a table. Consequently they can limit the degree of exposure of the underlying base table to the outer world. They are used for security purpose in database and act as an intermediate between real table schemas and programmability. They act as aggregate tables.

Types of Views:

There are two types of views.

Views in SQL
  1. Join View: A join view is a view that has more than one table or view in its from clause and it does not use any Group by Clause, Rownum, Distinct and set operation.
  2. Inline View: An inline view is a view which is created by replacing a subquery in the from clause which defines the data source that can be referenced in the main query. The sub query must be given an alias for efficient working.

Next TopicDBMS SQL Index

Youtube For Videos Join Our Youtube Channel: Join Now


Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Trending Technologies

B.Tech / MCA