Chitika Add

Friday, December 13

Joins and Types

Here we are giving sample queries for below types of joins
  1. Equi Join
  2. Non Equi Join
  3. Self Join
  4. Outer join

------------------ 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: