Oracle 试题分析（十七）

01. 查询各个部门的平均工资

SQL> select deptno as "部门编号",sum(sal)/count(sal) as "部门平均工资" from emp group by deptno;

SQL> select deptno as "部门编号",avg(nvl(sal,0)) as "部门平均工资" from emp group by deptno;

02. 显示各种职位的最低工资

SQL> select job as "职位",min(sal) from emp group by job;

03. 按照入职日期由新到旧排列员工信息

SQL> select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp order by hiredate desc;

04. 查询员工的基本信息，附加其上级的姓名

SQL>select a.empno,a.ename,a.job,a.mgr,a.hiredate,a.sal,a.comm,a.deptno,(select b.ename from emp b where a.mgr=b.empno) as "我的boss" from emp a order by a.empno;

05. 显示工资比'ALLEN'高的所有员工的姓名和工资

SQL> select a.ename,a.sal from emp a where sal>(select b.sal from emp b where b.ename='ALLEN');

06. 显示与'SCOTT'从事相同工作的员工的详细信息

SQL> select  a.empno,a.ename,a.job,a.mgr,a.hiredate,a.sal,a.comm,a.deptno from emp a where a.job=(select b.job from emp b where b.ename='SCOTT');

07. 显示销售部('SALES')员工的姓名

SQL> select a.ename as "销售部旗下员工的姓名" from emp a where a.deptno=(select b.deptno from dept b where b.dname='SALES');

08. 显示与30 号部门'MARTIN'员工工资相同的员工的姓名和工资

SQL> select a.ename,a.sal from emp a where a.sal=(select b.sal from emp b where b.deptno=30 and b.ename='MARTIN');

09. 查询所有工资高于平均工资(平均工资包括所有员工)的销售人员('SALESMAN')

SQL> select * from emp where sal<(select avg(nvl(sal,0)) from emp) and job='SALESMAN';

10. 显示所有职员的姓名及其所在部门的名称和工资

SQL> select a.*,b.dname from emp a left join dept b on a.deptno=b.deptno ;

11. 查询在研发部('RESEARCH')工作员工的编号，姓名，工作部门，工作所在地

SQL> select a.empno,a.ename,a.deptno,b.loc from emp a left join dept b on b.dname='RESEARCH';

12. 查询各个部门的名称和员工人数

SQL> select b.deptno as "部门号",b.dname as " 部门名称",(select count(*) from emp a where a.deptno=b.deptno ) as "部门人数" from dept b group by (b.deptno,b.dname);

13. 查询各个职位员工工资大于平均工资(平均工资包括所有员工)的人数和员工职位

SQL>  select job,count(*) as "员工人数" from emp where sal>(select avg(nvl(sal,0)) from emp) group by job;

14. 查询工资相同的员工的工资和姓名

SQL> select a.ename,a.sal from emp a where a.sal=(select b.sal from emp b where b.empno!=a.empno and b.sal=a.sal);

15. 查询工资最高的3 名员工信息

SQL> select * from (select * from emp b order by b.sal desc) where rownum between 0 and 3;

16. 按工资进行排名,排名从1 开始,工资相同排名相同(如果两人并列第1 则没有第2 名,从第

三名继续排)

SQL> select a.*,RANK () over(ORDER BY nvl(sal,0) DESC) as　"名次" from emp a;

17. 求入职日期相同的(年月日相同)的员工

SQL> select * from emp a where a.hiredate=(select b.hiredate from emp b where b.hiredate=a.hiredate and b.empno!=a.empno);

18. 查询每个部门的最高工资

SQL> select deptno,max(sal) from emp group by deptno;

19. 查询每个部门,每种职位的最高工资

SQL> select distinct(a.job),a.deptno,(select max(b.sal) from emp b where a.deptno=b.deptno and a.job=b.job) from emp a;

20. 查询每个员工的信息及工资级别(用到表Salgrade)

SQL> select a.*,b.grade from emp a join salgrade b on a.sal between b.losal and b.hisal;

21. 查询工资最高的第6-10 名员工

SQL> select a.* from (select b.*,rownum rown from (select c.*,rownum from emp c where rownum<=10 order by c.sal desc )  b ) a  where a.rown>5;

22. 查询各部门工资最高的员工信息

SQL> select c.*,b.sal "每部门最高工资" from (select deptno,max(sal) sal from emp group by deptno) b join emp c on c.deptno=b.deptno and c.sal=b.sal;

23. 查询每个部门工资最高的前2 名员工

SQL>  select a.* from ( select b.*,rank() over(PARTITION BY deptno ORDER BY nvl(sal,0) DESC) mc from emp b) a where a.mc<3;

24. 查询出有3 个以上下属的员工信息

SQL> select * from (select a.empno,a.ename,a.deptno,count(*) cu from emp a join emp b on a.empno=b.mgr group by (a.empno,a.ename,a.deptno)) c  where c.cu>2;

25. 查询所有大于本部门平均工资的员工信息()

SQL> select * from emp a  where a.sal>(select avg(nvl(c.sal,0)) from emp c where c.deptno=a.deptno)

26. 查询平均工资最高的部门信息

SQL> select * from dept d join (select b.dp sp,rank() over(order by b.ss desc) cc from (select deptno dp,avg(nvl(sal,0)) ss from emp group by deptno) b) c on d.deptno=c.sp where c.cc=1;

27. 查询大于各部门总工资的平均值的部门信息

SQL> select * from dept d where d.deptno in (select a.deptno from emp a join (select avg(sal) ss from emp ) b on a.sal>b.ss);

28. 查询大于各部门总工资的平均值的部门下的员工信息(考察知识点:子查询,组函数,连接

查询)

SQL> select * from emp a join (select avg(sal) ss from emp ) b on a.sal>b.ss;

29. 查询没有员工的部门信息

SQL>  select d.* from emp a right join dept d on a.deptno=d.deptno where a.deptno is null;

30. 查询用户（users 表）huxz 所下所有订单编号，下单日期，总价格(orders 表)，并包括订

单中的商品数量（orderitem 表），名称（product 表），价格（product 表）

SQL> select os.orderid,os.orderdate,os.totalprice,pm.qty ,pt.price from users us join orders os on us.username='huxz' and us.username=os.username join orderitem pm on os.orderid=pm.orderid join product pt on pt.productid=pm.productid;

31. 查询100001 号商品被哪些顾客（users 表）购买过，下单日期(orders 表)，每人购买的数

量（orderitem 表），购买时的价格（product 表）

SQL>select os.username,os.orderdate,om.qty,pt.price from orders os join orderitem om on os.orderid=om.orderid and om.productid='100001' join product pt on om.productid=pt.productid;

32. 查询出哪些商品从未被订购过

SQL>select pt.* from orderitem om right join product pt on pt.productid=om.productid and om.orderitemid is null;

33. 查询出被订购过2 次以上的商品信息

SQL> select * from product pt join (select om.productid ids,count(om.productid) oid from orderitem om group by om.productid) bb on bb.ids=pt.productid where bb.oid>=2;

<!--EndFragment-->