第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 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 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 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章_子查询 练习题

【题目】
#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_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`
);

#方式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`
);
# mysql # 学习 # 数据库
Logo
开放原子开发者工作坊

开放原子开发者工作坊旨在鼓励更多人参与开源活动，与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动，如meetup、训练营等，主打技术交流，干货满满，真诚地邀请各位开发者共同参与！

更多推荐

操作系统大会&openEuler Summit 2024参会指南，请查收！

avatar 开放原子开发者工作坊

推动工业软件核心技术攻关，开源工业软件算法集成大赛正式启动！

推动工业软件核心技术攻关，开源工业软件算法集成大赛正式启动！

avatar 开放原子开发者工作坊

第二届openEuler生态大会（中国·湖南）成功举办

10月30日，第二届openEuler生态大会（中国·湖南）成功举办。

avatar 开放原子开发者工作坊