2.9
SQL> select e.ename, e.sal, d.dname from scott.emp e, scott.dept d
  2  where e.sal > (select avg(sal) from scott.emp where e.deptno = deptno)
  3  and e.deptno = d.deptno
  4  order by e.sal;

ENAME             SAL DNAME
---------- ---------- --------------
ALLEN            1600 SALES
BLAKE            2850 SALES
JONES            2975 RESEARCH
SCOTT            3000 RESEARCH
FORD             3000 RESEARCH
KING             5000 ACCOUNTING

6 rows selected.





2.10
SQL> select d.dname, avg(e.sal)
  2  from scott.emp e, scott.dept d
  3  where 2500 < (select avg(sal) from scott.emp where deptno = e.deptno)
  4  and d.deptno = e.deptno
  5  group by d.dname;

DNAME          AVG(E.SAL)
-------------- ----------
ACCOUNTING     2916.66667


2.11
SQL> select e.ename, e.sal, d.dname from scott.emp e, scott.dept d
  2  where e.sal > (select avg(sal) from scott.emp where e.deptno = deptno)
  3  and e.deptno = d.deptno
  4  and 2000 < (select avg(sal) from scott.emp where deptno = e.deptno)
  5  order by e.sal;

ENAME             SAL DNAME
---------- ---------- --------------
JONES            2975 RESEARCH
FORD             3000 RESEARCH
SCOTT            3000 RESEARCH
KING             5000 ACCOUNTING