Oracle SQL部分练习题

SQL练习题

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

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

a.

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

b.

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

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

c.

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

a.

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

  

b.

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;

  

b.

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

  

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

a.

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

  

b.

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个雇员

a.

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(+);

  

b.

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、列出薪金水平处于第四位的雇员

a.

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

b.

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

原文地址:https://www.cnblogs.com/kingwwz/p/10759782.html

时间: 04-23

Oracle SQL部分练习题的相关文章

Oracle SQL语言基础及环境准备_超越OCP精通Oracle视频教程培训26

Oracle SQL语言基础及环境准备_超越OCP精通Oracle视频教程培训26 本课程介绍: Oracle视频教程,风哥本套oracle教程培训是<<Oracle数据库SQL语言实战培训教程>>的第1/5套:SQL语言之基础入门及环境准备.主要学习Oracle数据库SQL语言基础介绍.PL/SQL语言介绍.数据库SQL对象与数据类型介绍.SQL语言实战环境准备等. Oracle SQL语言之基础及环境准备,课程内容详细如下: Oracle数据库SQL语言基础介绍Oracle数据

Oracle SQL语言DDL和对象管理_超越OCP精通Oracle视频教程培训27

Oracle SQL语言DDL和对象管理_超越OCP精通Oracle视频教程培训27 本课程介绍: Oracle视频教程,风哥本套oracle教程培训是<<Oracle数据库SQL语言实战培训教程>>的第2/5套:Oracle SQL语言DDL和对象管理.主要学习Oracle数据库模式对象和表中可用的数据类型,表/临时表的创建与使用,索引/约束的创建与管理,视图/同义词/序列的创建和使用,触发器/存储过程/包的创建和使用. Oracle SQL语言DDL和对象管理,课程内容详细如下

Oracle SQL语言DML语句与事务_超越OCP精通Oracle视频教程培训28

Oracle SQL语言DML语句与事务_超越OCP精通Oracle视频教程培训28 本课程介绍: Oracle视频教程,风哥本套oracle教程培训是<<Oracle数据库SQL语言实战培训教程>>的第3/5套:Oracle SQL语言DML语句与事务.主要学习Oracle数据库SQL语句INSERT命令,学习使用UPDATE命令,学习使用DELETE命令,学习创建PL/SQL对象,事务概念与控制,锁的检测和锁争用,了解撤销数据. Oracle SQL语言DML语句与事务管理,课

Oracle SQL语言之查询语句_超越OCP精通Oracle视频教程培训29

Oracle SQL语言之查询语句_超越OCP精通Oracle视频教程培训29 本课程介绍: Oracle视频教程,风哥本套oracle教程培训是<<Oracle数据库SQL语言实战培训教程>>的第4/5套:Oracle SQL语言之查询语句.主要学习Oracle数据库SQL查询限制排序.Oracle SQL联接查询.Oracle SQL子查询等. 视频学习地址: http://edu.51cto.com/course/course_id-8047.html Oracle SQL语

oracle sql语句取得本周本月本年的数据

[sql] --国内从周一到周日 国外是周日到周六  select to_char(sysdate-1,'D') from dual;--取国内的星期几 去掉减一取国外的星期-- [sql] --取本周时间内的数据  select * from table  where DTIME >=trunc(next_day(sysdate-8,1)+1) and DTIME<=trunc(next_day(sysdate-8,1)+7)+1 ;     select * from table  whe

Oracle SQL性能优化

转载自:http://www.cnblogs.com/rootq/archive/2008/11/17/1334727.html (1)      选择最有效率的表名顺序(只在基于规则的优化器中有效): ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,FROM子句中写在最后的表(基础表 driving table)将被最先处理,在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.如果有3个以上的表连接查询, 那就需要选择交叉表(intersection ta

Oracle学习-Power Designer、visio 2003、Oracle sql developer、OEM、expdp

Oracle的体系太庞大了.对于刚開始学习的人来说,难免有些无从下手的感觉. 经过一学期的学习对Oracle学习有了一些深入的了解,由于之前学习过Oracle的一些主要的知识.所以学习起来上手比較快一点. 这次Oracle项目让我又一次对Oracle的基本知识进行了运用,而且对软件project也有了新的认识.从需求分析.概念结构设计.逻辑结构设计.物理结构设计.数据库的建立和測试.数据库执行和维护. 尽管说数据库设计的周期比較长将近用了三周左右,可是每一步做的还是非常认真的对待.我选择的是舰队

Oracle SQL语言之常用函数_超越OCP精通Oracle视频教程培训30

Oracle SQL语言之常用函数_超越OCP精通Oracle视频教程培训30 本课程介绍: Oracle视频教程,风哥本套oracle教程培训是<<Oracle数据库SQL语言实战培训教程>>的第5/5套:Oracle SQL语言之常用函数.主要学习Oracle数据库SQL聚合函数,分组函数,字符函数,转换函数,日期字符数字转换,日期函数,集合函数,分析函数等. Oracle SQL语言之常用函数,课程内容详细如下: 聚合函数-数据统计 分组函数-使用group by与havin

oracle sql语句

##########基本操作##########启动数据库:su - oraclesqlplus / as sysdbaSQL> startup 对scott用户解锁:SQL> conn / as sysdbaSQL> alter user scott identified by tiger account unlock; 连接到指定的数据库用户:SQL> conn scott/tiger 实现操作系统开机数据库自动open:vi /etc/oratab--------------

Oracle SQL篇(一)null值之初体验

    从我第一次正式的写sql语句到现在,已经超过10年的时间了.我写报表,做统计分析和财务对账,我一点点的接触oracle数据库,并尝试深入了解.这条路,一走就是10年,从充满热情,到开始厌倦,我不知道我还能坚持多久,未来的路,谁知道呢? 也许是该抓紧时间,做一点什么了,我不知道该开始写些什么,我从来没有在网上写东西的习惯.     先从简单的开始吧,那当然就是SQL,这是我SQL系列的第一篇,希望我能够坚持. 在Oracle数据库中,如果一个表中的列没有值的话,我们可以说是空值,比如IT员