> For the complete documentation index, see [llms.txt](https://adkdians.gitbook.io/database-management-system/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://adkdians.gitbook.io/database-management-system/1.-introduction-to-databases-and-transactions/sql-views.md).
# SQL Views
**Views in SQL** are a kind of virtual table. A view also has rows and columns like tables, but a view doesn’t store data on the disk like a table. View defines a customized query that retrieves data from one or more tables, and represents the data as if it was coming from a single source.
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 conditions.
In this article, we will learn about creating, updating, and deleting views in SQL.
### **D**emo SQL Database
We will be using these **two SQL tables** for examples.
**StudentDetails**
**StudentMarks**
You can create these tables on your system by writing the following SQL query:
MySQL-- Create StudentDetails tableCREATE TABLE StudentDetails ( S\_ID INT PRIMARY KEY, NAME VARCHAR(255), ADDRESS VARCHAR(255));INSERT INTO StudentDetails (S\_ID, NAME, ADDRESS)VALUES (1, 'Harsh', 'Kolkata'), (2, 'Ashish', 'Durgapur'), (3, 'Pratik', 'Delhi'), (4, 'Dhanraj', 'Bihar'), (5, 'Ram', 'Rajasthan');-- Create StudentMarks tableCREATE TABLE StudentMarks ( ID INT PRIMARY KEY, NAME VARCHAR(255), Marks INT, Age INT);INSERT INTO StudentMarks (ID, NAME, Marks, Age)VALUES (1, 'Harsh', 90, 19), (2, 'Suresh', 50, 20), (3, 'Pratik', 80, 19), (4, 'Dhanraj', 95, 21), (5, 'Ram', 85, 18);
### **CREATE VIEWS in SQL**
We can create a view using **CREATE VIEW** statement. A View can be created from a single table or multiple tables.
#### **Syntax**
CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE condition;
**Parameters:**
* **view\_name**: Name for the View
* **table\_name**: Name of the table
* **condition**: Condition to select rows
### **SQL CREATE VIEW Statement Examples**
Let’s look at some examples of CREATE VIEW Statement in SQL to get a better understanding of how to create views in SQL.
#### **Example 1: Creating View from a single table**
In this example, we will create a View named DetailsView from the table StudentDetails. Query:
CREATE VIEW DetailsView AS
SELECT NAME, ADDRESS
FROM StudentDetails
WHERE S_ID < 5;
To see the data in the View, we can query the view in the same manner as we query a table.
SELECT * FROM DetailsView;
**Output:**
#### **Example 2: Create View From Table**
In this example, we will create a view named StudentNames from the table StudentDetails. Query:
CREATE VIEW StudentNames AS
SELECT S_ID, NAME
FROM StudentDetails
ORDER BY NAME;
If we now query the view as,
SELECT * FROM StudentNames;
**Output:**
#### **Example 3: Creating View from multiple tables**
In this example we will create a View named MarksView from two tables StudentDetails and StudentMarks. To create a View from multiple tables we can simply include multiple tables in the SELECT statement. Query:
CREATE VIEW MarksView AS
SELECT StudentDetails.NAME, StudentDetails.ADDRESS, StudentMarks.MARKS
FROM StudentDetails, StudentMarks
WHERE StudentDetails.NAME = StudentMarks.NAME;
To display data of View MarksView:
SELECT * FROM MarksView;
**Output:**
### **LISTING ALL VIEWS IN A DATABASE**
We can list View using the **SHOW FULL TABLES** statement or using the **information\_schema table**. A View can be created from a single table or multiple tables.
#### **Syntax**
USE "database_name";
SHOW FULL TABLES WHERE table_type LIKE "%VIEW";
**Using information\_schema**
SELECT table_name
FROM information_schema.views
WHERE table_schema = 'database_name';
OR
SELECT table_schema, table_name, view_definition
FROM information_schema.views
WHERE table_schema = 'database_name';
### **DELETE VIEWS in SQL**
SQL allows us to delete an existing View. We can delete or drop View using the **DROP statement**.
#### **Syntax**
DROP VIEW view_name;
#### Example
In this example, we are deleting the View **MarksView.**
DROP VIEW MarksView;
### **UPDATE VIEW in SQL**
If you want to update the existing data within the view, use the [**UPDATE** ](https://www.geeksforgeeks.org/sql-update-statement)statement.
#### Syntax
UPDATE view_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
**Note:** Not all views can be updated using the UPDATE statement.
If you want to update the view definition without affecting the data, use the **CREATE OR REPLACE VIEW** statement. you can use this syntax
CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
#### Rules to Update Views in SQL:
Certain conditions need to be satisfied to update a view. If any of these conditions are **not** met, the view can not be updated.
1. The SELECT statement which is used to create the view should not include GROUP BY clause or ORDER BY clause.
2. The SELECT statement should not have the DISTINCT keyword.
3. The View should have all NOT NULL values.
4. The view should not be created using nested queries or complex queries.
5. The view should be created from a single table. If the view is created using multiple tables then we will not be allowed to update the view.
#### Examples
Let’s look at different use cases for updating a view in SQL. We will cover these use cases with examples to get a better understanding.
#### Example 1: Update View to Add or Replace a View Field
We can use the **CREATE OR REPLACE VIEW** statement to add or replace fields from a view.
If we want to update the view **MarksView** and add the field AGE to this View from **StudentMarks** Table, we can do this by:
CREATE OR REPLACE VIEW MarksView AS
SELECT StudentDetails.NAME, StudentDetails.ADDRESS, StudentMarks.MARKS, StudentMarks.AGE
FROM StudentDetails, StudentMarks
WHERE StudentDetails.NAME = StudentMarks.NAME;
If we fetch all the data from MarksView now as:
SELECT * FROM MarksView;
**Output:**
#### **Example 2: Update View to Insert a row in a view**
We can insert a row in a View in the same way as we do in a table. We can use the [INSERT INTO](https://www.geeksforgeeks.org/sql-insert-statement) statement of SQL to insert a row in a View.
In the below example, we will insert a new row in the View DetailsView which we have created above in the example of “creating views from a single table”.
INSERT INTO DetailsView(NAME, ADDRESS)
VALUES("Suresh","Gurgaon");
If we fetch all the data from DetailsView now as,
SELECT * FROM DetailsView;
**Output:**
#### **Example 3: Deleting a row from a View**
Deleting rows from a view is also as simple as deleting rows from a table. We can use the DELETE statement of SQL to delete rows from a view. Also deleting a row from a view first deletes the row from the actual table and the change is then reflected in the view.
In this example, we will delete the last row from the view DetailsView which we just added in the above example of inserting rows.
DELETE FROM DetailsView
WHERE NAME="Suresh";
If we fetch all the data from DetailsView now as,
SELECT * FROM DetailsView;
**Output:**
#### **WITH CHECK OPTION Clause**
The **WITH CHECK OPTION** clause in SQL is a very useful clause for views. It applies to an updatable view.
The WITH CHECK OPTION clause is used to prevent data modification (using INSERT or UPDATE) if the condition in the WHERE clause in the 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.
**WITH CHECK OPTION Clause Example:**
In the below example, we are creating a View SampleView from the StudentDetails Table with a WITH CHECK OPTION clause.
CREATE VIEW SampleView AS
SELECT S_ID, NAME
FROM StudentDetails
WHERE NAME IS NOT NULL
WITH CHECK OPTION;
In this view, if we now try to insert a new row with a null value in the NAME column then it will give an error because the view is created with the condition for the NAME column as NOT NULL. For example, though the View is updatable then also the below query for this View is not valid:
INSERT INTO SampleView(S_ID)
VALUES(6);
> **NOTE**: The default value of NAME column is *null*.
### **Uses of a View**
A good database should contain views for the given reasons:
1. **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.
2. **Hiding data complexity –** A view can hide the complexity that exists in multiple joined tables.
3. **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.
4. **Store complex queries –** Views can be used to store complex queries.
5. **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 a select statement. Thus, renaming helps to hide the names of the columns of the base tables.
6. **Multiple view facility –** Different views can be created on the same table for different users.
### Key Takeaways About SQL Views
> * Views in SQL are a kind of virtual table.
> * The fields in a view can be from one or multiple tables.
> * We can create a view using the CREATE VIEW statement and delete a view using the DROP VIEW statement.
> * We can update a view using the CREATE OR REPLACE VIEW statement.
> * WITH CHECK OPTION clause is used to prevent inserting new rows that do not satisfy the view’s filtering condition.