Wednesday, October 23, 2013

Oracle Joins


------------------ Equi Joins ------------
SELECT
Emp.Empno,
Emp.Ename,
Emp.Deptno,
Dept.Deptno,
Dept.Dname,
Dept.Loc
FROM Emp, Dept
WHERE Emp.Deptno = Dept.Deptno;

Select
Empno,
Ename,
Emp.Deptno,
Loc
FROM Emp, Dept
WHERE Emp.Deptno = Dept.Deptno
AND Job = UPPER('manager');

Select
Empno,
Ename,
Sal * 12 AnnSal,
Emp.Deptno,
Loc
FROM Emp, Dept
WHERE Emp.Deptno = Dept.Deptno;

Select
Dept.Deptno,
Dname,
Loc,
SUM(Sal)
FROM Emp, Dept
WHERE Emp.Deptno = Dept.Deptno
GROUP BY Dept.Deptno, Dname, Loc;

Select
TO_CHAR(HireDate, 'YYYY') Year,
Dept.Deptno,
Dname,
SUM(Sal)
FROM Emp, Dept
WHERE Emp.Deptno = Dept.Deptno
GROUP BY TO_CHAR(HireDate, 'YYYY'), Dept.Deptno, Dname
ORDER BY Year;

SELECT
E.Empno,
E.Ename,
D.Deptno,
D.Dname
FROM Emp E, Dept D
WHERE E.Deptno = D.Deptno;

SELECT
E.Ename,
E.Job,
D.Deptno,
D.Dname,
D.Loc
FROM Emp E, Dept D
WHERE E.Deptno = D.Deptno AND
E.Job IN('ANALYST', 'MANAGER' );

SELECT
E.Ename,
E.Job,
D.Dname,
D.Loc
FROM Emp E, Dept D
WHERE E.Deptno = D.Deptno AND
D.Dname < > 'BOSTON';

------ Non Equi Join -----
SELECT E.Ename , E.Sal , S.Grade
FROM Emp E, SalGrade S
WHERE E.Sal BETWEEN S.Losal AND S.Hisal;

SELECT E.Ename, E.Sal, S.Grade
FROM Emp E, SalGrade S
WHERE
(E.Sal >= S.LoSal AND E.Sal <= S.HiSal)
AND S.Grade = 1;

------- Self Join --------
SELECT
E1.Ename "Employee Name",
E2. Ename "Managers Name"
FROM Emp E1, Emp E2
WHERE E1.Mgr = E2.Empno;

SELECT
E1.Ename||'''s Managers is '||
E2.Ename  "Employees And Managers"
FROM  Emp E1, Emp E2
WHERE E1.Mgr = E2.Empno;

SELECT
E1.Ename||' Works For '||
E2.Ename "Employees And Managers"
FROM Emp E1, Emp E2
WHERE(E1.Mgr = E2.Empno)
AND E1.Job = 'CLERK';

------- OUTER JOIN ---------
SELECT E.Ename, D.Deptno, D.Dname
FROM Emp E, Dept D
WHERE E.Deptno (+) = D.Deptno
ORDER BY E.Deptno;

SELECT E.Ename, D.Deptno, D.Dname
FROM Emp E, Dept D
WHERE E.Deptno = D.Deptno(+)
ORDER BY E.Deptno;

SELECT
E.Ename Employee,
NVL(M.Ename, 'Supreme Authoriy') Manager
FROM Emp E, Emp M
WHERE E.MGR = M.Empno(+);

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect