Creating a view:
- A query can be embedded within the CREATE VIEW STATEMENT
- A query can have complex select statements containing join, groups and sub- queries
- A query that describes the view cannot contain an order by clause.
- DML operation (delete/ add / modify) cannot be applied if the view have any of the following:
|
Delete (You can't delete if view contains following)
|
Modify (you cannot modify if view contains following)
|
Insert (you cannot insert if view contains following)
|
|
|
Expressions
|
Expressions
- There are Not Null Columns in the base tables that are not selected by view.
|
Example: Create a view named employee salary having minimum, maximum and average salary for every department.
CREATE VIEW EMPSAL (NAME, MINSAL, MAXSAL, AVGSAL) AS
SELECT D.DNAME, MIN(E.SAL),MAX(E.SAL),AVG(E.SAL)
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO GROUP BY D.DNAME;
To view the result of the command above you can give the following command:
SELECT * FROM EMPSAL;
You may get some sample output like:
NAME MINSAL MAXSA AVG SAL
-------------- --------- --------- -------------
ACCOUNTING 1300 5000 2916.6667
RESEARCH 800 3000 2175
SALES 950 2850 1566.6667
To view the structure of the view so created, the command is given below:
DESCRIBE EMPSAL;
Name Null? Type
--------------- ------------ ---------------------
NAME VARCHAR2 (14)
MINSAL NUMBER
MAXSAL NUMBER
AVGSAL NUMBER