Creating Views in Oracle Database Using TOAD

A view logically represents subsets of data from one or more tables. You can restrict a user’s access to data in a table by using views. A view does not have a structure of its own and is stored as a SELECT statement in the database.

Views are of two types, simple and complex:

• Simple view: Retrieves data from only one table and contains no functions or group data. You can perform DML operations through a simple view.
• Complex view: Retrieves data from multiple tables or contains functions or group data. A complex view does not always allow you to perform DML operations.


Creating Simple View

You create a simple view based on only one table. In other words, the SELECT statement of the view retrieves data from only one table. For example:-

CREATE OR REPLACE VIEW EMP_VU
(EMPNO, EMP_NAME, ANNUAL_SALARY, DEPTNO)
AS SELECT e.EMPNO, e.EMP_NAME, e.SALARY*12, e.DEPTNO FROM employee e;

Write the SQL script in SQL tab and press F5 to execute.

Verify emp_vu View by using browser schema tab. 

You can create a view based on a SELECT statement with a WHERE condition. You can modify the previous example to include a WHERE condition, as follows:

You can modify a view by using CREATE OR REPLACE statement.

CREATE OR REPLACE VIEW EMP_VU
(EMPNO, EMP_NAME, ANNUAL_SALARY, DEPTNO)
AS 
SELECT e.EMPNO, e.EMP_NAME, e.SALARY*12, e.DEPTNO
FROM employee e
WHERE e.DEPTNO = 3;

Verify modified  emp_vu View and its data by using browser schema tab. 

Creating Complex View

A complex view contains group functions or displays data from multiple tables. The following example creates a complex view, which retrieves data from multiple tables and includes a group function:

CREATE OR REPLACE VIEW DEPT_VU
(DNAME, MINSAL, MAXSAL, TOTAL)
AS 
SELECT d.DEPT_NAME , MIN(e.salary), MAX(e.salary), SUM(salary)
FROM department d, employee e
WHERE e.deptno = d.deptno
GROUP BY d.DEPT_NAME;

Removing a View

You can remove a view from the database using the DROP VIEW statement, as follows:
DROP VIEW dept_vu;