1、 列出员工表中每个部门的员工数和部门编号
Select deptno,count(*) from emp group by deptno;
补充1:列出员工表中,员工人数大于3的部门编号和员工人数
Select deptno,count(*) from emp group by deptno having count(*)>3;
补充2:列出员工表中,员工人数大于3的部门的部门编号,部门名称和部门位置
答案1:Select d.* from dept d,(select deptno,count(*) from emp group bydeptno having count(*)>3)x where d.deptno=x.deptno;
答案2:select * from dept where deptno in (select deptno from emp group bydeptno having count(*)>3);
补充3:列出员工表中,员工人数大于3的部门的部门编号,名称,位置和员工人数。
Select d.*,x.co from dept d,(selectdeptno,count(*) co from emp group by deptno having count(*)>3)x whered.deptno=x.deptno;(注意看聚合函数的处理方式)
2、 列出员工表中每个部门的员工数(员工数必须大于3),和部门名称
(1).先列出员工数大于3的部门号
Select deptno,count(*) from emp group by deptno having count(*)>3;
(2)列出员工表中每个部门的员工数(员工数必须大于3),和部门名称
Select x.co,d.dname from dept d,( Select deptno,count(*) cofrom emp group by deptno having count(*)>3)x where x.deptno=d.deptno ;
3、 找出工资比JONES多的员工
Select * from emp where sal>(select salfrom emp where ename=’JONES’);Select * from emp where sal>(select sal from emp where ename=’JONES’);
4、 列出所有员工的姓名和其上级的姓名(表的自连接)
Select e1.ename,e2.ename from emp e1,emp e2where e1.mgr=e2.empno;
5、 以职位分组,找出平均工资最高的两种职位
(1)先以职位分组并按平均工资降序排序
Select job,avg(sal)avg_sal from emp group by job order by avg_sal desc;<