Oracle11g数据库基础教程-参考答案

Oracle11g数据库根底教程参考答案Oracle11g数据库安装与配置1．简答题〔1〕企业版数据库效劳器包含所有的数据库组件，主要针对高端的应用环境，适用于平安性和性能要求较高的联机事务处理〔OLTP〕、查询密集型的数据仓库和要求较高的Internet应用程序：标准版数据库效劳器提供大局部核心的数据库功能和特性，适合于工作组或部门级的应用程序：个人版数据库效劳器只提供根本数据库管理功能和特性，适合单用户的开发环境，为用户提供开发测试平台。〔2〕常用数据库类型包括事务处理类、数据仓库类以通用类型。其中事务处理类型主要针对具有大量并发用户连接，并且用户主要执行简单事务处理的应用环境。事务处理数据库的典型应用有银行系统数据库、Internet电子商务数据库、证券交易系统数据库等。对于需要较高的可用性和事务处理性能、存在大量用户并行访问相同数据以及需要较高恢复性能的数据库环境，事务处理类型的配置可以提供最正确性能；数据仓库类型的数据库主要针对有大量的对某个主题进行复杂查询的应用环境。数据仓库的典型应用有客户订单研究、支持呼叫、销售预测、采购模式以及其他战略性业务问题的历史数据研究。对于需要对大量数据进行快速访问，以及复杂查询的数据库环境，数据仓库类型配置是最正确选择；通用类型配置的数据库是事务处理数据库与数据仓库配置的折衷方案。既可以支持大量并发用户的事务处理，又可以快速对大量历史数据进行复杂的数据扫描和处理。〔3〕数据库名可以由字母、数字、下划线〔_〕、#和美元符号〔$〕组成，且必须以字母开头，长度不超过30个字符。在单机环境中，可以不设置域名，域名长度不能超过128个字符。Oracle效劳标识符〔SID〕是一个Oracle实例的唯一名称标识，长度不能超过12个字符。〔4〕OracleServiceORCL：数据库效劳〔数据库实例〕，是Oracle核心效劳，是数据库启动的根底，只有该效劳启动，Oracle数据库才能正常启动。〔必须启动〕OracleOraDb11g_home1TNSListener：监听器效劳，该效劳只有在远程访问数据库时才需要〔无论远程计算机还是本地计算机，但凡通过OracleNet网络协议连接数据库都属于远程访问〕。〔必须启动〕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〕建立与数据库的连接后，可以利用SQLDeveloper实现各种数据库管理与开发操作，包括浏览数据库对象、进行数据的DML操作〔插入、删除、修改数据〕、进行DDL操作〔创立、修改、删除数据库对象〕、开发和调试PL/SQL程序、进行数据的导出与导入以及创立与生成报表等操作。〔4〕Oracle提供的ONCA可以实现以下的网络配置任务。监听程序配置：可以添加、重新配置、删除或重命名监听程序。监听程序是数据库效劳器响应用户连接请求的进程。命名方法配置：选择命名方法。命名方法是将用户连接时使用的连接标识符解析成连接描述符的方法。本地网络效劳名配置：可以添加、重新配置、删除、重命名或测试本地网络效劳名，本地网络效劳名的解析存放在网络配置文件tnsnames.ora中。目录使用配置：可以配置符合LDAP协议的目录效劳器。〔5〕Oracle网络管理工具ONM是配置和管理Oracle网络环境的一种工具。使用ONM可以对以下的Oracle网络特性和组件进行配置和管理。概要文件：确定客户端如何连接到Oracle网络的参数集合。使用概要文件可以配置命名方法、事件记录、跟踪、外部命令参数以及OracleAdvancedSecurity的客户端参数。效劳命名：创立或修改数据库效劳器的网络效劳的描述。监听程序：创立或修改监听程序。2．实训题〔1〕略〔2〕略〔3〕略〔4〕略〔5〕略〔6〕略第3章Oracle数据库系统结构1．简答题Oracle数据库体系结构由物理存储结构、逻辑存储结构和实例组成。其中物理存储结构描述了操作系统层次数据的存储与管理，包括数据文件、日志文件、重做日志文件等组成。逻辑结构描述了数据库内部数据的组织与管理，由表空间、段、区、块组成。实例是数据库运行的软件结构，由内存结构和后台进程组成。数据库运行过程中，用户的操作在内存区中进行，最终通过后台进行转化为对数据库的操作。〔2〕Oracle数据库物理结构包括数据文件、控制文件、重做日志文件、初始化参数文件、归档文件、口令文件等。在控制文件中记录了当前数据库所有的数据文件的名称与位置、重做日志文件的名称与位置，以及数据文件、重做日志文件的状态等。〔3〕Oracle数据库的逻辑存储结构分为Oracle数据块〔OracleDataBlock〕、区〔Extent〕、段〔Segment〕和表空间〔Tablespace〕4种。一个或多个连续的Ora〔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．选择题DDBBDADBBACACCBAADA第4章案例数据库的创立与客户端的连接实训题略略略第5章数据库存储设置与管理1．简答题〔1〕数据库表空间分为系统表空间和非系统表空间两类，其中非系统表空间包括撤销表空间、临时表空间和用户表空间等。SYSTEM表空间主要用于存储数据库的数据字典、PL/SQL程序的源代码和解释代码、数据库对象的定义。撤销表空间专门进行回退信息的自动管理。临时表空间是专门进行临时段管理的表空间。用户表空间用于别离不同应用的数据，而且能够减少读取数据文件时产生的I/O冲突。〔2〕一个数据库由一个或多个表空间构成，不同的表空间用于存放不同应用的数据，表空间的大小决定了数据库的大小。一个表空间对应一个或多个数据文件，数据文件的大小决定了表空间的大小。一个数据文件只能附属于一个表空间。一个数据库对象只能存储在一个表空间中，但可以存储在该表空间所对应的一个或多个数据文件中。〔3〕数据文件中保存了数据库中的所有数据，包括数据字典以及用户数据。〔4〕控制文件保存数据库的物理结构信息，包括数据库名称、数据文件的名称与状态、重做日志文件的名称与状态等。在数据库启动时，数据库实例依赖初始化参数定位控制文件，然后根据控制文件的信息加载数据文件和重做日志文件，最后翻开数据文件和重做日志文件。〔5〕重做日志文件是以重做记录的形式记录、保存用户对数据库所进行的修改操作，包括用户执行DDL、DML语句的操作。如果用户只对数据库进行查询操作，那么查询信息是不会记录到重做日志文件中的。〔6〕归档是数据库恢复及热备份的根底。只用当数据库归档模式时，才可以进行热备份和完全恢复。进行归档设置包括归档模式设置〔ARCHIVELOG〕、归档方式设置以及归档路径的设置等。〔7〕每个数据库至少需要两个重做日志文件，采用循环写的方式进行工作。当一个重做日志文件在进行归档时，还有另一个重做日志文件可用。当一个重做日志文件被写满后，后台进程LGWR开始写入下一个重做日志文件，即日志切换，同时产生一个“日志序列号〞，并将这个号码分配给即将开始使用的重做日志文件。当所有的日志文件都写满后，LGWR进程再重新写入第一个日志文件。〔8〕采用多路复用控制文件可以防止由于一个控制文件的损坏而导致数据库无法正常启动。在数据库启动时根据一个控制文件翻开数据库，在数据库运行时多路复用控制文件采用镜像的方式进行写操作，保持所有控制文件的同步。2．实训题〔1〕createtablespaceUSERTBS1datafile'd:\oracle\oradata\orcl\usertbs1.dbf'size20Mextentmanagementlocal；〔2〕createtablespaceUSERTBS2datafile'd:\oracle\oradata\orcl\usertbs1.dbf'size50Mextentmanagementlocaluniformsize512k;〔3〕alterdatabasedatafile'd:\oracle\oradata\orcl\usertbs1.dbf'autoextendonnext5Mmaxsize100M;〔4〕createtemporarytablespaceTEMPTBStempfile'd:\oracle\oradata\orcl\temptbs.dbf'size20Mextentmanagementlocaluniformsize16K;alterdatabasedefaulttemporarytablespaceTEMPTBS;〔5〕altertablespaceUSERTBS1offline;altertablespaceUSERTBS1online;〔6〕droptablespaceUSERTBS2includingcontentsanddatafiles;〔7〕selecttablespace_name,file_namefromdba_data_files;〔8〕ALTERTABLESPACEUSERSADDDATAFILE‘D:\ORACLE\ORADATA\ORCL\userdata05.dbf’SIZE50M’;〔9〕ALTERTABLESPACEEXAMPLEADDDATAFILE‘D:\ORACLE\ORADATA\ORCL\example05.dbf’SIZE20M’;ALTERDATABASEDATAFILE‘D:\ORACLE\ORADATA\ORCL\userdata05.dbf’AUTOEXTENDONNEXT5MMAXSIZE100M;ALTERDATABASEDATAFILE‘D:\ORACLE\ORADATA\ORCL\example05.dbf’RESIZE40M;SHUTDOWNIMMEDIATE;在操作系统中重命名userdata05.dbf、example05.dbf分别为userdata005.dbf、example005.dbfSTARTUPMOUNT;ALTERDATABASERENAMEFILE‘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’;ALTERDATABASEOPEN;ALTERDATABASEBACKUPCONTROLFILETO‘D:\ORACLE\CONTROL.BKP’;ALTERDATABASEADDLOGFILEGROUP5(‘D:\ORACLE\ORADATA\ORCL\redo05a.log’,’D:\ORACLE\ORADATA\ORCL\redo05b.log’)SIZE5M;ALTERDATABASEADDLOGFILEMEMBER‘D:\ORACLE\ORADATA\ORCL\redo05c.log’TOGROUP5;SHUTDOWNIMMEDIATESTARTUPMOUNTALTERDATABASEARCHIVELOG;ALTERDATABASEOPEN;ALTERSYSTEMARCHIVELOGSTARTALTERSYSTEMSETLOG_ARCHIVE_DEST=’D:\ORACLE\BACKUP’3．选择题BABBADBCAACBCBBCBCBACBDCAAC第6章数据库对象的创立与管理1．简答题〔1〕在Oracle数据库中，根据表生存周期不同，可以分为永久性表和临时表；根据表中数据组织方式不同，可以分为标准表、索引化表和分区表。索引化表是一种特殊的表，它按B树的结构来组织和存储数据，将表的数据和索引的数据存储在一起。分区表是将一个表分成假设干个独立的组成局部进行存储和管理。〔2〕表的约束包括：主键约束：定义了主键约束的列取值不能为空，也不能重复。唯一值约束：定义了唯一值约束的列取值不能重复。检查约束：定义了检查约束的列取值要满足检查条件。外键约束：定义了外键约束的列取值要么是主表参照列的值，要么是空值。空／非空约束：定义了非空约束的列取值不能为空。〔3〕索引用于提高数据的查询效率。根据索引值是否唯一，可以分为唯一性索引和非唯一性索引；根据索引的组织结构不同，可以分为平衡树索引和位图索引；根据索引基于的列数不同可以分为单列索引和复合索引。视图是从一个或多个表或视图中提取出来的数据的一种表现形式。在数据库中只有视图的定义，而没有实际对应“表〞的存在。通过视图的使用可以提高数据的平安性，隐藏数据的复杂性，简化查询语句，别离应用程序与根底表，保存复杂查询等。〔5〕序列是用于产生唯一序列号的数据库对象，可以为多个数据库用户依次生成不重复的连续整数，通常使用序列自动生成表中的主键值。序列创立后，可以通过序列NEXTVAL属性产生序列值，通过属性CURRVAL查看当前的序列值。〔6〕分区的必要性：提高数据的平安性，一个分区的损坏不影响其他分区中数据的正常使用；将表的各个分区存储在不同磁盘上，提高数据的并行操作能力；简化数据的管理，可以将某些分区设置为不可用状态，某些分区设置为可用状态，某些分区设置为只读状态，某些分区设置为读写状态；操作的透明性，对表进行分区并不影响操作数据的SQL语句。对表进行分区的方法包括：范围分区：按照分区值的范围对表进行分区。列表分区：按照分区值对表进行分区。散列分区：采用HASH算法在指定数量的分区中均等地分配数据。复合分区：先对表进行范围分区，然后对每个分区进行散列分区。〔7〕本地分区索引是指为分区表中的各个分区单独创立索引分区，各个索引分区之间是相互独立的，索引的分区与表的分区是一一对应的。全局分区索引：全局分区索引是指先对整个表建立索引，然后再对索引进行分区。索引的分区之间不是相互独立的，索引分区与表分区之间也不是一一对应的全局非分区索引：建立索引但不分区2．实训题〔1〕略〔2〕Createtableexer_class(CNOnumber(2)primarykey,CNAMEvarchar2(20),NUMnumber(3))Createtableexer_student(SNOnumber(4)primarykey,SNAMEvarchar2(10)unique,SAGEnumber,SEXchar(2),CNOnumber(2))〔3〕Altertableexer_studentaddconstraintck_sagecheck(sage>0andsage<=100);〔4〕altertableexer_studentaddconstraintck_stucheck(sex='M'orsex='F')modifysexdefault'M'〔5〕Createuniqueindexind_cnameonexer_class(cname);〔6〕Createviews_casSelectsno,sname,sage,sex,so,cname,numFromexer_classcjoinexer_studentsOnco=so;〔7〕Createsequencesequ1startwith100000001;〔8〕createtableexer_student_range(snonumber(4)primarykey,snamevarchar2(10),sagenumber,sexchar(2),cnonumber(2))partitionbyrange(sage)(partitionpart1valueslessthan(20)tablespaceexample,partitionpart2valueslessthan(30)tablespaceorcltbs1,partitionpart3valueslessthan(maxvalue)tablespaceorcltbs2)〔9〕createtableexer_student_list(snonumber(4)primarykey,snamevarchar2(10),sagenumber,sexchar(2),cnonumber(2))partitionbylist(sex)(partitionmanvalues('M')tablespaceorcltbs1,partitionwomanvalues('F')tablespaceorcltbs2)〔10〕Createindexindonexer_student_range(sno)local;3．选择题CADBABBACBBCBDACE第7章数据操纵与事务处理1．实训题〔1〕略〔2〕INSERTINTOBOOKVALUES('100001','冶金工业出版社','李代平','冶金工业出版社',TO_DATE('2003-01-01','YYYY-MM-DD'),38);

INSERTINTOBOOKVALUES('100002','Oracle9i中文版入门与提高','赵松涛','人民邮电出版社',TO_DATE('2002-07-01','YYYY-MM-DD'),35);

INSERTINTOBOOKVALUES('100003','Oracle9i开发指南：PL/SQL程序设计','JoanCasteel','电子工业出版社',TO_DATE('2004-04-03','YYYY-MM-DD'),49);

INSERTINTOBOOKVALUES('100004','数据库原理辅助与提高','盛定宇','电子工业出版社',TO_DATE('2004-03-01','YYYY-MM-DD'),34);

INSERTINTOBOOKVALUES('100005','Oracle9i中文版实用培训教程','赵伯山','电子工业出版社',TO_DATE('2002-01-01','YYYY-MM-DD'),21);

INSERTINTOBOOKVALUES('100006','Oracle8实用教程','翁正科等','电子工业出版社',TO_DATE('2003-07-08','YYYY-MM-DD'),38);INSERTINTOREADERVALUES('200001','张三');

INSERTINTOREADERVALUES('200002','李凤');

INSERTINTOREADERVALUES('200003','孟欣');

INSERTINTOREADERVALUES('200004','谢非');

INSERTINTOREADERVALUES('200005','刘英');INSERTINTOBORROWVALUES('100001','200001',TO_DATE('2004-08-1010:06:14','YYYY-MM-DDHH:MI:SS'));

INSERTINTOBORROWVALUES('100002','200002',TO_DATE('2004-08-1010:06:27','YYYY-MM-DDHH:MI:SS'));

INSERTINTOBORROWVALUES('100003','200003',TO_DATE('2004-08-1010:06:36','YYYY-MM-DDHH:MI:SS'));

INSERTINTOBORROWVALUES('100004','200004',TO_DATE('2004-08-1010:06:48','YYYY-MM-DDHH:MI:SS'));

INSERTINTOBORROWVALUES('100005','200005',TO_DATE('2004-08-1010:06:58','YYYY-MM-DDHH:MI:SS'));〔3〕INSERTINTOBOOKVALUES('10000007','Java网络编成','李程等','电子工业出版社',TO_DATE('2000-08-01','YYYY-MM-DD'),35);

〔4〕UPDATEBOOKSETPRICE=29WHERENO='100007';

〔5〕DELETEFROMBOOKWHERENO='10000007'第8章数据查询1．实训题查询100号部门的所有员工信息。Selsect*fromemployeeswheredepartment_id=100查询所有职位编号为“SA_MAN〞的员工的员工号、员工名和部门号。Selectemployee_id,first_name,last_name,department_idfromemployeeswherejob_id=‘SA_MAN’查询每个员工的员工号、工资、奖金以及工资与奖金的和。Selectemployee_id,salary,commission_pct,salary*(1+nvl(commission_pct,0)fromemployees查询40号部门中职位编号为“AD_ASST〞和20号部门中职位编号为“SA_REP〞的员工的信息。 Select*fromemployeeswheredepartment_id=40andjob_id=’AD_ASST’ORdepartment_id=20andjob_id=’SA_REP’;查询所有职位名称不是“StockManager〞和“PurchasingManager〞，且工资大于或等于2000的员工的详细信息。Select*fromemployeeswherejob_idnotin(’StockManager’,’PurchasingManager’)andsalary>=2000查询有奖金的员工的不同职位编号和名称。Selectdistinctjob_id,job_titlefromjobswherejob_idin(selectjob_idfromemployeeswherejob_idisnotnull)查询没有奖金或奖金低于100元的员工信息。Select*fromemployeeswheresalary*commission_pct<100orcommissionisNULL查询员工名〔first_name〕中不包含字母“S〞的员工。Selectfirst_namefromemployeeswherefirst_namenotlike‘%S%’查询员工的姓名和入职日期，并按入职日期从先到后进行排序。Selectfirst_name,last_name,hire_datefromemployeesorderbyhire_date;查询所有员工的姓名及其直接上级的姓名。Selecta.first_name,b.first_namefromemployeesajoinemployeesbonb.employee_id=a.manage_id查询入职日期早于其直接上级领导的所有员工信息。select*fromemployeesawherehire_date<(selecthire_datefromemployeesbb.employee_id=a.manage_id)查询各个部门号、部门名称、部门所在地以及部门领导的姓名。Selectd.department_id,d.department_name,d.location,e.first_namefromdepartmentsdjoinemployeeseond.manager_id=e.employee_id查询所有部门及其员工信息，包括那些没有员工的部门。Selectdepartment_name,first_namefromdepartmentsdleftjoinemployeeseond.deparment_id=e.department_id查询所有员工及其部门信息，包括那些还不属于任何部门的员工。Selecte.first_name,d.department_nameFromemployeesleftjoindepartmentsone.department_id=d.department_id;查询所有员工的员工号、员工名、部门名称、职位名称、工资和奖金。Selecte.employee_id,e.first_name,d.department_name,j.job_title,e.salary,e.salary*emission_pct奖金Fromdepartmentsdjoinemployeeseond.department_id=e.department_idJoinjobsjonj.job_id=e.job_id;查询至少有一个员工的部门信息。select*fromdepartmentsdwhereexists(select1fromemployeeswheredepartment_id=d.department_id)查询工资比100号员工工资高的所有员工信息。Select*fromemployeeswheresalary>(selectsalaryfromemployeeswhereemployee_id=100);查询工资高于公司平均工资的所有员工信息。Select*fromemployeeswheresalary>(selectavg(salary)fromemployees)查询各个部门中不同职位的最高工资。Selectjob_id,max(salary)fromemployeesgroupbyjob_id查询各个部门的人数及平均工资Selectdepartment_id,count(*),avg(salary)fromemployeesgroupbydepartment_id;统计各个职位的员工人数与平均工资。Selectjob_id,count(employee_id),avg(salary)fromemployeesgroupbyjob_id;统计每个部门中各职位的人数与平均工资。Selectdepartment_id,job_id,count(*),avg(salary)fromemployeesgroupbydepartment_id,job_id;查询最低工资大于5000元的各种工作。Selectjob_id,job_titlefromjobswherejob_idin(Selectjob_idfromemployeesgroupbyjob_idhavingmin(salary)>5000);查询平均工资低于6000元的部门及其员工信息。Selecte.*,d.*fromemployeesejoindepartmentsdone.department_id=d.department_idanddepartment_idin(selectdepartment_Idfromemployeesgroupbyemployee_idhavingavg(salary)<6000);查询在“Sales〞部门工作的员工的姓名信息。Select*fromemployeewheredepartment_idin(selectdepartment_dfromdepartmentswheredepartment_name=’Sales’)查询与140号员工从事相同工作的所有员工信息。Select*fromemployeeswherejob_idin(selectjob_idfromemployeeswhereemployee_id=140);查询工资高于30号部门中所有员工的工资的员工姓名和工资。Selectfirst_name,last_name,salaryfromemployeeswheresalary>(selectmax(salary)fromemployeesdeparment_id=30);查询每个部门中的员工数量、平均工资和平均工作年限。Selectcount(*),avg(salary),avg(round((sysdate-hire_date)/365))fromemployeesgroupbydepartment_id查询工资为某个部门平均工资的员工的信息。Select*fromemployeeswheresalsryin(selectavg(Salary)fromemployeesgroupbydepartment_id)查询工资高于本部门平均工资的员工的信息。Select*fromemployeese1wheresalary>(selectavg(salary)fromemployeese2wheree2.department_id=e1.department_id)查询工资高于本部门平均工资的员工的信息及其部门的平均工资。Selecte.*,avgsalFromemployeesejoin(selectdepartment_id,avg(salary)avgsalfromemployeesgroupbydepartment_id)dOne.department_id=d.department_idAnde.salary>d.avgsal查询工资高于50号部门某个员工工资的员工的信息。Select*fromemployeeswheresalary>any(selectsalaryfromemployeeswheredepartment_id=50):查询工资、奖金与10号部门某员工工资、奖金都相同的员工的信息。Select*fromemployeeswhere(salary,nvl(commission_pct))in(Selectsalary,nvl(commission_pct)fromemployeeswheredepartment_id=10)查询部门人数大于10的部门的员工信息。Select*fromemployeeswheredepartment_idin(selectdepartment_idfromemployeesgroupbydepartment_idhavingcount(*)>10);查询所有员工工资都大于10000元的部门的信息Select*fromdepartmentwheredepartment_idin(selectdepartment_idfromemployeesgroupbydepartment_idhavingmin(salary)>10000)查询所有员工工资都大于5000元的部门的信息及其员工信息。Selecte.*,d.*FromemployeesejoindepartmentsdOne.department_id=d.department_idWheredepartment_idin(Selectdepartment_idfromemployeesgroupbydepartment_idhavingmin(salary)>5000))查询所有员工工资都在4000元～8000元之间的部门的信息。Select*fromdepartmentswheredepartment_idin(Selectdepartment_idfromemployeesgroupbydepartment_idhavingmin(salary)>=4000andmax(salary)<=8000)查询人数最多的部门信息。Select*fromdepartment_idwheredepartment_idin(Selectdepartment_idfromemployeesgroupbydepartment_idhavingCount(*)>=all(selectcount(*)fromemployeesgroupbydepartment_id))查询30号部门中工资排序前3名的员工信息。Select*fromemployeewheredepartment_id=30andsalaryisnotnullandrownum<=3orderbysalarydesc查询所有员工中工资排序在5~10名之间的员工信息。Select*from(Selectrownumrn,employee_id,salaryfrom(Selectemployee_id,salaryfromemployeeswheresalaryisnotnullorderbysalarydesc)e1)e2Wherernbetween5and10将各部门员工的工资修改为该员工所在部门平均工资加1000。updateemployeesesetsalary=(selectavg(Salary)fromemployeeswheredepartment_id=e.department_id)+1000;查询各月倒数第2天入职的员工信息。Select*fromemployeeswherehire_date=last(hire_date)-1’查询工龄大于或等于10年的员工信息。select*fromhr.employeeswhere(sysdate-hire_date)/365>=10;查询员工信息，要求以首字母大写的方式显示所有员工姓〔last_name〕和员工名〔first_name〕。 selectinitcap(first_name),initcap(last_name)fromhr.employees;查询员工名〔first_name〕正好为6个字符的员工的信息。select*fromhr.employeeswherelength(first_name)=6;查询员工名〔first_name〕的第2个字母为“M〞的员工信息。selectfirst_namefromhr.employeeswherefirst_namelike'_M%';查询所有员工名〔first_name〕，如果包含字母“s〞，那么用“S〞替换。selectreplace(first_name,'S','s')fromhr.employees;查询在2月份入职的所有员工信息。Select*fromemployeeswhereextract(mmfromhire_date)=2;2．选择题BBDAACEBDBCDCBDCBDAC第9章PL/SQL语言根底1．实训题〔1〕declare

cursorc_empisselect*fromemployees;

begin

forv_empinc_emploop

dbms_output.put_line(v_emp.first_name||''||v_emp.last_name||''||

v_emp.employee_id||''||v_emp.salary||''||v_emp.department_id);

endloop;

end;〔2〕declare

v_avgsalemployees.salary%type;

begin

forv_empin(select*fromemployees)loop

selectavg(salary)intov_avgsalfromemployees

wheredepartment_id=v_emp.department_id;

ifv_emp.salary>v_avgsalthen

dbms_output.put_line(v_emp.first_name||''||v_emp.last_name||''||

v_emp.employee_id||''||v_emp.salary||''||v_emp.department_id);

endif;

endloop;

end;

〔3〕declare

cursorc_empis

selecte.employee_ideid,e.last_nameename,

e.department_idedid,m.employee_idmid,m.last_namemname

fromemployeesejoinemployeesm

one.manager_id=m.employee_id;

v_empc_emp%rowtype;

begin

openc_emp;

loop

fetchc_empintov_emp;

exitwhenc_emp%notfound;

dbms_output.put_line(v_emp.eid||''||v_emp.ename||''||

v_emp.edid||''||v_emp.mid||''||v_emp.mname);

endloop;

closec_emp;

end;

〔4〕declare

v_empemployees%rowtype;

begin

select*intov_empfromemployeeswherelast_name='Smith';

dbms_output.put_line(v_emp.employee_id||''||

v_emp.first_name||''||v_emp.last_name||''||

v_emp.salary||''||v_emp.department_id);

exception

whenno_data_foundthen

insertintoemployees(employee_id,last_name,salary,email,hire_date,

job_id,department_id)

values(2023,'Smith',7500,'smith@',

to_date('2000-10-5','yyyy-mm-dd'),'AD_VP',50);

whentoo_many_rowsthen

forv_empin(select*fromemployeeswherelast_name='Smith')loop

dbms_output.put_line(v_emp.employee_id||''||

v_emp.first_name||''||v_emp.last_name||''||

v_emp.salary||''||v_emp.department_id);

endloop;

end;

〔5〕declare

cursorc_empisselect*fromemployees;

v_incrementemployees.salary%type;

begin

forv_empinc_emploop

case

whenv_emp.job_id='AD_PRES'ORv_emp.job_id='AD_VP'

ORv_emp.job_id='AD_ASST'THEN

v_increment:=1000;

whenv_emp.job_id='FI_MGR'ORv_emp.job_id='FI_ACCOUNT'THEN

v_increment:=800;

WHENv_emp.job_id='AC_MGR'ORv_emp.job_id='AC_ACCOUNT'THEN

v_increment:=700;

WHENv_emp.job_id='SA_MAN'ORv_emp.job_id='SA_REP'THEN

v_increment:=600;

WHENv_emp.job_id='PU_MAN'ORv_emp.job_id='PU_CLERK'THEN

v_increment:=500;

WHENv_emp.job_id='ST_MAN'ORv_emp.job_id='ST_CLERK'

ORv_emp.job_id='SH_CLERK'THENv_increment:=400;

WHENv_emp.job_id='IT_PROG'ORv_emp.job_id='MK_MAN'

ORv_emp.job_id='MK_REP'THENv_increment:=300;

ELSEv_increment:=200;

endcase;

updateemployeessetsalary=salary+v_increment

whereemployee_id=v_emp.employee_id;

endloop;

end;

〔6〕declare

v_lowsaljobs.min_salary%type;

v_highsaljobs.max_salary%type;

eexception;

begin

updateemployeessetsalary=8000whereemployee_id=201;

selectmin_salary,max_salaryintov_lowsal,v_highsal

fromjobswherejob_id=(selectjob_idfromemployeeswhere

employee_id=201);

if8000notbetweenv_lowsalandv_highsalthen

raisee;

endif;

exception

whenethen

raise_application_error(-20001,'beyondlimit');

rollback;

end;第10章PL/SQL程序设计〔1〕创立一个存储过程，以员工号为参数，输出该员工的工资。createorreplaceprocedurepro_showsal(p_empnoemployees.employee_id%type)asv_salemployees.salary%type;beginselectsalaryintov_salfromemployeeswhereemployee_id=p_empno;dbms_output.put_line(v_sal);exceptionwhenno_data_foundthendbms_output.put_line('thereisnotsuchanemployees');end;beginpro_showsal(100);end;〔2〕创立一个存储过程，以员工号为参数，修改该员工的工资。假设该员工属于10号部门，那么工资增加140元；假设属于20号部门，那么工资增加200元；假设属于30号部门，那么工资增加250元；假设属于其他部门，那么工资增长300元。createorreplaceprocedurepro_updatesal(p_empnoemployees.employee_id%type)asv_deptnoemployees.department_id%type;v_incnumber;beginselectdepartment_idintov_deptnofromemployeeswhereemployee_id=p_empno;casev_deptnowhen10thenv_inc:=140;when20thenv_inc:=200;when30thenv_inc:=250;elsev_inc:=300;endcase;updateemployeessetsalary=salary+v_incwhereemployee_id=p_empno;exceptionwhenno_data_foundthendbms_output.put_line('thereisnotsuchanemployees');end;〔3〕创立一个函数，以员工号为参数，返回该员工的工资。createorreplacefunctionfunc_retsal(p_empnoemployees.employee_id%type)returnemployees.salary%typeasv_salemployees.salary%type;beginselectsalaryintov_salfromemployeeswhereemployee_id=p_empno;returnv_sal;exceptionwhenno_data_foundthendbms_output.put_line('thereisnotsuchanemployees');end;begindbms_output.put_line(func_retsal(100));end;创立一个函数，以员工号为参数，返回该员工所在部门的平均工资。createorreplacefunctionfunc_retavgsal(p_empnoemployees.employee_id%type)returnemployees.salary%typeasv_deptnoemployees.department_id%type;v_avgsalemployees.salary%type;beginselectdepartment_idintov_deptnofromemployeeswhereemployee_id=p_empno;selectavg(salary)intov_avgsalfromemployeeswheredepartment_id=v_deptno;returnv_avgsal;exceptionwhenno_data_foundthendbms_output.put_line('thereisnotsuchanemployees');end;〔5〕创立一个包，包中包含一个函数和一个过程。函数以部门号为参数，返回该部门员工的最高工资；过程以部门号为参数，输出该部门中工资最高的员工名、员工号。createorreplacepackagepkg_empasfunctionfunc_ret_maxsal(p_deptnonumber)returnnumber;procedurepro_showemp(p_deptnonumber);end;createorreplacepackagebodypkg_empasfunctionfunc_ret_maxsal(p_deptnonumber)returnnumberasv_maxsalnumber;beginselectmax(salary)intov_maxsalfromemployeeswheredepartment_id=p_deptno;returnv_maxsal;end;procedurepro_showemp(p_deptnonumber)ascursorc_empisselect*fromemployeeswheredepartment_id=p_deptnoandsalary=func_ret_maxsal(p_deptno);beginforv_empinc_emploopdbms_output.put_line(v_emp.employee_id||''||v_emp.salary);endloop;end;end;〔6〕创立一个包，包中包含一个过程和一个游标。游标返回所有员工的信息；存储过程实现每次输出游标中的5条记录。createorreplacepackagepkg_showemp

as

cursorc_empisselect*fromemployees;

procedureshow_fiveemp;

end;

createorreplacepackagebodypkg_showemp

as

procedureshow_fiveemp

as

v_empc_emp%rowtype;

begin

ifnotc_emp%isopenthen

openc_emp;

endif;

foriin1..20loop

fetchc_empintov_emp;

ifc_emp%notfoundthen

closec_emp;

exit;

endif;

dbms_output.put_line(v_emp.employee_id||''||

v_emp.first_name);

endloop;

end;

end;

begin

pkg_showemp.show_fiveemp;

end;〔7〕在employees表上创立一个触发器，保证每天8：00～17：00之外的时间禁止对该表进行DML操作。createorreplacetriggertrg_empbeforeinsertorupdateordeleteonemployeesdeclarebeginifto_char(sysdate,'HH24:MI')notbetween'08:00'and'17:00'thenraise_application_error(-20000,'此时间内，不允许修改EMPLOYEES表');endif;end;〔8〕在employees表上创立一个触发器，当插入、删除或修改员工信息时，统计各个部门的人数及平均工资，并输出。createorreplacetriggertrg_8

afterinsertorupdateordelete

onemployees

declare

cursorc_deptis

selectdepartment_id,avg(salary)avgsal,count(*)num

fromemployeesgroupbydepartment_id;

begin

forvinc_deptloop

dbms_output.put_line(v.department_id||''||

v.avgsal||''||v.num);

endloop;

end;〔9〕在employees表上创立一个触发器，保证修改员工工资时，修改后的工资低于部门最高工资，同时高于部门的最低工资。createorreplacetriggertrg_after

afterupdateofsalary

onemployees

declare

v_maxsalemployees.salary%type;

v_minsalemployees.salary%type;

begin

selectmax(salary),min(salary)intov_maxsal,v_minsal

fromemployeeswheredepartment_id=pkg_10.v_deptno;

dbms_output.put_Line(v_minsal||''||v_maxsal||pkg_10.v_newsal);

ifpkg_10.v_newsal<v_minsalorpkg_10.v_newsal>v_maxsalthen

raise_application_error(-20001,'beyond');

dbms_output.put_Line('beyond');

endif;

end;〔10〕创立一个存储过程，以一个整数为参数，输出工资最高的前几个员工的信息。Createorreplaceprocedurepro_emp(pnumber)AsCursorc_empisSelect*fromemployeewheresalaryisnotnullandrownum<=porderbysalarydesc;BeginForvinv_EmploopDbms_output.put_line(v.employee_id||v.salary||v.department_id);Endloop;End;第11章PL/SQL高级应用实训题将employees表中所有员工信息存储到一个嵌套表变量中，输出该嵌套表中所有工资大于10000元的元素。DECLARECURSORall_empsISSELECT*FROMemployees;TYPEt_empnestedISTABLEOFemployees%ROWTYPE;v_empt_empnested:=t_empnested();indexvalueBINARY_INTEGER:=1;BEGINFORempINall_empsLOOPv_emp.extend;v_emp(indexvalue):=emp;indexvalue:=indexvalue+1;ENDLOOP;FORiIN1..v_emp.COUNTLOOPIFv_emp(i).salary>10000thenDBMS_OUTPUT.PUT_LINE(v_emp(i).employee_id||''||v_emp(i).first_name||’'||emp(i).last_name||''||v_emp(i).salary||''||v_emp(i).department_id);ENDIF;ENDLOOP;END;将departments表中所有部门信息存储到一个可变数组变量中，输出该可变数组中元素的个数及所有元素。DECLARECURSORc_deptISSELECT*FROMdepartments;TYPEt_deptISVARRAY(200)OFdepartments%ROWTYPE;v_deptt_dept:=t_dept();indexvalueBINARY_INTEGER:=1;BEGINFORvINc_deptLOOPv_dept.extend;v_dept(indexvalue):=v;indexvalue:=indexvalue+1;ENDLOOP;FORiIN1..v_dept.LASTLOOPDBMS_OUTPUT.PUT_LINE(v_dept(i).department_id||’‘||v_dept(i).department_name||’‘||v_dept(i).location);ENDLOOP;DBMS_OUTPUT.PUT_LINE(v_dept.count);END;创立一个存储过程，以表名和一个列名为参数，创立一个表。CREATEORREPLACEPROCEDUREcreate_table(p_tablenameVARCHAR2,p_col1VARCHAR2,p_col1_typeVARCHAR2)ASv_creationVARCHAR2(100);BEGINv_creation:='CREATETABLE'||p_tablename||'('||p_col1||''||p_col1_type||'primarykey';EXECUTEIMMEDIATEv_creation;END;创立一个存储过程，将某个表的SELECT权限授予某个用户。CREATEORREPLACEPROCEDUREgrant_priv(p_tablenameVARCHAR2,p_usernameVARCHAR2)ASsql_strvarchar2(100);BEGINsql_str:='GRANTSELECTON'||p_tablename||'TO'||p_username;EXECUTEIMMEDIATEsql_str;END;创立一个存储过程，以员工号为参数删除该员工。创立一个存储过程，参数为员工号、列名、值，实现对特定员工信息中特定列信息的修改。CREATEORREPLACEPROCEDUREdyn_dml_test(p_colVARCHAR2,p_valueVARCHAR2,p_empnoNUMBER)ISv_strVARCHAR2(100);BEGINv_str:='UPDATEempSET'||p_col||'=:ph_valueWHEREemployee_id=:ph_empno';EXECUTE