Database Oracle enhanced exercises for multiple table queries

Database Oracle enhanced exercises for multiple table queries

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 department names and employees of these departments, and list departments without employees

Select dept. deptno, dname, ename From emp, dept where emp. deptno (+) = dept. deptno;

3. display the employee names of all departments in the "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 employee "SMITH" Name and 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 name, Department name, salary, and salary level (salgrade Table grade field). The salary level must be higher 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 the names of employees managed by "KING" and "FORD" and their managers

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 the employee name, participation time, Manager name, and participation time: the participation time is earlier than the manager's.

Select e1.ename, e1.hiredate, e2.ename, e2.hiredate From emp e1, emp e2 where e1.mgr = e2.empno and e1.hiredate <e2.hiredate;