Database Oracle Intensive Practice multi-table query

1. List the names of all employees and their immediate superiors

Select a.ename,a.mgr,b.ename,b.empno from emp a,emp b where a.mgr=b.empno (+);

2. List the name of the department and the employees of those departments, together with the departments that do not have employees

Select dept.deptno,dname,ename from Emp,dept where Emp.deptno (+) =dept.deptno;

3. Show employee names for all departments in "NEW YORK" (Dept table Loc Field)

Select e.ename from emp e,dept d Where e.deptno=d.deptno and d.loc= ' NEW YORK ';

4. Display the name of the employee "SMITH", Department name

Select e.ename,d.dname from emp e,dept D where E.deptno=d.deptno and E.ename= ' SMITH ';

5. Display the employee's name, department name, salary, pay scale (Salgrade table grade field), require salary scale greater than 4

Select e.ename,d.dname,e.sal,s.grade from emp e,dept d,salgrade s where E.deptno=d.deptno and e.sal between S.losal and S. Hisal and S.grade >4;

6. Display Employee name and manager name of employee "KING" and "FORD" management

Select e1.ename,e2.ename from EMP e1,emp E2 where E1.mgr=e2.empno and (e2.ename= ' KING ' or e2.ename= ' FORD ');

7. Display employee's name, working hours, manager's name, working hours: Working hours are earlier than his manager

Select e1.ename,e1.hiredate,e2.ename,e2.hiredate from EMP e1,emp E2 where E1.mgr=e2.empno and e1.hiredate< E2.hiredate;

Database Oracle Intensive Practice multi-table query