双表练习3.1
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);
页:
[1]