数据库 两表 作业

已知2张基本表：部门表：dept （部门号，部门名称）;员工表 emp（员工号，员工姓名，年龄，入职时间，收入，部门号）
1：dept表中有4条记录：
部门号(dept1) 部门名称(dept_name )
101 财务
102 销售
103 IT技术
104 行政
2：emp表中有6条记录：
员工号 员工姓名 年龄 入职时间 收入 部门号对应字段名称为： (sid name age worktime_start incoming dept2)
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

1.列出每个部门的平均收入及部门名称;
select avg(incoming),dept_name from dept a left join emp b on a.dept1=b.dept2 group by dept_name;

2.财务部门的收入总和；
select sum(incoming) from dept inner join emp on
dept.dept1=emp.dept2 where dept_name="财务"

3.It技术部入职员工的员工号
select sid from dept a inner join emp b on a.dept1=b.dept2 where dept_name="IT技术";

4.财务部门收入超过2000元的员工姓名
select name from dept a inner join emp b on a.dept=b.emp where incoming>2000 and dept_name="财务"

select name from emp where incoming>2000 and dept2=(select dept1 from dept where dept_name="财务");

5.找出销售部收入最低的员工的入职时间；
select woektime_start from dept right join emp on dept.dept1=emp.dept2 where incoming=(select min(incoming) from
dept inner join emp on dept.dept1=emp.dept2 and dept2=(select dept1 from dept where dept_name='销售')) AND
dept_name='销售';

6.找出年龄小于平均年龄的员工的姓名，ID和部门名称
select name,sid ,dept_name from dept right join emp on dept.dept1=emp.dept2 where sge<(select avg(age）from emp);

7.列出每个部门收入总和高于9000的部门名称
select dept_name from dept inner join emp on dept.dept1=emp.dept2 group by dept_name having sum(incoming) >9000;

8.查出财务部门工资少于3800元的员工姓名

select name from dept inner join emp on dept.dept1=emp.dept2 where incoming<3800 and where dept_name="财务"

9.求财务部门最低工资的员工姓名；
select name from dept inner join emp on dept.dept1=emp.dept2 where incoming=(select min(incoming) from
dept inner join emp on dept.dept1=emp.dept2 and dept2=(select dept1 from dept where dept_name='财务')) AND
dept_name='财务';

10.找出销售部门中年纪最大的员工的姓名
select name from dept inner join emp on dept.dept1=emp.dept2 where age=(select max(age) from
dept inner join emp on dept.dept1=emp.dept2 and dept2=(select dept1 from dept where dept_name='销售')) AND
dept_name='销售';

11.求收入最低的员工姓名及所属部门名称：
SELECT name,dept_name from dept a INNER JOIN emp b on a.dept1=b.dept2 where incoming=(select min(incoming)
from dept a INNER JOIN emp b on a.dept1=b.dept2);

12.求李四的收入及部门名称
select incoming，dept_name from dept inner join emp on dept.dept1=emp.dept2 where name="李四"

13.求员工收入小于4000元的员工部门编号及其部门名称
select dept2,dept_name from dept inner join emp on dept.dept1=emp.dept2 where incoming<4000);

14.列出每个部门中收入最高的员工姓名，部门名称，收入，并按照收入降序；
SELECT dept.dept_name,emp.name,emp.incoming from emp LEFT JOIN dept on emp.dept2 =
dept.dept1 where (dept2,incoming) in (SELECT dept2,max(incoming) from emp GROUP BY dept2) ORDER BY emp.incoming desc

15.求出财务部门收益最高的俩位员工的姓名，工号，收益
select sid,name from dept inner join emp on dept.dept1=
emp.dept2 where dept_name="财务" order by incoming desc limit 2;

16.查询财务部低于平均收入的员工号与员工姓名：

SELECT sid,name from dept a INNER JOIN emp b on a.dept1=b.dept2
where incoming<(SELECT avg(incoming) from emp) and dept_name='财务';

17.列出部门员工数大于1个的部门名称；
select dept_name from(select count() ,dept_name from dept inner join emp on
dept.dept1=emp.dept2 group by dept_name having count()>1)a;

18.列出部门员工收入不超过7500，且大于3000的员工年纪及部门编号；
select age,dept2 from emp where incoming>3000 and incoming<7500;

19.求入职于20世纪70年代的员工所属部门名称；
select dept_name,name from dept INNER JOIN emp on dept.dept1=emp.dept2 where
woektime_start>'1970/01/01' and woektime_start<'1980/01/01';

20.查找张三所在的部门名称；
select dept_name from dept inner join emp on dept.dept1=emp.dept2 where name="张三";

21.列出每一个部门中年纪最大的员工姓名，部门名称；
select name,dept_name from dept inner join emp on dept.dept1=emp.dept2
where (age,dept_name) in(SELECT max(age),dept2 from emp GROUP BY dept2);

22.列出每一个部门的员工总收入及部门名称；
select sum(incoming),dept_name from dept inner join emp on dept.dept1=emp.dept2 group by dept2;

23.列出部门员工收入大于7000的员工号，部门名称；
select sid,dept_name from dept INNER JOIN emp on dept.dept1=emp.dept2 where incoming>7000;

24.找出哪个部门还没有员工入职；
select dept_name from dept inner join emp on dept.dept1=emp.dept2 where name is null;

25.先按部门号大小排序，再依据入职时间由早到晚排序员工信息表 ；
select * from dept inner join emp on dept.dept1=emp.dept2 order by dept1 desc,woektime_start asc;

26.求出财务部门工资最高员工的姓名和员工号
select sid,name from dept inner join emp on dept.dept1=emp.dept2 where incoming=(select max(incoming)
from dept inner join emp on dept.dept1=emp.dept2 where dept_name="财务") and dept_name="财务";

27.求出工资在7500到8500之间，年龄最大的员工的姓名和部门名称。
select name,dept_name from dept inner join emp on dept.dept1=emp.dept2 where(incoming,max(age) in (select incoming,max(age) from dept inner join emp on dept.dept1=emp.dept2 where incoming in(7500,8500));