Views

How To Use Views In SQL

Introduction:
In this article, you will learn what View is and how to use views in SQL.

SQL view is used to store in the database with his partner name. A view is the collection of one or more tables, which are based on SQL queries. When we use SQL view, it involves complete data of a table and limited rows.

It is a type of virtual table, which has features like:

• The view of the data is natural means the user can find easily.

• Rules of the data, which user can see and correct them, what they need.

• Divide the data from many tables, which can be used to generate the reports.

Creating Views:

Create view is a table, where the data is defined by other queries. The elements in a view are same as another real tables fields of the database. We can easily add some statement in the view of where and join statements as if the data is picked up from one table.

Syntax:

The basic syntax to create view is as follows:

create view_name as select column1, column2..... from table_name where [condition];

Example:

Here, we have Employee table to show the result:

Now, we create an Employee view from Employee table. We apply this view Name and Age from the employee.

Step 1: First, we write the query to create a view.

Create view employee_view as select Name, Age from employee;

Step2: Now, our employee_view table is ready. For showing the output, first, we run select query.

Select from employee_view;

Step 3: The output of the query, mentioned above, is:

The With Check Option:

SQL 'With Check View' statement is a create view statement option. If we are not sure that our insert and update condition will run properly or not in the view, we use this statement. Whenever we are not satisfied for the update or insert statements, it gives an error.

Example:

Here, we have an example to show the employee_view with the With Check Option:

create view employee_view as select Name, Age from employee where Age is not null with check option;

Updating a View:

When we use the update view, it does the work in the following conditions. If our condition is not satisfied, the following conditions do not work properly.

• Select clause can’t use the keyword Distinct, summary functions, set functions, set operators, order by clause.

• From clause can’t use the multiple tables.

• Where clause can’t use the subqueries, group by or having, calculated columns may not be updated.

• All not null columns arrange in the view as an insert function query.

Example:

Step 1: First, we write the query for update View.

Update employee_view set age =20 where Name='Arpit';

Now, select and execute the query.

Step 2: The output of the above query is:

Inserting Rows into a View:

We easily insert the data into the database tables but we can’t add the data in employee_view, because we do not include all the columns of the table in the view. On the other hand, we can add the rows in a view in same as insert them in a table.

Here, we can’t insert the rows in employee_view

Deleting Rows into a View:

We easily delete the data from employee_view. Delete the command and apply the same conditions as the update or insert command.

Step 1: First, we write the query to create a view.

delete from employee_view where age=20;

Step 2: The output of the query, mentioned above, is:

Dropping Views:

It is also possible to drop a view in our database. If we need to remove the view, we use to drop the view.

Syntax:

The basic syntax of Drop view is as follows:

drop view view_name;

Example:

Step 1: We write the query to Drop view.

dropiew employee_view;

Summary:
Thus, we learnt, view is used to store in the database and also learnt its use in SQL.