Oracle Learning Notes (iii)

1. Title: Find employees who pay more than the average salary of the department.

Select ename from EMP E1 where sal> (select AVG (SAL) from EMP E2 where E1.deptno=e2.deptno);

2. Title: Find employee information for the top three employees in the employee table

Ideas:

1) First query the data according to the salary sort

2) Then make the queried data as a table

3) Re-query the first three data in this table

SELECT * FROM (select Sal from emp ORDER by DESC) where rownum<=3;

RowNum is a pseudo-column that represents the line number recorded in the result set.
1,rownum once generated, it does not change (the rownum is generated in the default order when no sorting is performed, and then the sort is executed).
2, for rownum, can only use < and <=, can not use > and >= and =.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Oracle Learning Notes (iii)