SQL | What are Views
Views in SQL are kind of virtual tables.
A view also has rows and columns as they are in a real table in the database.
We can create a view by selecting fields from one or more tables present in the database.
A View can either have all the rows of a table or specific rows based on certain condition.
Create a View
Get Data from View
Creating View from multiple tables:
To create a View from multiple tables we can simply include multiple tables in the SELECT statement. Query:
List down all views a Database
Inserting a row in a view:
We can insert a row in a View in a same way as we do in a table. We can use the INSERT INTO statement of SQL to insert a row in a View.
Deleting a row from a View:
We can Delete a row from a view as we can do in Table. We can use the DELETE statement of SQL to delete rows from a view.
Also deleting a row from a view first delete the row from the actual table and the change is then reflected in the view.
WITH CHECK OPTION clause
The WITH CHECK OPTION clause in SQL is a very useful clause for views. It is applicable to an updatable view. If the view is not updatable, then there is no meaning of including this clause in the CREATE VIEW statement.
- The WITH CHECK OPTION clause is used to prevent the insertion of rows in the view where the condition in the WHERE clause in CREATE VIEW statement is not satisfied.
- If we have used the WITH CHECK OPTION clause in the CREATE VIEW statement, and if the UPDATE or INSERT clause does not satisfy the conditions then they will return an error.
In this View if we now try to insert a new row with null value in the NAME column then it will give an error because the view is created with the condition for NAME column as NOT NULL.
Uses of a View:
Restricting data access – Views provide an additional level of table security by restricting access to a predetermined set of rows and columns of a table.
Hiding data complexity – A view can hide the complexity that exists in multiple tables join.
Simplify commands for the user – Views allow the user to select information from multiple tables without requiring the users to actually know how to perform a join.
Store complex queries – Views can be used to store complex queries.
Rename Columns – Views can also be used to rename the columns without affecting the base tables provided the number of columns in view must match the number of columns specified in select statement. Thus, renaming helps to hide the names of the columns of the base tables.
Multiple view facility – Different views can be created on the same table for different users.
