题目
1.找出销售部门中年纪最大的员工的姓名
2.求财务部门最低工资的员工姓名
3.列出每个部门收入总和高于9000的部门名称
4.求工资在7500到8500元之间，年龄最大的人的姓名及部门
5.找出销售部门收入最低的员工入职时间
6.财务部门收入超过2000元的员工姓名一
7.列出每个部门的平均收入及部门名称
8.运维部入职员工的员工号
9.财务部门的收入总和；
10.先按部门号大小排序，再依据入职时间由早到晚排序员工信息表
11.找出哪个部门还没有员工入职；
12.列出部门员工收入大于7000的部门编号，部门名称：
13.列出每一个部门的员工总收入及部门名称；
14.列出每一个部门中年纪最大的员工姓名，部门名称；
15.求李四的收入及部门名称
16.列出部门员工数大于1个的部门名称
创建库并插入数据
mysql> create table emp_new(sid int, name varchar(11), age int, worktime_start date, incoming int, dept2 int );
Query OK, 0 rows affected (0.04 sec)mysql> insert into emp_new values (1789,'张三',35,'1980/1/1',4000,101), (1674,'李四',32,'1983/4/1',3500,101), (1776,'王五',24,'1990/7/1',2000,101), (1568,'赵六',57,'1970/10/11',7500,102), (1564,'荣七',64,'1963/10/11',8500,102), (1879,'牛八',55,'1971/10/20',7300,103);mysql> select * from emp_new;
+------+------+------+----------------+----------+-------+
| 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 |
| 1568 | 赵六 | 57 | 1970-10-11 | 7500 | 102 |
| 1564 | 荣七 | 64 | 1963-10-11 | 8500 | 102 |
| 1879 | 牛八 | 55 | 1971-10-20 | 7300 | 103 |
+------+------+------+----------------+----------+-------+
6 rows in set (0.00 sec)mysql> create table dept(dept1 int, dept_name varchar(11));
Query OK, 0 rows affected (0.04 sec)mysql> insert into dept values(101,"财务"),(102,"销售"),(103,"运维"),(104,"行政");
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0mysql> select * from dept;
+-------+-----------+
| dept1 | dept_name |
+-------+-----------+
| 101 | 财务 |
| 102 | 销售 |
| 103 | 运维 |
| 104 | 行政 |
+-------+-----------+
4 rows in set (0.00 sec)查询语句
#1.找出销售部门中年纪最大的员工的姓名
mysql> select name,age from emp_new where age=(select max(age) from emp_new where dept2=(select dept1 from dept where dept_name='销售'));
+------+------+
| name | age |
+------+------+
| 荣七 | 64 |
+------+------+
1 row in set (0.00 sec)###第二种方法
mysql> select name,age from dept a,emp_new b where a.dept1=b.dept2 anddept_name='销售' order by age desc limit 1;
+------+------+
| name | age |
+------+------+
| 荣七 | 64 |
+------+------+
1 row in set (0.00 sec)#2.求财务部门最低工资的员工姓名
mysql> select name from emp_new where incoming=(select min(incoming) from emp_new where dept2=(select dept1 from dept where dept_name='财务'));
+------+----------+
| name | incoming |
+------+----------+
| 王五 | 2000 |
+------+----------+
1 row in set (0.00 sec)#3.列出每个部门收入总和高于9000的部门名称
mysql> select dept_name as '部门',sum(incoming) '总工资' from emp_new,dept where emp_new.dept2=dept.dept1 group by dept_name having sum(inco
ming)>9000;
+------+--------+
| 部门 | 总工资 |
+------+--------+
| 财务 | 9500 |
| 销售 | 16000 |
+------+--------+
2 rows in set (0.00 sec)#4.求工资在7500到8500元之间，年龄最大的人的姓名及部门
mysql> select name,dept_name-> from emp_new-> join dept on emp_new.dept2 = dept.dept1-> where emp_new.incoming between 7500 and 8500-> and age = (-> select max(age)-> from emp_new-> where incoming between 7500 and 8500-> );
+------+-----------+
| name | dept_name |
+------+-----------+
| 荣七 | 销售 |
+------+-----------+
1 row in set (0.00 sec)#5.找出销售部门收入最低的员工入职时间
mysql> select worktime_start from emp_new inner join dept on emp_new.d
ept2=dept.dept1 where dept_name="销售" order by incoming limit 1;
+----------------+
| worktime_start |
+----------------+
| 1970-10-11 |
+----------------+
1 row in set (0.00 sec)#6.财务部门收入超过2000元的员工姓名
mysql> select name from emp_new join dept on emp_new.dept2 = dept.dept1 where dept_name='财务' and incoming > 2000;
+------+
| name |
+------+
| 张三 |
| 李四 |
+------+
2 rows in set (0.00 sec)#7.列出每个部门的平均收入及部门名称
mysql> select dept_name '部门名称',round(avg(incoming),2) '平均收入' from emp_new join dept on emp_new.dept2 = dept.dept1 group by dept_name;
+----------+----------+
| 部门名称 | 平均收入 |
+----------+----------+
| 财务 | 3166.67 |
| 销售 | 8000.00 |
| 运维 | 7300.00 |
+----------+----------+
3 rows in set (0.00 sec)#8.运维部入职员工的员工号
mysql> select sid from emp_new inner join dept on emp_new.dept2=dept.dept1 where dept_name='运维';
+------+
| sid |
+------+
| 1879 |
+------+
1 row in set (0.00 sec)#9.财务部门的收入总和；
mysql> select sum(incoming) as '收入总和' from emp_new inner join depton emp_new.dept2=dept.dept1 where dept_name='财务';
+----------+
| 收入总和 |
+----------+
| 9500 |
+----------+
1 row in set (0.00 sec)#10.先按部门号大小排序，再依据入职时间由早到晚排序员工信息表
mysql> select * from emp_new order by dept2 desc, worktime_start;
+------+------+------+----------------+----------+-------+
| sid | name | age | worktime_start | incoming | dept2 |
+------+------+------+----------------+----------+-------+
| 1879 | 牛八 | 55 | 1971-10-20 | 7300 | 103 |
| 1564 | 荣七 | 64 | 1963-10-11 | 8500 | 102 |
| 1568 | 赵六 | 57 | 1970-10-11 | 7500 | 102 |
| 1789 | 张三 | 35 | 1980-01-01 | 4000 | 101 |
| 1674 | 李四 | 32 | 1983-04-01 | 3500 | 101 |
| 1776 | 王五 | 24 | 1990-07-01 | 2000 | 101 |
+------+------+------+----------------+----------+-------+
6 rows in set (0.00 sec)#11.找出哪个部门还没有员工入职；
mysql> select dept_name from dept left join emp_new on dept.dept1=emp_
new.dept2 where sid is null;
+-----------+
| dept_name |
+-----------+
| 行政 |
+-----------+
1 row in set (0.00 sec)#12.列出部门员工收入大于7000的部门编号，部门名称：
mysql> select dept2,dept_name from emp_new join dept on emp_new.dept2=
dept.dept1 where incoming > 7000;
+-------+-----------+
| dept2 | dept_name |
+-------+-----------+
| 102 | 销售 |
| 102 | 销售 |
| 103 | 运维 |
+-------+-----------+
3 rows in set (0.00 sec)#13.列出每一个部门的员工总收入及部门名称；
mysql> select dept_name,sum(incoming) from emp_new join dept on emp_ne
w.dept2=dept.dept1 group by dept_name;
+-----------+---------------+
| dept_name | sum(incoming) |
+-----------+---------------+
| 财务 | 9500 |
| 销售 | 16000 |
| 运维 | 7300 |
+-----------+---------------+
3 rows in set (0.00 sec)#14.列出每一个部门中年纪最大的员工姓名，部门名称；
mysql> select name,dept_name from emp_new join dept on emp_new.dept2=d
ept.dept1-> where(emp_new.dept2,emp_new.age)-> in (select dept2,max(age) from emp_new group by dept2);
+------+-----------+
| name | dept_name |
+------+-----------+
| 张三 | 财务 |
| 荣七 | 销售 |
| 牛八 | 运维 |
+------+-----------+
3 rows in set (0.00 sec)#方法二
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;
+------+----------+
| 姓名 | 部门名称 |
+------+----------+
| 张三 | 财务 |
| 荣七 | 销售 |
| 牛八 | 运维 |
+------+----------+
3 rows in set (0.00 sec)#15.求李四的收入及部门名称
mysql> select incoming , dept_name from emp_new join dept on emp_new.d
ept2=dept.dept1 where name='李四';
+----------+-----------+
| incoming | dept_name |
+----------+-----------+
| 3500 | 财务 |
+----------+-----------+
1 row in set (0.00 sec)#16.列出部门员工数大于1个的部门名称
mysql> select dept_name from emp_new join dept on emp_new.dept2=dept.dept1-> group by dept_name-> having count(emp_new.sid)>1;
+-----------+
| dept_name |
+-----------+
| 财务 |
| 销售 |
+-----------+
2 rows in set (0.00 sec)#方法二
mysql> select dept_name as 部门名称 from dept inner join (select count(*) as 员工数,dept2 from emp_new group by dept2) as de on dept.dept1=de.dept2 where de.员工数 > 1;
+----------+
| 部门名称 |
+----------+
| 财务 |
| 销售 |
+----------+
2 rows in set (0.00 sec)
