Sql Server

SQL | What are Views

SQL | What are Views

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

CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE condition;

Get Data from View

SELECT * FROM view_name ;

Creating View from multiple tables:
To create a View from multiple tables we can simply include multiple tables in the SELECT statement. Query:

CREATE VIEW UserDesignationView AS
SELECT User.NAME, User.ADDRESS, UserDesignations.Designation
FROM User, UserDesignations
WHERE User.Id = UserDesignations.UserId;

List down all views a Database

from information_schema.views
where table_schema = "database_name";

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.

INSERT INTO view_name(column1, column2 , column3,..)
VALUES(value1, value2, value3..);

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.

DELETE FROM view_name
WHERE NAME="Deepak";

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.

FROM  StudentDetails

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.

Related Post

About Us

Community of IT Professionals

A Complete IT knowledgebase for any kind of Software Language, Development, Programming, Coding, Designing, Networking, Hardware and Digital Marketing.