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);