Views in SQL

In this tutorial you will learn everything about views in SQL from creating a view to deleting and updating a view.

A view in SQL can be considered as a virtual table. View also has rows and columns similar to a table. For creating a view, we can select fields from either a single table or from more than one tables present in a database. A view can either contain specific rows of a table based on requirement or all the rows of a table.

Let’s consider two sample tables for creating, updating and deleting the views in SQL.

Employee details table
Employee salary table

Creating Views in SQL

 CREATE VIEW statement is used to create a view in SQL. A view can be created from a single table or from multiple tables in the database.

Syntax:

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

Creating a view from a single table

Let’s create a view named emp_details using the table employee details.

Query:

 CREATE VIEW emp_details AS  
SELECT NAME, ADDRESS 
FROM Employee_Details 
WHERE ID < 4;

Just like tables we can use SELECT statement to display data of a view.

SELECT * FROM emp_details;
Creating a view from a single table output

Creating a view from multiple tables

We can create a view from multiple tables by simply including multiple tables in the SELECT statement.

Query:

CREATE VIEW emp_salary AS   
SELECT Employee_Details.NAME,Employee_Details.ADDRESS,Employee_Salary.SALARY 
FROM Employee_Details, Emloyee_Salary 
WHERE Employee_Details.NAME = Employee_Salary.NAME;

Displaying the data from emp_salary view.

SELECT * FROM emp_salary;
Creating a view from multiple tables example

FORCE VIEW Creation

FORCE keyword is used to create a view forcefully. FORCE VIEW is used to create a View even if the base table does not exist in the database. If we create the FORCE VIEW first and then create the base table and enter values in it, the view will be updated automatically.

Syntax

 CREATE FORCE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;   

Deleting Views in SQL

After creating a view if you want to delete that view then DROP statement comes into use. We can use DROP statement to delete or drop a view.

Syntax

DROP VIEW
view_name;

For example, if I want to delete the view emp_salary then query will be.

DROP VIEW
emp_salary;

Updating Views in SQL

UPDATE command is used to update a view. It’s syntax is similar to syntax of UPDATE command in tables.

Syntax

 UPDATE view-name SET VALUE
WHERE condition;

NOTE: If a value is updated in view then it is automatically updated in the base tables automatically.

Inserting a row into Views

Inserting a row into a view is also similar to inserting rows into tables. We can insert  a row into a view by using INSERT INTO statement.

Syntax

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

Example

 INSERT INTO emp_details(NAME, ADDRESS)
VALUES("Suresh","Gurgaon");

Now fetch all the data from the view.

 SELECT * FROM emp_details;  
Inserting a row into Views

Deleting a row from Views

Deleting a row from a view is also similar to deleting rows from tables. We can delete  a row from a view by using DELETE FROM statement.

Syntax

 DELETE FROM view_name
WHERE condition;

Example

 DELETE FROM emp_details
 WHERE NAME="Suresh";
 

Now fetch all the data from the view.

SELECT * FROM
emp_details;
Deleting a row from Views

Read-Only VIEW

We can even create a view with read-only option that restricts access to the view.

syntax

CREATE or REPLACE FORCE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition WITH read-only;

This syntax will create a view for read-only purpose. This cannot be updated or data cannot be inserted or deleted.

Types of View

There are two types of views in SQL.

  • Simple View
  • Complex View
Simple ViewComplex View
Created from a single tableCreated from one or more than one tables
It doesn’t contain functionsIt Contains functions
It doesn’t contain groups of dataIt contains groups of data

Also Read: --> File system and DBMS difference  --> Data Models DBMS --> Integrity Constraints In DBMS

FAQ?

What is a view in DBMS?

A view in SQL can be considered as a virtual table. View also has rows and columns similar to a table.

What are different types of views?

There are 2 types of Views in SQL. One is Simple View and other is Complex View.

What is a simple view?

view that is based on only a single table and it doesn’t contain GROUP BY clause and any functions.

Leave a comment