views in SQL with examples

In this article we will understand and learn about views in SQL with examples and also we see what are the advantages of views and where we use views in real time projects. In previous post we were discussed SQL subquery with examples go though this post also to know about how subquery is used in SQL. Now we will understand today's concept.

view is nothing but a saved SQL Query that is stored in the database. A view can also be considered as a virtual table. It is also contains rows and columns just like real table.

Why we need to create a view in their databases? this is the questions raising for every developers. The following the reasons to choose views in SQL.

1) The main advantage of view over stored procedure is that you can use a view just like you use a table.

2)Views can provide security,after creating a view,it limits columns or row access and grant permissions to users to see view.

3) Views can make common join queries easy as one query a table name instead of doing all the joins. That means views can hide complexity.

4) We can provide Row level security and column level security

5) Just like table we can ALTER and DROP view

Now we will understand this topic with examples. Here i am taking two tables 
1) Department table 
2) Employee table

Department table:

create table Dept
(
Dept_id int,
Dept_Name varchar2(20)
);

After creating Department table,insert the data into Department table.

Note: I have used Oracle 10g application to create tables and views

Result:

Employee table:

create table emp
(
Emp_Id int,
Emp_Name varchar2(20),
salary number,
Dept_Id int
);

After creating Employee table insert some data as you required.

Result:

Now, we will write a query which joins above two tables using joins concept. 

select Emp_Id,Emp_Name,salary,Dept_Name
from emp
join Dept
on emp.Dept_id=Dept.Dept_id;  

After joining above two tables the result will be:

  

Notice that in the above result emp_id,emp_name,salary columns from employee table and Dept_name column from Department table, so to do that i have used joins concept. It returns Emp_id,Emp_Name,salary and Dept_Name columns

Now we will see how to create a view, first we will write a syntax for view.

create view view_name
as
select column1,column2......column n from emp;

To create a view we use create view following view_name then as after you specify select query. That means this view returning employeewithDept table.

Create view EmployeewithDept
as
select Emp_Id,Emp_Name,salary,Dept_Name
from Emp
join Dept
on emp.Dept_id=Dept.Dept_id; 

Then the result will View created successfully.

Once view created successfully, then how can we see that view. the following query shows how to see view

select * from EmployeewithDept;

Result:

 







Note: Here view name treating as table. That is the reason views are also consider virtual table.

How to provide Row level and column level security using views:

Now we will check Row level  security using views. Suppose, from the above view there is a Banking department employees and i want provide them an access only Banking department employees ,in that case to achieve only Banking department employees then we need to write a query like below

Create view BankingEmployee
as
select Emp_Id,Emp_Name,salary,Dept_Name
from Emp
join Dept
on emp.Dept_id=Dept.Dept_id
where Dept.Dept_Name='Banking';

To see the view which is only accessible Banking department employees.

select * from  BankingEmployee;

Result:

 

 

Similarly to check column level security using views,suppose if you want hide salary column then write a query like below

Create view HideSalaryColumn
as
select Emp_Id,Emp_Name,Dept_Name
from Emp
join Dept
on emp.Dept_id=Dept.Dept_id;

select * from HideSalaryColumn;

Result:

 












 I hope you enjoy this topic and keep follow me for new updates and share this to your friends Via social website and give your comments on this concept.