1) Find all jobs which appear both in department 10 and in department 20.
Order results by jobs.
1) select job from EMP where deptno=10 INTERSECT
(select job from EMP where deptno=20) order by job;
2) Find people who earn the lowest salary for their job and grade.
2) select distinct E1.empno,E1.ename,E1.sal,E1.job from EMP E1, SALGRADE S1
where E1.sal=(select min(E2.sal) from EMP E2
where E1.job=E2.job) order by E1.sal;
3) How many people have been employed earlier than the one employed
as the last one in ACCOUNTING
3) select count(ename) from EMP where
hiredate<(select max(hiredate) from EMP where
deptno=(select deptno from DEPT where dname='ACCOUNTING'));
4) For each manager find the number of his subordinates. Display
name of the manager and the number of people working under his
supervision. Order by the number of subordinates descending.
4)
5) Find all people working as CLERK and employed after ADAMS. Display
results ordered by hire dates.
5) select * from EMP where job='CLERK' and hiredate>(select hiredate from EMP
where ename='ADAMS') order by hiredate;
6) Display names, jobs and hire dates for all employees working in
DALLAS. Order results by names.
6) select ename,job,hiredate from EMP
where deptno=(select deptno from DEPT where loc='DALLAS') order by ename;
7) How many people earn less than the maximal salary for the department
located in NEW YORK.
7) select count(empno) from EMP where sal<(select max(sal) from EMP where
deptno=(select deptno from DEPT where loc='NEW YORK'));
8) Find the number of people who supervise the others.
8) select count(M.empno) from EMP M where exists
(select E.empno from EMP E where M.empno=E.mgr);
9) Find all employees (personal number name, profession and salary),
who earn less than the average salary for people with salaries in the
third grade. Display results order by salaries ascending.
9) select empno,ename,job,sal from EMP where sal<=(select AVG(sal)
from EMP where sal between(select losal from SALGRADE where grade=3) and
(select hisal from SALGRADE where grade=3)) order by sal;
10) For each job in each grade find the average yearly income. Order results by jobs.
10) select avg(12*E.sal+NVL(comm,0)),job,grade from EMP E, SALGRADE S where
E.sal between S.losal and S.hisal group by E.job, S.grade
order by job;