Perform following operations on views • Simple Views • Complex Views • Modifying a View DML Operations on a View • Denying DML Operations on view • Removing a View

 

EXERCISE : 10

AIM :  Perform following operations on views
• Simple Views                                                                                                                                    • Complex Views                                                                                                                                       • Modifying a View                         DML Operations on a View
• Denying DML Operations on view                                                                                                    • Removing a View

Description :

a) Simple views :

            It is the view created by involving only single table.
Syntax :
            CREATE VIEW view_name AS
            SELECT column1, column2, ...
            FROM table_name
            WHERE condition;

Example:

create view sailorsv As select * from sailors;

Output:

 

 

 

b) Complex views :

When the view is created based on multiple tables then it is known as a complex view in SQL Server. The most important point that we need to remember is, on a complex view in SQL Server, we may or may not perform the DML operations and more ever the complex view may not update the data correctly on the underlying database tables.

1. In a Complex View, if your update statement affects one base table, then the update succeeded but it may or may not update the data correctly.
2. if your update statement affects more than one table, then the update failed and we will get an error message stating “View or function ‘vwEmployeesByDepartment’ is not updatable because the modification affects multiple base tables”.

Example:

create view dept_stat1

 As

select dname,sum(sal) Total_Salary,Min(sal) Min_sal,Max(sal) Max_Sal

 from emp e,dept d

 where e.deptno=d.deptno

group by dname;

output:

 

 

c) Modifying a 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 statements and functions to a view and present the data as if the data were coming from one single table.

d) DML operations on a view :

The DML operations INSERT, UPDATE, and DELETE can be performed on simple views.
These operations can be used to change the data in the underlying base tables.
If you create a view that allows users to view restricted information using the WHERE clause, users can still perform DML operations on all columns of the view.

INSERT

Synatx:

Example: insert into sailorsv values(105,'srgec',4,21);

select * from sailorsv;

UPDATE:

Example:

update sailorsv set sname='gec' where sid=105;

Output:

 

 

 

 

e) Denying DML operations on view :

• The deny statement denies permission to a principal for accessing the securable. For example, if we do not want a user to perform a delete operation on a table, DBA can deny delete permission on the object.
• The WITH READ ONLY option ensures that no DML operations occur through the view.
• Any attempt to execute an INSERT, UPDATE, or DELETE statement will result in an Oracle server error.

Syntax :

CREATE OR REPLACE VIEW view_dept50

AS SELECT department_id, employee_id, first_name, last_name, salary

FROM employees

WHERE department_id = 50

WITH READ ONLY;
INSERT EXAMPLE:

 

 

 

 

 

 

DELETE:

EXAMPLE:

f) Removing a view :

Syntax :Drop view viewname;

Example: Drop view sailorsv;

Output:

 

VIVA QUESTIONS

1.   What is view?

2.   Is view updatable?

3.   What are the advantages of views?