第五次作业

 1.找出销售部门中年纪最大的员工的姓名
mysql> select name,age from emp
    -> inner join dept on dept.dept1=emp.dept2
    -> where dept_name='销售'
    -> order by age desc limit 1;

  2.求财务部门最低工资的员工姓名
mysql> select name from emp
    -> where incoming=(select min(incoming) from emp);

  3.列出每个部门收入总和高于9000的部门名称
mysql> select dept_name as 部门名称,sum(incoming) as 收入总和 from emp
    -> inner join dept on emp.dept2=dept.dept1
    -> group by dept_name
    -> having 收入总和>9000;

  4.求工资在7500到8500元之间，年龄最大的人的姓名及部门
mysql> select name from emp
    -> where incoming between 7500 and 8500
    -> order by age desc
    -> limit 1;

  5.找出销售部门收入最低的员工入职时间
mysql> select worktime_start from emp
    -> inner join dept on emp.dept2=dept.dept1
    -> where dept_name='销售'
    -> order by incoming
    -> limit 1;

  6.财务部门收入超过2000元的员工姓名
mysql> select name as 姓名 from emp
    -> inner join dept on emp.dept2=dept.dept1
    -> where dept_name='财务' and incoming>2000;

  7.列出每个部门的平均收入及部门名称
mysql> select dept_name as 部门名称,avg(incoming) as 平均收入 from emp
    -> inner join dept on emp.dept2=dept.dept1
    -> group by dept_name;

  8.IT技术部入职员工的员工号
mysql> select sid from emp
    -> inner join dept on emp.dept2=dept.dept1
    -> where dept_name='IT技术';

  9.财务部门的收入总和；
mysql> select sum(incoming) as 收入总和 from emp
    -> inner join dept on emp.dept2=dept.dept1
    -> where dept_name='财务';

  10.找出哪个部门还没有员工入职；
mysql> select dept_name from emp
    -> right join dept on emp.dept2=dept.dept1
    -> where sid is null;

  11.列出部门员工收入大于7000的部门编号，部门名称；
mysql> select dept2 as 部门编号,dept_name as 部门名称 from emp
    -> inner join dept on emp.dept2=dept.dept1
    -> where incoming>7000;

  12.列出每一个部门的员工总收入及部门名称；
mysql> select dept_name as 部门名称,sum(incoming) as 总收入 from emp
    -> inner join dept on emp.dept2=dept.dept1
    -> group by dept_name;

  13.列出每一个部门中年纪最大的员工姓名，部门名称；
mysql> select emp.name as 员工姓名,dept.dept_name as 部门名称 from dept
    -> inner join (select max(age) as age,dept2 from emp group by dept2) as em on dept.dept1=em.dept2
    -> inner join emp on dept.dept1=emp.dept2 and em.age=emp.age;

  14.求李四的收入及部门名称
mysql> select incoming as 收入,dept_name from emp
    -> inner join dept on emp.dept2=dept.dept1
    -> where name='李四';