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(+);