Oracle SQL部分练习题

SQL练习题

注：查询列表不建议用 “*”

1.列出至少有一个雇员的所有部门；

select * from dept where deptno in(select distinct deptno from emp);

(oracle11gCBO 新特性 in（多个值）会智能过滤掉重复字段，通过执行计划验证)；

select * from dept where deptno in (select deptno from emp group by deptno having count(deptno)>=1);

select * from dept a where exists (select null from emp b where a.deptno=b.deptno);

2.列出薪金比smith多的所有雇员

select ename from emp where sal>(select sal from emp where ename='SMITH');

3.列出雇员的姓名及其直接上级姓名(注：列出执行计划原由于显示结果不同，结果显示第二个执行计划要优秀一点，第一个执行计划走了全表扫描也符合常理，第二个走的唯一索引，索引>堆表；测试表较小没有参考价值仅得出结果不同，有利于以后做查询优化)

select e.ename,p.ename from emp e,emp p where  p.empno(+)=e.mgr;

select p.ename,e.ename from emp p left join emp e on  e.empno=p.mgr;

执行计划：

Execution Plan

----------------------------------------------------------

Plan hash value: 2341341676

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |    14 |   280 |     7  (15)| 00:00:01 |

|*  1 |  HASH JOIN OUTER   |      |    14 |   280 |     7  (15)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| EMP  |    14 |   140 |     3   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| EMP  |    14 |   140 |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("E"."EMPNO"(+)="P"."MGR")

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

         15  consistent gets

          0  physical reads

          0  redo size

        823  bytes sent via SQL*Net to client

        523  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

         14  rows processed

select e.ename,(select ename from emp p where  p.empno=e.mgr)as BoosName from emp e;

执行计划：

Execution Plan

----------------------------------------------------------

Plan hash value: 4000517069

--------------------------------------------------------------------------------

------

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time

     |

--------------------------------------------------------------------------------

------

|   0 | SELECT STATEMENT            |        |    14 |   140 |     3   (0)| 00:0

0:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    10 |     1   (0)| 00:0

0:01 |

|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:0

0:01 |

|   3 |  TABLE ACCESS FULL          | EMP    |    14 |   140 |     3   (0)| 00:0

0:01 |

--------------------------------------------------------------------------------

------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("P"."EMPNO"=:B1)

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

         17  consistent gets

          0  physical reads

          0  redo size

        849  bytes sent via SQL*Net to client

        523  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

         14  rows processed

4.列出入职日期早于其直接上级的所有雇员

a.

select p.ename from emp e,emp p where e.empno=p.mgr  and p.hiredate<e.hiredate;

select e.ename from emp e where e.hiredate<(select  hiredate from emp p where p.empno=e.mgr);

5、列出部门名称和这些部门的雇员,同时列出那些没有雇员的部门

select a.dname,b.ename from dept a,emp b where  a.deptno=b.deptno(+);

select a.dname,b.ename from dept a left join emp b on  a.deptno=b.deptno

6、列出所有“CLERK”（办事员）的姓名及其部门名称

select b.ename,a.dname from dept a,emp b where  a.deptno=b.deptno and b.job='CLERK';

7、列出各种工作类别的最低薪金，显示最低薪金大于1500的记录

select min(sal)as minsal,job from emp group by job having  min(sal)>1500;

--8、列出从事“SALESMAN”（销售）工作的雇员的姓名，假定不知道销售部的部门编号

select ename from emp where deptno=(select deptno from  dept where dname='SALES');

9、列出薪金高于公司平均水平的所有雇员

select ename from emp where sal>(select avg(sal) from  emp);

10、列出与“SCOTT”从事相同工作的所有雇员

select ename from emp where job=(select job from emp  where ename='SCOTT');

11、列出某些雇员的姓名和薪金，条件是他们的薪金等于部门30中任何一个雇员的薪金

select ename,sal from emp where sal in (select sal from  emp where deptno=30);

12、列出某些雇员的姓名和薪金，条件是他们的薪金高于部门30中所有雇员的薪金

select ename,sal from emp where sal > (select max(sal)  from emp where deptno=30);

13、列出每个部门的信息以及该部门中雇员的数量

select a.deptno,a.dname,a.loc,b.ss from dept a,(select  deptno,count(ename) ss from emp group by deptno) b

where a.deptno=b.deptno;

14、列出所有雇员的雇员名称、部门名称和薪金

select a.ename,b.dname,a.sal from emp a,dept b where  a.deptno=b.deptno(+);

15、列出从事同一种工作但属于不同部门的雇员的不同组合

select a.ename,b.ename,a.job,b.job,a.deptno,b.deptno from  emp a,emp b

where a.job=b.job and a.deptno<>b.deptno;

16、列出分配有雇员数量的所有部门的详细信息，即使是分配有0个雇员

select a.deptno,a.dname,a.loc,nvl(b.ss,0) from dept a,

(select deptno,count(ename) ss from emp group by deptno)  b where a.deptno=b.deptno(+);

select a.deptno,dname,loc,count(empno) ss from dept a,emp  b where a.deptno=b.deptno(+)

group by a.deptno,a.dname,a.loc;

17、列出各种类别工作的最低工资

select job,min(sal) as minjobsal from emp group by job;

18、列出各个部门的MANAGER（经理）的最低薪金

select deptno,min(sal) ss from emp where job='MANAGER'  group by deptno;

19、列出按年薪排序的所有雇员的年薪

select ename,(sal*12+nvl(comm,0)) as nianxin from emp  order by nianxin;

20、列出薪金水平处于第四位的雇员

select ename,a.tt from (select rownum  tt,ename,(sal*12+nvl(comm,0)) as nianxin from emp)a where  a.tt=4;

select * from (select ename,sal,rank()over(order by sal  desc)as nn from emp) where nn=4;