Oracle basic statement Learning (3) subquery

1 subquery ex: Find the name, whose salary is above the average salary selectename, salfromempwheresal (selectavg (sal) fromemp); 2. See table subquery for the persons with the highest salary in each department. intuitive method selectename, salfromempwheresalin (selectmax (sal) fromempgroupbydept

1 subquery ex: name, whose salary is above the average wage select ename, sal from emp where sal (select avg (sal) from emp ); 2. See the subquery for the persons with the highest salaries in each department. intuitive method select ename, sal from emp where sal in (select max (sal) from emp group by dept

1 subquery

Ex: name. The salary is above the average salary.

Select ename, sal from emp where sal> (select avg (sal) from emp );

2. See table subquery.

Query the persons with the highest salaries in each department.

Intuitive method select ename, sal from emp where sal in (select max (sal) from emp group by deptno );

This query method may cause an error. If the highest salary of Department A is 5000 while that of department A's p employee is 3000, which is equal to the highest salary of department B, the employee will also be shown;

The correct method is as follows:

First, we can find that select max (sal) from emp group by deptno can get a table, as shown in

You only need to find the employees with the same department and salary in emp.

The statement is as follows:

1 select ename, sal from emp

2 join (select max (sal) max_sal, deptno from emp group by deptno) t // connect table

3 on (emp. sal = t. max_sal and emp. deptno = t. deptno); // connection Condition