Dept Table
Invalid because the view is created with check option
Views:
A view is like a window through which user can access a table.
A view has the following three main purposes
1 Restrict the user from performing insert, update and delete only on specific rows of the table
2 Restrict the user from accessing only specific rows of the table.
3 Simplify the complex queries like set operators joins and sub queries when we have to repeatedly execute that statement.
Creating a View:
To create a view use the create view command that has the following syntax:
Create view <view name> ((columnist)) [with encryption][,][schema binding]] as <select statement>
[with check option]
Create a view to restrict the user to access only the details of employee working in deptno=20
create view emp20 as select * from emp where deptno=20
Important:
Once the view is created it can be used like a table and you can perform select, insert, update, and delete on the view same as on the table.
But a view does not contain any data physically and whatever you select any data from a view, it will execute the select statement specify while creating a view gets data from the table and displays it to you.
When you perform insert, update, delete on a view than it will perform that insert, update & delete on the table on which the view was created. Hence the view is called as a logical object and not the physical object.
Views will not store data and will not use memory. Views contains only the structure or columns by using those views will display values from the tables. View will not contain data of its own the data which it displays will be from the table.
The following examples retrieves data from the view emp20
select * from emp20
Example:
The following example inserts a row into the view emp20
insert emp20 values(1001,'A','clerk',7902,'10/21/1980',2000,NULL,30)
Check option: The above statement will successfully insert a row even if it is not satisfying the condition specified in select statement of the view.
Emp20 that is dept no 20 because the condition is specified in select statement of the view will be checked only during the select and not during insert, update, delete.
To check the condition specify in the select statement of the view during insert, update and delete also you must specify the “check option” while creating the view.
The following example alter the view emp20 to include check option
alter view emp20 as
select * from emp where DEPTNO=20
with check option
Note:
Using grant and revoke we can restrict user operations.
But by using view we can restrict data (particular columns)
Now we will execute the same insert query again and check what happens
insert emp20 values(1005,'A','clerk',7902,'10/21/1980',2000,NULL,30)
Error: The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.
The statement has been terminated.
Types of Views:
Types of Views:
Simple view
Complex view
Simple view:
- These Views as based upon a single table, which access the data from the single table.
- They contain a Sub Query which retrieves the data from one base table.
Syntax
Create view <viewname>
(<with option>)
As
Select statement
[where condition]
Example:
createview v1 as
selectempno,ename, sal from emp
When the above view is created select statement is stored in dB, that why view is also called as stored query.
CREATE VIEW SIMPLE_VIEW
AS SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP
Once the view is created we can access the data from it as if it was a table as following:
SELECT * FROM SIMPLE_VIEW
SELECT EMPNO, ENAME, SAL, SAL*12 AS [ANNUAL SAL], DEPTNO FROM SIMPLE_VIEW
-We can also perform DML operations on the Simple Views which will effect on the base table.
INSERTINTO SIMPLE_VIEWVALUES(1234, 'SANTOSH', 4300, 20)
DELETEFROM SIMPLE_VIEWWHERE DEPTNO=20
UPDATEEMP SET SAL=5600 WHERE EMPNO=1001
All the columns that are referenced in the view can be modified through the view.
We cannot perform insert operations on the view if he view does not contain all the not null columns of the base table.
Example
createview v4
as
selectempno, sal,deptno from emp
wheredeptno=10
withcheck option
insertinto v4 values(100,900,10)
| insert into v4 values(100,900,30) |
Complex Views:
- If the View is based on multiple tables it is a complex view
- If it is based on a single table with any of the following:
o Group By Clause
o Having Clause
o Group Functions
o Distinct Function
o Function Calls
CREATEVIEW EMP_GRADE
AS
SELECT E.EMPNO, E.ENAME, E.SAL, S.GRADE, S.LOSAL, S.HISAL
FROM EMPE INNER JOIN SALGRADE S
ON E.SAL BETWEEN S.LOSAL AND S.HISAL
CREATEVIEW EMP_DEPT
AS
SELECTE.EMPNO, E.ENAME, E.SAL, D.DEPTNO, D.DNAME, D.LOC
FROMEMP E INNER JOIN DEPT D
ONE.DEPTNO=D.DEPTNO
CREATE VIEW EMP_GRADE1
AS
SELECT E.EMPNO, E.ENAME, E.SAL, S.GRADE, S.LOSAL, S.HISAL
FROM EMPE INNER JOIN SALGRADE S
ON E.SAL BETWEEN S.LOSAL AND S.HISAL
CREATEVIEW EMP_DESIGNATIONS
AS
SELECTJOB FROM EMP WHERE DEPTNO=10
UNION
SELECTJOB FROM EMP WHERE DEPTNO=20
UNION
SELECTJOB FROM EMP WHERE DEPTNO=30
CREATEVIEW EMP_MAX_SAL
AS
SELECTDEPTNO, MAX(SAL) AS [HIGH SAL] FROM EMP GROUP BY DEPTNO
Create a view that provides access to details of employees along with manager Name of the employee, grade of the employee based on salary ,and deptname and location in which employee is working.
createview empdetails
as
selecte.EMPNO,e.ENAME,e.JOB,e.MGR,m.ENAME as manager,e.SAL ,losal,hisal,grade,e.DEPTNO,d.DNAME,d.LOC
from emp e inner join emp m on e.MGR=m.EMPNO inner join salgrade on e.SAL between losal and hisal inner join dept d on e.DEPTNO =d.DEPTNO
Note:
When a view is complex view then insert, update, delete are not allowed on that view but when the view is created on multiple tables, than if you write insert, update statement that effect only one table then they are not allowed.
Query fired on view takes more time than query fired on base table. Views degrades performance.
We can also classify views as Updateable Views and Non Updateable Views:
- A View, which allows manipulations on it, is known as Updateable View.
- A View, which will not allow manipulations on it, is known as Non Updateable View.
If we want to perform manipulations on the Complex Views we have the following restrictions:
Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.
- The columns being modified cannot be affected by GROUP BY, HAVING, or DISTINCT clauses.
With Check Option:
- Forces all data modification statements executed against the view to follow the criteria set within select statement.
- When a row is modified through a view, the WITH CHECK OPTION makes sure the data remains visible through the view after the modification is committed.
-
- CREATE VIEW SALES_EMP
- AS
- SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE DEPTNO=20
-
- INSERT INTO SALES_EMP VALUES(1050, ‘RAJU’, 3500, 30)
-
- -The above insert statement executes even if it does not satisfy the condition in the View, if this has to be restricted the view has to be created by using With Check Option clause.
ALTER VIEW SALES_EMP
AS
SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE DEPTNO=20
WITH CHECK OPTION
View Attributes:
Encryption:
If the view is created with encryption, then view definition will be hidden in information schema views table.
Schemabinding:
- When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition.
- We need to specify the column names individual in the select statement, cannot use “*” in the select statement.
- All referenced objects must be in the same database.
- Tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding.
- CREATE VIEW EMP_BIND
- WITH SCHEMABINDING
- AS
- SELECT EMNO, ENAME, JOB, MGR FROM DBO.EMP
-
After the view is created EMP table cannot be dropped.
When a view is created with schema binding option than it is not possible to alter the columns that are selected into the view within the table until the views exists.
While creating a view with schema binding option, within the select statement of the view you must specify the table name in the format “schemaname.tablename” where schema name is same as username.
The following examples creates a view that provides access to empno, ename, job, Sal and comm by specifying schema binding.
Create view empview
With schema binding as
Select empno, ename, job, Sal, comm from dbo.emp
Now, we will try to alter.
Alter table EMP alter column sal small money
Error
Getting List of Views:
To get list of view available in the dB, you can use any of the following two options
It gets the definition of the view, table name
Select* from INFORMATION_SCHEMA.VIEWS
Select* from sys.views
(View date, created data, modified data)
To get list of the tables on which view was created use the following select statement.
Getting definition of a view:
To get the definition of a view, use the stored procedure sp_helptext that has the following syntax:
sp_helptext ‘view name’
Example
sp_helptext'v4'
Note:
When you don’t want to display the definition of your view to anyone then specify encryption option while creating the view.
To delete the view
To delete the view use the drop view command that has the following syntax
Drop view <view name>
The following examples deletes the view v4
Dropview v4
