1、财务部门的收入总和; | select sum(incoming) from emp where dept2=(select dept1 from dept where dept_name='caiwu'); |
2、It技术部入职员工的员工号 | select name from emp where dept2=(select dept1 from dept where dept_name='ITjishu'); |
3、列出每一个部门中年纪最大的员工姓名,部门名称; | select max(age),name,dept_name from (select*from dept left join emp on dept.dept1=emp.dept2)t group by t.dept_name;
|
4、列出每一个部门的员工总收入及部门名称; | select sum(incoming),dept_name from (select*from dept left join emp on dept.dept1=emp.dept2)t group by t.dept_name; |
5、列出部门员工收入大于7000的部门编号,部门名称; | select name,incoming,dept_name,dept2 from(select*from dept left join emp on dept.dept1=emp.dept2)t group by t.dept_name having incoming>7000;
|
6、找出哪个部门还没有员工入职; | select dept_name from dept where dept1 not in(select dept2 from emp); |