select + join

( EQUI OR INNER, NON EQUI JOIN, SELF , OUTER - [LEFT, RIGHT, FULL], NATURAL, CROSS )

    1. Display employee names who are working in NEW YORK.

        Select e.ename from emp e JOIN dept d ON e.deptno = d.deptno where UPPER(loc) LIKE 'NEW YORK' ;

    2. Display employee names, dept names and grade who are working in NEW YORK.

        Select e.ename, d.dname, s.grade from emp e
            JOIN dept d ON e.deptno = d.deptno
            JOIN salgrade s ON e.sal between s.losal AND s.hisal;

    3. Display employee details who are working under MANAGER Blake.

        Select e1.ename, e1.sal, e1.deptno from emp e
        JOIN emp e1 ON e.empno = e1.mgr
        Where e.ename = 'BLAKE';

    4. Display employee names and their respective dept names. Include all Employee records.

        Select e.ename, d.dname from emp e
        LEFT OUTER JOIN dept d ON e.deptno = d.deptno;

    5. Display employee names and their respective dept Locations. Include all Dept records.

        Select e.ename, d.loc from emp e
        RIGHT OUTER JOIN dept d ON e.deptno = d.deptno;

    6. Display employee names, salary and their respective dept names and Locations. Include all records from both the tables.

        Select e.ename, e.sal, d.dname, d.loc from emp e
        FULL OUTER JOIN dept d ON e.deptno = d.deptno;

    7. Display employee names who are working as MANAGER in Sales Department.

        Select e.ename from emp e
        JOIN dept d USING (deptno)
        Where e.job like 'MANAGER' AND d.dname like 'SALES';

    8. Display employee names thier departments using CROSS JOIN.

        Select e.ename, d.dname from emp e, dept d;