DBMS SQL View

What is a View in DBMS?

Writing Complex queries and Securing Database access is very challenging for any Database Developer and Database Administrator. Sometimes SQL queries get very complicated by joins, Group By clauses, and other referential dependencies, So those Types of queries can be simplified to proxy data or virtual data which simplifies the queries.

Also, Security challenges can also be overcome by giving restricted access to only the data user needs. Let's take an example to understand this better. Suppose, the user needs only 2 columns of data, so instead of giving him access to the whole table in the database, the Database Administrator can easily create a virtual table of 2 columns that the user needs using the views. This will not give full access to the table and the user is only seeing the projection of only 2 columns and it keeps the database secure.

Views act as a proxy or virtual table created from the original table. Views simplify SQL queries and allow secure access to underlying tables. Views in DBMS can be visualized as virtual tables that are formed by original tables from the database.

As you can see from the above image, we can extract data columns from more than one table by running queries on the data tables. Views contain only the definition of the view data in the data dictionary, not the actual data.

The view has two primary purposes:

• Simplifying complex SQL queries.
• Restricting users from accessing sensitive data.

Sample Table

For creating a view (simple and complex views), and updating and deleting the views we will take some sample data and tables that store the data.

Let's take an employee table that stores data of employees in a particular company:

Employee Table:

This table contains details of employees in a particular company and has data fields such as EmpID, EmpName, Address, Contact. We have added 6 records of employees for our purpose.

EmpRole Table:

This table contains details of employees' roles in a particular company and has data fields as EmpID, Role, Dept. We have stored all the records particular to the EmpID of each employee.

Creating View in DBMS

The view can be created by using the CREATE VIEW statement, Views can be simple or complex depending on their usage.

Syntax:

Here, viewName is the Name for the View we set, tableName is the Name of the table and condition is the Condition by which we select rows.

Creating a Simple View

Simple view is the view that is made from a single table, It takes only one table and just the conditions, It also does not take any inbuilt SQL functions like AVG(), MIN(), MAX() etc, or GROUP BY clause.

While creating a simple view, we are not creating an actual table, we are just projecting the data from the original table to create a view table.

Let's look at some examples for creating a simple view.

Example 1: In this example, we are creating a view table from Employee Table for getting the EmpID and EmpName. So the query will be:

Now to see the data in the EmpView1 view created by us, We have to simply use the SELECT statement.

Output:

The view table EmpView1 that we have created from Employee Table contains EmpID and EmpName as its data fields.

Example 2: In this example, we are creating a view table from EmpRole Table for getting the EmpID and Dept for employees having EmpIDs less than 4. So the query will be:

Now to see the data in the EmpView2 view created by us, We have to simply use the SELECT statement.

Output:

The view table EmpView2 that we have created from EmpRole Table contains EmpID and Dept as its data fields.

Creating a Complex View

The complex view is the view that is made from multiple tables, It takes multiple tables in which data is accessed using joins or cross products, It contains inbuilt SQL functions like AVG(), MIN(), MAX() etc, or GROUP BY clause. So, whenever we need to access data from multiple tables we can make use of Complex Views.

Let's look at some examples for creating the complex view.

Example:

In this example, we are creating a view table using Employee Table and EmpDept table for getting the EmpName from the Employee table and Dept from EmpDept. So the query will be:

Now to see the data in the EmpView1 view created by us, We have to simply use the SELECT statement.

Output:

The view table CompView that we have created from Employee Table and EmpRole Table contains EmpName and Dept as its data fields. We have used the cross-join to get our necessary data.

Deleting View in DBMS

Now we know how to create simple and complex views but what if we don't need our created views anymore, So we need to delete the view so as we DROP a table in SQL, similarly, we can delete or drop a view using the DROP statement.

The DROP statement completely deletes the structure of the view.

Syntax:

Here ViewName is the name of the view to be deleted.

Example:

Let's delete one of our created views, say EmpView1.

Let's use the SELECT statement on Deleted View.

Output:

Now SQL Editor will give us an error saying the table does not exist as the table is now been deleted.

Updating View in DBMS

Suppose we want to add more columns to the created view so we will have to update the view.

For updating the views we can use CREATE OR REPLACE VIEW statement, new columns will replace or get added to the view.

Syntax:

For Updating a view CREATE OR REPLACE statement is used. Here, viewName is the Name of the view we want to update, tableName is the Name of the table and condition is the Condition by which we select rows.

Example 1:

let's take the above created simple view EmpView1 and we want to one more column of address to our EmpView1 from Employee Table.

Now to see the data in the EmpView1 view created by us, We have to simply use the SELECT statement.

Output:

The data fields of view table EmpView1 have been updated to EmpID, EmpName, and Address.

Example 2:

Let's take the above created simple view EmpView2 and we want to replace the Dept column with the Role column.

Now to see the data in the EmpView2 view created by us, We have to simply use the SELECT statement.

Output:

The fields of view table EmpView2 have been updated to EmpID and Role.

Manipulating Data in a View

Views can also be used to manipulate data in the scope of the view table only, manipulating data in a view does not affect the data of the original table. Updateable Views are views that allow for data manipulation but there are certain conditions needed to be taken care of while manipulating the data of the view:

• The 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 NOT NULL values should be present in the View.
• Nested or complex queries should not be used to construct the view.
• A single table should be used to generate the view. We would not be able to update the view if it was constructed using several tables.

Manipulating data in a view includes inserting or deleting a row from the view table

Inserting a Row in a View

Inserting a row in the view takes the same syntax as we use to insert a row in a simple table

Syntax:

Here, viewName is the view in which we have to insert data and we add values according to the columns in the view table.

Example: let's take the above created simple view EmpView2 and we want to insert a new row.

Now to see the data in the EmpView2, We have to simply use the SELECT statement.

Output:

Now the updated table EmpView2 has one more row of EmpID 4 and Role Intern.

Deleting a Row in a View

Deleting a row in the view takes the same syntax as we use to delete a row in a simple table.

Syntax:

Here, viewName is the view from which data has been deleted, condition is the Condition by which we select rows to be deleted.

Example: let's take the above created simple view EmpView2 and we want to delete the row having EmpID = 1.

Now to see the data in the EmpView2, We have to simply use the SELECT statement.

Output:

Now 1 record with data EmpID 1 and Role Intern has been deleted.

Read More:

• Join in DBMS.