MySQL学习笔记——课后练习

最新推荐文章于 2023-08-09 20:19:25 发布
最新推荐文章于 2023-08-09 20:19:25 发布
阅读量1.6w 收藏 74
点赞数 17
分类专栏： SQL 文章标签： mysql 学习 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_51929833/article/details/122967087
版权

第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数据库全套教程笔记-SELECT使用篇】
weixin_46040278的博客
07-28 737
MySQL数据库是程序开发必备的技能,根据尚硅谷的宋红康老师学习上传一套MySQL从入门到高级的MySQL全套的学习笔记 从环境搭建到增删改查再到高级底层原理 索引调优等,希望对你有所帮助
阿里天池AI训练营SQL笔记——Task01：初识数据库与SQL
cjb_tjy的博客
07-21 251
数据库中存储的表结构类似于excel中的行和列，在数据库中，行称为记录，它相当于一条记录，列称为字段，它代表了表中存储的数据项目。行和列交汇的地方称为单元格，一个单元格中只能输入一条记录。根据对RDBMS赋予的指令种类的不同，SQL语句可以分为以下三类.DDL（DataDefinitionLanguage，数据定义语言）用来创建或者删除存储数据用的数据库以及数据库中的表等对象。DDL包含以下几种指令。CREATE创建数据库和表等对象DROP删除数据库和表等对象ALTER。......
MySQL小题
weixin_67450855的博客
07-02 2978
多看多记多理解
mysql 之select语句应用
weixin_64409959的博客
07-12 1784
对数据库数据的一些简单操作
MySQL - 基础题练习题 - 查询篇(6)
一个随时准备删库跑路的芳心纵火犯的博客
06-21 1788
数据在本专栏的第一篇博客里 本篇是连接查询，开始快乐起来了，到处找表 7.1.显示所有员工的姓名、部门号和部门名称 SELECT e.last_name, e.department_id, d.department_name FROM departments d, employees e WHERE d.department_id = e.department_id; 7.2.查询90号部门员工的job_id和90号部门的location_id SELECT e.job_id, d.l
sql 人员按部门不同编号_SQL_join27题(万_门)
weixin_39630498的博客
12-09 438
首先基础文件csv有6组，分别如下所示countries.csvdepartments.csvemployees.csvjob_history.csvjobs.csvlocations.csv#1. Write a query in SQL to display the first name, last name, department number, and department name fo...
MySQL学习（6）|连接查询
积一时之步，臻千里之遥程
01-28 530
进阶6：连接查询 含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询 笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行 发生原因：没有有效的连接条件 如何避免：添加有效的连接条件 按年代分类： sql92标准:仅仅支持内连接（等值、非等值、自连接） sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接 按功能分类： 内连接： 等值连接 非等值连接 自连...
数据库编程——MySQL API
Alliswell_WP
07-20 350
在学习数据库编程总结了笔记，并分享出来。有问题请及时联系博主：Alliswell_WP，转载请注明出处。 09-数据库编程day04(mysql api) 一、学习目标 1.mysql的约束2.mysql的中文乱码问题3.通过api连接mysql实现增删改查4.了解mysql预处理api的思想和实现流程5.mysql通过API开启事务 二、复习 1、Oracle 》oracle常...
Java JDK 7学习笔记(国内第一本Java 7，前期版本累计销量5万册)
12-13
丛书名： 学习笔记 出版社：清华大学出版社 ISBN：9787302282082 上架时间：2012-5-9 出版日期：2012 年5月 开本：16开 页码：564 版次：1-1 所属分类：计算机 > 软件与程序设计 > JAVA(J#) > Java 编辑推荐 　 ...
mysql-基础篇
weixin_49383196的博客
04-25 377
Mysql基础篇：本文主要介绍了有关MySQL的基础知识，其中主要分为概述、SQL语句、数据类型、函数、约束、多表查询、事务等相关知识，是在之前学习笔记的基础上加以补充然后同步在线的。
MySQL基础 Day02
gengsj
03-24 1035
排序查询介绍 -- 语法 select 查询列表 from 表 where 筛选条件 order by 排序列表 asc|dsc 案例1：查询员工信息，要求工资从高到低排序 SELECT * FROM employees ORDER BY salary DESC; 特点： ①asc升序，desc降序，默认升序 排序查询示例 案例2：查询部门编号>=90的员工信息，按入职时间先后排序【添加筛选条件】 SELECT * FROM employ
【可能是全网最好的】MySQL基础入门总结笔记
qq_41112238的博客
01-15 8650
之前已经介绍过了MySQL，这次是为了细化其中的细节部分，安装登陆等操作就不再赘述。
Oracle-数据库所有查询命令
春风十里不如你
03-17 3457
1.简单查询 1. 消除重复的姓 select distinct e.last_name as 姓氏 from employees e 2. 计算员工的月收入（工资+佣金） select salary,salary*(nvl(commission_pct,0)+1) as 工资佣金 from employees –3. 查询在 1998 年入职的员工 select * from employees e where to_char(e.hire_date,‘yyyy’)=‘1998’ –4. 查询姓以 B 开
MySQl入门基础操作
最新发布
weixin_73943382的博客
08-09 1296
指令： select *from mydb.worker;
Oracle第五章笔记
weixin_45951867的博客
06-15 909
第五章 多表连接 什么是连接? 通过一定的条件， 是表发生关联 使用一条sql语句， 查询员工姓名， 部门名称， 共工作地点 select ename, dname, loc from emp, dept where emp.deptno = dept.deptno; 可以起别名， 标识关键字， 使用别名 注意： 为表起别名时， 不能使用as 关键字 select ename, dname, loc from emp e, dept d where e.deptno=d.dept
MySql尚硅谷入门（六）
梅山
09-19 445
进阶六 #进阶6：连接查询 /* 含义:又成多表查询，当查询的字段来自多个表时，就会用 笛卡尔乘积现象：表1 有m行，表2有n行 结果= m*n行 发生原因：没有有效的连接条件 如何避免：添加有效的连接条件 分类： 按照年代分类： sql92标准：仅仅支持内连接 sql199标准【推荐】：支持所有的内连接+外连接（左外和右外）+交叉连接 按照功能分类： 内连接： 等值连接 非等值连接 自连接 外连接： 左外连接 右外连接 全外连接 交叉连接： */ SELECT * FRO
mySQL基础之连接查询
weixin_45342806的博客
11-14 3267
进阶6：连接查询 含义：又称多表查询,当查询的字段来自于多个表时，就会用到连接查询 笛卡尔乘积现象：表1 有m行、表2 有n行，结果 = m*n 行 发生原因：当查询多个表时，没有有效的连接条件 如何避免：添加有效的连接条件 分类： 按年代分类： 1. sql 92标准：仅仅支持内链接 等值连接 非等值连接 自连接 不稳定 也支持一部分外连接(用于oracle、sqlserver\mysql不支持)
Mysql习题系列（二）：多表查询（一篇学会做Mysql多表查询题，超详细~）
阿里云专家博主、数据科学领域优质创作者、统计er在读
03-17 4935
案例数据 提取码：2rd5 1.单表查询 1.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.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员
P21 第四章运算符练习题
weixin_54671087的博客
11-23 428
第04章运算符练习题 # 第4章练习题 # 1.选择工资不在5000到12000的员工的姓名和工资 SELECT last_name,salary FROM employees WHERE salary <5000 OR salary>12000; WHERE salary NOT BETWEEN 5000 AND 12000; # 2.选择在20或50号部门工作的员工姓名和部门号 SELECT last_name,department_id FROM employees WHERE
package com.unit2.test; public class Test4 { public static void main(String[] args) { Salary s=new Salary("员工A","北京",3,3600.00); Employee e=new Salary("员工B","上海",2,2400.00); System.out.println("使用Salary的引用调用mailcheck--"); s.mailCheck(); System.out.println("\n使用employee的引用调用mailcheck--"); e.mailCheck(); } public class Employee{ private String name; private String address; private int number; public Employee(String name,String address,int number) { System.out.println("Employee 构造函数"); this.name=name; this.address=address; this.number=number; } public void mailCheck() { System.out.println("邮寄支票给："+this.name+" "+this.address); } public String toString() { return name+" "+address+" "+number; } public String getName() { return name; } public String getAddress(String newAddress) { return address; } public void setAddress(String newAddress) { address=newAddress; } public int getNumber() { return number; } } public static class Salary extends Employee { private double salary;//全年工资 public Salary(String name,String address,int number,double salary) { super(name,address,number); setSalary(salary); } public void mailCheck() { System.out.println("Salary类的mailcheck方法"); System.out.println("邮寄支票给："+getName()+",工资为"+salary); } public double getSalary() { return salary; } public void setSalary(double newSalary) { if(newSalary>=0.0) { salary=newSalary; } } public double computePay() { System.out.println("计算工资，付给："+getName()); return salary/52; } } }更正以上代码错误
05-25
以下是更正后的代码： ``` package com.unit2.test; public class Test4 { public static void main(String[] args) { Salary s = new Salary("员工A", "北京", 3, 3600.00); Employee e = new Salary("员工B", "上海", 2, 2400.00); System.out.println("使用Salary的引用调用mailcheck--"); s.mailCheck(); System.out.println("\n使用employee的引用调用mailcheck--"); e.mailCheck(); } public static class Employee { private String name; private String address; private int number; public Employee(String name, String address, int number) { System.out.println("Employee 构造函数"); this.name = name; this.address = address; this.number = number; } public void mailCheck() { System.out.println("邮寄支票给：" + this.name + " " + this.address); } public String toString() { return name + " " + address + " " + number; } public String getName() { return name; } public String getAddress() { return address; } public void setAddress(String newAddress) { address = newAddress; } public int getNumber() { return number; } } public static class Salary extends Employee { private double salary;// 全年工资 public Salary(String name, String address, int number, double salary) { super(name, address, number); setSalary(salary); } public void mailCheck() { System.out.println("Salary类的mailcheck方法"); System.out.println("邮寄支票给：" + getName() + ",工资为" + salary); } public double getSalary() { return salary; } public void setSalary(double newSalary) { if (newSalary >= 0.0) { salary = newSalary; } } public double computePay() { System.out.println("计算工资，付给：" + getName()); return salary / 52; } } } ``` 更正说明： 1. 将 `Employee` 类和 `Salary` 类都声明为静态内部类，以便在 `main` 方法中使用。 2. `Employee` 类中的 `getAddress` 方法声明错误，应该是 `public String getAddress()`。 3. 在 `Salary` 类中重写了 `mailCheck` 方法，输出的信息更具体，同时也调用了 `getName` 方法来获取员工名字。 4. 在 `Salary` 类中新增了 `computePay` 方法，用于计算每周的工资。
评论 3
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

java小豪

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值