第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.查询哪些部门没有员工
- 查询哪个城市没有部门
- 查询部门名为 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 100SELECT 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 e
WHERE 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 NULL9. 查询部门名为 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 100SELECT 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 E1.显示系统时间(注：日期+时间)
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 72000SELECT 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 ASELECT 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章_聚合函数
- 查询公司员工工资的最大值，最小值，平均值，总和
- 查询各job_id的员工工资的最大值，最小值，平均值，总和
- 选择具有各个job_id的员工人数
- 查询员工最高工资和最低工资的差距（DIFFERENCE）
- 查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
- 查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序
- 查询每个工种、每个部门的部门名、工种名和最低工资
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 salary
FROM employees
WHERE 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_id
FROM employees
WHERE employee_id = 141
)
AND salary > (
SELECT salary
FROM employees
WHERE 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 salary
FROM employees
ORDER BY salary ASC
LIMIT 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_id
FROM employees
WHERE employee_id = 141
)
AND department_id = (SELECT department_id
FROM employees
WHERE 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 employees
WHERE 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_id
FROM departments
WHERE 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_sal
FROM employees
GROUP 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_sal
FROM employees
GROUP 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_sal
FROM employees
GROUP 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 e2
WHERE department_id = e1.`department_id`
);SELECT e.last_name,e.salary,e.department_id
FROM employees e,(
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP 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_name
FROM departments d
WHERE 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 j
WHERE 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_id
FROM employees);第三种：使用EXISTS
SELECT employee_id,last_name,job_id,department_id
FROM employees e1
WHERE EXISTS (
SELECT *
FROM employees e2
WHERE 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 e
WHERE 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 employees
WHERE last_name = 'Abel'
);第09章_子查询 练习题
【题目】：
- 查询和Zlotkey相同部门的员工姓名和工资
- 查询工资比公司平均工资高的员工的员工号，姓名和工资。
- 选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary
- 查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
- 查询在部门的location_id为1700的部门工作的员工的员工号
- 查询管理者是King的员工姓名和工资
- 查询工资最低的员工信息: last_name, salary
- 查询平均工资最低的部门信息
- 查询平均工资最低的部门信息和该部门的平均工资（相关子查询）
- 查询平均工资最高的 job 信息
- 查询平均工资高于公司平均工资的部门有哪些?
- 查询出公司中所有 manager 的详细信息
- 各个部门中 最高工资中最低的那个部门的 最低工资是多少?
- 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
- 查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号
- 选择所有没有管理者的员工的last_name
- 查询员工号、姓名、雇用时间、工资，其中员工的管理者为 'De Haan'
- 查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资（相关子查询）
- 查询每个部门下的部门人数大于 5 的部门名称（相关子查询）
- 查询每个国家下的部门个数大于 2 的国家编号（相关子查询）
1.查询和Zlotkey相同部门的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE department_id = (
SELECT department_id
FROM employees
WHERE 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 salary
FROM employees
WHERE job_id = 'SA_MAN');4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
SELECT employee_id,last_name
FROM employees
WHERE department_id = ANY(
SELECT DISTINCT department_id
FROM employees
WHERE last_name LIKE '%u%');5.查询在部门的location_id为1700的部门工作的员工的员工号
SELECT employee_id
FROM employees
WHERE department_id IN(
SELECT department_id
FROM departments
WHERE location_id = 1700);6.查询管理者是King的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE manager_id IN (
SELECT employee_id
FROM employees
WHERE 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_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
SELECT MIN(dept_avgsal)
FROM (
SELECT AVG(salary) dept_avgsal
FROM employees
GROUP BY department_id) avg_sal
)
);方式二：
SELECT *
FROM departments
WHERE department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL(
SELECT AVG(salary)
FROM employees
GROUP BY department_id
)
);方式三：
SELECT *
FROM departments
WHERE department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal
LIMIT 0,1
)
);方式四：
SELECT d.*
FROM departments d,(
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal
LIMIT 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_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
SELECT MIN(dept_avgsal)
FROM (
SELECT AVG(salary) dept_avgsal
FROM employees
GROUP 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_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL(
SELECT AVG(salary) avg_sal
FROM employees
GROUP 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_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal
LIMIT 0,1
)
);方式四：
SELECT d.*,dept_avg_sal.avg_sal
FROM departments d,(
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal
LIMIT 0,1) dept_avg_sal
WHERE d.`department_id` = dept_avg_sal.`department_id`;10. 查询平均工资最高的 job 信息
方式一：
SELECT *
FROM jobs
WHERE job_id = (
SELECT job_id
FROM employees
GROUP BY job_id
HAVING AVG(salary) = (
SELECT MAX(avg_sal)
FROM (
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY job_id
) job_avgsal
)
);方式二：
SELECT *
FROM jobs
WHERE job_id = (
SELECT job_id
FROM employees
GROUP BY job_id
HAVING AVG(salary) >= ALL(
SELECT AVG(salary)
FROM employees
GROUP BY job_id
)
);方式三：
SELECT *
FROM jobs
WHERE job_id = (
SELECT job_id
FROM employees
GROUP BY job_id
HAVING AVG(salary) = (
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY job_id
ORDER BY avg_sal DESC
LIMIT 0,1
)
);方式四：
SELECT j.*
FROM jobs j,(
SELECT job_id,AVG(salary) avg_sal
FROM employees
GROUP BY job_id
ORDER BY avg_sal DESC
LIMIT 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_id
FROM 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 e2
WHERE 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_sal
FROM 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_sal
FROM employees
GROUP BY department_id
) dept_max_sal
);
# 第四步 查询最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary) = (
SELECT MIN(max_sal)
FROM (
SELECT MAX(salary) max_sal
FROM 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_sal
FROM employees
GROUP BY department_id
);
# 第三步 查询最高工资的部门中的最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary) <= ALL(
SELECT MAX(salary) max_sal
FROM employees
GROUP BY department_id
)
);方式三：
SELECT MIN(salary)
FROM employees
WHERE department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary) = (
SELECT MAX(salary) max_sal
FROM employees
GROUP BY department_id
ORDER BY max_sal
LIMIT 0,1
)
);方式四：
SELECT employee_id,MIN(salary)
FROM employees e,(
SELECT department_id,MAX(salary) max_sal
FROM employees
GROUP BY department_id
ORDER BY max_sal
LIMIT 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_id
FROM employees
WHERE department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
SELECT MAX(max_sal)
FROM (
SELECT AVG(salary) max_sal
FROM employees
GROUP 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 employees
WHERE department_id = (
SELECT department_id FROM employees e GROUP BY department_id
HAVING AVG(salary) >= ALL(
SELECT AVG(salary) FROM employees
GROUP 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 employees
GROUP BY department_id ORDER BY avg_sal DESC LIMIT 0,1) dept_avg_sal
WHERE 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_id
FROM employees
WHERE job_id = 'ST_CLERK'
);方式二：
SELECT department_id
FROM departments d
WHERE NOT EXISTS (
SELECT *
FROM employees e
WHERE 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 e2
WHERE 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_id
FROM employees
WHERE last_name = 'De Haan'
);方式二：
#方式2：
SELECT employee_id, last_name, hire_date, salary
FROM employees e1
WHERE EXISTS (
SELECT *
FROM employees e2
WHERE e2.`employee_id` = e1.manager_id
AND e2.last_name = 'De Haan'
);18.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资(难)
方式一：
#方式一：相关子查询
SELECT employee_id,last_name,salary
FROM employees e1
WHERE salary > (
# 查询某员工所在部门的平均
SELECT AVG(salary)
FROM employees e2
WHERE e2.department_id = e1.`department_id`
);方式二：
#方式二：
SELECT employee_id,last_name,salary
FROM employees e1,
( SELECT department_id,AVG(salary) avg_sal
FROM 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 e
WHERE d.`department_id` = e.`department_id`
);20.查询每个国家下的部门个数大于 2 的国家编号
SELECT country_id
FROM locations l
WHERE 2 < (
SELECT COUNT(*)
FROM departments d
WHERE l.`location_id` = d.`location_id`
);
