A view is nothing more than a saved query. A view can also be considered as a virtual table.
| DeptId | DeptName |
| 1 | IT |
| 2 | Payroll |
| 3 | HR |
| 4 | Admin |
| Id | Name | Salary | Gender | DepartmentId |
| 1 | Jhon | 5000 | Male | 3 |
| 2 | Mike | 3400 | Male | 2 |
| 3 | Pam | 6000 | Female | 1 |
| 4 | Todd | 4800 | Male | 4 |
| 5 | Sara | 3200 | Female | 1 |
| 6 | Ben | 4800 | Male | 3 |
Select Query
Select Id, Name, Salary, Gender, DeptName
From tblEmployee
Join tblDepartment
On tblEmployee.DepartmentId=tblDepartment.DeptID
Result
| Id | Name | Salary | Gender | DeptName |
| 1 | Jhon | 5000 | Male | HR |
| 2 | Mike | 3400 | Male | Payroll |
| 3 | Pam | 6000 | Female | IT |
| 4 | Todd | 4800 | Male | Admin |
| 5 | Sara | 3200 | Female | IT |
| 6 | Ben | 4800 | Male | HR |
Creating View using select Query
Create View vWEmployeesByDepartment
As
Select Id, Name, Salary, Gender, DeptName
From tblEmployee
Join tblDepartment
On tblEmployee.DepartmentId=tblDepartment.DeptID
Select * from vWEmployeesByDepartment
Result
| Id | Name | Salary | Gender | DeptName |
| 1 | Jhon | 5000 | Male | HR |
| 2 | Mike | 3400 | Male | Payroll |
| 3 | Pam | 6000 | Female | IT |
| 4 | Todd | 4800 | Male | Admin |
| 5 | Sara | 3200 | Female | IT |
| 6 | Ben | 4800 | Male | HR |
Advantages of views
- Views can be used to reduce the complexity of the database schema
- Views can be used as a mechanism to implement row and column level security
Example if IT department employees only to show
Create View vWITEmployeesByDepartment
As
Select Id, Name, Salary, Gender, DeptName
From tblEmployee
Join tblDepartment
On tblEmployee.DepartmentId=tblDepartment.DeptID
Where tblDepartment.DeptName=’IT’
Select * from vWITEmployeesByDepartment
Result
| Id | Name | Salary | Gender | DeptName |
| 3 | Pam | 6000 | Female | IT |
| 5 | Sara | 3200 | Female | IT |
Example if salary to hide
Create View vWNonconfedentialdata
As
Select Id, Name, Gender, DeptName
From tblEmployee
Join tblDepartment
On tblEmployee.DepartmentId=tblDepartment.DeptID
Select * from vWNonconfedentialdata
Result
| Id | Name | Gender | DeptName |
| 1 | Jhon | Male | HR |
| 2 | Mike | Male | Payroll |
| 3 | Pam | Female | IT |
| 4 | Todd | Male | Admin |
| 5 | Sara | Female | IT |
| 6 | Ben | Male | HR |
- Views can be used to present aggregated data and hide detailed data.
Example
Create view VwsummarizedData
As
select DeptName, Count(ID) as Total
From tblEmployee
Join tblDepartment
On tblEmployee.DepartmentId=tblDepartment.DeptID
Group by DeptName
Select * from VwsummarizedData
Result
| DeptName | Total |
| Admin | 1 |
| HR | 2 |
| IT | 2 |
| Payroll | 1 |
To modify view – ALTER VIEW statement
To droop a view – DROP VIEW viewname