--1.列出所有员工及直接上级的姓名 select e.ename,nvl(m.ename,'无') from emp e,emp m where e.mgr = m.empno(+); --2.列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称 select e.empno,e.ename,d.dname from emp e,emp m,dept d where e.mgr = m.empno(+) and e.deptno = d.deptno and e.HIREDATE < m.hiredate; --3.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门. select d.dname,nvl(e.ename,'无') from emp e,dept d where e.deptno(+) = d.deptno; --4.列出至少有一个员工的所有部门 select d.dname,count(e.empno) from emp e,dept d where e.deptno = d.deptno groupby d.dname havingcount(e.empno) >0; --5.列出薪金比"SMITH"多的所有员工信息. select*from emp where sal > (select sal from emp where ename ='SMITH'); --6.列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数. select e.ename,d.dname,ed.cc from emp e,dept d,(select deptno,count(empno) cc from emp groupby deptno) ed where e.deptno = d.deptno and d.deptno = ed.deptno and e.job ='CLERK'; select*from emp where JOB ='CLERK' ; --7.列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数. select e.job,count(e.empno) from emp e where e.job in (select job from emp groupby job havingmin(sal)>1500) groupby e.job; --8.列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部门编号. select ename from emp where deptno = (select deptno from dept where dname ='SALES'); --9.列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级. select ename 姓名,d.dname 部门名称,nvl((select m.ename from emp m where e.mgr = m.empno),'无') 上级领导, (select grade from salgrade where e.sal between losal and hisal) 工资等级 from emp e,dept d where e.deptno = d.deptno and e.sal > (selectavg(sal) from emp); --或 select e.ename 姓名,d.dname 部门名称,nvl(m.ename,'无') 上级领导,s.grade 工资等级 from emp e,dept d,emp m,salgrade s where e.deptno = d.deptno and e.sal > (selectavg(sal) from emp) and e.deptno = d.deptno and e.mgr = m.empno(+) and e.sal between losal and hisal; --10.列出与"SCOTT"从事相同工作的所有员工及部门名称. select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno and e.job = (select job from emp where ename ='SCOTT') and e.ename <>'SCOTT'; --11.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金. select ename,sal from emp where sal in (select sal from emp where deptno =30) and deptno <>30; --12.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金.部门名称. select e.ename,e.sal,d.dname from emp e,dept d where e.deptno = d.deptno and e.sal >all (select sal from emp where deptno =30) and e.deptno <>30; --13.列出在每个部门工作的员工数量,平均工资和平均服务期限. select d.dname 部门名称,ed.c 员工数量,round(ed.a,2) 平均工资,round(ed.h,0) 服务期限 from dept d ,(select deptno,count(empno) c,avg(sal) a,avg(months_between(sysdate,hiredate)/12) h from emp groupby deptno) ed where d.deptno = ed.deptno; --或 select d.dname 部门名称,count(e.empno) 员工数量,round(avg(e.sal),2) 平均工资,round(avg(months_between(sysdate,hiredate)/12),0) 服务期限 from emp e,dept d where e.deptno = d.deptno groupby d.dname; --14.列出所有员工的姓名、部门名称和工资。 select e.ename,d.dname,e.sal from emp e,dept d where e.deptno = d.deptno; --15.列出所有部门的详细信息和人数 select d.*,(selectcount(e.empno) from emp e where e.deptno = d.deptno) 人数 from dept d; --或 select d.*,nvl(ed.c,0) 人数 from dept d,(select deptno,count(empno) c from emp groupby deptno) ed where d.deptno = ed.deptno(+); --16.列出各种工作的最低工资及从事此工作的雇员姓名 select*from emp where sal in (selectmin(sal) sal from emp groupby job) --17.列出各个部门的MANAGER(经理)的最低薪金 select deptno,min(sal) from emp where job ='MANAGER'groupby deptno; --18.列出所有员工的年工资,按年薪从低到高排序 select ename, (sal + nvl(comm,0)) *12 income from emp orderby income; --19.查出某个员工的上级主管,并要求出这些主管中的薪水超过3000. selectdistinct m.ename 主管, m.sal 主管工资 from emp e,emp m where e.mgr = m.empno and m.sal >3000; --20.求出部门名称中,带'S'字符的部门员工的工资合计、部门人数. select d.dname 部门,count(e.sal) 工资合计,count(e.empno) 部门人数 from emp e,dept d where e.deptno(+) = d.deptno and d.dname like'%S%'groupby d.dname; --21.给任职日期超过10年的员工加薪10%. update emp set sal = sal + sal *0.1where (months_between(sysdate,hiredate)/12) >10;