Understanding Views in SQL: A Complete Guide

What is a View?

View can be described as virtual table which derived its data from one or more than one table columns. It is stored in the database. View can be created using tables of same database or different database. It is used to implement the security mechanism in the SQL.

For example:

Create table Emp_Details(
    EmpId int,
    EmpName nvarchar(200),
    EmpLogin nvarchar(20),
    EmpPassword nvarchar(20) ,
    EmploymentDate datetime 
)

And for example, table has the following data of employees:

EmpId	EmpName	EmpLogin	Emppassword	EmploymentDate
1	EmployeeA	EmpA	EmpAPwd	29/01/2006
2	EmployeeB	EmpB	EmpBPwd	06/02/2007
3	EmployeeC	EmpC	EmpCPwd	14/05/2007
4	EmployeeD	EmpD	Empd	30/03/2008
5	EmployeeE	EmpE	EmpEPwd	30/06/2007
6	EmployeeF	EmpF	EmpFPwd	12/09/2012

Now suppose that the Administrator do not want that the users to access the whole data of Emp_Details table which contains some critical information (Emplogin, EmpPassword, etc.) of the Employees. So he can create a view which gives the empid, empname, employmentdate as the output and gives permission for the view to the user. In this way, the administrator does not need to give access permission for the table to the user.

Use of a View

Views are used for security purposes because they provide encapsulation of the name of the table. Data is in the virtual table, not stored permanently. Views display only selected data.

Types of View

There are two types of view,

• Simple View
• Complex View

Simple View	Complex View
Created from one table	Created from one or more table
Does not contain functions	Contain functions
Does not contain groups of data	Contains groups of data

The syntax for creating a View is given below:

Syntax:

Create View Viewname As
 Select Column1, Column2  From Tablename
 Where (Condition) 
 Group by (Grouping Condition) 
 having (having Condition)

Example:

Code:

Create View View_Employeeinfo As s
       Select EmpId, EmpName, employmentdate  From EmployeeInfo

Now user can use the view View_EmployeeInfo as a table to get the empid, empname and employmentdate information of the employees by using the following query:

Select  *   from  View_EmployeeInfo  where empid=3 

It would give the following result:

EmpId	EmpName	EmploymentDate
3	EmployeeC	14/05/2007

We can also use Sql Joins in the Select statement in deriving the data for the view.

Create table EmpProjInfo (EmpId int, Projectname nvarchar(200))

and it contains the following data:

EmpId	Projectname
1	OnlineBookA
2	OnlineBookB
3	OnlineBookC
4	OnlineBookD
5	OnlineBookE

Now we can create a view Vw_EmployeeProj which gives information about the Employees and their projects:

Create view Vw_EmployeeProj As
 Select Emp_Details.EmpId, Emp_Details.EmpName,
     EmpProjInfo.Projectname from EmployeeInfo inner join
     EmpProjInfo on Emp_Details.EmpId=EmpProjInfo.EmpId

Altering an View

If we want to alter the view, then we can use the Alter View command to alter the view. For example,

Alter view Vw_EmployeeProj As
  Select Emp_Details.EmpId, Emp_Details.EmpName, 
   EmpProjInfo.Projectname from Emp_Details inner join 
   EmpProjInfo on Emp_Details.EmpId=EmpProjInfo.EmpId where Emp_Details.EmpId in (2,3,4)

Getting Information about the Views

We can use the System Procedure Sp_Helptext to get the definition about the views. For example, we can use the sp_helptext command to get the information about the view Vw_EmployeeProj.

sp_helptext Vw_EmployeeProj

Renaming the View

We can use the sp_rename system procedure to rename a view. The syntax of the sp_rename command is given below:

SP_Rename 'Old Name', 'New name'

For example, if we want to rename our view View_Employeeinfo to Vw_EmployeeInfo, we can write the sp_rename command as follows:

 sp_rename 'View_Employeeinfo', 'Vw_EmployeeInfo'

Dropping a View

We can use the Drop command to drop a view. For example, to drop the view Vw_EmployeeInfo, we can use the following statement:

 Drop view Vw_EmployeeInfo

Conclusion

• We can create view
• We can alter view
• We can drop view
• In SQL, a view is a virtual table based on the result-set of an SQL statement.
• A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
• You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.