3月1号作业

3月1号作业
1、select sum(incoming),dept_name  from (select*from dept left join empon dept.dept1=emp.dept2)t where t.dept_name='caiwu'; （显示收入总和财务部门）
     select sum(incoming),dept_name  from (select*from emp right join dept on dept.dept1=emp.dept2)t where t.dept_name='caiwu'; （显示收入总和财务部门）
     select sum(incoming) from emp where dept2 =(select dept1 from dept where dept_name='caiwu'); （只显示收入总和）
     select sum(incoming) from emp where dept2 in(select dept1 from dept where dept_name='caiwu');  (只显示收入总和）
     select sum(incoming) from emp where dept2 not in(select dept1 from dept where dept_name!='caiwu'); （只显示收入总和）

2、select sid,name,dept_name from (select*from emp left join dept on dept.dept1=emp.dept2)t where t.dept_name='IT';
     select sid,name,dept_name from (select*from dept right join emp on dept.dept1=emp.dept2)t where t.dept_name='IT';
     select sid,name from emp where dept2 =(select dept1 from dept where dept_name='IT'); （只显示员工号和姓名）
     select sid,name from emp where dept2 in(select dept1 from dept where dept_name='IT');  （只显示员工号和姓名）
     select sid,name from emp where dept2 not in(select dept1 from dept where 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;
      select max(age),name,dept_name from (select*from emp right join dept 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;
      select sum(incoming),dept_name from (select*from emp right join dept on dept.dept1=emp.dept2)t group by t.dept_name;

5、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;
      select name,incoming,dept_name,dept2 from (select*from emp right join dept 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';
      select name,worktime,dept_name from (select*from emp right join dept on dept.dept1=emp.dept2)t where t.worktime>'2022-03-01';