SQL View

About SQL View

In SQL, views are similar to virtual tables. Rows and columns in a view are the same as they are in a database table. A view can be created by choosing fields from one or more tables in the database. A View can contain all of a table's rows or only certain rows based on a condition.We can add SQL statements and functions to a view and present the data as if the data were coming from one single table.

Creating a View

CREATE VIEW statement can be used to create a View. A single table or numerous tables can be used to construct a View.

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

Let's understand this by taking one example,

CREATE VIEW StudentView AS
SELECT Name, Age
FROM Student

We may query the view in the same way that we query a table to see what data is in it.

SELECT *
FROM StudentView

Deleting View

We learnt how to create a View, but what if the View is no longer required? Obviously, we'll want to get rid of it. An existing View can be deleted using SQL. The DROP statement can be used to delete or drop a View.

DROP VIEW view_name

Let's understand this by taking one example,

DROP VIEW StudentView

The above query will delete the view named, StudentView.

Updating View

Certain requirements must be met in order to update a view.We will not be able to refresh the view if any of these conditions are not met and these requirements are :
🚀 GROUP BY and ORDER BY clauses should not be included in the SELECT statement used to generate the view.
🚀 The DISTINCT keyword should not be used in the SELECT statement.
🚀 All of the values in the View should be NOT NULL.
🚀 Nested or sophisticated queries should not be used to construct the view.
🚀 A single table should be used to generate the view.
🚀 We will not be able to update the view if it was constructed using several tables.

CREATE OR REPLACE VIEW

We can use the CREATE OR REPLACE VIEW statement to add or remove fields from a view.

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, column3 ....
FROM table_name
WHERE  condition

Let's understand this by taking one example,

CREATE OR REPLACE VIEW StudentView AS
SELECT Name, Age
FROM Student

We may query the view in the same way that we query a table to see what data is in it.

SELECT *
FROM StudentView

Inserting a row in a view

In a View, we can insert a row in the same way that we do in a table. To insert a row in a View, we can use the SQL INSERT INTO statement.

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

Let's understand this by taking one example,

INSERT INTO StudentView(Name, Age)
VALUES("Atul", 20);

We may query the view in the same way that we query a table to see what data is in it.

SELECT *
FROM StudentView

Deleting a row in a view

Delete rows from a view in the same way that you delete rows from a table. To delete rows from a view, we can use the SQL DELETE statement. Also, when deleting a row from a view, the change is reflected in the view after the row is deleted from the actual table.

DELETE FROM view_name
WHERE condition;

Let's understand this by taking one example,

DELETE FROM StudentView
WHEREName = Atul;

We may query the view in the same way that we query a table to see what data is in it.

SELECT *
FROM StudentView

Applications of View

Because of the following reasons, a good database should include views:

🚀 Access to data is restricted –
By restricting access to a predetermined group of rows and columns in a database, views give an extra layer of table security.

🚀 Hiding the complexity of data –
A view can be used to mask the complexities of a multiple table join.

🚀 Simplify user instructions –
Views allow users to choose data from several tables without having to know how to construct a join.

🚀 Store complex queries in views –
Views can be used to keep track of complex queries.

🚀 Rename Columns –
Views can also be used to rename columns without altering the underlying tables, as long as the number of columns in the view matches the number of columns supplied in the select statement. As a result, changing the columns of the base tables helps to obscure the names of the columns.

🚀 Multiple view capability –
Various views for different users can be established on the same table.