2. On the oracle tab, find the employees whose salaries are higher than the average salaries of the Department.

2. On the oracle tab, find the employees whose salaries are higher than the average salaries of the Department.
 ROWNUM knowledge point

A rownum is generated according to the default oracle mechanism.

B rownum can only use <= <No., cannot use> =

Rownum Implementation Mechanism

Rownum indicates the row number of the returned result set (it is an attribute that is solidified into a row and will not change because of your sorting ). Without the first line, there is no second line; without the second line, there is no third line.

Oracle Top-N

Select rownum, empno, ename, sal From (select empno, ename, sal From emp Order by sal desc) Where rownum <= 3;

4. Calculate the data between 5 = <x <= 8 (paging)

Select r, empno, ename, sal From ( Select rownum r, empno, ename, sal From ( Select empno, ename, sal From emp Order by sal desc ) Where rownum <= 8 ) Where r> = 5;

Conclusion: Memory sorting and outer Selection

Summary of Oracle paging ideas:

(Sorting in the inner layer, selecting in the outer layer, requiring layer-3 queries)

Internal: Sorting

Medium: select the first n rows using rownum, and specify an alias for rownum for filtering at the outermost layer.

Outer: remove the first m results.

Find the employees whose salaries are higher than the average salaries of the Department in the employee table

Employee table average salary of the Department

Train of Thought 1: Check employees' salaries average salaries of the Department

Department relationship

The salary of the Department should be requested, and the Department should be grouped into groups.

Idea 2: Search for the employee table and department salary table ====

Idea 3: equivalent join conditions

Method 1:

Select e. empno, e. ename, e. sal, d. avgsal From emp e, (Select deptno, avg (sal) avgsal From emp Group by deptno) d Where e. deptno = d. deptno and e. sal> d. avgsal;

Method 2

Related subqueries: the parameters of the primary query, which are used for subqueries. Generally, aliases are used.

Subquery: The subquery result, which is used by the primary query.

Select empno, ename, sal, (select avg (sal) from emp where deptno = e. deptno) avgsal From emp e Where sal> ( Select avg (sal) From emp Where deptno = e. deptno );