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));