双表查询

#1、财务部门的收入总和；
select sum(incoming),dept_name  from (select*from emp right join dept on dept.dept1=emp.dept2)t where t.dept2=101;
#2、It技术部入职员工的员工号
SELECT sid,name FROM(SELECT*from dept LEFT JOIN emp on dept.dept1=emp.dept2)t where t.dept2=103;
#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 dept2,name,incoming,dept_name 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,name,worktime_start,dept2 FROM(SELECT*FROM dept LEFT JOIN emp on dept.dept1=emp.dept2)t WHERE t.worktime_start is null;