mysql查询作业

 

1、查询每个部门的所属员工

 select ename,name from dept3 left join emp3.deptno=dept_id;

 

2、查询研发部门的所属员工 

 select ename,name from dept3 left join emp3 on dept3.deptno=dept_id where deptno = '1001' ;

  

3、查询研发部和销售部的所属员工 

 select ename,name from dept3 left join emp3 on dept3.deptno=dept_id where deptno = '1001' or deptno = '1002' ;

 

4、查询每个部门的员工数,并升序排序 

 select name,count(1) from emp3,dept3 where dept3.deptno=emp3.dept_id and deptno > 3 group by deptno order by count(1) asc ;

 

5、查询人数大于等于3的部门，并按照人数降序排序 

  select name,count(1) from emp3,dept3 where dept3.deptno=emp3.dept_id group by deptno having count(1) >= 3 order by count(1) desc ;