The 'SQL VIEW ' is a database object. It provides derived data from one or more tables. It is normally stored as a
'Select' statement in the database.
The SQL VIEW
The 'SQL VIEW " is an object that acts as a security mechanism in SQL Server. It is a virtual table based on a SQL query. A view does not contain
data on its own. They provide security to the tables that lie underneath. A view provides a smokescreen by displaying data from one or more tables as if it is
coming from a single table. Each time the user queries the database for this view, it recreates the data from the tables by using the given SQL statement.
Create View Syntax
Create VIEW viewname
as
Select column1, column2, column3, column4 datatype, .... columnn
From
tablename
Where condition
The SQL View Example
You know the 'Employees' table as shown below
1 |
Richard Hughes |
4/23/1945 12:00:00 AM |
1 |
1 |
343-123-2002 |
New Orleans |
950.00 |
2 |
Taryn Sinclair |
3/22/1980 12:00:00 AM |
2 |
2 |
232-344-8755 |
San Francisco |
800.00 |
3 |
Ted Horowitz |
3/31/1960 12:00:00 AM |
3 |
4 |
435-657-9068 |
New York |
700.00 |
4 |
Jonathan Douglas |
7/7/1971 12:00:00 AM |
3 |
4 |
323-454-7656 |
Salt Lake City |
600.00 |
5 |
Miranda Leigh |
7/19/1983 12:00:00 AM |
7 |
3 |
634-465-8046 |
New York |
500.00 |
6 |
Jana Rae |
9/23/1976 12:00:00 AM |
8 |
2 |
434-434-1254 |
Houston |
400.00 |
7 |
Lita Rosanna |
9/14/1982 12:00:00 AM |
4 |
6 |
565-782-1223 |
Long Island |
650.00 |
8 |
Colin Flooks |
12/29/1988 12:00:00 AM |
4 |
2 |
954-652-2111 |
Salt Lake City |
600.00 |
9 |
Anthony Frank |
2/22/1988 12:00:00 AM |
9 |
3 |
323-243-1249 |
San Francisco |
750.00 |
10 |
Stephanie Lynn |
7/30/1979 12:00:00 AM |
8 |
2 |
543-765-4653 |
Salt Lake City |
450.00 |
11 |
Jack Martin |
8/25/1985 12:00:00 AM |
NULL |
NULL |
443-324-6522 |
Austin |
500.00 |
CREATE View view_Employees
as
Select EmployeeID,EmployeeName,DateOfBirth,DesignationID, DeptID, PhoneNo, City
From
Employees
Where DesignationID <> 1
2 |
Taryn Sinclair |
3/22/1980 12:00:00 AM |
2 |
2 |
232-344-8755 |
San Francisco |
3 |
Ted Horowitz |
3/31/1960 12:00:00 AM |
3 |
4 |
435-657-9068 |
New York |
4 |
Jonathan Douglas |
7/7/1971 12:00:00 AM |
3 |
4 |
323-454-7656 |
Salt Lake City |
5 |
Miranda Leigh |
7/19/1983 12:00:00 AM |
7 |
3 |
634-465-8046 |
New York |
6 |
Jana Rae |
9/23/1976 12:00:00 AM |
8 |
2 |
434-434-1254 |
Houston |
7 |
Lita Rosanna |
9/14/1982 12:00:00 AM |
4 |
6 |
565-782-1223 |
Long Island |
8 |
Colin Flooks |
12/29/1988 12:00:00 AM |
4 |
2 |
954-652-2111 |
Salt Lake City |
9 |
Anthony Frank |
2/22/1988 12:00:00 AM |
9 |
3 |
323-243-1249 |
San Francisco |
10 |
Stephanie Lynn |
7/30/1979 12:00:00 AM |
8 |
2 |
543-765-4653 |
Salt Lake City |
In the above example, the 'view_Employees' view is created on the basis that the salary column is hidden and also the record that has DesignationID = 1.
Now since the values are coming from the view 'view_Employees', it hides or encapsulates that the underlying table 'Employees'.
Updating a View Syntax
Create or replace VIEW viewname
as
Select column1, column2, column3, column4 datatype, .... columnn
From
tablename
Where condition
Dropping a View Syntax
Drop VIEW viewname