1、-1列出至少有一个员工的所有部门。select count(*),deptno from emp group by deptno having count(*)1;-2列出薪金比 “SMITH”多的所有员工。select * from empwhere sal(select sal from emp where ename=SMITH);-3列出所有员工的姓名及其直接上级的姓名。select ename,(select ename from emp where empno=a.mgr) from emp a;select a.ename,b.ename from emp a,emp b whe
2、re a.mgr=b.empno(+);-4列出受雇日期晚于其直接上级的所有员工。select ename from emp a where hiredate(select hiredate from emp where empno=a.mgr);列出受雇日期早于其直接上级的所有员工。select ename from emp a where hiredate1500;-8列出在部门 “SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。select ename from emp where deptno=(select deptno from dept where dname
3、=SALES);-9列出薪金高于公司平均薪金的所有员工。select ename from emp where sal(select avg(sal) from emp);-10列出与 “SCOTT”从事相同工作的所有员工。select * from emp where job=(select job from emp where ename=SCOTT);-11列出薪金等于部门 30 中员工的薪金的所有员工的姓名和薪金。select * from emp where sal in(select sal from emp where deptno=30);-12列出薪金高于在部门 30 工作的
4、所有员工的薪金的员工姓名和薪金。select * from emp where salall(select sal from emp where deptno=30);-13列出在每个部门工作的员工数量、平均工资和平均服务期限。select deptno,count(*),trunc(avg(sal+nvl(comm,0) avgsal,trunc(avg(sysdate-hiredate) avgdayfrom emp group by deptno;-14列出所有员工的姓名、部门名称和工资。select ename,dname,sal+nvl(comm,0) from emp,dept w
5、here emp.deptno=dept.deptno;-15列出从事同一种工作但属于不同部门的员工的一种组合。select distinct a.ename,a.job,a.deptno from emp a,emp bwhere (a.job=b.job)and(a.deptno!=b.deptno) order by a.job;-16列出所有部门的详细信息和部门人数。select dept.*,(select count(*) from emp group by deptno having deptno=dept.deptno) totfrom dept;-17列出各种工作的最低工资。
6、select job,min(sal+nvl(comm,0) from emp group by job;-18列出各个部门的 MANAGER（经理）的最低薪金。select min(sal) from emp where job=MANAGER ;-19列出所有员工的年工资,按年薪从低到高排序。select ename,(sal+nvl(comm,0)*12 tot from emp order by tot;要求给工资不到部门平均工资的那些人加上部门平均工资的 5update emp set sal*1.05 where empno in( select empno from emp e ,(select avg(sal) as avg_sal,deptno from emp group by deptno) aa where e.deptno = aa.deptno and e.sal aa.avg_sal)
