MySQL之DML和DQL练习（多表关联）

目录

1.找出销售部门中年纪最大的员工的姓名

2.求财务部门最低工资的员工姓名

3.列出每个部门收入总和高于9000的部门名称

4.求工资在7500到8500元之间，年龄最大的人的姓名及部门

5.找出销售部门收入最低的员工入职时间

6.财务部门收入超过2000元的员工姓名

7.列出每个部门的平均收入及部门名称

8.运维部入职员工的员工号

9.财务部门的收入总和

10.先按部门号大小排序，再依据入职时间由早到晚排序员工信息表

11.找出哪个部门还没有员工入职

12.列出部门员工收入大于7000的部门编号，部门名称；

13.列出每一个部门的员工总收入及部门名称

14.列出每一个部门中年纪最大的员工姓名，部门名称 ***

15.求李四的收入及部门名称

16.列出部门员工数大于1个的部门名称

---

完成下列查询工作

1.找出销售部门中年纪最大的员工的姓名

mysql> select name from emp_new a join dept b on a.dept2=b.dept1 and dept_name=' 销售' order by age desc limit 1;
+------+
| name |
+------+
| 荣七 |
+------+

2.求财务部门最低工资的员工姓名

mysql> select name from emp_new a join dept b on a.dept2=b.dept1 and dept_name =
'财务' order by incoming asc limit 1;
+------+
| name |
+------+
| 王五 |
+------+

3.列出每个部门收入总和高于9000的部门名称

mysql> select dept_name'部门' ,sum(incoming)'收入总和' from emp_new a join dept b
 on a.dept2=b.dept1  group by dept_name having  sum(incoming)>9000;
+------+----------+
| 部门 | 收入总和 |
+------+----------+
| 财务 |     9500 |
| 销售 |    16000 |
+------+----------+

4.求工资在7500到8500元之间，年龄最大的人的姓名及部门

mysql> select name,dept_name from emp_new a join dept b on a.dept2=b.dept1 and in
coming in (7500,8500) order by age desc limit 1;
+------+-----------+
| name | dept_name |
+------+-----------+
| 荣七 | 销售      |
+------+-----------+

5.找出销售部门收入最低的员工入职时间

mysql> select worktime_start from emp_new a join dept b on a.dept2 = b.dept1 and  dept_name='销售' order by incoming limit 1;
+----------------+
| worktime_start |
+----------------+
| 1970-10-11     |
+----------------+

6.财务部门收入超过2000元的员工姓名

mysql> select name  from emp_new a join dept b on a.dept2 = b.dept1 and dept_name =
'财务' and incoming>2000;
+------+
| name |
+------+
| 张三 |
| 李四 |
+------+

7.列出每个部门的平均收入及部门名称

mysql> select round(avg(incoming),2)'平均收入',dept_name'部门' from emp_new a joi
n dept b on a.dept2 = b.dept1 group by dept_name;
+----------+------+
| 平均收入 | 部门 |
+----------+------+
|  3166.67 | 财务 |
|  8000.00 | 销售 |
|  7300.00 | 运维 |
+----------+------+

8.运维部入职员工的员工号

mysql> select sid from emp_new a join dept b on a.dept2=b.dept1 and dept_name = '
运维';
+------+
| sid  |
+------+
| 1879 |
+------+

9.财务部门的收入总和

mysql> select sum(incoming) from emp_new a join dept b on a.dept2=b.dept1 and dep
t_name = '财务' ;
+---------------+
| sum(incoming) |
+---------------+
|          9500 |
+---------------+

10.先按部门号大小排序，再依据入职时间由早到晚排序员工信息表

mysql> select *from emp_new order by dept2 , year(worktime_start) asc;
+------+------+------+----------------+----------+-------+
| sid  | name | age  | worktime_start | incoming | dept2 |
+------+------+------+----------------+----------+-------+
| 1789 | 张三 |   35 | 1980-01-01     |     4000 |   101 |
| 1674 | 李四 |   32 | 1983-04-01     |     3500 |   101 |
| 1776 | 王五 |   24 | 1990-07-01     |     2000 |   101 |
| 1564 | 荣七 |   64 | 1963-10-11     |     8500 |   102 |
| 1568 | 赵六 |   57 | 1970-10-11     |     7500 |   102 |
| 1879 | 牛八 |   55 | 1971-10-20     |     7300 |   103 |
+------+------+------+----------------+----------+-------+
6 rows in set (0.00 sec)

11.找出哪个部门还没有员工入职

mysql> select dept_name from emp_new right join dept on emp_new.dept2 = dept.dept
1 where sid is null;
+-----------+
| dept_name |
+-----------+
| 行政      |
+-----------+

12.列出部门员工收入大于7000的部门编号，部门名称；

mysql> select b.dept1,dept_name from emp_new a join dept b on a.dept2=b.dept1 and
 incoming>7000;
+-------+-----------+
| dept1 | dept_name |
+-------+-----------+
|   102 | 销售      |
|   102 | 销售      |
|   103 | 运维      |
+-------+-----------+

13.列出每一个部门的员工总收入及部门名称

mysql> select sum(incoming),dept_name  from emp_new a join dept b on a.dept2=b.de
pt1 group by dept_name;
+---------------+-----------+
| sum(incoming) | dept_name |
+---------------+-----------+
|          9500 | 财务      |
|         16000 | 销售      |
|          7300 | 运维      |
+---------------+-----------+

14.列出每一个部门中年纪最大的员工姓名，部门名称 ***

mysql> select name as "姓名", dept_name as "部门名称" from dept,(select max(age)
    -> age, dept2 from emp_new group by dept2) em, emp_new where dept.dept1=em.dept2
    -> and em.age=emp_new.age;
+------+----------+
| 姓名 | 部门名称 |
+------+----------+
| 张三 | 财务     |
| 荣七 | 销售     |
| 牛八 | 运维     |
+------+----------+

15.求李四的收入及部门名称

mysql> select incoming,dept_name  from emp_new a join dept b on a.dept2=b.dept1 a
nd name='李四';
+----------+-----------+
| incoming | dept_name |
+----------+-----------+
|     3500 | 财务      |
+----------+-----------+

16.列出部门员工数大于1个的部门名称

mysql> select dept_name ,count(1) from emp_new a join dept b on a.dept2=b.dept1 g
roup by dept_name having count(1)>1;
+-----------+----------+
| dept_name | count(1) |
+-----------+----------+
| 财务      |        3 |
| 销售      |        2 |
+-----------+----------+