1． 列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。(经典的两个表连接) select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno and d.dname=’SALES’; 2． 列出薪金高于公司平均薪金的所有员工。(反复查自己) select ename,sal from emp where sal>(select avg(sal) from emp); 3． 列出与“SCOTT”从事相同工作的所有员工。(排除自己) select ename,job from emp where ename!=’SCOTT’ and job=(select job from emp where ename=’SCOTT’); 4． 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。(max的用法) select ename,sal from emp where sal>(select max(sal) from emp where deptno=30); 5． 列出在每个部门工作的员工数量、平均工资和平均服务期限。 select deptno as “部门编号”,count(empno) as “员工数量”,round(avg(sal),2) “平均工资”,trunc(avg(months_between(sysdate,hiredate))) as “服务期限/月” from emp group by deptno; 6． 列出所有员工的姓名、部门名称和工资. select e.ename,d.dname,e.sal from emp e,dept d where e.deptno=d.deptno; 7． 列出所有部门的详细信息和部门人数。 select d.deptno,d.dname,d.loc,count(e.empno) as “部门人数” from dept d,emp e where d.deptno=e.deptno(+) group by d.deptno,d.dname,d.loc; 8． 找出所有的普通员工 select * from emp where empno not in (Select mgr from emp where mgr is not null); 9． 求部门中哪些人的薪水最高 select ename,sal from emp where sal in(select max(sal) from emp group by deptno); 10． 求部门平均薪水的等级 select t.name as “部门名称”,round(t.avgsal,2) as “部门平均薪水”,s.grade as “等级” from (select avg(e.sal) as avgsal,d.dname as name from emp e,dept d where e.deptno=d.deptno group by d.dname) t,salgrade s where round(t.avgsal,2) between losal and hisal; 11． 雇员中哪些人是经理人 select ename from emp where empno in (select distinct mgr from emp); 12． 不准用组函数，求薪水的最高值 select ename,sal from (select * from emp order by sal desc) where rownum=1; 13． 求平均薪水最高的部门的部门编号 select t.deptno as “部门编号”,t.sal as “部门最高平均薪水” from (select d.deptno as deptno ,round(avg(e.sal),2) as sal from dept d,emp e where d.deptno=e.deptno group by d.deptno order by sal desc) t where rownum=1; 14． 求平均薪水最高的部门的部门名字 select t.dname as “部门名称”,t.sal as “部门最高平均薪水” from (select d.dname as dname,round(avg(e.sal),2) as sal from dept d,emp e where d.deptno=e.deptno group by d.dname order by sal desc) t where rownum=1; 15． 求比普通员工的最高薪水还要高的经理人名称 select ename,sal from emp where sal>(select max(sal) from emp where job not in (‘MANAGER’,’PRESIDENT’,’ANALYST’)) and job in (‘MANAGER’,’PRESIDENT’,’ANALYST’);
