Advanced classic examples of mysql subquery

Query the information that the average salary of the department is the lowest.

Law 1: first find the department number where the average wage is equal to the minimum average wage, and then match the department table as a screening condition

SELECT d.*FROM departments dWHERE d.departmentprincipid = (SELECT department_id FROM employees GROUP BY department_id HAVING AVG (salary) = (SELECT MIN (a) FROM (SELECT AVG (salary) a departmentoriginid FROM employees GROUP BY department_id) b)

Or

Method 2: through sorting and then LIMIT directly finds the lowest-paid department label, and then matches the department table

SELECT d.*FROM departments dWHERE d.departmentorganizid1 = (SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG (salary) ASC LIMIT 1)

Inquire about the department with the lowest average wage and the average salary in that department

Method: join the department table with the table with the minimum average wage, and then query

SELECT D. Magna from departments dINNER JOIN (SELECT AVG (salary) a FROM employees GROUP BY department_id ORDER BY AVG (salary) ASC LIMIT 1) bON d.department_id=b.department_id

Query the job information with the highest average salary

SELECT * FROM jobsWHERE jobs.`job _ id` = (SELECT job_id FROM employees e GROUP BY e.job_id ORDER BY AVG (salary) DESC LIMIT 1)

Inquire about some departments whose average wages are higher than the average wages of the company.

Law: find the table where the average salary is higher than the average salary of the company, and then connect with the department table

SELECT department_nameFROM departments dINNER JOIN (SELECT AVG (salary), department_id FROM employees GROUP BY department_id HAVING AVG (salary) > (SELECT AVG (salary) FROM employees)) aWHERE d.department_id=a.department_id

Query the details of all the manager in the company in the employees table

SELECT * FROM employeesWHERE employee_id IN (SELECT manager_id FROM employees)

Inquire about the minimum wage of the department with the highest wage in each department.

SELECT MIN (e.salary) FROM employees eWHERE e.departmentorganizid1 = (SELECT department_id FROM employees GROUP BY department_id ORDER BY MAX (salary) DESC LIMIT 1)

Check the manager details of the department with the highest average salary: last_name,department_id,email,salary

SELECT e.lastroomnameree.departmentcorrecidree.emailrecovere.salaryFROM employees eINNER JOIN departments dON d.manager_id=e.employee_idWHERE d.departmentorganizid1 = (SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG (salary) DESC LIMIT 1)