上海25期 黄筠清 3.1日作业
1、财务部门的收入总和；select sum(incoming),dept_namefrom (select*from dept left join empon dept.dept1=emp.dept2)t where t.dept_name='财务';
2、It技术部入职员工的员工号
select sid,name,dept_name from (select*from emp left join dept on dept.dept1=emp.dept2)t where t.dept_name='IT技术';
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 name,worktime,dept_name from (select*from dept left join emp on dept.dept1=emp.dept2)t where t.worktime>'2022-03-01';
页:
[1]