Oracle hands-on series (2)

-------- 9. list all employees whose salaries are higher than the company's average salaries. ----------
Select ename from emp where sal> (select avg (sal) from emp );
9.1 list all employees whose salaries are higher than the average salaries of their respective departments
First
Select f. ename, f. sal from emp f,
(
Select emp. deptno, avg (emp. sal) av from emp
Group by emp. deptno
) F2 where f. deptno = f2.deptno and f. sal> f2.av;
Second
Select emp. ename, emp. sal from emp
Where emp. sal> (
Select avg (e2.sal) from emp e2 where e2.deptno = emp. deptno
);
Third
Select f. ename, f. sal from emp f,
(
Select distinct emp. deptno, avg (emp. sal) over (partition by emp. deptno) av from emp
) F2 where f. deptno = f2.deptno and f. sal> f2.av;
 
-- 12. List the names and salaries of employees whose salaries are higher than the salaries of all employees who work in the Department 30. ---------

Select ename, sal from emp where sal> (select max (sal) from emp where deptno = 30 );

Select ename, sal from emp where sal> all (select sal from emp where deptno = 30 );
Author "simple"