MySQL数据库第二张纸的代码

-- 统计查询
-- 12. 获取部门 ID 为 90 的员工的总薪水、最大薪水、最小薪水和平均薪水（按工作 ID 分）
SELECT job_id, 
       SUM(salary) AS total_salary, 
       MAX(salary) AS max_salary, 
       MIN(salary) AS min_salary, 
       AVG(salary) AS avg_salary 
FROM employee 
WHERE department_id = 90 
GROUP BY job_id;

-- 13. 获取最大薪水大于或等于 $4000 的员工的工作 ID 和最大薪水
SELECT job_id, MAX(salary) AS max_salary 
FROM employee 
GROUP BY job_id 
HAVING max_salary >= 4000;

-- 14. 获取雇佣超过 10 名员工的所有部门的平均薪水
SELECT department_id, AVG(salary) AS avg_salary 
FROM employee 
GROUP BY department_id 
HAVING COUNT(*) > 10;

-- 子查询
-- 1. 查找薪水高于姓为 Bull 的员工的员工名（first_name, last_name）和薪水
SELECT first_name, last_name, salary 
FROM employee 
WHERE salary > (SELECT salary FROM employee WHERE last_name = 'Bull');

-- 2. 查找所有在 IT 部门工作的员工的名字（first_name, last_name）
SELECT first_name, last_name 
FROM employee 
WHERE department_id = (SELECT department_id FROM department WHERE department_name = 'IT');

-- 3. 查找有经理且在美国部门工作的员工的名字（first_name, last_name）
SELECT first_name, last_name 
FROM employee 
WHERE manager_id IS NOT NULL 
AND department_id IN (SELECT department_id FROM department WHERE location_id IN (SELECT location_id FROM location WHERE country = 'USA'));

-- 4. 查找员工中是经理的员工名字（first_name, last_name）
SELECT DISTINCT e.first_name, e.last_name 
FROM employee e 
WHERE e.employee_id IN (SELECT DISTINCT manager_id FROM employee);

-- 5. 查找薪水高于平均薪水的员工的名字（first_name, last_name）和薪水
SELECT first_name, last_name, salary 
FROM employee 
WHERE salary > (SELECT AVG(salary) FROM employee);

-- 6. 查找薪水等于其工作等级的最低薪水的员工的名字（first_name, last_name）和薪水
SELECT first_name, last_name, salary 
FROM employee 
WHERE salary = (SELECT MIN(salary) FROM employee WHERE job_id = employee.job_id);

-- 7. 查找薪水高于平均薪水并且在任意 IT 部门工作的员工的名字（first_name, last_name）和薪水
SELECT first_name, last_name, salary 
FROM employee 
WHERE salary > (SELECT AVG(salary) FROM employee WHERE department_id IN (SELECT department_id FROM department WHERE department_name LIKE 'IT%'));

-- 8. 查找薪水高于 Bell 先生薪水的员工的名字（first_name, last_name）和薪水
SELECT first_name, last_name, salary 
FROM employee 
WHERE salary > (SELECT salary FROM employee WHERE last_name = 'Bell');

-- 9. 查找薪水与所有部门最低薪水相同的员工的名字（first_name, last_name）和薪水
SELECT first_name, last_name, salary 
FROM employee 
WHERE salary IN (SELECT MIN(salary) FROM employee GROUP BY department_id);

-- 10. 查找薪水高于所有部门的平均薪水的员工的名字（first_name, last_name）和薪水
SELECT first_name, last_name, salary 
FROM employee 
WHERE salary > (SELECT AVG(salary) FROM employee);

-- 11. 查找薪水高于所有运输文员（JOB_ID ='SH_CLERK'）薪水的员工的名字（first_name, last_name）和薪水，并按薪水从低到高排序
SELECT first_name, last_name, salary 
FROM employee 
WHERE salary > (SELECT MAX(salary) FROM employee WHERE job_id = 'SH_CLERK') 
ORDER BY salary ASC;

-- 12. 查找不是主管的员工的名字（first_name, last_name）
SELECT first_name, last_name 
FROM employee 
WHERE employee_id NOT IN (SELECT DISTINCT manager_id FROM employee);

-- 13. 显示所有员工的员工 ID、名字（first_name, last_name）和部门名称
SELECT e.employee_id, e.first_name, e.last_name, d.department_name 
FROM employee e 
JOIN department d ON e.department_id = d.department_id;

-- 其他查询
-- 14. 显示薪水高于其部门平均薪水的所有员工的员工 ID、名字（first_name, last_name）、薪水
SELECT employee_id, first_name, last_name, salary 
FROM employee 
WHERE salary > (SELECT AVG(salary) FROM employee WHERE department_id = employee.department_id);

-- 15. 查找员工表中的第 5 大薪水
SELECT DISTINCT salary 
FROM employee 
ORDER BY salary DESC 
LIMIT 1 OFFSET 4;

-- 16. 查找员工表中的第 4 小薪水
SELECT DISTINCT salary 
FROM employee 
ORDER BY salary ASC 
LIMIT 1 OFFSET 3;

-- 17. 选择表中的最后 10 条记录
SELECT * 
FROM employee 
ORDER BY employee_id DESC 
LIMIT 10;

-- 18. 列出没有员工工作的所有部门的部门 ID 和名称
SELECT department_id, department_name 
FROM department 
WHERE department_id NOT IN (SELECT DISTINCT department_id FROM employee);

-- 19. 获取 3 个最高薪水
SELECT DISTINCT salary 
FROM employee 
ORDER BY salary DESC 
LIMIT 3;

-- 20. 获取 3 个最低薪水
SELECT DISTINCT salary 
FROM employee 
ORDER BY salary ASC 
LIMIT 3;

-- 连接查询
-- 1. 查找所有部门的地址（location_id, street_address, city, state_province, country_name）
SELECT d.location_id, d.street_address, d.city, d.state_province, c.country_name 
FROM department d 
NATURAL JOIN location l 
JOIN country c ON l.country_id = c.country_id;

-- 2. 查找员工的名字（first_name, last_name）、部门 ID 和部门名称
SELECT e.first_name, e.last_name, e.department_id, d.department_name 
FROM employee e 
JOIN department d ON e.department_id = d.department_id;

-- 3. 查找在伦敦工作的员工的名字（first_name, last_name）、工作、部门 ID 和部门名称
SELECT e.first_name, e.last_name, e.job_id, d.department_name 
FROM employee e 
JOIN department d ON e.department_id = d.department_id 
WHERE d.city = 'London';

-- 4. 查找员工 ID、名字（last_name）、经理 ID 和经理名字（last_name）
SELECT e.employee_id, e.last_name, e.manager_id, m.last_name AS manager_last_name 
FROM employee e 
LEFT JOIN employee m ON e.manager_id = m.employee_id;

-- 5. 查找在 Jones 之后被雇佣的员工的名字（first_name, last_name）和雇佣日期
SELECT first_name, last_name, hire_date 
FROM employee 
WHERE hire_date > (SELECT hire_date FROM employee WHERE last_name = 'Jones');

-- 6. 获取部门名称及部门中员工数量
SELECT d.department_name, COUNT(e.employee_id) AS number_of_employees 
FROM department d 
LEFT JOIN employee e ON d.department_id = e.department_id 
GROUP BY d.department_name;

-- 7. 查找员工 ID、工作标题、结束日期和开始日期之间的天数
SELECT employee_id, job_id, DATEDIFF(end_date, start_date) AS days_between 
FROM job_history 
WHERE department_id = 90;

-- 8. 显示部门 ID 和名称以及经理的名字
SELECT d.department_id, d.department_name, e.first_name AS manager_first_name 
FROM department d 
JOIN employee e ON d.manager_id = e.employee_id;

-- 9. 显示部门名称、经理姓名和城市
SELECT d.department_name, e.first_name AS manager_first_name, d.city 
FROM department d 
JOIN employee e ON d.manager_id = e.employee_id;

-- 10. 显示工作标题和员工的平均薪水
SELECT job_id, AVG(salary) AS average_salary 
FROM employee 
GROUP BY job_id;

-- 11. 显示工作标题、员工名字，以及员工薪水与最低薪水之间的差额
SELECT e.job_id, e.first_name, e.last_name, (e.salary - (SELECT MIN(salary) FROM employee WHERE job_id = e.job_id)) AS salary_difference 
FROM employee e;

-- 12. 显示当前薪水超过 $10,000 的员工所做的工作历史
SELECT * 
FROM job_history 
WHERE employee_id IN (SELECT employee_id FROM employee WHERE salary > 10000);

-- 13. 显示部门名称、经理的名字（first_name, last_name）、雇佣日期和薪水，所有经理经验超过 15 年
SELECT d.department_name, e.first_name, e.last_name, e.hire_date, e.salary 
FROM department d 
JOIN employee e ON d.manager_id = e.employee_id 
WHERE YEAR(CURDATE()) - YEAR(e.hire_date) > 15;