SQL练习：

simply_happy

已于 2024-11-08 19:19:00 修改

阅读量879

点赞数 12

文章标签： sql 大数据数据库

于 2024-11-03 19:27:35 首次发布

本文链接：https://blog.youkuaiyun.com/simply_happy/article/details/143466885

版权

一、单表练习:

1. 查询出部门编号为D2019060011的所有员工
2. 所有财务总监的姓名、编号和部门编号。
3. 找出奖金高于工资的员工。
4. 找出奖金高于工资40%的员工。
5 找出部门编号为D2019090011中所有财务总监，和部门编号为D2019060011中所有财务专员的详细资料。
6. 找出部门编号为D2019090001中所有总经理，部门编号为D2019090011中所有财务总监，还有即不是总经理又不是销售总监但其工资大或等于4000的所有员工详细资料。
7.有奖金的工种。
8无奖金或奖金低于1000的员工。
9. 查询名字由两个字组成的员工。
10.查询2020年入职的员工。
11. 查询所有员工详细信息，用编号升序排序。
12. 查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排序。
13. 查询每个部门的平均工资。
14. 求出每个部门的雇员数量。
15. 查询每种工作的最高工资、最低工资、人数.
16. 列出最低薪金大于4000的各种工作及从事此工作的员工人数。
17. 统计各部门工资总和，显示部门编号和该部门雇员的月工资的总和，并且要满足该部门雇员的月工资合计大于6000，输出结果按月工资的合计升序排列。

二、多表练习:
1. 列出所有员工的姓名及其直接上级的姓名。
2. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
3. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
4. 列出在财务部工作的员工的姓名，假定不知道财务部的部门编号。
5. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导。
6. 列出与陈超从事相同工作的所有员工及部门名称。
7. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。
8. 列出薪金高于在财务部工作员工平均薪金的员工姓名和薪金、部门名称。

需要用到的两张表:

一、创建

drop table if exists employee;
create table employee(
id bigint not null auto_increment,
number varchar(20) not null,
name varchar(50),
job varchar(50),
leader_NO varchar(20),
hire_date date,
salary decimal(7,2),
bonus decimal(7,2),
level integer default 0 comment '职位级别，9最高',
department_NO varchar(20),
primary key(id)
);
drop table if exists department;
create table department(
id bigint not null auto_increment,
number varchar(20) not null,
name varchar(50),
location varchar(50),
super_NO varchar(20),
primary key(id)
);

insert into department values
 (0, 'D2019050001', '清华集团', '北京', null), (0, 'D2019050002', '集团总部', '北京','D2019050001'),
(0, 'D2019060001', '成都中心', '成都', 'D2019050001'), (0, 'D2019060002', '武汉中心', '武汉','D2019050001'),
(0, 'D2019090001', '上海中心', '上海', 'D2019050001'), (0, 'D2020010001', '广州中心', '广州','D2019050001'),
(0, 'D2019090011', '财务部', '上海', 'D2019090001'),(0, 'D2020020012', '行政部', '上海','D2019090001'),
(0, 'D2019060011', '财务部', '成都', 'D2019060001'), (0, 'D2019060012', '技术部', '成都','D2019060001'),
(0, 'D2019060013', '网络部', '成都', 'D2019060001'), (0, 'D2019060014', '市场部', '成都', 'D2019060001');

insert into employee values(0,'E2018010001','吴所为','总经理', null,'2018-01-01',2800,4000,9,'D2019060001');
insert into employee values(0,'E2018070003','韩金龙','总经理', null,'2018-07-01',2800,4000,8,'D2019090001');
insert into employee values(0,'E2018060002','王黎明','总经理', null,'2018-06-01',2800,4000,8,'D2019060002');
insert into employee values(0,'E2018020002','龚爱国','总经理', null,'2018-02-01',2800,4000,8,'D2020010001');
insert into employee values(0,'E2019050001','马金花','财务总监', 'E2018010001','2019-01-01',3800,500,6,'D2019060011');
insert into employee values(0,'E2019050018','李昌贵','财务专员', 'E2019050001','2019-04-21',2800,800,4,'D2019060011');
insert into employee values(0,'E2019100011','王建国','网络管理员', 'E2018010001','2019-10-01',3200,null,5,'D2019060013');
insert into employee values(0,'E2019110004','黎锦熙','网络管理员', 'E2019100011','2019-11-01',3200,null,5,'D2019060013');
insert into employee values(0,'E2020020023','繁茂森','销售专员', 'E2019060005','2020-02-01',2800,0,4,'D2019060014');
insert into employee values(0,'E2019060005','张善民','销售经理', 'E2018010001','2019-06-01',2800,500,6,'D2019060014');
insert into employee values(0,'E2019060009','廖云龙','技术总监', 'E2018010001','2019-06-01',4800,2000,7,'D2019060012');
insert into employee values(0,'E2019120021','刘盛会','研发工程师', 'E2019060009','2019-12-11',4800,500,5,'D2019060012');
insert into employee values(0,'E2019020001','马明全','高级工程师', 'E2019060009','2019-02-01',4800,1000,6, 'D2019060012');
insert into employee values(0,'E2019120015','李意','行政专员', 'E2018070003','2019-12-20',2800,500,4,'D2019090001');
insert into employee values(0,'E2019020017','刘六一','财务总监', 'E2018070003','2019-02-16',3800,1000,6,'D2019090011');
insert into employee values(0,'E2020020012','陈超','研发工程师', 'E2019060009','2020-02-18',4200,500,5,'D2019060012');

二、单表练习

1. 查询出部门编号为D2019060011的所有员工

mysql> select name from employee where department_NO = "D2019060011";
+--------+
| name   |
+--------+
| 马金花 |
| 李昌贵 |
+--------+

2.所有财务总监的姓名、编号和部门编号

mysql> select name,number,department_NO from employee where job ="财务总监";
+--------+-------------+---------------+
| name   | number      | department_NO |
+--------+-------------+---------------+
| 马金花 | E2019050001 | D2019060011   |
| 刘六一 | E2019020017 | D2019090011   |
+--------+-------------+---------------+

3. 找出奖金高于工资的员工

mysql> select name from employee where bonus>salary;
+--------+
| name   |
+--------+
| 吴所为 |
| 韩金龙 |
| 王黎明 |
| 龚爱国 |
+--------+

4.找出奖金高于工资40%的员工

mysql> select name from employee where bonus>0.4*salary;
+--------+
| name   |
+--------+
| 吴所为 |
| 韩金龙 |
| 王黎明 |
| 龚爱国 |
| 廖云龙 |
+--------+

5.找出部门编号为D2019090011中所有财务总监，和部门编号为D2019060011中所有财务专员的详细资料

select * from employee where (department_NO = "D2019090011" AND job = "财务总监")
or (department_NO = "D2019060011" AND job = "财务专员");

mysql> select * from employee where department_NO = "D2019090011" AND job = "财务总监";
+----+-------------+--------+----------+-------------+------------+---------+---------+-------+---------------+
| id | number      | name   | job      | leader_NO   | hire_date  | salary  | bonus   | level | department_NO |
+----+-------------+--------+----------+-------------+------------+---------+---------+-------+---------------+
| 15 | E2019020017 | 刘六一 | 财务总监 | E2018070003 | 2019-02-16 | 3800.00 | 1000.00 |     6 | D2019090011   |
+----+-------------+--------+----------+-------------+------------+---------+---------+-------+---------------+
1 row in set (0.00 sec)

mysql> select * from employee where department_NO = "D2019060011" AND job = "财务专员";
+----+-------------+--------+----------+-------------+------------+---------+--------+-------+---------------+
| id | number      | name   | job      | leader_NO   | hire_date  | salary  | bonus  | level | department_NO |
+----+-------------+--------+----------+-------------+------------+---------+--------+-------+---------------+
|  6 | E2019050018 | 李昌贵 | 财务专员 | E2019050001 | 2019-04-21 | 2800.00 | 800.00 |     4 | D2019060011   |
+----+-------------+--------+----------+-------------+------------+---------+--------+-------+---------------+
1 row in set (0.00 sec)

6.找出部门编号为D2019090001中所有总经理，部门编号为D2019090011中所有财务总监，还有即不是总经理又不是销售总监但其工资大或等于4000的所有员工详细资料

select * from employee where (department_NO = "D2019090001" AND job = "总经理")
or (department_NO = "D2019090001" AND job = "财务总监")
or (job <> "财务总监" AND job <>"总经理" AND salary >= 4000);

mysql> select * from employee where department_NO = "D2019090001" AND job = "总经理";
+----+-------------+--------+--------+-----------+------------+---------+---------+-------+---------------+
| id | number      | name   | job    | leader_NO | hire_date  | salary  | bonus   | level | department_NO |
+----+-------------+--------+--------+-----------+------------+---------+---------+-------+---------------+
|  2 | E2018070003 | 韩金龙 | 总经理 | NULL      | 2018-07-01 | 2800.00 | 4000.00 |     8 | D2019090001   |
+----+-------------+--------+--------+-----------+------------+---------+---------+-------+---------------+
1 row in set (0.00 sec)

mysql> select * from employee where department_NO = "D2019090001" AND job = "财务总监";
Empty set (0.00 sec)

mysql> select * from employee where  job <> "财务总监" AND job <>"总经理"AND salary >= 4000;
+----+-------------+--------+------------+-------------+------------+---------+---------+-------+---------------+
| id | number      | name   | job        | leader_NO   | hire_date  | salary  | bonus   | level | department_NO |
+----+-------------+--------+------------+-------------+------------+---------+---------+-------+---------------+
| 11 | E2019060009 | 廖云龙 | 技术总监   | E2018010001 | 2019-06-01 | 4800.00 | 2000.00 |     7 | D2019060012   |
| 12 | E2019120021 | 刘盛会 | 研发工程师 | E2019060009 | 2019-12-11 | 4800.00 |  500.00 |     5 | D2019060012   |
| 13 | E2019020001 | 马明全 | 高级工程师 | E2019060009 | 2019-02-01 | 4800.00 | 1000.00 |     6 | D2019060012   |
| 16 | E2020020012 | 陈超   | 研发工程师 | E2019060009 | 2020-02-18 | 4200.00 |  500.00 |     5 | D2019060012   |
+----+-------------+--------+------------+-------------+------------+---------+---------+-------+---------------+
4 rows in set (0.00 sec)

7.有奖金的工种

mysql> select job from employee where bonus is no null;
+------------+
| job        |
+------------+
| 总经理     |
| 总经理     |
| 总经理     |
| 总经理     |
| 财务总监   |
| 财务专员   |
| 销售专员   |
| 销售经理   |
| 技术总监   |
| 研发工程师 |
| 高级工程师 |
| 行政专员   |
| 财务总监   |
| 研发工程师 |
+------------+

8.无奖金或奖金低于1000的员工

mysql> select name from employee where bonus <=1000 or bonus ="null";
+--------+
| name   |
+--------+
| 马金花 |
| 李昌贵 |
| 繁茂森 |
| 张善民 |
| 刘盛会 |
| 马明全 |
| 李意   |
| 刘六一 |
| 陈超   |
+--------+

9. 查询名字由两个字组成的员工

mysql> select name from employee where  name like "__" ;
+------+
| name |
+------+
| 李意 |
| 陈超 |
+------+

10.查询2020年入职的员工

mysql> select name from employee where hire_date like "2020%";
+--------+
| name   |
+--------+
| 繁茂森 |
| 陈超   |
+--------+

11.查询所有员工详细信息，用编号升序排序

mysql> select * from employee order by number asc;
+----+-------------+--------+------------+-------------+------------+---------+---------+-------+---------------+
| id | number      | name   | job        | leader_NO   | hire_date  | salary  | bonus   | level | department_NO |
+----+-------------+--------+------------+-------------+------------+---------+---------+-------+---------------+
|  1 | E2018010001 | 吴所为 | 总经理     | NULL        | 2018-01-01 | 2800.00 | 4000.00 |     9 | D2019060001   |
|  4 | E2018020002 | 龚爱国 | 总经理     | NULL        | 2018-02-01 | 2800.00 | 4000.00 |     8 | D2020010001   |
|  3 | E2018060002 | 王黎明 | 总经理     | NULL        | 2018-06-01 | 2800.00 | 4000.00 |     8 | D2019060002   |
|  2 | E2018070003 | 韩金龙 | 总经理     | NULL        | 2018-07-01 | 2800.00 | 4000.00 |     8 | D2019090001   |
| 13 | E2019020001 | 马明全 | 高级工程师 | E2019060009 | 2019-02-01 | 4800.00 | 1000.00 |     6 | D2019060012   |
| 15 | E2019020017 | 刘六一 | 财务总监   | E2018070003 | 2019-02-16 | 3800.00 | 1000.00 |     6 | D2019090011   |
|  5 | E2019050001 | 马金花 | 财务总监   | E2018010001 | 2019-01-01 | 3800.00 |  500.00 |     6 | D2019060011   |
|  6 | E2019050018 | 李昌贵 | 财务专员   | E2019050001 | 2019-04-21 | 2800.00 |  800.00 |     4 | D2019060011   |
| 10 | E2019060005 | 张善民 | 销售经理   | E2018010001 | 2019-06-01 | 2800.00 |  500.00 |     6 | D2019060014   |
| 11 | E2019060009 | 廖云龙 | 技术总监   | E2018010001 | 2019-06-01 | 4800.00 | 2000.00 |     7 | D2019060012   |
|  7 | E2019100011 | 王建国 | 网络管理员 | E2018010001 | 2019-10-01 | 3200.00 |    NULL |     5 | D2019060013   |
|  8 | E2019110004 | 黎锦熙 | 网络管理员 | E2019100011 | 2019-11-01 | 3200.00 |    NULL |     5 | D2019060013   |
| 14 | E2019120015 | 李意   | 行政专员   | E2018070003 | 2019-12-20 | 2800.00 |  500.00 |     4 | D2019090001   |
| 12 | E2019120021 | 刘盛会 | 研发工程师 | E2019060009 | 2019-12-11 | 4800.00 |  500.00 |     5 | D2019060012   |
| 16 | E2020020012 | 陈超   | 研发工程师 | E2019060009 | 2020-02-18 | 4200.00 |  500.00 |     5 | D2019060012   |
|  9 | E2020020023 | 繁茂森 | 销售专员   | E2019060005 | 2020-02-01 | 2800.00 |    0.00 |     4 | D2019060014   |
+----+-------------+--------+------------+-------------+------------+---------+---------+-------+---------------+

12.查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排序

mysql> select * from employee order by salary desc,hire_date asc;
+----+-------------+--------+------------+-------------+------------+---------+---------+-------+---------------+
| id | number      | name   | job        | leader_NO   | hire_date  | salary  | bonus   | level | department_NO |
+----+-------------+--------+------------+-------------+------------+---------+---------+-------+---------------+
| 13 | E2019020001 | 马明全 | 高级工程师 | E2019060009 | 2019-02-01 | 4800.00 | 1000.00 |     6 | D2019060012   |
| 11 | E2019060009 | 廖云龙 | 技术总监   | E2018010001 | 2019-06-01 | 4800.00 | 2000.00 |     7 | D2019060012   |
| 12 | E2019120021 | 刘盛会 | 研发工程师 | E2019060009 | 2019-12-11 | 4800.00 |  500.00 |     5 | D2019060012   |
| 16 | E2020020012 | 陈超   | 研发工程师 | E2019060009 | 2020-02-18 | 4200.00 |  500.00 |     5 | D2019060012   |
|  5 | E2019050001 | 马金花 | 财务总监   | E2018010001 | 2019-01-01 | 3800.00 |  500.00 |     6 | D2019060011   |
| 15 | E2019020017 | 刘六一 | 财务总监   | E2018070003 | 2019-02-16 | 3800.00 | 1000.00 |     6 | D2019090011   |
|  7 | E2019100011 | 王建国 | 网络管理员 | E2018010001 | 2019-10-01 | 3200.00 |    NULL |     5 | D2019060013   |
|  8 | E2019110004 | 黎锦熙 | 网络管理员 | E2019100011 | 2019-11-01 | 3200.00 |    NULL |     5 | D2019060013   |
|  1 | E2018010001 | 吴所为 | 总经理     | NULL        | 2018-01-01 | 2800.00 | 4000.00 |     9 | D2019060001   |
|  4 | E2018020002 | 龚爱国 | 总经理     | NULL        | 2018-02-01 | 2800.00 | 4000.00 |     8 | D2020010001   |
|  3 | E2018060002 | 王黎明 | 总经理     | NULL        | 2018-06-01 | 2800.00 | 4000.00 |     8 | D2019060002   |
|  2 | E2018070003 | 韩金龙 | 总经理     | NULL        | 2018-07-01 | 2800.00 | 4000.00 |     8 | D2019090001   |
|  6 | E2019050018 | 李昌贵 | 财务专员   | E2019050001 | 2019-04-21 | 2800.00 |  800.00 |     4 | D2019060011   |
| 10 | E2019060005 | 张善民 | 销售经理   | E2018010001 | 2019-06-01 | 2800.00 |  500.00 |     6 | D2019060014   |
| 14 | E2019120015 | 李意   | 行政专员   | E2018070003 | 2019-12-20 | 2800.00 |  500.00 |     4 | D2019090001   |
|  9 | E2020020023 | 繁茂森 | 销售专员   | E2019060005 | 2020-02-01 | 2800.00 |    0.00 |     4 | D2019060014   |
+----+-------------+--------+------------+-------------+------------+---------+---------+-------+---------------+

13. 查询每个部门的平均工资

mysql> select department_NO,avg(salary) from employee group by department_NO;
+---------------+-------------+
| department_NO | avg(salary) |
+---------------+-------------+
| D2019060001   | 2800.000000 |
| D2019090001   | 2800.000000 |
| D2019060002   | 2800.000000 |
| D2020010001   | 2800.000000 |
| D2019060011   | 3300.000000 |
| D2019060013   | 3200.000000 |
| D2019060014   | 2800.000000 |
| D2019060012   | 4650.000000 |
| D2019090011   | 3800.000000 |
+---------------+-------------+

14.求出每个部门的雇员数量

mysql> select department_NO,group_concat(job),count(job) from employee group by department_NO;
+---------------+-------------------------------------------+------------+
| department_NO | group_concat(job)                         | count(job) |
+---------------+-------------------------------------------+------------+
| D2019060001   | 总经理                                    |          1 |
| D2019060002   | 总经理                                    |          1 |
| D2019060011   | 财务总监,财务专员                         |          2 |
| D2019060012   | 技术总监,研发工程师,高级工程师,研发工程师 |          4 |
| D2019060013   | 网络管理员,网络管理员                     |          2 |
| D2019060014   | 销售专员,销售经理                         |          2 |
| D2019090001   | 总经理,行政专员                           |          2 |
| D2019090011   | 财务总监                                  |          1 |
| D2020010001   | 总经理                                    |          1 |
+---------------+-------------------------------------------+------------+

15. 查询每种工作的最高工资、最低工资、人数

mysql> select job,max(salary),min(salary),count(job) from employee group by job;
+------------+-------------+-------------+------------+
| job        | max(salary) | min(salary) | count(job) |
+------------+-------------+-------------+------------+
| 总经理     |     2800.00 |     2800.00 |          4 |
| 财务总监   |     3800.00 |     3800.00 |          2 |
| 财务专员   |     2800.00 |     2800.00 |          1 |
| 网络管理员 |     3200.00 |     3200.00 |          2 |
| 销售专员   |     2800.00 |     2800.00 |          1 |
| 销售经理   |     2800.00 |     2800.00 |          1 |
| 技术总监   |     4800.00 |     4800.00 |          1 |
| 研发工程师 |     4800.00 |     4200.00 |          2 |
| 高级工程师 |     4800.00 |     4800.00 |          1 |
| 行政专员   |     2800.00 |     2800.00 |          1 |
+------------+-------------+-------------+------------+

16. 列出最低薪金大于4000的各种工作及从事此工作的员工人数。

mysql> select job,count(job) from employee where (salary+bonus)>=4000 group by job ;
+------------+------------+
| job        | count(job) |
+------------+------------+
| 总经理     |          4 |
| 财务总监   |          2 |
| 技术总监   |          1 |
| 研发工程师 |          2 |
| 高级工程师 |          1 |
+------------+------------+
5 rows in set (0.00 sec)

17.统计各部门工资总和，显示部门编号和该部门雇员的月工资的总和，并且要满足该部门雇员的月工资合计大于6000，输出结果按月工资的合计升序排列

mysql> select department_NO,sum(salary) from employee group by department_NO having sum(salary) > 6000 order by sum(sala
ry) asc
    -> ;
+---------------+-------------+
| department_NO | sum(salary) |
+---------------+-------------+
| D2019060013   |     6400.00 |
| D2019060011   |     6600.00 |
| D2019060012   |    18600.00 |
+---------------+-------------+
3 rows in set (0.00 sec)

三、多表练习

1. 列出所有员工的姓名及其直接上级的姓名

mysql> select t1.name as boss_name,t2.name from employee t2 left join employee t1 on t1.number =t2.leader_NO;
+-----------+--------+
| boss_name | name   |
+-----------+--------+
| NULL      | 吴所为 |
| NULL      | 韩金龙 |
| NULL      | 王黎明 |
| NULL      | 龚爱国 |
| 吴所为    | 马金花 |
| 马金花    | 李昌贵 |
| 吴所为    | 王建国 |
| 王建国    | 黎锦熙 |
| 张善民    | 繁茂森 |
| 吴所为    | 张善民 |
| 吴所为    | 廖云龙 |
| 廖云龙    | 刘盛会 |
| 廖云龙    | 马明全 |
| 韩金龙    | 李意   |
| 韩金龙    | 刘六一 |
| 廖云龙    | 陈超   |
+-----------+--------+

mysql> select t1.name,t2.name as boss_name from employee t1 left join employee t2 on t1.leader_NO =t2.number;
+--------+-----------+
| name   | boss_name |
+--------+-----------+
| 吴所为 | NULL      |
| 韩金龙 | NULL      |
| 王黎明 | NULL      |
| 龚爱国 | NULL      |
| 马金花 | 吴所为    |
| 李昌贵 | 马金花    |
| 王建国 | 吴所为    |
| 黎锦熙 | 王建国    |
| 繁茂森 | 张善民    |
| 张善民 | 吴所为    |
| 廖云龙 | 吴所为    |
| 刘盛会 | 廖云龙    |
| 马明全 | 廖云龙    |
| 李意   | 韩金龙    |
| 刘六一 | 韩金龙    |
| 陈超   | 廖云龙    |
+--------+-----------+
16 rows in set (0.00 sec)

2. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。

mysql> select e1.number as number,
    -> e1.name as name,
    -> (select d.name
    -> from department d
    -> where d.number = e1.department_NO) as department_name
    -> from employee e1
    -> where e1.hire_date <
    -> (select e2.hire_date
    -> from employee e2
    -> where e2.number = e1.leader_NO);
+-------------+--------+-----------------+
| number      | name   | department_name |
+-------------+--------+-----------------+
| E2019020001 | 马明全 | 技术部          |
+-------------+--------+-----------------+

3.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

mysql> select * from employee e right join department d on d.number = e.department_no;
+------+-------------+--------+------------+-------------+------------+---------+---------+-------+---------------+----+-------------+----------+----------+-------------+
| id   | number      | name   | job        | leader_NO   | hire_date  | salary  | bonus   | level | department_NO | id | number      | name     | location | super_NO    |
+------+-------------+--------+------------+-------------+------------+---------+---------+-------+---------------+----+-------------+----------+----------+-------------+
| NULL | NULL        | NULL   | NULL       | NULL        | NULL       |    NULL |    NULL |  NULL | NULL          |  1 | D2019050001 | 清华集团 | 北京     | NULL        |
| NULL | NULL        | NULL   | NULL       | NULL        | NULL       |    NULL |    NULL |  NULL | NULL          |  2 | D2019050002 | 集团总部 | 北京     | D2019050001 |
|    1 | E2018010001 | 吴所为 | 总经理     | NULL        | 2018-01-01 | 2800.00 | 4000.00 |     9 | D2019060001   |  3 | D2019060001 | 成都中心 | 成都     | D2019050001 |
|    3 | E2018060002 | 王黎明 | 总经理     | NULL        | 2018-06-01 | 2800.00 | 4000.00 |     8 | D2019060002   |  4 | D2019060002 | 武汉中心 | 武汉     | D2019050001 |
|   14 | E2019120015 | 李意   | 行政专员   | E2018070003 | 2019-12-20 | 2800.00 |  500.00 |     4 | D2019090001   |  5 | D2019090001 | 上海中心 | 上海     | D2019050001 |
|    2 | E2018070003 | 韩金龙 | 总经理     | NULL        | 2018-07-01 | 2800.00 | 4000.00 |     8 | D2019090001   |  5 | D2019090001 | 上海中心 | 上海     | D2019050001 |
|    4 | E2018020002 | 龚爱国 | 总经理     | NULL        | 2018-02-01 | 2800.00 | 4000.00 |     8 | D2020010001   |  6 | D2020010001 | 广州中心 | 广州     | D2019050001 |
|   15 | E2019020017 | 刘六一 | 财务总监   | E2018070003 | 2019-02-16 | 3800.00 | 1000.00 |     6 | D2019090011   |  7 | D2019090011 | 财务部   | 上海     | D2019090001 |
| NULL | NULL        | NULL   | NULL       | NULL        | NULL       |    NULL |    NULL |  NULL | NULL          |  8 | D2020020012 | 行政部   | 上海     | D2019090001 |
|    6 | E2019050018 | 李昌贵 | 财务专员   | E2019050001 | 2019-04-21 | 2800.00 |  800.00 |     4 | D2019060011   |  9 | D2019060011 | 财务部   | 成都     | D2019060001 |
|    5 | E2019050001 | 马金花 | 财务总监   | E2018010001 | 2019-01-01 | 3800.00 |  500.00 |     6 | D2019060011   |  9 | D2019060011 | 财务部   | 成都     | D2019060001 |
|   16 | E2020020012 | 陈超   | 研发工程师 | E2019060009 | 2020-02-18 | 4200.00 |  500.00 |     5 | D2019060012   | 10 | D2019060012 | 技术部   | 成都     | D2019060001 |
|   13 | E2019020001 | 马明全 | 高级工程师 | E2019060009 | 2019-02-01 | 4800.00 | 1000.00 |     6 | D2019060012   | 10 | D2019060012 | 技术部   | 成都     | D2019060001 |
|   12 | E2019120021 | 刘盛会 | 研发工程师 | E2019060009 | 2019-12-11 | 4800.00 |  500.00 |     5 | D2019060012   | 10 | D2019060012 | 技术部   | 成都     | D2019060001 |
|   11 | E2019060009 | 廖云龙 | 技术总监   | E2018010001 | 2019-06-01 | 4800.00 | 2000.00 |     7 | D2019060012   | 10 | D2019060012 | 技术部   | 成都     | D2019060001 |
|    8 | E2019110004 | 黎锦熙 | 网络管理员 | E2019100011 | 2019-11-01 | 3200.00 |    NULL |     5 | D2019060013   | 11 | D2019060013 | 网络部   | 成都     | D2019060001 |
|    7 | E2019100011 | 王建国 | 网络管理员 | E2018010001 | 2019-10-01 | 3200.00 |    NULL |     5 | D2019060013   | 11 | D2019060013 | 网络部   | 成都     | D2019060001 |
|   10 | E2019060005 | 张善民 | 销售经理   | E2018010001 | 2019-06-01 | 2800.00 |  500.00 |     6 | D2019060014   | 12 | D2019060014 | 市场部   | 成都     | D2019060001 |
|    9 | E2020020023 | 繁茂森 | 销售专员   | E2019060005 | 2020-02-01 | 2800.00 |    0.00 |     4 | D2019060014   | 12 | D2019060014 | 市场部   | 成都     | D2019060001 |
+------+-------------+--------+------------+-------------+------------+---------+---------+-------+---------------+----+-------------+----------+----------+-------------+
19 rows in set (0.00 sec)

4. 列出在财务部工作的员工的姓名，假定不知道财务部的部门编号

mysql> select name from employee where department_NO in (select number from department where name = "财务部");
+--------+
| name   |
+--------+
| 马金花 |
| 李昌贵 |
| 刘六一 |
+--------+

5. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导

mysql>  select e1.*,
    -> d.name as department_name,
    ->  (select e2.name
    ->  from employee e2
    -> where e2.number = e1.leader_NO) as boss_name
    -> from employee e1,department d
    -> where e1.salary > (select avg(salary) from employee)
    -> and e1.department_NO = d.number;
+----+-------------+--------+------------+-------------+------------+---------+---------+-------+---------------+-----------------+-----------+
| id | number      | name   | job        | leader_NO   | hire_date  | salary  | bonus   | level | department_NO | department_name | boss_name |
+----+-------------+--------+------------+-------------+------------+---------+---------+-------+---------------+-----------------+-----------+
| 15 | E2019020017 | 刘六一 | 财务总监   | E2018070003 | 2019-02-16 | 3800.00 | 1000.00 |     6 | D2019090011   | 财务部          | 韩金龙    |
|  5 | E2019050001 | 马金花 | 财务总监   | E2018010001 | 2019-01-01 | 3800.00 |  500.00 |     6 | D2019060011   | 财务部          | 吴所为    |
| 16 | E2020020012 | 陈超   | 研发工程师 | E2019060009 | 2020-02-18 | 4200.00 |  500.00 |     5 | D2019060012   | 技术部          | 廖云龙    |
| 13 | E2019020001 | 马明全 | 高级工程师 | E2019060009 | 2019-02-01 | 4800.00 | 1000.00 |     6 | D2019060012   | 技术部          | 廖云龙    |
| 12 | E2019120021 | 刘盛会 | 研发工程师 | E2019060009 | 2019-12-11 | 4800.00 |  500.00 |     5 | D2019060012   | 技术部          | 廖云龙    |
| 11 | E2019060009 | 廖云龙 | 技术总监   | E2018010001 | 2019-06-01 | 4800.00 | 2000.00 |     7 | D2019060012   | 技术部          | 吴所为    |
+----+-------------+--------+------------+-------------+------------+---------+---------+-------+---------------+-----------------+-----------+
6 rows in set (0.01 sec)

6. 列出与陈超从事相同工作的所有员工及部门名称

mysql> select e.name as name,
    ->  d.name as department_name
    ->  from employee e
    -> join department d on e.department_NO=d.number
    -> where e.job =(select job from employee
    -> where name = "陈超");
+--------+-----------------+
| name   | department_name |
+--------+-----------------+
| 陈超   | 技术部          |
| 刘盛会 | 技术部          |
+--------+-----------------+
2 rows in set (0.00 sec)

7. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数


mysql> select d.number as department_number,
    -> d.name as department,
    -> d.location as location,
    -> (select count(*)
    -> from employee e
    -> where e.department_NO = d.number) as member
    -> from department d
    -> where d.number in (select distinct department_NO from employee);
+-------------------+------------+----------+--------+
| department_number | department | location | member |
+-------------------+------------+----------+--------+
| D2019060001       | 成都中心   | 成都     |      1 |
| D2019060002       | 武汉中心   | 武汉     |      1 |
| D2019090001       | 上海中心   | 上海     |      2 |
| D2020010001       | 广州中心   | 广州     |      1 |
| D2019090011       | 财务部     | 上海     |      1 |
| D2019060011       | 财务部     | 成都     |      2 |
| D2019060012       | 技术部     | 成都     |      4 |
| D2019060013       | 网络部     | 成都     |      2 |
| D2019060014       | 市场部     | 成都     |      2 |
+-------------------+------------+----------+--------+
9 rows in set (0.00 sec)

8. 列出薪金高于在财务部工作员工平均薪金的员工姓名和薪金、部门名称

mysql> select e.name as 员工姓名,
    -> e.salary as 薪金,
    -> d.name as 部门名称
    -> from employee e, department d
    -> where e.department_no = d.number
    -> and e.salary > (select avg(salary)
    -> from employee
    -> where department_no = (select number
    -> from department
    -> where name = '财务部'
    -> limit 1));
+----------+---------+----------+
| 员工姓名 | 薪金    | 部门名称 |
+----------+---------+----------+
| 陈超     | 4200.00 | 技术部   |
| 马明全   | 4800.00 | 技术部   |
| 刘盛会   | 4800.00 | 技术部   |
| 廖云龙   | 4800.00 | 技术部   |
+----------+---------+----------+
4 rows in set (0.00 sec)