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:

<span class="pln">
</span><span class="typ">Create</span><span class="pln"> table </span><span class="typ">Emp_Details</span><span class="pun">(</span><span class="pln">
    </span><span class="typ">EmpId</span><span class="pln"> </span><span class="kwd">int</span><span class="pun">,</span><span class="pln">
    </span><span class="typ">EmpName</span><span class="pln"> nvarchar</span><span class="pun">(</span><span class="lit">200</span><span class="pun">),</span><span class="pln">
    </span><span class="typ">EmpLogin</span><span class="pln"> nvarchar</span><span class="pun">(</span><span class="lit">20</span><span class="pun">),</span><span class="pln">
    </span><span class="typ">EmpPassword</span><span class="pln"> nvarchar</span><span class="pun">(</span><span class="lit">20</span><span class="pun">)</span><span class="pln"> </span><span class="pun">,</span><span class="pln">
    </span><span class="typ">EmploymentDate</span><span class="pln"> datetime 
</span><span class="pun">)</span><span class="pln">
</span>

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

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

The syntax for creating a View is given below:

Syntax:

<span class="pln">
</span><span class="typ">Create</span><span class="pln"> </span><span class="typ">View</span><span class="pln"> </span><span class="typ">Viewname</span><span class="pln"> </span><span class="typ">As</span><span class="pln">
 </span><span class="typ">Select</span><span class="pln"> </span><span class="typ">Column1</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Column2</span><span class="pln">  </span><span class="typ">From</span><span class="pln"> </span><span class="typ">Tablename</span><span class="pln">
 </span><span class="typ">Where</span><span class="pln"> </span><span class="pun">(</span><span class="typ">Condition</span><span class="pun">)</span><span class="pln"> 
 </span><span class="typ">Group</span><span class="pln"> </span><span class="kwd">by</span><span class="pln"> </span><span class="pun">(</span><span class="typ">Grouping</span><span class="pln"> </span><span class="typ">Condition</span><span class="pun">)</span><span class="pln"> 
 having </span><span class="pun">(</span><span class="pln">having </span><span class="typ">Condition</span><span class="pun">)</span><span class="pln">
</span>

Example:

Code:

<span class="pln">
</span><span class="typ">Create</span><span class="pln"> </span><span class="typ">View</span><span class="pln"> </span><span class="typ">View_Employeeinfo</span><span class="pln"> </span><span class="typ">As</span><span class="pln"> s
       </span><span class="typ">Select</span><span class="pln"> </span><span class="typ">EmpId</span><span class="pun">,</span><span class="pln"> </span><span class="typ">EmpName</span><span class="pun">,</span><span class="pln"> employmentdate  </span><span class="typ">From</span><span class="pln"> </span><span class="typ">EmployeeInfo</span><span class="pln">
</span>

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:

<span class="pln">
</span><span class="typ">Select</span><span class="pln">  </span><span class="pun">*</span><span class="pln">   </span><span class="kwd">from</span><span class="pln">  </span><span class="typ">View_EmployeeInfo</span><span class="pln">  </span><span class="kwd">where</span><span class="pln"> empid</span><span class="pun">=</span><span class="lit">3</span><span class="pln"> 
</span>

It would give the following result:

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

<span class="pln">
</span><span class="typ">Create</span><span class="pln"> table </span><span class="typ">EmpProjInfo</span><span class="pln"> </span><span class="pun">(</span><span class="typ">EmpId</span><span class="pln"> </span><span class="kwd">int</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Projectname</span><span class="pln"> nvarchar</span><span class="pun">(</span><span class="lit">200</span><span class="pun">))</span><span class="pln">
</span>

and it contains the following data:

<span class="pln">
</span><span class="typ">EmpId</span><span class="pln">	</span><span class="typ">Projectname</span><span class="pln">
</span><span class="lit">1</span><span class="pln">	</span><span class="typ">OnlineBookA</span><span class="pln">
</span><span class="lit">2</span><span class="pln">	</span><span class="typ">OnlineBookB</span><span class="pln">
</span><span class="lit">3</span><span class="pln">	</span><span class="typ">OnlineBookC</span><span class="pln">
</span><span class="lit">4</span><span class="pln">	</span><span class="typ">OnlineBookD</span><span class="pln">
</span><span class="lit">5</span><span class="pln">	</span><span class="typ">OnlineBookE</span><span class="pln">
</span>

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

<span class="pln">
</span><span class="typ">Create</span><span class="pln"> view </span><span class="typ">Vw_EmployeeProj</span><span class="pln"> </span><span class="typ">As</span><span class="pln">
 </span><span class="typ">Select</span><span class="pln"> </span><span class="typ">Emp_Details</span><span class="pun">.</span><span class="typ">EmpId</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Emp_Details</span><span class="pun">.</span><span class="typ">EmpName</span><span class="pun">,</span><span class="pln">
     </span><span class="typ">EmpProjInfo</span><span class="pun">.</span><span class="typ">Projectname</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> </span><span class="typ">EmployeeInfo</span><span class="pln"> inner </span><span class="kwd">join</span><span class="pln">
     </span><span class="typ">EmpProjInfo</span><span class="pln"> on </span><span class="typ">Emp_Details</span><span class="pun">.</span><span class="typ">EmpId</span><span class="pun">=</span><span class="typ">EmpProjInfo</span><span class="pun">.</span><span class="typ">EmpId</span><span class="pln">
</span>

Altering an View

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

<span class="pln">
</span><span class="typ">Alter</span><span class="pln"> view </span><span class="typ">Vw_EmployeeProj</span><span class="pln"> </span><span class="typ">As</span><span class="pln">
  </span><span class="typ">Select</span><span class="pln"> </span><span class="typ">Emp_Details</span><span class="pun">.</span><span class="typ">EmpId</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Emp_Details</span><span class="pun">.</span><span class="typ">EmpName</span><span class="pun">,</span><span class="pln"> 
   </span><span class="typ">EmpProjInfo</span><span class="pun">.</span><span class="typ">Projectname</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> </span><span class="typ">Emp_Details</span><span class="pln"> inner </span><span class="kwd">join</span><span class="pln"> 
   </span><span class="typ">EmpProjInfo</span><span class="pln"> on </span><span class="typ">Emp_Details</span><span class="pun">.</span><span class="typ">EmpId</span><span class="pun">=</span><span class="typ">EmpProjInfo</span><span class="pun">.</span><span class="typ">EmpId</span><span class="pln"> </span><span class="kwd">where</span><span class="pln"> </span><span class="typ">Emp_Details</span><span class="pun">.</span><span class="typ">EmpId</span><span class="pln"> </span><span class="kwd">in</span><span class="pln"> </span><span class="pun">(</span><span class="lit">2</span><span class="pun">,</span><span class="lit">3</span><span class="pun">,</span><span class="lit">4</span><span class="pun">)</span><span class="pln">
</span>

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.

<span class="pln">
sp_helptext </span><span class="typ">Vw_EmployeeProj</span><span class="pln">
</span>

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:

<span class="pln">
SP_Rename </span><span class="str">'Old Name'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'New name'</span><span class="pln">
</span>

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

<span class="pln">
 sp_rename </span><span class="str">'View_Employeeinfo'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Vw_EmployeeInfo'</span><span class="pln">
</span>

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:

<span class="pln">
 </span><span class="typ">Drop</span><span class="pln"> view </span><span class="typ">Vw_EmployeeInfo</span><span class="pln">
</span>

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.