Oracle subquery statement

The Oracle subquery statement sets another subquery statement in one select statement: the name of the person with the highest salary in emp: SQL> select ename, sal from emp where sal = (select max (sal) from emp); persons with higher salaries than average: SQL> select ename, sal from where sal> (select avg (sal) from emp); queries the persons with the highest salaries in each department: SQL> select ename, sal from emp join (select max (sal) max_sal, deptno from emp group by deptno) ton (emp. sal = t. max_sal and emp. deptno = t. deptno); query the average salary of a department: SQL> select avg (sal), deptno from emp group by deptno; query the employee ID, name, and employee MANAGER: SQL> select empno, ename, mgr from emp; query who is the manager of each employee: SQL> select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno; rank the employee's salary: SQL> select ename, dname, grade from emp e, dept d, salgrade swhere e. deptno = d. deptno and e. sal between s. losal and s. hisal and job <> 'cler'; average salary level of the Department: SQL> select deptno, avg_sal, grade from, (select deptno, avg (sal) avg_sal from emp group by deptno) tjoin salgrade s on (t. avg_sal between s. losal and s. hisal); average department salary level: SQL> select avg (grade) from (select deptno, ename, grade from emp join salgrade son (emp. sal between s. losal and s. hisal) t group by deptno;) SQL> select deptno, avg (grade) from (select deptno, ename, grade from emp join salgrade) son (emp. sal between s. losal and s. hisal) t group by deptno; the employees are managers: SQL> select ename from emp where empno in (select mgr from emp); group functions are not allowed, maximum salary: (this is the maximum value in the middle) SQL> select distinct e1.sal from emp e1 join emp e2on (e1.sal <e2.sal); (except the maximum value without the maximum value) SQL> select distinct sal from emp where sal not in (select distinct e1.sal from emp e1 join emp e2) on (e1.sal <e2.sal>); Department ID of the Department with the highest average salary: calculate the average salary of each department: SQL> select deptno, avg (sal) from emp group by deptno; find the highest average salary of all departments: SQL> select max (sal_avg) from (select avg (sal) sal_avg, deptno from emp group by deptno); obtain the Department Number of the average highest salary: SQL> select deptno, avg_sal from (select avg (sal) avg_sal, deptno from emp group by deptno) where avg_sal = (select max (avg_sal) from (select avg (sal) avg_sal, deptno from emp group by deptno )); department name for the highest average salary: SQL> select dname from dept where deptno = (select deptno from (select avg (sal) avg_sal, deptno from emp group by deptno) where avg_sal = (select max (avg_sal) from (select avg (sal) avg_sal, deptno from emp group by deptno); find the name of the Department with the lowest average salary level: the average salary of each department is obtained: SQL> select deptno, grade, avg_sal from (select deptno, avg (sal) avg_sal from emp group by deptno) tjoin salgrade s on (t. avg_sal between s. losal and s. hisal); obtained the department with the lowest salary: SQL> select min (grade) from (select deptno, grade, avg_sal from (select deptno, avg (sal) avg_sal from emp group by deptno) tjoin salgrade s on (t. avg_sal between s. losal and s. hisal); find the name of the Department with the lowest average salary level: Create a view to simplify the process: the name of the manager with the lowest average salary in the department manager, which is higher than the highest salary in the ordinary employee: (find the highest salary in the ordinary employee) select max (sal) from emp where empno not in (select distinct mgr from emp where mgr is not null); select ename from emp where empno in (select distinct mgr from emp where mgr is not null) and sal> (select max (sal) from emp where empno not in (select distinct mgr from emp where mgr is not null); interview questions: Compare the efficiency of the two statements: select * from emp where deptno = 10 and ename like '% A %'; select * from emp where ename like '% A %' and deptno = 10; the first efficiency will be high (many items can be excluded by number); the top five employees with the highest salary: select empno, ename from emp; rownum: equivalent to the row number: it can only be used with values less than or equal to. It cannot be used with values greater than or equal to: select empno, ename from emp where rownum <5; select empno. ename from emp where rownum <= 5; (query for rows greater than 10) select rownum r, ename from emp; select ename from (select rownum r, ename from emp) where r> 10; (query the persons with the highest salary in reverse order) select ename, sal from emp order by sal desc; select ename, sal from (select ename, sal from emp order by sal desc) where rownum <= 5; sixth to tenth: (view rownum) select ename, sal, rownum r from emp order by sal desc; (sort rownum) select ename, sal, rownum r from (select ename, sal from emp order by sal desc); select ename, sal from (select ename, sal, rownum r from (select ename, sal from emp order by sal desc ));