Wednesday, October 23, 2013

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 VIEW OrgDesignations
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 CONSTRAINT EDept30ChkView;

CREATE OR REPLACE VIEW EDptRead
(EmpID, Name, Designation)
AS
SELECT Empno, Ename, Job
FROM Emp
WHERE Deptno = 20
WITH READ ONLY;

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect