Oracle经典练习题--进阶篇

-- 列出至少有三个员工的所有部门和部门信息。
 select count(empno) cotemp,deptno from emp group by deptno having count(empno)>2;
 select d.deptno,d.dname,d.loc from dept d join ( select count(empno) cotemp,deptno from emp group by deptno having count(empno)>2) t on d.deptno=t.deptno;
-- 2.列出受雇日期早于直接上级的所有员工的编号，姓名，部门名称
select e1.empno,e1.ename,d.dname 
from emp e1 ,emp e2 ,dept d
where e1.mgr=e2.empno and e1.deptno=d.deptno and e1.hiredate<e2.hiredate;
select * from emp;
   
-- 3.列出职位为“CLERK”的姓名和部门名称，部门人数：
/*
select e.ename,e.deptno,count(e.empno),d.dname from emp e join dept d on e.deptno=d.deptno 
where e.job='CLERK' group by e.deptno,d.dname,e.ename;
*/
select deptno,count(1) n from emp group by deptno;
select e.ename,d.dname,t.n from emp e join dept d on e.deptno=d.deptno join 
(select deptno,count(1) n from emp group by deptno) t on
d.deptno=t.deptno where e.job='CLERK';
      

-- 4.列出和“SCOTT”从事相同工作的所有员工及部门名称