Join query and group query intensive exercises

--Ask for the name, salary, and salary level of each employee

Select A.ename, A.sal, B.grade from emp A, Salgrade bwhere a.sal between B.losal and B.hisal;

--Ask for the name, department name, department number of each employee

Select A.ename, B.dname, B.deptno from  emp A, dept b where A.deptno = B.deptno;

--Ask everyone's name and his manager's name, and take the king out with an external connection.

Select A.ename, B.ename mgrname from emp A, emp bwhere a.mgr = b.empno;

Select A.ename, B.ename mgrname from emp A, emp bwhere a.mgr = b.empno (+);

--to find out who is the most paid in the company

SELECT * from emp a WHERE a.sal = (select Max (SAL) from EMP);

--The name and salary of the employee who asks for salary above the average salary of the department

Select A.ename, A.sal, A.deptno from  emp A, (select AVG (SAL) avgsal, deptno from EMP Group by Deptno) b where A.sal &G T B.avgsal and   a.deptno = B.deptno;

--ask for the highest paid employee number in each department, employee name, department number, department name

The first step is to ask for the highest salary for each department

Select Max (SAL) maxsal, deptno from EMP Group by DEPTNO;

The second step is to match the maximum wage of each department and inquire the name of the highest wage employee

Select A.empno, A.ename, B.deptno, c.dname from  emp A,       (select Max (SAL) maxsal, deptno from EMP Group by DEPTNO) b ,       dept C where A.deptno = B.deptno and   B.deptno = C.deptno and   a.sal = b.maxsal;

--The grade of the department average salary

Select A.sal, B.grade from  (select AVG (sal) Sal, deptno from EMP Group by Deptno) A, Salgrade b where a.sal between B. Losal and B.hisal;

--to find the average salary level of the Department

Follow up on SQL

-who are the managers of the employees?

SELECT * from emp where job in (' MANAGER ', ' president ', ' ANALYST ');

-A manager's name that is higher than the average employee's salary

Select Ename, sal from  emp where sal > (select Max (sal) from                EMP               where job is not in (' MANAGER ', ' President ', ' Analyst ')   and job in (' MANAGER ', ' president ', ' Analyst ');

- the top 5 employees with the highest salary

Select C.ename, c.sal from  (select B.ename, B.sal, rownum rn from          (select A.ename, a.sal from emp A order by Sal D ESC) b) c where C.rn <= 5;

--The name of the employee with the highest salary of 6 to 10 employees

Select C.ename, c.sal from  (select B.ename, B.sal, rownum rn from          (select A.ename, a.sal from emp A order by Sal D ESC) b) c where c.rn between 6 and 10;

--5 employees for last entry

Select C.ename, c.hiredate from  (select B.ename, B.hiredate, rownum rn from          (select A.ename, a.hiredate from EMP A ORDER BY hiredate Desc) b) c where C.rn <= 5;

-The top two salaried employees in each department

SELECT * from EMP e where (SELECT count (*) from EMP where Sal > e.sal and deptno = E.deptno) < 2order by E.deptno, E.sal desc;

--Don't use the group function to find the highest salary

Select A.sal from (SELECT * to emp ORDER BY Sal Desc) a WHERE rownum = 1;select e.sal from emp e where e.sal not in (Sele CT e2.sal from emp e1,emp E2 where e1.sal>e2.sal), select distinct Sal from Empminusselect distinct e2.sal from emp e1,e MP E2 where e1.sal>e2.sal;

--Find the name of the department with the highest average salary

Select A.dname, B.deptno from  dept A,       (select AVG (SAL) avgsal, deptno from EMP Group by Deptno) B,       (select Max (a.avgsal) Maxsal from          (select AVG (SAL) avgsal, deptno from EMP Group by Deptno) a) c where A.deptno = B.deptno   an D b.avgsal = c.maxsal;

The above topics are done by themselves, if there are errors, please correct me, thank you.

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

Join query and group query intensive exercises