mysql的一些题目,有难度
案例题目,有些题目有难度,自己动手,丰衣足食!
直接po代码
#案例题目
# 1. 查询工资最低的员工信息: last_name, salary
SELECT
last_name,
salary
FROM
employees
WHERE salary =
(SELECT
MIN(salary)
FROM
employees) ;
# 2. 查询平均工资最低的部门信息
#每个部门的平均工资
SELECT * FROM
(SELECT
AVG(salary) AS avg_sal,
department_id
FROM
employees
GROUP BY department_id) AS emp;
#所有部门中,平均工资最低的
SELECT MIN(emp.avg_sal) FROM
(SELECT
AVG(salary) AS avg_sal,
department_id
FROM
employees
GROUP BY department_id) AS emp;
#
SELECT
AVG(salary) AS avg_sal,
department_id
FROM
employees
GROUP BY department_id
HAVING AVG(salary) = (
SELECT MIN(emp.avg_sal) FROM
(SELECT
AVG(salary) AS avg_sal,
department_id
FROM
employees
GROUP BY department_id) AS emp);
#
SELECT dep.* FROM departments dep WHERE
department_id = (
SELECT
department_id
FROM
employees
GROUP BY department_id
HAVING AVG(salary) = (
SELECT MIN(emp.avg_sal) FROM
(SELECT
AVG(salary) AS avg_sal,
department_id
FROM
employees
GROUP BY department_id) AS emp));
#有好多种写法
#方式一
SELECT
department_id,
AVG(salary) AS avg_sal
FROM
employees
GROUP BY department_id
ORDER BY avg_sal ASC
LIMIT 0, 1;
#或者
SELECT
department_id,
AVG(salary) AS avg_sal
FROM
employees
GROUP BY department_id
ORDER BY avg_sal ASC
LIMIT 1;
#方式二
SELECT * FROM
(SELECT
department_id,
AVG(salary) AS avg_sal
FROM
employees
GROUP BY department_id) AS emp01
INNER JOIN
(SELECT MIN(avg_sal_emp.avg_sal) AS min_sal FROM (
SELECT
AVG(salary) AS avg_sal
FROM
employees
GROUP BY department_id) AS avg_sal_emp) emp02
ON emp01.avg_sal = emp02.min_sal;
#
SELECT * FROM departments WHERE department_id = (
SELECT department_id FROM
(SELECT
department_id,
AVG(salary) AS avg_sal
FROM
employees
GROUP BY department_id) AS emp01
INNER JOIN
(SELECT MIN(avg_sal_emp.avg_sal) AS min_sal FROM (
SELECT
AVG(salary) AS avg_sal
FROM
employees
GROUP BY department_id) AS avg_sal_emp) emp02
ON emp01.avg_sal = emp02.min_sal);
# 3. 查询平均工资最低的部门信息和该部门的平均工资
SELECT dep.*, avg_dep.avg_sal FROM departments dep
INNER JOIN
(SELECT
department_id AS dep_id,
AVG(salary) AS avg_sal
FROM
employees
GROUP BY department_id
ORDER BY avg_sal ASC
LIMIT 0, 1) AS avg_dep
ON dep.department_id = avg_dep.dep_id;
#
SELECT dep.*,
(SELECT
AVG(salary) AS avg_sal
FROM
employees emp
WHERE emp.department_id = dep.`department_id`
GROUP BY department_id
ORDER BY avg_sal ASC
LIMIT 0, 1) AS avg_salary
FROM departments dep;
#select后面仅仅支持标量子查询(标量子查询即结果集只有一行一列,又叫做单行子查询)
#最终效果(有难度)
SELECT dep.*,
(SELECT
AVG(salary) AS avg_sal
FROM
employees emp
WHERE emp.department_id = dep.`department_id`
GROUP BY department_id
ORDER BY avg_sal ASC
LIMIT 0, 1) AS avg_salary
FROM departments dep
HAVING avg_salary IS NOT NULL
ORDER BY avg_salary ASC
LIMIT 0, 1;
# 4. 查询平均工资最高的 job 信息
SELECT AVG(salary) AS avg_sal,job_id FROM employeeS
GROUP BY job_id
ORDER BY avg_sal DESC
LIMIT 0, 1;
#
SELECT * FROM jobs WHERE job_id = (
SELECT job_id FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 0, 1);
# 5. 查询平均工资高于公司平均工资的部门有哪些?
SELECT AVG(salary) AS avg_dep,department_id FROM employees GROUP BY department_id
HAVING avg_dep >(
SELECT AVG(salary) FROM employees);
# 6. 查询出公司中所有 manager 的详细信息.
SELECT * FROM employees WHERE employee_id IN (
SELECT DISTINCT manager_id FROM employees emp);
#或者使用ANY
SELECT * FROM employees WHERE employee_id = ANY (
SELECT DISTINCT manager_id FROM employees emp);
# 7. 各个部门中 最高工资中最低的那个部门的 最低工资是多少
SELECT MIN(salary) FROM employees WHERE department_id = (
SELECT department_id FROM employees GROUP BY department_id
ORDER BY MAX(salary) ASC LIMIT 0, 1);
#select min(salary) from employees where department_id = 10;
#select * from employees where department_id = 10;
# 8. 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
#
SELECT AVG(salary) AS avg_sal, department_id FROM employees GROUP BY
department_id ORDER BY avg_sal DESC LIMIT 0, 1;
#最终效果
SELECT employee_id, last_name, department_id, email, salary FROM employees WHERE
employee_id IN (
SELECT DISTINCT manager_id FROM employees WHERE department_id = (
SELECT department_id FROM employees GROUP BY
department_id ORDER BY AVG(salary) DESC LIMIT 0, 1
));
#最终效果
SELECT
last_name, d.department_id, email, salary
FROM
employees e
INNER JOIN departments d
ON d.manager_id = e.employee_id
WHERE d.department_id =
(SELECT
department_id
FROM
employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1) ;
#