View in SQL: CREATE VIEW statement, advantages, disadvantages of views

View: A view is a virtual table in the database with a name. As the name suggests, it lets a user view a database from a view of his own. It lets users to see specific rows and columns based on their permissions. From the perspective of a user a view is a regular column in a database. However, view in reality is just a result of a query. Every view has source tables from which it gets its data.

CREATE VIEW statement: CREATE VIEW statement is used for creating a view. For creating a view you must have access to the source tables. While creating a view, each column can be named. Length, data type, and other features of each column are derived from the definition of the columns in the source tables.

For Example:


The above query creates a view to list employees whose salary is greater than average salary.

Advantages of views:

1) It makes a query simple: A view can take data from several tables and create a simple single table.

2) Simple for user to understand: A view makes a database simple for a user to understand.

3) Views make a database secure: Views are used for restricting access to important data in the database. Hence, they make a database more secure.

4) For data integrity constraints: DBMS can check the data for meeting integrity constraints if the data is accessed through views.

5) Still works if source tables are changed: A view still works even if the source tables are modified.

Disadvantages of views:

1) Updating rows of a view: It is a difficult task to update the rows of a view as the data is constructed from the source tables.

2) Decreases performance of database: Since the DBMS has to take data from many tables, views decrease the performance of a database.

Leave a Reply