第05章_排序与分页
题目：
1. 查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序显示2. 选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序，显示第21到40位置的数据3. 查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序
1. 查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序显示
SELECT last_name,department_id, salary * 12 annual_sal
FROM employees
ORDER BY salary * 12 DESC, last_name ASC;
2. 选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序，显示第21到40位置的数据
SELECT last_name,salary
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary ASC
LIMIT 20,20;
3. 查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序
SELECT last_name,email,department_id
FROM employees
#WHERE email LIKE '%e%'
WHERE email REGEXP '[e]'
ORDER BY LENGTH(email) DESC,department_id ASC;
第06章_多表查询课后练习
多表查询-1
【题目】
# 1.显示所有员工的姓名，部门号和部门名称。
# 2.查询90号部门员工的job_id和90号部门的location_id
# 3.选择所有有奖金的员工的 last_name , department_name , location_id , city
# 4.选择city在Toronto工作的员工的 last_name , job_id,department_id,department_name
# 5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’
# 6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式employees Emp # manager Mgr#kochhar 101 king 100
# 7.查询哪些部门没有员工
# 8. 查询哪个城市没有部门
# 9. 查询部门名为 Sales 或 IT 的员工信息
1.显示所有员工的姓名，部门号和部门名称。
SELECT last_name,e.department_id,d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id;
2.查询90号部门员工的job_id和90号部门的location_id
SELECT e.job_id job_id,d.location_id location_id
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id = 90;
3.选择所有有奖金的员工的 last_name , department_name , location_id , city
SELECT e.last_name,d.department_name,d.location_id,l.city
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id
WHERE e.commission_pct IS NOT NULL;
4.选择city在Toronto工作的员工的 last_name , job_id,department_id,department_name
SELECT e.last_name,e.job_id,d.department_id,d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id
LEFT JOIN locations l
ON d.location_id = l.location_id
WHERE l.city = 'Toronto';
5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’
SELECT department_name,street_address,last_name,job_id,salary
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id
LEFT JOIN locations l
ON d.location_id = l.location_id
WHERE d.department_name = 'Executive';
6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式employees Emp # manager Mgr#kochhar 101 king 100
SELECT emp.last_name employees, emp.employee_id "Emp#", mgr.last_name manager,
mgr.employee_id "Mgr#"
FROM employees emp
LEFT OUTER JOIN employees mgr
ON emp.manager_id = mgr.employee_id;
7.查询哪些部门没有员工
SELECT d.department_id
FROM departments d
LEFT JOIN employees e
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;
SELECT department_id
FROM departments d
WHERE NOT EXISTS (SELECT * FROM employees eWHERE e.department_id = d.department_id
);
8. 查询哪个城市没有部门
SELECT l.location_id,l.city
FROM locations l LEFT JOIN departments d
ON l.`location_id` = d.`location_id`
WHERE d.`location_id` IS NULL
9. 查询部门名为 Sales 或 IT 的员工信息
SELECT employee_id,last_name,department_name
FROM employees e,departments d
WHERE e.department_id = d.`department_id`
AND d.`department_name` IN ('Sales','IT');
第06章_多表查询
多表查询-1
【题目】
# 1.显示所有员工的姓名，部门号和部门名称。
# 2.查询90号部门员工的job_id和90号部门的location_id
# 3.选择所有有奖金的员工的 last_name , department_name , location_id , city
# 4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name
# 5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’
# 6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式employees Emp # manager Mgr#kochhar 101 king 100
# 7.查询哪些部门没有员工
# 8. 查询哪个城市没有部门
# 9. 查询部门名为 Sales 或 IT 的员工信息
1.显示所有员工的姓名，部门号和部门名称。
SELECT last_name,e.department_id,department_name
FROM employees e
JOIN depatments d
ON e.`department_id` = d.`department_id`;
2.查询90号部门员工的job_id和90号部门的location_id
SELECT job_id,location_id
FROM employees e
JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` = 90;
SELECT job_id,location_id
FROM employees e,departments d
WHERE e.`department_id` = d.`department_id`
AND e.`department_id` = 90;
3.选择所有有奖金的员工的 last_name , department_name , location_id , city
SELECT last_name,department_name,d.location_id,city
FROM employees e
LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
LEFT JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE commission_pct IS NOT NULL;
4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name
SELECT last_name,job_id,e.department_id,department_name
FROM employees e
LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
LEFT JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE city = 'Toronto';
5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’
SELECT last_name,job_id,salary,department_name,street_address
FROM employees e
LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
LEFT JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE department_name = 'Executive';
SELECT last_name , job_id , e.department_id , department_name
FROM employees e, departments d, locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`
AND city = 'Toronto';
6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
employees Emp # manager Mgr#kochhar 101 king 100
SELECT emp.last_name employees,emp.employee_id "Emp#",mgr.last_name manager,mgr.employee_id "Mgr#"
FROM employees emp
LEFT JOIN employees mgr
ON emp.`manager_id` = mgr.`employee_id`;
7.查询哪些部门没有员工
SELECT d.department_id
FROM departments d
LEFT JOIN employees e
ON e.`department_id` = d.`department_id`
WHERE e.department_id IS NULL;
8. 查询哪个城市没有部门
SELECT l.location_id,l.city
FROM locations l
LEFT JOIN departments d
ON l.`location_id` = d.`location_id`
WHERE d.location_id IS NULL;
9. 查询部门名为 Sales 或 IT 的员工信息
SELECT employee_id,last_name,department_name
FROM employees e,departments d
WHERE e.department_id = d.`department_id`
AND d.`department_name` IN ('Sales','IT');
第07章_单行函数
# 1.显示系统时间(注：日期+时间)
# 2.查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）
# 3.将员工的姓名按首字母排序，并写出姓名的长度（length）
# 4.查询员工id,last_name,salary，并作为一个列输出，别名为OUT_PUT
# 5.查询公司各员工工作的年数、工作的天数，并按工作年数的降序排序
# 6.查询员工姓名，hire_date , department_id，满足以下条件：雇用时间在1997年之后，department_id为80 或 90 或110, commission_pct不为空
# 7.查询公司中入职超过10000天的员工姓名、入职时间
# 8.做一个查询，产生下面的结果
<last_name> earns <salary> monthly but wants <salary*3>
# 9.使用case-when，按照下面的条件：
job grade
AD_PRES A
ST_MAN B
IT_PROG C
SA_REP D
ST_CLERK E
1.显示系统时间(注：日期+时间)
SELECT NOW()
FROM DAUL;
2.查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）
SELECT employee_id,last_name,salary,salary * 1.2 "new salary"
FROM employees;
3.将员工的姓名按首字母排序，并写出姓名的长度（length）
SELECT last_name,length(last_name)
FROM employees
ORDER BY last_name DESC;
4.查询员工id,last_name,salary，并作为一个列输出，别名为OUT_PUT
SELECT CONCAT(employee_id, ',' ,last_name, ',' ,salary) OUT_PUT
FROM employees;
5.查询公司各员工工作的年数、工作的天数，并按工作年数的降序排序
SELECT DATEIFF(SYSDATE(), hire_date) / 365 worked_years,DATEIFF(SYSDATE(),hire_date) worked_days
FROM employees
ORDER BY worked_days DESC;
6.查询员工姓名，hire_date , department_id，满足以下条件：雇用时间在1997年之后，department_id为80 或 90 或110, commission_pct不为空
SELECT last_name,hire_date,department_id
FROM employees
#WHERE hire_date >= 1997;
#WHERE hire_date >= STR_TO_DATE('1997-01-01', '%Y-%m-%d')
WHERE DATE_FORMAT(hire_date, '%Y') >= '1997'
AND department_id IN (80, 90, 110)
AND commission_pct IS NOT NULL;
7.查询公司中入职超过10000天的员工姓名、入职时间
SELECT last_name,hire_date
FROM employees
#WHERE TO_DAYS(NOW()) - to_days(hire_date) > 10000;
WHERE DATEDIFF(NOW(), hire_date) > 10000;
8.做一个查询，产生下面的结果
-- <last_name> earns `<salary>` monthly but wants <salary*3>
-- Dream Salary
-- King earns 24000 monthly but wants 72000
SELECT CONCAT(last_name, ' earns ', TRUNCATE(salary, 0) , ' monthly but wants ',
TRUNCATE(salary * 3, 0)) "Dream Salary"
FROM employees;
9.使用CASE-WHEN，按照下面的条件：
-- job grade
-- AD_PRES A
-- ST_MAN B
-- IT_PROG C
-- SA_REP D
-- ST_CLERK E
-- 产生下面的结果
-- Last_name Job_id Grade
-- king AD_PRES A
SELECT last_name Last_name, job_id Job_id, CASE job_id
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
WHEN 'SA_REP' THEN 'D'
WHEN 'ST_CLERK' THEN 'E'
ELSE 'F'
END "grade"
FROM employees;
第08章_聚合函数
#1.查询公司员工工资的最大值，最小值，平均值，总和
#2.查询各job_id的员工工资的最大值，最小值，平均值，总和
#3.选择具有各个job_id的员工人数
#4.查询员工最高工资和最低工资的差距（DIFFERENCE）
#5.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
#6.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序
#7.查询每个工种、每个部门的部门名、工种名和最低工资
1.查询公司员工工资的最大值，最小值，平均值，总和
SELECT MAX(salary),MIN(salary),SUM(salary)
FROM employees;
2.查询各job_id的员工工资的最大值，最小值，平均值，总和
SELECT job_id, MAX(salary), MIN(salary), AVG(salary), SUM(salary)
FROM employees
GROUP BY job_id;
3.选择具有各个job_id的员工人数
SELECT job_id, COUNT(*)
FROM employees
GROUP BY job_id;
4.查询员工最高工资和最低工资的差距（DIFFERENCE）
SELECT MAX(salary), MIN(salary), MAX(salary) - MIN(salary) DIFFERENCE
FROM employees;
5.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
SELECT manager_id, MIN(salary)
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary) > 6000;
6.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序
SELECT department_name, location_id, COUNT(employee_id), AVG(salary) avg_sal
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
GROUP BY department_name, location_id
ORDER BY avg_sal DESC;
7.查询每个工种、每个部门的部门名、工种名和最低工资
SELECT department_name, job_id,MIN(salary)
FROM departments d LEFT JOIN employees e
ON e.`department_id` = d.`department_id`
GROUP BY department_name,job_id;
第09章_子查询案例分析
1、查询工资大于149号员工工资的员工信息
#第一步 先查询149号的工资
SELECT employee_id,salary
FROM employees
WHERE employee_id = 149;
#第二步 在进行查询员工的信息
SELECT employee_id,salary
FROM employees
WHERE salary > (SELECT salaryFROM employeesWHERE employee_id = 149);
2、返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
#第一步 查询143号员工的工资
SELECT salary
FROM employees
WHERE employee_id = 143;
# 第二步查询信息
SELECT last_name,job_id,salary
FROM employees
WHERE job_id = (SELECT job_idFROM employeesWHERE employee_id = 141)
AND salary > (SELECT salaryFROM employeesWHERE employee_id = 143);
3、返回公司工资最少的员工的last_name,job_id和salary
# 第一步 先查询工资最少的员工
SELECT MIN(salary)
FROM employees;
SELECT salary
FROM employees
ORDER BY salary ASC
LIMIT 1;
# 第二步 进行查询last_name,job_id和salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary = (SELECT MIN(salary) FROM employees);
SELECT last_name,job_id,salary
FROM employees
WHERE salary = (SELECT salaryFROM employeesORDER BY salary ASCLIMIT 1);
4、查询与141号员工的manager_id和department_id相同的其他员工的employee_id,manager_id,department_id
#第一步 查询141号员工的manager_id和department_id
SELECT manager_id
FROM employees
WHERE employee_id = 141
#######################
SELECT department_id
FROM employees
WHERE employee_id = 141#第二步 查询其他员工的employee_id,manager_id,department_id
SELECT employee_id,manager_id,department_id
FROM employees
WHERE manager_id = (SELECT manager_idFROM employeesWHERE employee_id = 141)
AND department_id = (SELECT department_idFROM employeesWHERE employee_id = 141)
AND employee_id <> 141;
5、查询最低工资大于50号部门最低工资的部门id和其最低工资
# 第一步 查询50号部门的最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id = 50;# 第二步 查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (SELECT MIN(salary)FROM employeesWHERE department_id = 50);
6、显示员工的employee_id,last_name和location,其中，若员工的department_id与location_id为1800的department_id相同，则location为’Canada’，其余则为’USA’
# 第一步 查询location_id为1800员工的department_id
SELECT department_id
FROM departments
WHERE location_id = 1800;
# 第二步
SELECT employee_id,last_name,CASE department_id WHEN (SELECT department_idFROM departmentsWHERE location_id = 1800)THEN 'Canada'ELSE 'USA' END "location"
FROM employees;
7、查询平均工资最低的部门id
# 第一步 查询每个部门的最低工资按部门分组
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id;
# 第二步 查询最低平均工资
SELECT MIN(avg_sal)
FROM (SELECT AVG(salary) avg_salFROM employeesGROUP BY department_id) dept_avg_sal;
# 1、第三步 查询平均工资最低的部门id
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (SELECT MIN(avg_sal)FROM (SELECT AVG(salary) avg_salFROM employeesGROUP BY department_id ) dept_avg_sal);
# 2、第三步 查询平均工资最低的部门id
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL(SELECT AVG(salary) avg_salFROM employeesGROUP BY department_id);
8、查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
SELECT last_name,salary,department_id
FROM employees e1
WHERE salary > (SELECT AVG(salary)FROM employees e2WHERE department_id = e1.`department_id`);
SELECT e.last_name,e.salary,e.department_id
FROM employees e,(SELECT department_id,AVG(salary) avg_salFROM employeesGROUP BY department_id) tdas
WHERE e.department_id = tdas.department_id
AND e.salary > tdas.avg_sal;
9、查询员工的id,salary,按照department_name 排序
SELECT employee_id,salary
FROM employees e
ORDER BY (SELECT department_nameFROM departments dWHERE e.`department_id` = d.`department_id`);
10、若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些员工的employee_id和其job_id
SELECT employee_id,last_name,job_id
FROM employees e
WHERE 2 <= (SELECT COUNT(*)FROM job_history jWHERE e.`employee_id` = j.`employee_id`);
11、查询公司管理者的employee_id,last_name,job_id,department_id信息
第一种：自连接
SELECT DISTINCT mgr.employee_id,mgr.last_name,mgr.job_id,mgr.department_id
FROM employees emp JOIN employees mgr
ON emp.`manager_id` = mgr.`employee_id`;
第二种：子查询
SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN (SELECT DISTINCT manager_idFROM employees);
第三种：使用EXISTS
SELECT employee_id,last_name,job_id,department_id
FROM employees e1
WHERE EXISTS (SELECT *FROM employees e2WHERE e1.`employee_id` = e2.`manager_id`);
12、查询departments表中，不存在于employees表中的部门的department_id和department_name
第一种：多表连接
SELECT d.department_id,d.department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;
第二种：使用EXISTS
SELECT department_id,department_name
FROM departments d
WHERE NOT EXISTS (SELECT *FROM employees eWHERE d.`department_id` = e.`department_id`);
13、谁的工资比Abel高？
第一种：自连接
SELECT e2.last_name,e2.salary
FROM employees e1,employees e2
WHERE e1.last_name = 'Abel'
AND e1.`salary` < e2.`salary`;
第二种：子查询
SELECT last_name,salary
FROM employees
WHERE salary > (SELECT salary FROM employeesWHERE last_name = 'Abel');
第09章_子查询 练习题
【题目】
#1.查询和Zlotkey相同部门的员工姓名和工资
#2.查询工资比公司平均工资高的员工的员工号，姓名和工资。
#3.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary
#4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
#5.查询在部门的location_id为1700的部门工作的员工的员工号
#6.查询管理者是King的员工姓名和工资
#7.查询工资最低的员工信息: last_name, salary
#8.查询平均工资最低的部门信息
#9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询）
#10.查询平均工资最高的 job 信息
#11.查询平均工资高于公司平均工资的部门有哪些?
#12.查询出公司中所有 manager 的详细信息
#13.各个部门中 最高工资中最低的那个部门的 最低工资是多少?
#14.查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary #15. 查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号
#16. 选择所有没有管理者的员工的last_name
#17．查询员工号、姓名、雇用时间、工资，其中员工的管理者为 'De Haan'
#18.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资（相关子查询）
#19.查询每个部门下的部门人数大于 5 的部门名称（相关子查询）
#20.查询每个国家下的部门个数大于 2 的国家编号（相关子查询）
1.查询和Zlotkey相同部门的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE department_id = (SELECT department_idFROM employeesWHERE last_name = 'Zlotkey');
2.查询工资比公司平均工资高的员工的员工号，姓名和工资。
SELECT employee_id,last_name,salary
FROM employees
WHERE salary > (SELECT AVG(salary)FROM employees);
3.选择工资大于所有JOB_ID = 'SA_MAN’的员工的工资的员工的last_name, job_id, salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary > ALL(SELECT salaryFROM employeesWHERE job_id = 'SA_MAN');
4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
SELECT employee_id,last_name
FROM employees
WHERE department_id = ANY(SELECT DISTINCT department_idFROM employeesWHERE last_name LIKE '%u%');
5.查询在部门的location_id为1700的部门工作的员工的员工号
SELECT employee_id
FROM employees
WHERE department_id IN(SELECT department_idFROM departmentsWHERE location_id = 1700);
6.查询管理者是King的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE manager_id IN (SELECT employee_idFROM employeesWHERE last_name = 'King');
7.查询工资最低的员工信息: last_name, salary
SELECT last_name,salary
FROM employees
WHERE salary = (SELECT MIN(salary)FROM employees);
8.查询平均工资最低的部门信息
方式一：
SELECT *
FROM departments
WHERE department_id = (SELECT department_idFROM employeesGROUP BY department_idHAVING AVG(salary) = (SELECT MIN(dept_avgsal)FROM (SELECT AVG(salary) dept_avgsalFROM employeesGROUP BY department_id) avg_sal));
方式二：
SELECT *
FROM departments
WHERE department_id = (SELECT department_idFROM employeesGROUP BY department_idHAVING AVG(salary) <= ALL(SELECT AVG(salary)FROM employeesGROUP BY department_id));
方式三：
SELECT *
FROM departments
WHERE department_id = (SELECT department_idFROM employeesGROUP BY department_idHAVING AVG(salary) = (SELECT AVG(salary) avg_salFROM employeesGROUP BY department_idORDER BY avg_salLIMIT 0,1));
方式四：
SELECT d.*
FROM departments d,(SELECT department_id,AVG(salary) avg_salFROM employeesGROUP BY department_idORDER BY avg_salLIMIT 0,1) dept_avg_sal
WHERE d.department_id = dept_avg_sal.department_id;
9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询）
方式一：
SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.department_id) avg_sal
FROM departments d
WHERE department_id = (SELECT department_idFROM employeesGROUP BY department_idHAVING AVG(salary) = (SELECT MIN(dept_avgsal)FROM (SELECT AVG(salary) dept_avgsal FROM employeesGROUP BY department_id) avg_sal));
方式二：
SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.department_id) avg_sal
FROM departments d
WHERE department_id = (SELECT department_idFROM employeesGROUP BY department_idHAVING AVG(salary) <= ALL(SELECT AVG(salary) avg_salFROM employeesGROUP BY department_id));
方式三：
SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.department_id) avg_sal
FROM departments d
WHERE department_id = (SELECT department_idFROM employeesGROUP BY department_idHAVING AVG(salary) = (SELECT AVG(salary) avg_salFROM employeesGROUP BY department_idORDER BY avg_salLIMIT 0,1));
方式四：
SELECT d.*,dept_avg_sal.avg_sal
FROM departments d,(SELECT department_id,AVG(salary) avg_salFROM employeesGROUP BY department_idORDER BY avg_salLIMIT 0,1) dept_avg_sal
WHERE d.`department_id` = dept_avg_sal.`department_id`;
10. 查询平均工资最高的 job 信息
方式一：
SELECT *
FROM jobs
WHERE job_id = (SELECT job_idFROM employeesGROUP BY job_idHAVING AVG(salary) = (SELECT MAX(avg_sal)FROM (SELECT AVG(salary) avg_salFROM employeesGROUP BY job_id) job_avgsal));
方式二：
SELECT *
FROM jobs
WHERE job_id = (SELECT job_idFROM employeesGROUP BY job_idHAVING AVG(salary) >= ALL(SELECT AVG(salary)FROM employeesGROUP BY job_id));
方式三：
SELECT *
FROM jobs
WHERE job_id = (SELECT job_idFROM employeesGROUP BY job_idHAVING AVG(salary) = (SELECT AVG(salary) avg_salFROM employeesGROUP BY job_idORDER BY avg_sal DESCLIMIT 0,1));
方式四：
SELECT j.*
FROM jobs j,(SELECT job_id,AVG(salary) avg_salFROM employeesGROUP BY job_idORDER BY avg_sal DESCLIMIT 0,1) job_avg_sal
WHERE j.`job_id` = job_avg_sal.`job_id`;
11.查询平均工资高于公司平均工资的部门有哪些?
SELECT department_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING AVG(salary) > (SELECT AVG(salary)FROM employees);
12.查询出公司中所有 manager 的详细信息
方式一：
SELECT employee_id,last_name,salary
FROM employees
WHERE employee_id IN (SELECT DISTINCT manager_idFROM employees);
方式二：
SELECT DISTINCT e1.employee_id,e1.last_name,e1.salary
FROM employees e1 JOIN employees e2
WHERE e1.`employee_id` = e2.`manager_id`;
方式三：
SELECT employee_id,last_name,salary
FROM employees e1
WHERE EXISTS (SELECT *FROM employees e2WHERE e2.manager_id = e1.employee_id);
13.各个部门中 最高工资中最低的那个部门的 最低工资是多少?
方式一：
# 第一步 先查询各部门最高工资
SELECT MAX(salary)
FROM employees
GROUP BY department_id;
# 第二步 查询最低的工资
SELECT MIN(max_sal)
FROM (SELECT MAX(salary) max_salFROM employees GROUP BY department_id) dept_max_sal;
# 第三步 查询最低工资的部门id
SELECT department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary) = (SELECT MIN(max_sal)FROM (SELECT MAX(salary) max_salFROM employees GROUP BY department_id) dept_max_sal);
# 第四步 查询最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id = (SELECT department_idFROM employeesGROUP BY department_idHAVING MAX(salary) = (SELECT MIN(max_sal)FROM (SELECT MAX(salary) max_salFROM employees GROUP BY department_id) dept_max_sal));
SELECT *
FROM employees
WHERE department_id = 10;
方式二：
# 第一步 先查询各部门的最高工资按部门id分组
SELECT MAX(salary) max_sal
FROM employees
GROUP BY department_id;
# 第二步 查询最高工资的部门id
SELECT department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary) <= ALL(SELECT MAX(salary) max_salFROM employeesGROUP BY department_id);
# 第三步 查询最高工资的部门中的最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id = (SELECT department_idFROM employeesGROUP BY department_idHAVING MAX(salary) <= ALL(SELECT MAX(salary) max_salFROM employeesGROUP BY department_id));
方式三：
SELECT MIN(salary)
FROM employees
WHERE department_id = (SELECT department_id FROM employeesGROUP BY department_id HAVING MAX(salary) = (SELECT MAX(salary) max_sal FROM employeesGROUP BY department_id ORDER BY max_salLIMIT 0,1));
方式四：
SELECT employee_id,MIN(salary)
FROM employees e,(SELECT department_id,MAX(salary) max_salFROM employeesGROUP BY department_idORDER BY max_salLIMIT 0,1) dept_max_sal
WHERE e.`department_id` = dept_max_sal.`department_id`;
14.查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
方式一：
SELECT employee_id,last_name,department_id,email,salary
FROM employees
WHERE employee_id IN(SELECT DISTINCT manager_idFROM employeesWHERE department_id = (SELECT department_idFROM employeesGROUP BY department_idHAVING AVG(salary) = (SELECT MAX(max_sal)FROM (SELECT AVG(salary) max_salFROM employeesGROUP BY department_id) dept_sal)));
方式二：
#方式二：
SELECT employee_id,last_name, department_id, email, salary
FROM employees
WHERE employee_id IN (SELECT DISTINCT manager_id FROM employeesWHERE department_id = (SELECT department_id FROM employees e GROUP BY department_idHAVING AVG(salary) >= ALL(SELECT AVG(salary) FROM employeesGROUP BY department_id)));
方式三：
#方式三：
SELECT *
FROM employees
WHERE employee_id IN (SELECT DISTINCT manager_id FROM employees e,(SELECT department_id,AVG(salary) avg_sal FROM employeesGROUP BY department_id ORDER BY avg_sal DESC LIMIT 0,1) dept_avg_salWHERE e.department_id = dept_avg_sal.department_id
);
15. 查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号
方式一：
SELECT department_id
FROM departments
WHERE department_id NOT IN (SELECT DISTINCT department_idFROM employeesWHERE job_id = 'ST_CLERK');
方式二：
SELECT department_id
FROM departments d
WHERE NOT EXISTS (SELECT *FROM employees eWHERE d.`department_id` = e.`department_id`AND job_id = 'ST_CLERK'
);
16. 选择所有没有管理者的员工的last_name
SELECT last_name
FROM employees e1
WHERE NOT EXISTS (SELECT *FROM employees e2WHERE e1.`manager_id` = e2.`employee_id`);
17．查询员工号、姓名、雇用时间、工资，其中员工的管理者为 ‘De Haan’
#方式1：
SELECT employee_id, last_name, hire_date, salary
FROM employees
WHERE manager_id = (SELECT employee_idFROM employeesWHERE last_name = 'De Haan'
);
方式二：
#方式2：
SELECT employee_id, last_name, hire_date, salary
FROM employees e1
WHERE EXISTS (SELECT *FROM employees e2WHERE e2.`employee_id` = e1.manager_idAND e2.last_name = 'De Haan'
);
18.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资(难)
方式一：
#方式一：相关子查询
SELECT employee_id,last_name,salary
FROM employees e1
WHERE salary > (# 查询某员工所在部门的平均SELECT AVG(salary)FROM employees e2WHERE e2.department_id = e1.`department_id`
);
方式二：
#方式二：
SELECT employee_id,last_name,salary
FROM employees e1,( SELECT department_id,AVG(salary) avg_salFROM employees e2 GROUP BY department_id) dept_avg_sal
WHERE e1.`department_id` = dept_avg_sal.department_id
AND e1.`salary` > dept_avg_sal.avg_sal;
19.查询每个部门下的部门人数大于 5 的部门名称
SELECT department_name,department_id
FROM departments d
WHERE 5 < (SELECT COUNT(*)FROM employees eWHERE d.`department_id` = e.`department_id`
);
20.查询每个国家下的部门个数大于 2 的国家编号
SELECT country_id
FROM locations l
WHERE 2 < (SELECT COUNT(*)FROM departments dWHERE l.`location_id` = d.`location_id`
);
#方式1：
SELECT employee_id, last_name, hire_date, salary
FROM employees
WHERE manager_id = (SELECT employee_idFROM employeesWHERE last_name = 'De Haan'
);
方式二：
#方式2：
SELECT employee_id, last_name, hire_date, salary
FROM employees e1
WHERE EXISTS (SELECT *FROM employees e2WHERE e2.`employee_id` = e1.manager_idAND e2.last_name = 'De Haan'
);
18.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资(难)
方式一：
#方式一：相关子查询
SELECT employee_id,last_name,salary
FROM employees e1
WHERE salary > (# 查询某员工所在部门的平均SELECT AVG(salary)FROM employees e2WHERE e2.department_id = e1.`department_id`
);
方式二：
#方式二：
SELECT employee_id,last_name,salary
FROM employees e1,( SELECT department_id,AVG(salary) avg_salFROM employees e2 GROUP BY department_id) dept_avg_sal
WHERE e1.`department_id` = dept_avg_sal.department_id
AND e1.`salary` > dept_avg_sal.avg_sal;
19.查询每个部门下的部门人数大于 5 的部门名称
SELECT department_name,department_id
FROM departments d
WHERE 5 < (SELECT COUNT(*)FROM employees eWHERE d.`department_id` = e.`department_id`
);
20.查询每个国家下的部门个数大于 2 的国家编号
SELECT country_id
FROM locations l
WHERE 2 < (SELECT COUNT(*)FROM departments dWHERE l.`location_id` = d.`location_id`
);