MySQL练习4

1.新增员工表emp和部门表dept
create table dept (deptl int ,dept_name varchar(11))charset=utf8;
create table emp (sid int ,name varchar (11),age int,worktime_start date, incoming int,dept2 int) charset=utf8;
insert into dept values(101，'财务')，(102，'销售'),(103，'IT技术')，(104,'行政');
insert into emp values
(1789,'zhangsan',35,'1980/1/1',4000,101),(1674,'lisi',32,'1983/4/1',3500,101),(1776,'wangwu',24,'1990/7/1',2000,101),(1568,'zhaoliu',57,'1970/10/11',7500,102),(1564,'rongqi',64,'1963/10/11',8500,102),(1879,'niuba',55,'1971/10/20',7300,103),
(1668,'qianjiu',64,'1963/5/4',8000,102),(1724,'wushi',22,'2023/5/8',1500,103),(1770,'suner',65,'1986/8/12',9500,101),(18400,'gouyi',65,'1986/8/12',1500,101);

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

select name from emp where age in

(select max(age) from emp where dept2 in

(select deptl  from dept where dept_name ='xiaoshou'));

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

select name from emp where incoming in

(select min(incoming) from emp where dept2 in

(select deptl  from dept where dept_name ='caiwu'));

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

select dept_name,sum(incoming) t1 

from dept,emp

where dept.deptl = emp.dept2

group by dept_name

having t1>9000;

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

select name,dept_name

from dept,emp

where dept.deptl = emp.dept2

and age=(select max(age) from emp where incoming >7500 and incoming <8500)；

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

select worktime_start,name

from emp

where incoming =(select min(incoming) from emp where dept2 =102);

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

select name from emp inner join dept

on dept.deptl=emp.dept2

where incoming >2000

and dept.dept_name ='caiwu'

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

select avg(incoming),dept.dept_name from  dept inner join emp

on dept.deptl = emp.dept2

group by dept.dept_name;

8.IT技术部入职员工的员工号

select sid from  dept inner join emp

on dept.deptl = emp.dept2

where dept.dept_name ='ITjishu';

9.财务部门的收入总和;

select sum(incoming) from

dept inner join emp

on dept.deptl = emp.dept2

where dept.dept_name = 'caiwu';

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

select dept.dept_name  from dept left outer join emp

on  dept.deptl = emp.dept2

where sid is null;

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

select dept.deptl,dept.dept_name  from 

dept inner join emp

on  dept.deptl = emp.dept2

where emp.incoming>7000;

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

select dept.dept_name,sum(incoming)  from 

dept inner join emp

on   dept.deptl = emp.dept2

group by  dept.dept_name;

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

select name,dept.dept_name  from 

dept inner join emp

on   dept.deptl = emp.dept2

where (age ,emp.dept2) in (select max(age),dept2 from emp group  by  dept2);

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

select incoming , dept.dept_name from 

dept inner join emp

on   dept.deptl = emp.dept2

where name = 'lisi';

15.列出每个部门中收入最高的员工姓名，部门名称，收入，并按照收入降序

select dept.dept_name,name,incoming from 

dept inner join emp

on   dept.deptl = emp.dept2

where (incoming,dept2) in ( select max(incoming) ,dept2  from emp

group by dept2

order by incoming);