Views in Oracle

CREATE VIEW Employees

AS

SELECT

Empno "ID Number",

Ename Name,

Sal "Basic Salary",

Job Designation

FROM Emp;

SELECT Name, Designation

FROM Employees;

SELECT "ID Number",

Name,

"Basic Salary" * 12

FROM Employees;

SELECT

"ID Number",

Name,

TO_CHAR("Basic Salary", '99,99,999.99') Monthly,

"Basic Salary" * 12 Annual

FROM Employees

WHERE "Basic Salary" > 2500;

CREATE VIEW EmpInfo

AS

SELECT

E.Empno     EmployeeID,

E.Ename     Name,

D.Deptno    DepartmentID,

D.Dname DepartmentName

FROM Emp E, Dept D

WHERE D.Deptno = E.Deptno

ORDER BY D.Deptno;

CREATE OR REPLACE VIEW EmpManagers

AS

SELECT

RowNum SerialNo,

INITCAP(E.Ename)||' Works Under '

||M.Ename  "Employee And Managers"

FROM Emp E, Emp M

WHERE E.MGR = M.Empno;

CREATE OR REPLACE VIEW EmpAccounts

AS

SELECT

Ename,

Deptno,

Sal Monthly,

Sal * 12 Annual

FROM  Emp

WHERE Deptno = (SELECT Deptno

                FROM Dept

                WHERE Dname = 'ACCOUNTING')

ORDER BY Annual;

CREATE OR REPLACE VIEWOrgDesignations

AS

SELECT

Job

FROM Emp

WHERE Deptno = 10

UNION

SELECT Job

FROM Emp

WHERE Deptno IN(20, 30);

SELECT VIEW_NAME, TEXT

FROM USER_VIEWS;

CREATE VIEW DeptSalSummary

(

DepartmentName,

MinimumSalary,

MaxSalary,

AverageSalary,

SalarySum

)

AS

SELECT

D.Dname,

MIN(E.Sal),

MAX(E.Sal),

AVG(E.Sal),

SUM(E.Sal)

FROM Emp E, Dept D

WHERE E.Deptno = D.Deptno

GROUP BY D.Dname;

CREATE VIEW InsertDept10

AS

SELECT *

FROM Emp

WHERE Deptno = 10;

CREATE TABLE Dept10

AS

SELECT *

FROM InsertDept10;

SELECT

E1.Ename,

E1.Sal,

E1.Deptno,

E2.Maxsal

FROM Emp E1, (SELECT Deptno, MAX(Sal) Maxsal

                FROM Emp

                GROUP BY Deptno) E2

WHERE E1.Deptno = E2.Deptno

AND E1.Sal < E2.Maxsal;

CREATE VIEW InsertDept

(

 DeptID,

 DeptName ,

 Place

)

 AS

 SELECT

 Deptno,

 Dname,

 Loc

 FROM Dept;

INSERT INTO InsertDept(DeptID, DeptName, Place)

VALUES(50, 'ADMINISTRATION', 'DELHI');

UPDATE InsertDept

SET PLACE = 'MUMBAI'

WHERE DeptID = 50;

DELETE

FROM InsertDept

WHERE DeptID = 50;

CREATE OR REPLACE VIEW EDept30

AS

SELECT *

FROM Emp WHERE Deptno = 30

WITH CHECK OPTION CONSTRAINTEDept30ChkView;

CREATE OR REPLACE VIEW EDptRead

(EmpID, Name, Designation)

AS

SELECT Empno, Ename, Job

FROM Emp

WHERE Deptno = 20

WITH READ ONLY;