Oracle11g数据库基础教程课后习题答案

Oracle11g数据库基础教程
参考答案
第1章Oracle 11g数据库安装与配置1．简答题
（1）
企业版数据库服务器包含所有的数据库组件，主要针对高端的应用环境，适用于安全性和性能要求较高的联机事务处理（OLTP）、查询密集型的数据仓库和要求较高的Internet应用程序：标准版数据库服务器提供大部分核心的数据库功能和特性，适合于工作组或部门级的应用程序：个人版数据库服务器只提供基本数据库管理功能和特性，适合单用户的开发环境，为用户提供开发测试平台。

（2）
常用数据库类型包括事务处理类、数据仓库类以通用类型。

其中事务处理类型主要针对具有大量并发用户连接，并且用户主要执行简单事务处理的应用环境。

事务处理数据库的典型应用有银行系统数据库、Internet电子商务数据库、证券交易系统数据库等。

对于需要较高的可用性和事务处理性能、存在大量用户并行访问相同数据以及需要较高恢复性能的数据库环境，事务处理类型的配置可以提供最佳性能；数据仓库类型的数据库主要针对有大量的对某个主题进行复杂查询的应用环境。

数据仓库的典型应用有客户订单研究、支持呼叫、销售预测、采购模式以及其他战略性业务问题的历史数据研究。

对于需要对大量数据进行快速访问，以及复杂查询的数据库环境，数据仓库类型配置是最佳选择；通用类型配置的数据库是事务处理数据库与数据仓库配置的折衷方案。

既可以支持大量并发用户的事务处理，又可以快速对大量历史数据进行复杂的数据扫描和处理。

（3）
数据库名可以由字母、数字、下划线（_）、#和美元符号（$）组成，且必须以字母开头，长度不超过30个字符。

在单机环境中，可以不设置域名，域名长度不能超过128个字符。

Oracle服务标识符（SID）是一个Oracle实例的唯一名称标识，长度不能超过12个字符。

（4）
● OracleServiceORCL：数据库服务（数据库实例），是Oracle核心服务，是数据库启动
的基础，只有该服务启动，Oracle数据库才能正常启动。

（必须启动）
● OracleOraDb11g_home1TNSListener：监听器服务，该服务只有在远程访问数据库时才
需要（无论远程计算机还是本地计算机，凡是通过Oracle Net网络协议连接数据库都属于远程访问）。

（必须启动）
● OracleOraDb11g_home1ConfigurationManager：配置Oracle启动时的参数的服务。

（非
必须启动）
● OracleOraDb11g_home1ClrAgent：提供对.NET支持的Oracle数据库扩展服务。

（非必
须启动）
● OracleJobSchedulerORCL：数据库作业调度服务。

（非必须启动）
● OracleDBConsoleorcl：Oracle控制台服务，即企业管理器服务。

只有该服务启动了，
才可以使用Web方式的企业管理器管理数据库。

（非必须启动）
● OracleVssWriterORCL：是Oracle对VSS提供支持的服务。

（非必须启动）
● OracleMTSRecoveryService：是允许数据库充当一个微软事务服务器、COM/COM+对
象和分布式环境下的事务资源管理器的服务。

2．实训题
（1）略
（2）
打开网络管理对话框，分别选中左侧树状结构中的监听器和数据库，修改右侧对话框中的主机名为当前的计算机名。

（3）略
第2章Oracle数据库管理与开发工具1．简答题
（1）
●实现对Oracle运行环境的完全管理，包括Oracle数据库、Oracle应用服务器、HTTP
服务器等的管理；
●实现对单个Oracle数据库的本地管理，包括系统监控、性能诊断与优化、系统维护、
对象管理、存储管理、安全管理、作业管理、数据备份与恢复、数据移植等；
●实现对多个Oracle数据库的集中管理；
●实现对Oracle应用服务器的管理；
●检查与管理目标计算机系统软硬件配置。

（2）
利用SQL*Plus可以实现以下操作：
●输入、编辑、存储、提取、运行和调试SQL语句和PL/SQL程序；
●开发、执行批处理脚本；
●执行数据库管理；
●处理数据，生成报表，存储、打印、格式化查询结果；
●检查表和数据库对象定义。

（3）
建立与数据库的连接后，可以利用SQL Developer实现各种数据库管理与开发操作，包括浏览数据库对象、进行数据的DML操作（插入、删除、修改数据）、进行DDL操作（创建、修改、删除数据库对象）、开发和调试PL/SQL程序、进行数据的导出与导入以及创建与生成报表等操作。

（4）
Oracle提供的ONCA可以实现下列的网络配置任务。

●监听程序配置：可以添加、重新配置、删除或重命名监听程序。

监听程序是数据库
服务器响应用户连接请求的进程。

●命名方法配置：选择命名方法。

命名方法是将用户连接时使用的连接标识符解析成
连接描述符的方法。

●本地网络服务名配置：可以添加、重新配置、删除、重命名或测试本地网络服务名，
本地网络服务名的解析存放在网络配置文件tnsnames.ora中。

●目录使用配置：可以配置符合LDAP协议的目录服务器。

（5）
Oracle网络管理工具ONM是配置和管理Oracle网络环境的一种工具。

使用ONM可以对下列的Oracle网络特性和组件进行配置和管理。

●概要文件：确定客户端如何连接到Oracle网络的参数集合。

使用概要文件可以配置
命名方法、事件记录、跟踪、外部命令参数以及Oracle Advanced Security的客户端参数。

●服务命名：创建或修改数据库服务器的网络服务的描述。

●监听程序：创建或修改监听程序。

2．实训题
（1）略
（2）略
（3）略
（4）略
（5）略
（6）略
第3章Oracle数据库系统结构1．简答题
（1）
Oracle数据库体系结构由物理存储结构、逻辑存储结构和实例组成。

其中物理存储结构描述了操作系统层次数据的存储与管理，包括数据文件、日志文件、重做日志文件等组成。

逻辑结构描述了数据库内部数据的组织与管理，由表空间、段、区、块组成。

实例是数据库运行的软件结构，由内存结构和后台进程组成。

数据库运行过程中，用户的操作在内存区中进行，最终通过后台进行转化为对数据库的操作。

（2）
Oracle数据库物理结构包括数据文件、控制文件、重做日志文件、初始化参数文件、归档文件、口令文件等。

在控制文件中记录了当前数据库所有的数据文件的名称与位置、重做日志文件的名称与位置，以及数据文件、重做日志文件的状态等。

（3）
Oracle数据库的逻辑存储结构分为Oracle数据块（Oracle Data Block）、区（Extent）、段（Segment）和表空间（Table space）4种。

一个或多个连续的Ora cle数据块构成区，一个或多个区构成段，一个或多个段构成表空间，所有表空间构成数据库。

（4）
SGA区由数据缓冲区、共享池、重做日志缓冲区、大型池、JAVA池构成；PGA区由排序区、私有SQL区以及堆栈构成。

SGA区是由Oracle分配的共享内存结构，包含一个数据库实例共享的数据和控制信息。

当多个用户同时连接同一个实例时，SGA区数据供多个用户共享，所以SGA区又称为共享全局区。

用户对数据库的各种操作主要在SGA区中进行。

该内存区随数据库实例的创建而分配，随实例的终止而释放。

PGA区是在用户进程连接数据库，创建一个会话时，由Oracle为用户分配的内存区域，保存当前用户私有的数据和控制信息，因此该区又称为私有全局区。

每个服务器进程只能访问自己的PGA区，所有服务器进程PGA区总和即为实例的PGA区的大小。

（5）
Oracle实例的主要后台进程包括数据库写进程（DBWR）、日志写进程（LGWR）、检查点进程（CKPT）、系统监控进程（SMON）、进程监控进程（PMON）、归档进程（ARCH）、恢复进程（RECO）、锁进程（LCKn）、调度进程（Dnnn）等。

DBWR进程负责把数据高速缓冲区中已经被修改过的数据（“脏”缓存块）成批写入数据文件中永久保存，同时使数据高速缓冲区有更多的空闲缓存块，保证服务器进程将所需要的数据从数据文件读取到数据高速缓冲区中，提高缓存命中率。

LGWR日志写入进程负责把重做日志缓冲区的重做记录写入重做日志文件中永久保存。

CKPT进程的作用就是执行检查点，更新控制文件与数据文件的头部，使其同步；，触发
DBWR进程，将脏缓存块写入数据文件。

SMON进程的主要功能包括：在实例启动时负责对数据库进行恢复；回收不再使用的临时空间；将各个表空间的空闲碎片合并。

PMON进程的主要功能包括：负责恢复失败的用户进程或服务器进程，并且释放进程所
占用的资源；清除非正常中断的用户进程留下的孤儿会话，回退未提交的事务，释放会话所占用的锁、SGA、PGA等资源；监控调度进程和服务器进程的状态，如果它们失败，则尝试重新启动它们，并释放它们所占用的各种资源。

ARCH进程用户归档重做日志文件。

RECO进程用户数据库恢复。

（6）
当下列某个条件满足时，DBWR进程将启动，将数据高速缓冲区中的脏数据写入数据文件。

●服务器进程在数据高速缓存区中搜索一定数量的缓存块后，仍然没有找不到可用的
空闲缓存块，此时DBWR进程将被启动。

●检查点发生时，将启动DBWR进程。

●当数据高速缓冲区中LRU列表长度达到初始化参数DB_BLOCK_WRITE_BATCH指定值
的一半时，DBWR进程将被启动。

●DBWR进程发生超时（大约3秒），DBWR进程将被启动。

（7）
当下列事件发生时，LGWR进程将重做日志缓冲区中的重做记录写入重做日志文件。

●用户通过COMMIT语句提交当前事务。

●重做日志缓冲区被写满三分之一。

●DBWR进程开始将脏缓存块写入数据文件。

●LGWR进程超时（大约3秒），LGWR进程将启动。

3．选择题
（1） D
（2） D
（3） B
（4） B
（5） D
（6） A
（7） D
（8） B
（9） B
（10）A
（11）C
（12）A
（13）C
（14）C
（15）B
（16）A
（17）AD
（18）A
第4章案例数据库的创建与客户端的连接
实训题
（1）略
（2）略
（3）略
第5章数据库存储设置与管理
1．简答题
（1）
数据库表空间分为系统表空间和非系统表空间两类，其中非系统表空间包括撤销表空间、临时表空间和用户表空间等。

SYSTEM表空间主要用于存储数据库的数据字典、PL/SQL程序的源代码和解释代码、数据库对象的定义。

撤销表空间专门进行回退信息的自动管理。

临时表空间是专门进行临时段管理的表空间。

用户表空间用于分离不同应用的数据，而且能够减少读取数据文件时产生的I/O 冲突。

（2）
一个数据库由一个或多个表空间构成，不同的表空间用于存放不同应用的数据，表空间的大小决定了数据库的大小。

一个表空间对应一个或多个数据文件，数据文件的大小决定了表空间的大小。

一个数据文件只能从属于一个表空间。

一个数据库对象只能存储在一个表空间中，但可以存储在该表空间所对应的一个或多个数据文件中。

（3）
数据文件中保存了数据库中的所有数据，包括数据字典以及用户数据。

（4）
控制文件保存数据库的物理结构信息，包括数据库名称、数据文件的名称与状态、重做日志文件的名称与状态等。

在数据库启动时，数据库实例依赖初始化参数定位控制文件，然后根据控制文件的信息加载数据文件和重做日志文件，最后打开数据文件和重做日志文件。

（5）
重做日志文件是以重做记录的形式记录、保存用户对数据库所进行的修改操作，包括用户执行DDL、DML语句的操作。

如果用户只对数据库进行查询操作，那么查询信息是不会记录到重做日志文件中的。

（6）
归档是数据库恢复及热备份的基础。

只用当数据库归档模式时，才可以进行热备份和完全恢复。

进行归档设置包括归档模式设置（ARCHIVELOG）、归档方式设置以及归档路径的设置等。

（7）
每个数据库至少需要两个重做日志文件，采用循环写的方式进行工作。

当一个重做日志文件在进行归档时，还有另一个重做日志文件可用。

当一个重做日志文件被写满后，后台进程LGWR开始写入下一个重做日志文件，即日志切换，同时产生一个“日志序列号”，并将这个号码分配给即将开始使用的重做日志文件。

当所有的日志文件都写满后，LGWR进程再重新写入第一个日志文件。

（8）
采用多路复用控制文件可以避免由于一个控制文件的损坏而导致数据库无法正常启动。

在数据库启动时根据一个控制文件打开数据库，在数据库运行时多路复用控制文件采用镜像的方式进行写操作，保持所有控制文件的同步。

2．实训题
（1）
CREATE TABLESPACE USERTBS1 DATAFILE 'D:\ORACLE\ORADATA\ORCL\USERTBS1.DBF' SIZE 20M EXTENT MANAGEMENT LOCAL；
（2）
CREATE TABLESPACE USERTBS2 DATAFILE 'D:\ORACLE\ORADATA\ORCL\USERTBS1.DBF' SIZE 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K;
（3）
ALTER DATABASE DATAFILE 'D:\ORACLE\ORADATA\ORCL\USERTBS1.DBF'
AUTOEXTEND ON NEXT 5M MAXSIZE 100M;
（4）
CREATE TEMPORARY TABLESPACE TEMPTBS TEMPFILE
'D:\ORACLE\ORADATA\ORCL\TEMPTBS.DBF' SIZE 20M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16K;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPTBS;
（5）
ALTER TABLESPACE USERTBS1 OFFLINE;
ALTER TABLESPACE USERTBS1 ONLINE;
（6）
DROP TABLESPACE USERTBS2 INCLUDING CONTENTS AND DATAFILES;
（7）
SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_DATA_FILES;
（8）
ALTER TABLESPACE USERS
ADD DATAFILE ‘D:\ORACLE\ORADATA\ORCL\userdata05.dbf’ SIZE 50M’;
（9）
ALTER TABLESPACE EXAMPLE
ADD DATAFILE ‘D:\ORACLE\ORADATA\ORCL\example05.dbf’ SIZE 20M’;
（10）
ALTER DATABASE DATAFILE ‘D:\ORACLE\ORADATA\ORCL\userdata05.dbf’ AUTOEXTEND ON NEXT 5M MAXSIZE 100M;
（11）
ALTER DATABASE DATAFILE ‘D:\ORACLE\ORADATA\ORCL\example05.dbf’ RESIZE 40M; （12）
SHUTDOWN IMMEDIATE;
在操作系统中重命名userdata05.dbf、example05.dbf分别为userdata005.dbf、example005.dbf
STARTUP MOUNT;
ALTER DATABASE RENAME FILE
‘D:\ORACLE\ORADATA\ORCL\userdata05.dbf’, ‘D:\ORACLE\ORADATA\ORCL\example05.dbf’TO
‘D:\ORACLE\ORADATA\ORCL\userdata005.dbf’,
‘D:\ORACLE\ORADATA\ORCL\example005.dbf’;
ALTER DATABASE OPEN;
（13）
ALTER DATABASE BACKUP CONTROLFILE TO ‘D:\ORACLE\CONTROL.BKP’;
（14）
ALTER DATABASE ADD LOGFILE GROUP 5
(‘D:\ORACLE\ORADATA\ORCL\redo05a.log’,
’D:\ORACLE\ORADATA\ORCL\redo05b.log’)SIZE 5M;
（15）
ALTER DATABASE ADD LOGFILE MEMBER ‘D:\ORACLE\ORADATA\ORCL\redo05c.log’ TO
GROUP 5;
（16）
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
ALTER SYSTEM ARCHIVE LOG START
（17）
ALTER SYSTEM SET LOG_ARCHIVE_DEST=’D:\ORACLE\BACKUP’
3．选择题
（1） B
（2） A
（3） B
（4） B
（5） A
（6） D
（7）BC
（8） A
（9） A
（10）C
（11）B
（12）C
（13）B
（14）B
（15）C
（16）BC
（17）B
（18）AC
（19）BD
（20）C
（21）A （22）AC
第6章数据库对象的创建与管理
1．简答题
（1）
在Oracle 数据库中，根据表生存周期不同，可以分为永久性表和临时表；根据表中数据组织方式不同，可以分为标准表、索引化表和分区表。

索引化表是一种特殊的表，它按B 树的结构来组织和存储数据，将表的数据和索引的数据存储在一起。

分区表是将一个表分成若干个独立的组成部分进行存储和管理。

（2）
表的约束包括：
➢主键约束：定义了主键约束的列取值不能为空，也不能重复。

➢唯一值约束：定义了唯一值约束的列取值不能重复。

➢检查约束：定义了检查约束的列取值要满足检查条件。

➢外键约束：定义了外键约束的列取值要么是主表参照列的值，要么是空值。

➢空／非空约束：定义了非空约束的列取值不能为空。

（3）
索引用于提高数据的查询效率。

根据索引值是否唯一，可以分为唯一性索引和非唯一性索引；根据索引的组织结构不同，可以分为平衡树索引和位图索引；根据索引基于的列数不同可以分为单列索引和复合索引。

（4）
视图是从一个或多个表或视图中提取出来的数据的一种表现形式。

在数据库中只有视图的定义，而没有实际对应“表”的存在。

通过视图的使用可以提高数据的安全性，隐藏数据的复杂性，简化查询语句，分离应用程序与基础表，保存复杂查询等。

（5）
序列是用于产生唯一序列号的数据库对象，可以为多个数据库用户依次生成不重复的连续整数，通常使用序列自动生成表中的主键值。

序列创建后，可以通过序列NEXTVAL属性产生序列值，通过属性CURRVAL查看当前的序列值。

（6）
分区的必要性：
●提高数据的安全性，一个分区的损坏不影响其他分区中数据的正常使用；
●将表的各个分区存储在不同磁盘上，提高数据的并行操作能力；
●简化数据的管理，可以将某些分区设置为不可用状态，某些分区设置为可用状态，
某些分区设置为只读状态，某些分区设置为读写状态；
●操作的透明性，对表进行分区并不影响操作数据的SQL语句。

对表进行分区的方法包括：
●范围分区：按照分区值的范围对表进行分区。

●列表分区：按照分区值对表进行分区。

●散列分区：采用HASH算法在指定数量的分区中均等地分配数据。

●复合分区：先对表进行范围分区，然后对每个分区进行散列分区。

（7）
本地分区索引是指为分区表中的各个分区单独创建索引分区，各个索引分区之间是相互独立的，索引的分区与表的分区是一一对应的。

全局分区索引：全局分区索引是指先对整个表建立索引，然后再对索引进行分区。

索引的分区之间不是相互独立的，索引分区与表分区之间也不是一一对应的
全局非分区索引：建立索引但不分区
2．实训题
（1）略
（2）
Create table exer_class(
CNO number(2) primary key,
CNAME varchar2(20),
NUM number(3)
)
Create table exer_student(
SNO number(4) primary key,
SNAME varchar2(10) unique,
SAGE number,
SEX char(2),
CNO number(2)
)
（3）
Alter table exer_student add constraint ck_sage check (sage>0 and sage<=100);
（4）
alter table exer_student add constraint ck_stu check(sex='M' or sex='F')modify sex default 'M'
（5）
Create unique index ind_cname on exer_class(cname);
（6）
Create view s_c as
Select sno,sname,sage,sex,o,cname,num
From exer_class c join exer_student s
On o=o;
（7）
Create sequence sequ1 start with 100000001;
（8）
create table exer_student_range(
sno number(4) primary key,
sname varchar2(10),sage number,
sex char(2),cno number(2))
partition by range(sage)
(partition part1 values less than(20) tablespace example,
partition part2 values less than(30) tablespace orcltbs1,
partition part3 values less than(maxvalue) tablespace orcltbs2) （9）
create table exer_student_list(
sno number(4) primary key,
sname varchar2(10),sage number,
sex char(2),cno number(2))
partition by list(sex)
(partition man values('M') tablespace orcltbs1,
partition woman values('F') tablespace orcltbs2)
（10）
Create index ind on exer_student_range(sno) local;
3．选择题
（1）C
（2）A
（3）D
（4）B
（5）A B
（6）B
（7）A C
（8）B
（9）B
（10） C
（11）BD
（12）ACE
第7章数据操纵与事务处理1．实训题
（1）略
（2）
INSERT INTO BOOK VALUES('100001','冶金工业出版社','李代平',
'冶金工业出版社',TO_DATE('2003-01-01','YYYY-MM-DD'),38);
INSERT INTO BOOK VALUES('100002','Oracle9i中文版入门与提高','赵松涛', '人民邮电出版社',TO_DATE('2002-07-01','YYYY-MM-DD'),35);
INSERT INTO BOOK VALUES('100003','Oracle9i开发指南：PL/SQL程序设计', 'Joan Casteel','电子工业出版社',TO_DATE('2004-04-03','YYYY-MM-DD'),49); INSERT INTO BOOK VALUES('100004','数据库原理辅助与提高','盛定宇',
'电子工业出版社',TO_DATE('2004-03-01','YYYY-MM-DD'),34);
INSERT INTO BOOK VALUES('100005','Oracle9i中文版实用培训教程','赵伯山', '电子工业出版社',TO_DATE('2002-01-01','YYYY-MM-DD'),21);
INSERT INTO BOOK VALUES('100006','Oracle8实用教程','翁正科等',
'电子工业出版社',TO_DATE('2003-07-08','YYYY-MM-DD'),38);
INSERT INTO READER VALUES('200001','张三');
INSERT INTO READER VALUES('200002','李凤');
INSERT INTO READER VALUES('200003','孟欣');
INSERT INTO READER VALUES('200004','谢非');
INSERT INTO READER VALUES('200005','刘英');
INSERT INTO BORROW VALUES('100001','200001',
TO_DATE('2004-08-10 10:06:14','YYYY-MM-DD HH:MI:SS'));
INSERT INTO BORROW VALUES('100002','200002',
TO_DATE('2004-08-10 10:06:27','YYYY-MM-DD HH:MI:SS'));
INSERT INTO BORROW VALUES('100003','200003',
TO_DATE('2004-08-10 10:06:36','YYYY-MM-DD HH:MI:SS'));
INSERT INTO BORROW VALUES('100004','200004',
TO_DATE('2004-08-10 10:06:48','YYYY-MM-DD HH:MI:SS'));
INSERT INTO BORROW VALUES('100005','200005',
TO_DATE('2004-08-10 10:06:58','YYYY-MM-DD HH:MI:SS')); （3）
INSERT INTO BOOK VALUES('10000007','Java网络编成','李程等', '电子工业出版社',TO_DATE('2000-08-01','YYYY-MM-DD'),35); （4）
UPDATE BOOK SET PRICE=29 WHERE NO='100007';
（5）
DELETE FROM BOOK WHERE NO='10000007'
第8章数据查询
1．实训题
（1）查询100号部门的所有员工信息。

Selsect * from employees where department_id = 100
（2）查询所有职位编号为“SA_MAN”的员工的员工号、员工名和部门号。

Select employee_id,first_name,last_name,department_id from employees where job_id=
‘SA_MAN’
（3）查询每个员工的员工号、工资、奖金以及工资与奖金的和。

Select employee_id,salary,commission_pct,
salary*(1+nvl(commission_pct,0) from employees
（4）查询40号部门中职位编号为“AD_ASST”和20号部门中职位编号为“SA_REP”的员工的信息。

Select * from employees where department_id=40 and job_id=’ AD_ASST’ OR department_id=20 and job_id=’ SA_REP’;
（5）查询所有职位名称不是“Stock Manager”和“Purchasing Manager”，且工资大于或等于2000的员工的详细信息。

Select * from employees where job_id not in(’ Stock Manager’,’ Purchasing Manager’) and salary>=2000
（6）查询有奖金的员工的不同职位编号和名称。

Select distinct job_id, job_title from jobs where job_id in (select job_id from employees where job_id is not null)
（7）查询没有奖金或奖金低于100元的员工信息。

Select * from employees where salary*commission_pct<100 or commission is NULL
（8）查询员工名（first_name）中不包含字母“S”的员工。

Select first_name from employees where first_name not like ‘%S%’
（9）查询员工的姓名和入职日期，并按入职日期从先到后进行排序。

Select first_name,last_name,hire_date from employees order by hire_date;
（10）查询所有员工的姓名及其直接上级的姓名。

Select a.first_name,b.first_name from employees a join employees b on b.employee_id =
a.manage_id
（11）查询入职日期早于其直接上级领导的所有员工信息。

select * from employees a where hire_date<(select hire_date from employees b
b.employee_id=a.manage_id)
（12）查询各个部门号、部门名称、部门所在地以及部门领导的姓名。

Select d.department_id,d.department_name,d.location,e.first_name from departments d join employees e
on d.manager_id=e.employee_id
（13）查询所有部门及其员工信息，包括那些没有员工的部门。

Select department_name,first_name from departments d left join employees e on
d.deparment_id=
e.department_id
（14）查询所有员工及其部门信息，包括那些还不属于任何部门的员工。

Select e.first_name,d.department_name
From employees left join departments on e.department_id=d.department_id;
（15）查询所有员工的员工号、员工名、部门名称、职位名称、工资和奖金。

Select e.employee_id,e.first_name,d.department_name,j.job_title,e.salary,
e.salary*mission_pct 奖金
From departments d join employees e on d.department_id=e.department_id
Join jobs j on j.job_id=e.job_id;
（16）查询至少有一个员工的部门信息。

select * from departments d where exists(select 1 from employees
where department_id=d.department_id)
（17）查询工资比100号员工工资高的所有员工信息。

Select * from employees where salary>(select salary from employees where employee_id = 100); （18）查询工资高于公司平均工资的所有员工信息。

Select * from employees where salary>(select avg(salary) from employees)
（19）查询各个部门中不同职位的最高工资。

Select job_id,max(salary) from employees group by job_id
（20）查询各个部门的人数及平均工资
Select department_id,count(*),avg(salary ) from employees
group by department_id;
（21）统计各个职位的员工人数与平均工资。

Select job_id ,count(employee_id),avg(salary)
from employees
group by job_id;
（22）统计每个部门中各职位的人数与平均工资。

Select department_id,job_id,count(*),avg(salary) from employees group by
department_id,job_id;
（23）查询最低工资大于5000元的各种工作。

Select job_id,job_title from jobs where job_id in(
Select job_id from employees group by job_id having min(salary)>5000);
（24）查询平均工资低于6000元的部门及其员工信息。

Select e.*,d.* from employees e join departments d on e.department_id=d.department_id and department_id in(select department_Id from employees group by employee_id having
avg(salary)<6000);
（25）查询在“Sales”部门工作的员工的姓名信息。

Select * from employee where department_id in(select department_d from departments where department_name=’Sales’)
（26）查询与140号员工从事相同工作的所有员工信息。

Select * from employees where job_id in (select job_id from employees where employee_id = 140);
（27）查询工资高于30号部门中所有员工的工资的员工姓名和工资。

Select first_name,last_name,salary from employees where salary>(select max(salary) from employees deparment_id=30);
（28）查询每个部门中的员工数量、平均工资和平均工作年限。

Select count(*),avg(salary),avg(round((sysdate-hire_date)/365)) from employees group by department_id
（29）查询工资为某个部门平均工资的员工的信息。

Select * from employees where salsry in(select avg(Salary) from employees group by department_id)
（30）查询工资高于本部门平均工资的员工的信息。

Select * from employees e1 where salary>(select avg(salary) from employees e2 where
e2.department_id=e1.department_id )
（31）查询工资高于本部门平均工资的员工的信息及其部门的平均工资。

Select e.*,avgsal
From employees e join (select department_id,avg(salary) avgsal from employees group by department_id) d
On e.department_id=d.department_id
And e.salary>d.avgsal
（32）查询工资高于50号部门某个员工工资的员工的信息。

Select *from employees where salary>any(select salary from employees where
department_id=50):
（33）查询工资、奖金与10号部门某员工工资、奖金都相同的员工的信息。

Select * from employees where (salary,nvl(commission_pct) ) in(
Select salary,nvl(commission_pct) from employees where department_id=10
)
（34）查询部门人数大于10的部门的员工信息。

Select * from employees where department_id in(select department_id from employees group by department_id having count(*)>10);
（35）查询所有员工工资都大于10000元的部门的信息
Select * from department where department_id in (select department_id from employees group by department_id having min(salary)>10000)
（36）查询所有员工工资都大于5000元的部门的信息及其员工信息。

Select e.*,d.*
From employees e join departments d
On e.department_id=d.department_id
Where department_id in (
Select department_id from employees group by department_id having min(salary)>5000)
)
（37）查询所有员工工资都在4000元～8000元之间的部门的信息。

Select * from departments where department_id in(
Select department_id from employees group by department_id having min(salary)>=4000 and max(salary)<=8000)
（38）查询人数最多的部门信息。

Select * from department_id where department_id in(
Select department_id from employees group by department_id having
Count(*)>=all(
select count(*) from employees group by department_id )
)
（39）查询30号部门中工资排序前3名的员工信息。

Select * from employee where department_id=30 and salary is not null and rownum<=3 order by salary desc
（40）查询所有员工中工资排序在5~10名之间的员工信息。

Select * from
(
Select rownum rn,employee_id,salary from (
Select employee_id,salary from employees where salary is not null order by salary desc) e1
)e2
Where rn between 5 and 10
（41）将各部门员工的工资修改为该员工所在部门平均工资加1000。

update employees e set salary=(select avg(Salary) from employees where
department_id=e.department_id)+1000;
（42）查询各月倒数第2天入职的员工信息。

Select * from employees where hire_date=last(hire_date)-1’
（43）查询工龄大于或等于10年的员工信息。

select * from hr.employees where (sysdate-hire_date)/365>=10;
（44）查询员工信息，要求以首字母大写的方式显示所有员工姓（last_name）和员工名（first_name）。

select initcap(first_name),initcap(last_name) from hr.employees;
（45）查询员工名（first_name）正好为6个字符的员工的信息。

select * from hr.employees where length(first_name)=6;
（46）查询员工名（first_name）的第2个字母为“M”的员工信息。

select first_name from hr.employees where first_name like '_M%';
（47）查询所有员工名（first_name），如果包含字母“s”，则用“S”替换。

select replace(first_name,'S','s') from hr.employees;
（48）查询在2月份入职的所有员工信息。

Select * from employees where extract(mm from hire_date)=2;
2．选择题
（1） B
（2） B D
（3） A
（4） A
（5） C
（6） E
（7） B
（8） D
（9） B C
（10）D
（11）C
（12）BD
（13）C
（14）B D
（15）A
（16）C
第9章PL/SQL语言基础1．实训题
（1）
declare
cursor c_emp is select * from employees;
begin
for v_emp in c_emp loop
dbms_output.put_line(v_emp.first_name||' '||v_st_name||' '|| v_emp.employee_id||' '||v_emp.salary||' '||v_emp.department_id); end loop;
end;
（2）
declare
v_avgsal employees.salary%type;
begin
for v_emp in (select * from employees) loop
select avg(salary) into v_avgsal from employees
where department_id=v_emp.department_id;
if v_emp.salary>v_avgsal then
dbms_output.put_line(v_emp.first_name||' '||v_st_name||' '|| v_emp.employee_id||' '||v_emp.salary||' '||v_emp.department_id);
end if;
end loop;
end;
（3）
declare
cursor c_emp is
select e.employee_id eid,st_name ename,
e.department_id edid,m.employee_id mid,st_name mname
from employees e join employees m
on e.manager_id=m.employee_id;
v_emp c_emp%rowtype;
begin
open c_emp;
loop
fetch c_emp into v_emp;
exit when c_emp%notfound;
dbms_output.put_line(v_emp.eid||' '||v_emp.ename||' '||
v_emp.edid||' '||v_emp.mid||' '||v_emp.mname);
end loop;
close c_emp;
end;
（4）
declare
v_emp employees%rowtype;
begin
select * into v_emp from employees where last_name='Smith';
dbms_output.put_line(v_emp.employee_id||' '||
v_emp.first_name||' '||v_st_name||' '||
v_emp.salary||' '||v_emp.department_id); exception
when no_data_found then
insert into employees(employee_id,last_name,salary,email,hire_date,
job_id,department_id)
values(2010,'Smith',7500,'*****************.cn',
to_date('2000-10-5','yyyy-mm-dd'),'AD_VP',50);
when too_many_rows then
for v_emp in(select * from employees where last_name='Smith')loop
dbms_output.put_line(v_emp.employee_id||' '||
v_emp.first_name||' '||v_st_name||' '||
v_emp.salary||' '||v_emp.department_id);
end loop;
end;
（5）
declare
cursor c_emp is select * from employees;
v_increment employees.salary%type;
begin
for v_emp in c_emp loop
case
when v_emp.job_id='AD_PRES'OR v_emp.job_id='AD_VP'
OR v_emp.job_id='AD_ASST' THEN
v_increment:=1000;
when v_emp.job_id='FI_MGR' OR v_emp.job_id='FI_ACCOUNT'THEN v_increment:=800;
WHEN v_emp.job_id='AC_MGR' OR v_emp.job_id='AC_ACCOUNT' THEN v_increment:=700;
WHEN v_emp.job_id='SA_MAN' OR v_emp.job_id='SA_REP' THEN v_increment:=600;
WHEN v_emp.job_id='PU_MAN' OR v_emp.job_id='PU_CLERK' THEN v_increment:=500;
WHEN v_emp.job_id='ST_MAN' OR v_emp.job_id='ST_CLERK' OR v_emp.job_id='SH_CLERK' THEN v_increment:=400;
WHEN v_emp.job_id='IT_PROG' OR v_emp.job_id='MK_MAN' OR v_emp.job_id='MK_REP' THEN v_increment:=300;
ELSE v_increment:=200;
end case;
update employees set salary=salary+v_increment
where employee_id=v_emp.employee_id;
end loop;
end;
（6）
declare
v_lowsal jobs.min_salary%type;
v_highsal jobs.max_salary%type;
e exception;
begin
update employees set salary=8000 where employee_id=201;
select min_salary,max_salary into v_lowsal,v_highsal
from jobs where job_id=(select job_id from employees where employee_id=201);
if 8000 not between v_lowsal and v_highsal then
raise e;
end if;
exception
when e then
raise_application_error(-20001,'beyond limit');
rollback;
end;
第10章PL/SQL程序设计
（1）创建一个存储过程，以员工号为参数，输出该员工的工资。

create or replace procedure pro_showsal(
p_empno employees.employee_id%type)
as
v_sal employees.salary%type;
begin
select salary into v_sal from employees
where employee_id=p_empno;
dbms_output.put_line(v_sal);
exception
when no_data_found then
dbms_output.put_line('there is not such an employees');
end;
begin
pro_showsal(100);
end;
（2）创建一个存储过程，以员工号为参数，修改该员工的工资。

若该员工属于10号部门，则工资增加140元；若属于20号部门，则工资增加200元；若属于30号部门，则工资增加250元；若属于其他部门，则工资增长300元。

create or replace procedure pro_updatesal(
p_empno employees.employee_id%type)
as
v_deptno employees.department_id%type;
v_inc number;
begin
select department_id into v_deptno from employees
where employee_id=p_empno;
case v_deptno
when 10 then v_inc:=140;
when 20 then v_inc:=200;
when 30 then v_inc:=250;
else v_inc:=300;
end case;
update employees set salary=salary+v_inc
where employee_id=p_empno;
exception
when no_data_found then
dbms_output.put_line('there is not such an employees');
end;
（3）创建一个函数，以员工号为参数，返回该员工的工资。

create or replace function func_retsal(
p_empno employees.employee_id%type)
return employees.salary%type
as
v_sal employees.salary%type;
begin
select salary into v_sal from employees
where employee_id=p_empno;
return v_sal;
exception
when no_data_found then
dbms_output.put_line('there is not such an employees');
end;
begin
dbms_output.put_line(func_retsal(100));
end;
（4）创建一个函数，以员工号为参数，返回该员工所在部门的平均工资。

create or replace function func_retavgsal(
p_empno employees.employee_id%type)
return employees.salary%type
as
v_deptno employees.department_id%type;
v_avgsal employees.salary%type;。