Introduction
View is a virtual table which use to display the data from one and more than one table .View use for security and restriction of acess the data. Suppose there is one software company X and the client of X company is Y. Y would like to know the all details of Employee of X. But Company X would not like to share salary details to Y. In this case company will be create one view excluding Salary column and they will show the details to their client Y.
Before discussing about view we will be discuss the task which has to perform in database.
- Creating table in database
- Creating View
- Creating table in database
Step 1: Go to start button and select SQL server management studio as showing in below figure.
Now click on SQL server management studio and go to object explorer.
Step 2: Create a new database by using.
Create database database_Name
In this Discussion I have created a database as name DVView as showing in below figure.
Now go to table and create two table:
- Employee
- Branch
Create table employee with 4 column as:
- create table Employee(eid int primary key,ename varchar(20),designation varchar(20),mgrid int)
Now insert the values in table Employee:
- insert into Employee values(1,'Sandeep','DBA',2)
- insert into Employee values(2,'Rakesh','.Net Developer',1)
- insert into Employee values(3,'sohan','Java Developer',1)
- insert into Employee values(4,'Suresh','Software Trainee',3)
- insert into Employee values(5,'Mohan','Software Trainee',4)
After inserting the data in table run the query:
Now output will be as below figure.
Again create one more table as name Branch.
Create table Branch.
Now insert the values in table branch.
- insert into branch values(1oo,'Hyd',1)
- insert into branch values(1o1,'DEL',2)
- insert into branch values(1o3,'Bag',3)
- insert into branch values(1o4,'Chn',4)
- insert into branch values(1o5,'Nod',5)
Now run the query.
Output will be display as below figure.
Creating view
Syntax for creating View(ANSI QUERY):
Createview viewName
As
Selectcolumns
Fromtables Name
Where condition
Go
Example:
- createview viewEmp
- as
- select e1.ename,e1.designation,e1.mgrid,b1.bid,b1.bname
- from Employee e1,branch b1
- where e1.eid=b1.eid
- go
While we will be execute the view:
Output will be like
Figure: Output by using ANSI Query
Syntax for creating view(TSQL Query)
createview viewName
as
selectcolumn name
from table1
jointable 2
on condition
go
Example
- createview viewe
- as
- select e1.ename,e1.designation,e1.mgrid,b1.bid,b1.bname
- from Employee e1
- join branch b1
- on e1.eid=b1.eid
- go
While we will be execute the view:
Output will be like:
Figure: Output by using TSQL
Recap
View is a virtual table which uses to display the data from one and more than one table. Also it use to restrict the access of data.