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