Mysql基础--子查询经典案例题目

1. 查询工资最低的员工信息

SELECT last_name,salary
FROM employees 
WHERE salary=(
	SELECT MIN(salary) 
	FROM employees);

2. 查询平均工资最低的部门信息

SELECT * FROM departments WHERE department_id=(
	SELECT department_id FROM(
		SELECT department_id,AVG(salary) avgs FROM employees GROUP BY department_id) AS new_table ORDER BY avgs LIMIT 1);

3. 查询平均工资最低的部门信息和该部门的平均工资

SELECT departments.*,MIN(avgs) FROM(
	SELECT department_id,AVG(salary) avgs FROM employees GROUP BY department_id)AS new_table 
JOIN departments ON departments`.`department_id=new_table.department_id;

查询xx信息的时候 只需要一个job_id或者department_id 不需要连接，而查询XX信息和平均工资的时候用不了=，所以需要连接

4. 查询平均工资最高的 job 信息

SELECT * FROM jobs WHERE job_id=(
	SELECT job_id FROM employees GROUP BY job_id ORDER BY AVG(salary) DESC LIMIT 1);

5. 查询平均工资高于公司平均工资的部门有哪些

SELECT department_id,AVG(salary) FROM employees GROUP BY department_id HAVING AVG(salary)>(
	SELECT AVG(salary) FROM employees);

6. 查询出公司中所有 manager 的详细信息

SELECT * FROM employees WHERE employee_id IN(SELECT DISTINCT manager_id FROM employees);

7. 各个部门中 最高工资中最低的那个部门的最低工资是多少

SELECT MIN(salary) FROM employees WHERE department_id=(
	SELECT department_id FROM employees GROUP BY department_id ORDER BY MAX(salary) LIMIT 1);

8. 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary

SELECT last_name,department_id,email,salary FROM employees WHERE employee_id=(
	SELECT manager_id FROM departments WHERE department_id=(
		SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) DESC LIMIT 1));