select * from emp LEFT JOIN dept on emp.depart = dept.didwhere dept.did = 101 ORDER BY salary , hiretime
16、 查询101部门有多少员工,每月平均发多少工资
select avg(salary),count(*) from emp LEFT JOIN dept onemp.depart = dept.did where dept.did = 101
17、 查询101部门的所有员工信息,并显示所在部门名称
select * from emp LEFT JOIN dept on emp.depart = dept.did
18、 查询1001员工的部门领导信息
select * from emp LEFT JOIN dept on emp.depart = dept.didwhere dept.did = 101 and title = '经理'
19、 查询部门员工数量,平均工资,最低工资及最低工资的员工姓名
select ename,depart,'员工数量',minSalary,'平均工资' from emp,
(select dept.didas bumen,count(*) as '员工数量',MIN(salary) asminSalary,AVG(salary) as '平均工资'
from emp LEFT JOIN depton emp.depart = dept.did GROUP BY dept.did ) as a
where emp.depart = a.bumen and emp.salary =a.minSalary