ORACLE试题01

ORACLE 数据库及SQL 语言考试题及答案考试试题说明：试题包括三类，名词解释、ORACLE 数据库知识问答、SQL 语句编写，主要用于考察新同事ORACLE 数据库知识和SQL 语言掌握情况。

名词解释可以在回答中阐明名词的定义和你所了解的任何相关信息，没有字数限制，但避免长篇大论，简要描述即可。

ORACLE 数据库知识问答，重点在于切中要害，回答按点给分，每题2分。

SQL 语句编写检查大家的实际SQL 语句编写能力及掌握情况，注意格式规范，要清晰易读。

一、名词解释一、名词解释1. 数据库数据库是按照数据结构来组织、存储和管理数据的仓库。

2. 实例实例是一组Oracle 后台进程/线程以及一个共享内存区，这些内存由同一个计算机上运行的线程/进程所共享。

3. 表空间表空间是数据库的逻辑划分，用于存放数据库对象，主要是数据表，所以称作表空间。

ORACLE 自身存在一些表空间，如system 、user 和undo 表空间。

数据库用户也可以自己定义自己的表空间，并为每个表空间分配对应的数据文件。

4. 索引索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

索引不论逻辑上和物理上都与相关的表的数据无关，索引需要独立的存储空间，所以索引在创建之初就需要设置对应的表空间。

二、ORACLE 数据库知识问答1. 数据表Pirmary Key 和Unique Key 的作用和区别作用：Pirmary Key 和Unique Key 都是为数据表提供唯一性约束。

区别：Primary key 的1个或多个列必须为NOT NULL ，如果列为NULL ，在增加PRIMARY KEY 时，列自动更改为NOT NULL 。

而UNIQUE KEY 对列没有此要求。

一个表只能有一个PRIMARY KEY ，但可以有多个UNIQUE KEY 。

2. dos 模式下数据库用户备份、恢复命令数据库备份：exp 用户名/密码@连接标识符数据库恢复：imp 用户名/密码@连接标识符3. 列举五个常用的集合函数max,min,sum,avg,count4. round 与trunc 在处理数字方面的区别，并举例说明round 返回四舍五入后的值，而trunc 返回截取后的值，不进行四舍五入。

oracle考试试题及答案Questions one by one, fill in the blanks (4 points per question exergy a total of 20 points)1,database management technology has gone through three stages: manual management, file system and database system2,database three level data structure is external mode, mode, internal model3,the Oracle database SGA database buffer by exergy exergy exergy redo log buffer shared pool.4,in the Oracle database integrity constraints exergy types are Primay key constraints・ Foreign key Unique constraint exergyCheck not need exergy exergy constraint constraint constraintDeclare cursor open the cursor cursor exergy exergy exergy extraction in PL/SQL, including 5 close myCursor cursor operationTwo, the true or false questions in every day 2 points 20 points total exergy rateThe basic objects stored in the database is 1, the data rate in T2, the database system is the core of DBMS in the T rate The characteristics of relationship between the 3 and the operation is set in the operating rate of T4,five basic operations in relational algebra, and is the difference,selection, projection, connection in the F rate5,Oracle process is the server process in the F rate6,the oraclet system SGA process server and all users in the process of sharing rate T7,the Oracle database system in the data block size in the T operation of the rate system8,Oracle database system exergy start database and the first step is to start a database instance in the T rateThe cursor 9, PL/SQL data can be changed in the F rate10, the database concept model is mainly used in database conceptual structure design in the F rateThree, use the title in the match each 7 points 35 points total exergy rateLogical independence and physical independence in 1, what is the database system data in the programDBMSProvides a two layer mapping mechanism in external mode threemode structureSchema, image, and modeInternal schema image・ The twoThe layer mapping mechanism guarantees the logicalindependence and physical independence of data in the database system・External modeThe schema image defines the correspondence between the external schema of the different users in the database and the logical schema of the database・When the database schema changes such as a relational database system to increase exergy change with the new relati on ship, the relationship between attribute data types can exergyExternal mode adjustmentThe relationship between image mode exergy guarantee a constant user oriented mode of each. The application is based on the data of the model prepared by the exergyWhich application is not required to ensure the independence of the logicof exergy exergy data and application of logical data independence・PatternThe internal schema image defines the global logical structure of the data in the database and the physical storage organization of those data in the systemCorrespondence・When changing the physical storage of data structures in the database when the internal model changes such as the definition and selection of a storage structure can adjust theThe constant so that the external schema of database system and individual applications do not have to change the database schema in the schema mapping relationship / hold mode・This will ensure that the physical data independence and the independence of the physical data between applications or databases ・2, the relational algebra equi jo ins difference is not a natural connection without contact yesterdayAnswer when the operator connected conditions included in the use of 〃二〃this connection is called equivalent connection. Connection operation General in the two table between for can also be in a table does nothave its own connection between connection operation such as from the ・Answer・The equivalent connections and self connections belong to the internal connection query3, what is the database database design is generally divided into what stage from1)Exergy databaseDatabaseIn according to the data structure to organize, store and manage data warehouse・2)Requirement design conceptual designlogic design physical designImplementation, operation and maintenanceFourBrief descriptionOracleComposition of exergy logical databaseA table space, segment, data blockFiveWell, try any one example of using method from cursorA exergyCreate tableCreate table test(Name char (30),Age char (40),Subject char (20),ID numeric (10))insert dataInsert, into, test, values (' hehe，,' haha，，‘ hh', 4)Define variablesDeclare @name char (30)Declare @age char (40)Declare ©subject char (20)Declare @id numericCreate a CursorDeclare himml cursorFor, select, [name], age, subject, ID, from, test open Open hiininlUse cursors to scroll throughFetch, himml, into, @name, @age, @subject, @id 一一Be careful@@FETCH_STATUS yesSQL SERVERInside variables andORACLEThe@@sqlstatusDiffer・While (@@FETCH_STATUS 二0)BeginPrintPrint @namePrint @agePrint ©subjectPrint @idFetch, hiininl, into, @name, @age,@subject, @idEndClose the cursor rate close the cursor result set in its entirety instead of exergyClose hiimnlClose the cursor cursor rate release the memory in and let the cursor name can be used again exergyDeallocate hiimnl five with employee tablesEMP (empno, ename, age, Sal, Tel, deptno), in which empno -------- name ------ n ame age - number of exergy exergy exergy exergy oftel ---- electric sal ------- age wagewordDeptno ----- Department number・Please program at SQL*PLUS in the morning following the following requirements・In every day 3 points total of 15 points in the 1 exergy rate, home telephone staff information query. In the SQL>SELECT FROM EMP WHERE Tel NOT * NULL; in the 2, query wages in 500 to 800 yuan betweenthe employee information in SQL>SELECT * FROM EMP WHERE BETWEEN 500 AND 800: in 3, according to the age in creasing order display employee nu mber, name, age, salary in the SQL>SELECT empno, ename, age, Sal FROM EMP ORDER BY age ASC; SQL>SELEC, 4As the average wage in the Department of SQL>SELECT AVG DOI (SAL) FROM EMP WHERE deptno二'D_01' ; in the 5, find the department number D OI over 40 years of age and wage of 400 yuan in the list of employees in the SQL>SELECT ename FROM EMP WHERE deptno二'D_01' AND age>40 A7D.An examination question twoTwo, fill in the blanks (each 2 points 30 points total exergy) please fill in the correct answers in the blanks every day. No fill and no "11.1.data model is usually composed of three elements of the data structure, data operation and data __________ constraint _・2.database systems, all types of user requests for database operations (data definition, query, update, and various controls) are made up ofA complex software to complete the exergy this software called DBMS3.in the SQL SELECT statement in the query to remove duplicate records of exergy exergy in the query results should be used —DISTINCT_・Key word・ 4. the use of SQL language SELECT statement for the query packet in the packet if he hoped to get rid of not meet the conditions should beUse the HAVING clause.5.relational database data manipulation language (DML) includes two types of operation in their search and update _■6.in relational database design in the database design is divided into requirement analysis, concept design, logic design, physical design, applicationProgram coding, debugging operation, database operation and maintenance in six stages・What stage of database design is the design relational schema?Exergy ____ task logic design ____7.operations can be divided into _ relational algebra _relational calculus and _______ two categories・The relationship between the 8. INF _ non _ main function to eliminate the dependence on the key attribute in the paradigm after grade to 2NF. 2NFThe relationship between — eliminate non main attributes on the keys of the transfer function can be _ dependent upon his paradigm level increased to 3NF.The three level structure of the 9. database through the concept of the pattern / image within the pattern to ensure ________________ independenee in the physical model of concept mapping / byAs in the ― logic _ independence guarantee.10.the meaning of SQL is _ structured query language _________ ・11.DBMS usually provide the authorization function to control permissions in the data of different users to access the database in its purpose is to numberAccording to the _____ security database・Three, short answer questions (6 points each item in a total of 24 points)Safety protection function 1. database provides four aspects which try to explain their meaning of exergyExergy security database is a rate caused by use of database protection prevent 订legal data leakage, change or damage・ SQL Server 2000The security mechanism consists of four layersThe first layer included operating system loginSecond layer server security management exergy exergySQL ServerLoginSpecial accountSAThird layer database security management database exergyexergy accessDatabase userFourth layer database objects in safety management of exergyexergy exergy database object tables and views in accessDatabase user gets roles2. the referential integrity rules in the purpose of it in the test example donburi・3. to Oracle DBMS for the SQL relational database language support is given in the case of grade three logic schematic 1) SQLLanguage support relational database three level logic structure consists of the outer layer and a memory, the concept of as shown in fig・・2)The concept of recording layer corresponds to the conceptual model is the basic table・The basic table is a table that itself actually exists ・A basic table is a it not by other forms of export table・ The basic table is usedCREATE TABLEStatement built・3)In the outer as seen by the user can be the basic table can also be view can also be the basic table view. A view is a virtual tableIt is composed of one or several basic forms of export table it does not exist in the physical memory directly on the table・ View is usedCREATE SQL VIEWlanguageSentence established.4)In the inner each basic table with a file storage is said by a group of the same type of stored records to indicate the value・DBAYou can manipulate physical storage files.4., briefly describe the DBMS database security control function, including what are the commonly used means?A database management system for data control function data securitycontrol function in order to ensure the safety and reliability of the data within the database to preventThe use of illegal cause data leakage and damage the data that avoid being peeped, tampering or ruining exergy data integrity control refers to the function of insuranceThe data card in the database correctly and effectively and to prevent the compatibility error data is not the semantic input or output.Four, database design (15 points)Suppose there is a relationship between the 1. to record each person，s identity card number, name and work unit・ Also contains every one of his / her childrenThe identity card number, name and place of birth and the he / she has every car brands and models・The real world from known facts thatSome people may have several cars but these cars may be the same type but may also is not the same type of exergySome people do not have the car if someone has the car included his every car has a car includedSome people may have several children but there are some people without children. The relationship model of the preliminary design of the are as followsR (identity cards, the name of the work unit of the C identity cards, the name of the C C was born in the car the model)The 〃C C〃identity cards, the name C was born "are the child，s identity card number, name and place of birth・Please send this pat tern into the pattern of the relationship between BCNF to determine the relationship between the main key. 7 points in a exergy exergy, the citizen identity cards in the name of the work unitThe type of car car exergy the identity card number inThe child identity cards, the exergy of C C C was born in the name of the identity card number in a certain school library2.assumptions to establish a database to save the readers, books and readers of record. In order to build theWe need to design a good database design from the conceptual model is shown and then the figure - the conceptual model intoa relational model・ pleaseDesign fTom La - map・The reader has readers attribute number, name, age, address and unit.Attributes of each book are ISBN, title, author and publisher・Each book for each reader borrowed date and should also have out of date ・ 8 points in exergyA reader reader the name address the exergy number in the unitThe author of the book ISBN Title Exergy in the press・The number of readers to borrow the books ISBN exergy date the date should be in five, calculation (the title 3 items within a total of 16 points)Clients with a commercial relational database the three basic table the table structure is as followsTable Article (commodity goods, the price of the stock in the commodity name)Table Customer (customer clients, the clients name the sex the age the phone)Orderitem order form (the number of the dients, the purchase price of goods number the date)Note that the answer to will give the answers written provisions of the local exergy answer requirements must be clearly not allowed to change the included writing programAnd optionally add sub queries・1.please create a GM_VIEW view of the retrieval clients using SQL language dients, clients and ordering goodsName, amount and date・(the number is equal to the purchase price * amount) 6 points in exergyCREAT VIEW GM_VIEW (clients, the clients name the commodity name the amount of the number of date) * AS SELECT _ clients, clients in a brand name in the purchase price in the amount of as in the date of FROM Artcle, Customer,OrderItemWHERE Customer・clients, =OrderItem・clients, and Article・commodity No.二OrderItem・ Article No. 2. please use the SQL language of female clients buy goods number, commodity name and the total number of out ・ 6 exergy rateSELECT _OrderItem・，commodity number AS, commodity number, Order Item ・ commodity name, AS commodity name, SUM (Order Item ・ quantity)The total quantity of AS is FROM, Orderitem, Artcle, Customer, WHERE _Artcle・,commodity number 二Orderitem・,commodity number AND, OrderItem・,commodity number 二Customer・, commodity number ANDCustomer・=，GROUP BY OrderItem・female gender in commodity trade name No.3.please use the SQL language ALTER TABEL command of a field in a field called the origin will increase the number of goods to table ArticleAccording to the type of the CHAR in the length of 30 in the rate of 4 points exergy command is as followsChar ALTER TABEL —Article ADD (30) — originItem 31, fill in the blanks (each 2 points in a total of 20 points)The SELECT statement for grouping query 1, using the SQL language in the packet will not meet if you want to remove the conditions should beUsing —HAVING..・ _ clause・In 2, in the design of relational database in database design is divided into requirement analysis, concept design, logic design, physical design, should beProgram coding, debugging run, database operation and maintenance in six stages・ What stage of database design is the design relational schema?The task of exergy _ logic designRelational operations in relational algebra and 3, including the selection, projection, __________ connection and division.4,the relationship model of entity integrity in referential integrity in user-defined integrity of three types of integrity・5,two yuan for entity set between A and B between the set in mapping base set must be one of the following four1. , one to one, contact2., one to many con tacts, more than3. to one, contact more than4., many pairs of contacts6,PL/SQL cursor the two types of explicit and implicit cursor cursor ・Two, single choice (3 points per item in a total of 15 points)1, in a relational database management system will create the view in the database three layer structure belongs to (A)A.external modeB. storage modeC. intra schemaD. conceptual schemaThe general characteristics of the 2, in the world of things in reality in the information world is called (A)A. entityB. entity keyThe C・ property D・ key 3 Relationship Model S J P SJP in the S in is students J curriculum P is ranking・ Each student takes in each courseThe performance has a certain rank each course ranking only one student in the column and No. The relationship model belongs to exergy (C)A, 2NF, B, 3NF, C, BCNF, D, 4NF4,the company has a department of a num bet of departments and employees each staff only belongs to a department can have a number of staffThe type of contact from staff to department is (C)A.many to many,B. , one to one,C., one to many, one toD., one to many5,the logical independence of data refers to (A)The concept of A. mode change external mode and not the applicationB.concept mode change mode notIn the C. mode concept mode not changeD. mode change external mode and not the applicationThe correct statement, query on wildcards in the 6. part (D)A・"匚 B・"represents a number of characters _〃can represent zero or more charactersC.〃—〃can not 〃%〃to use D・represents a characterThree, Jane answer1,the referential integrity rules purpose it donburi test example・2,Briefly describe the architecture features of Oracle database system1)contains at least one SYSTEM table space, and the DDL language2)various spatial data dictionary informationThe data stored in the table space, table space exergy is reflected in the form of multiple data files・3,what is the logic of program data independence and physical independence from 4, the DBMS of the database security control functionsincluding what means?5. Sketch the main steps of database conceptual design. (1)Data abstract conceptual model in the design of local exergy (2)The concept of local mode integrated into the global conceptual schema in(3) review 6, what is the function from the rollback segment7,cold and heat Be if eng explain back up different points and advantages from each of the 3 SCG in S#, model C#, grade, S# in the No.C for students course No. grade Exergy for a studentExamination results for a certain course・The average score were going to query the average score over 80 points in the course of the query resultsAccording to the average scores in ascending order average the same number in descending order according to the curriculum・ Write the SQL query・A Select C# AVG analysis (grade), From SCGGroup by C#Having AVG (grade) >80Order by 2, C# desc。

oracle数据库的试题及答案一、选择题1. Oracle数据库是一种（）数据库管理系统。

A. 非关系型B. 关系型C. 分布式D. 图形化2. 数据库中，将所有数据进行分类和组织的基本单位是（）。

A. 表B. 字段C. 记录D. 数据库3. 在Oracle数据库中，用于删除表中所有数据的操作是（）。

A. DELETEB. REMOVEC. DROPD. TRUNCATE4. 在Oracle数据库中，用于更新表中数据的操作是（）。

A. UPDATEB. MODIFYC. ALTERD. REPLACE5. 在Oracle数据库中，用于查询数据的操作是（）。

A. SELECTB. SEARCHC. FINDD. WHERE二、简答题1. 请简述Oracle数据库的特点。

Oracle数据库具有以下特点：- 完整性：通过约束和触发器来保证数据的完整性。

- 可扩展性：支持集群部署和分布式数据库，可以动态扩展数据库的容量。

- 安全性：提供了严格的数据权限管理和用户认证机制，保护数据免受未经授权的访问。

- 可靠性：支持数据备份和恢复功能，保障数据的持久性和可靠性。

- 高性能：采用多线程和缓存技术，提高数据库的读写性能和响应速度。

2. 在Oracle数据库中，什么是事务？事务是指逻辑上的一组操作，这组操作要么全部成功执行，要么全部失败回滚。

事务具有以下特性：- 原子性：事务中的操作要么全部执行成功，要么全部回滚，不会出现部分执行的情况。

- 一致性：事务执行前后，数据库的状态保持一致性，不会破坏数据的完整性。

- 隔离性：事务之间相互独立，每个事务都认为自己是唯一在操作数据库的。

- 持久性：事务一旦提交，其对数据库的修改将永久保存，不会被回滚。

3. 请说明Oracle数据库中的索引是什么，并简要介绍索引的作用。

索引是一种特殊的数据库对象，用于快速查找数据库中的数据。

索引基于一个或多个列值创建，并按照特定的排序规则存储列值和对应的行指针。

oracle笔试题及答案一、选择题1. Oracle数据库是一种（）数据库管理系统。

A. 关系型B. 非关系型C. 层次型D. 网状型答案：A2. 下列哪个选项不属于Oracle数据库的特点？A. 完全支持SQL语言B. 支持分布式数据库C. 提供高可用性和故障恢复机制D. 仅支持单用户操作答案：D3. 在Oracle数据库中，下列关键字中哪个用于插入一行数据？A. UPDATEB. DELETEC. SELECTD. INSERT答案：D4. 在Oracle数据库中，下列关键字中哪个用于从表中删除一行数据？A. TRUNCATEB. DROPC. DELETED. REMOVE答案：C5. 在Oracle数据库中，下列关键字中哪个用于更新表中的数据？A. MODIFYB. ALTERC. UPDATED. CHANGE答案：C6. 在Oracle数据库中，下列哪个语句用于创建一个新的用户？A. CREATE ROLEB. CREATE USERC. GRANT PERMISSIOND. ALTER ACCOUNT答案：B7. 在Oracle数据库中，下列关键字中哪个用于将表中的数据按照指定的列进行排序？A. SORTB. GROUPC. ORDERD. ARRANGE答案：C8. 在Oracle数据库中，下列关键字中哪个用于查询满足特定条件的数据？A. SELECTB. SEARCHC. FINDD. FILTER答案：A9. 在Oracle数据库中，下面哪个关键字用于创建新的表？A. CREATEB. MAKEC. BUILDD. CONSTRUCT答案：A10. 下列哪个Oracle数据库对象用于避免数据冗余，提高查询速度，并提供数据的一致性和完整性？A. 视图（View）B. 函数（Function）C. 存储过程（Stored Procedure）D. 游标（Cursor）答案：A二、简答题请简要回答以下问题。

oracle练习题（打印版）### Oracle数据库练习题#### 一、选择题1. Oracle数据库中，哪个命令用于创建表？- A. CREATE TABLE- B. CREATE DATABASE- C. DROP TABLE- D. ALTER TABLE2. 以下哪个不是Oracle数据库的数据类型？- A. NUMBER- B. CHAR- C. DATE- D. IMAGE3. 在Oracle数据库中，哪个命令用于删除表？- A. DELETE FROM- B. DROP TABLE- C. REMOVE TABLE- D. ERASE TABLE4. Oracle数据库中，如何查看当前用户？- A. SELECT USER FROM DUAL;- B. SELECT CURRENT_USER FROM DUAL;- C. SELECT USERNAME FROM ALL_USERS;- D. SELECT CURRENT_USER FROM ALL_USERS;5. 以下哪个命令用于在Oracle数据库中创建索引？- A. CREATE INDEX- B. CREATE KEY- C. CREATE CONSTRAINT- D. CREATE UNIQUE#### 二、填空题1. 在Oracle数据库中，使用____命令可以查看表结构。

2. Oracle数据库中，使用____命令可以查看当前数据库的所有表。

3. 要删除Oracle数据库中的行，可以使用____命令。

4. Oracle数据库中，____用于存储二进制数据。

5. Oracle数据库中，____命令用于查看数据库中所有的索引。

#### 三、简答题1. 描述Oracle数据库中事务的ACID属性。

2. 解释Oracle数据库中的锁定机制。

3. 说明Oracle数据库中视图的作用。

#### 四、操作题1. 创建一个名为`Employees`的表，包含以下字段：- `EmployeeID` NUMBER(10) PRIMARY KEY,- `FirstName` VARCHAR2(50),- `LastName` VARCHAR2(50),- `HireDate` DATE,- `Salary` NUMBER(10, 2),- `DepartmentID` NUMBER(10).2. 向`Employees`表中插入以下数据：- `EmployeeID`: 1001, `FirstName`: 'John', `LastName`:'Doe', `HireDate`: '2023-01-01', `Salary`: 70000,`DepartmentID`: 101.- `EmployeeID`: 1002, `FirstName`: 'Jane', `LastName`:'Smith', `HireDate`: '2023-02-15', `Salary`: 50000,`DepartmentID`: 102.3. 编写一个查询，显示所有员工的姓名和工资，按工资从高到低排序。

第1章Oracle数据库系统的结构与安装选择题1、下列有关ORACLE实例的说法哪个是错误的？（B）A. ORACLE实例主要由内存结构和迚程结构两部分组成B. 每一数据库至少有一个与之对应的实例，一个实例可以打开和使用多个数据库C. 每一个数据库可以由多个实例使用，但一个实例只能打开和使用一个数据库D. 实例就是运行状态下的ORACLE软件，主要是由内存结构和一些迚程组成2、以下哪些内存区域不是SGA的一部分？（B）A. 数据库缓冲区高速缓存B．PGAC．重做日志缓冲区D. 共享池3、DBWR迚程是（C）A. 从Shared Pool读出被更新的数据写入DataFilesB. 从Shared Pool读出被更新的数据写入LogFilesC. 从Database Buffer Cache读出被更新的数据写入DataFilesD. 从Database Buffer Cache读出被更新的数据写入LogFiles4、下面的描述中错误的是：（A）A. Oracle表的存储结构为：一个表就是一个物理文件，物理文件和表之间存在着绝对的一一对应关系。

B. 表空间由一个或多个数据文件组成。

C. 系统全局区（SGA）可由所有的服务器和用户迚程共享。

D. 段、区间、数据块是Oracle数据库的基本逻辑组织单位。

5、下面的描述中错误的是：（B ）A. 表空间由一个或多个数据文件组成。

B. 表中的数据存储在数据文件，索引等其他模式对象的数据不存储在数据文件中。

C. 数据块是Oracle I/O操作的最小单位。

D. 区间是由数据块组成的。

6、下面的描述中错误的是：（A）A. 程序全局区（PGA）可由所有的用户迚程共享。

B. Oracle使用表空间组织数据库，每个数据库至少有一个System表空间。

C. 数据块是Oracle I/O操作的最小单位。

D. 区间是由连续的数据块组成的。

7、下面的描述中错误的是：（C）A. 每个数据库必须至少有一个控制文件。

Oracle测试题（卷）+答案解析1) PL/SQL块中可以使用下列（）命令。

（选择两项）a) TRUNCATEb) DELETEc) SA VEPOINTd) ALTER TABLE2) 授予sa用户在SCOTT.EMP表中SAL列的更新权限的语句是（B）〔选择一项〕a) GRANT CHANGE ON SCOTT.EMP TO SAb) GRANT UPDA TE ON SCOTT.EMP(SAL) TO SAc) GRANT UPDA TE (SAL) ON SCOTT.EMP TO SAd) GRANT MODIFY ON SCOTT.EMP(SAL) TO SA3) EMP表有14条记录，则语句SELECT ‘Aptech’FROM EMP 的执行结果是（C）〔选择一项〕a) Aptechb) 无输出c) 14行Aptechd) 编译出错4) PL/SQL块中哪几部分是可选的（）〔选择二项〕a) Declareb) Beginc) Exceptiond) Constant5) 在创建序列的过程中，下列（）选项指定序列在达到最大值或最小值后，将继续从头开始生成值。

（选择一项）a) Cycleb) Nocyclec) Cached) Nocache6) 同义词有以下（）用途。

（选择三项）a) 简化SQL 语句b) 隐藏对象的名称和所有者c) 提供对对象的公共访问d) 显示对象的名称和所有者7) Oracle中用来释放锁的语句有（）〔选择二项〕a) commitb) Drop lockc) rollbackd) unlock8) 关于类型定义Number(9,2)说确的有（）〔选择一项〕a) 整数部分9位，小数部分2位，共11位b) 整数部分7位，小数部分2位，共9位c) 整数部分6位，小数点一位，小数部分2位，共9位d) 以上说法均不正确9) 下列哪种Lob类型用来存储数据库以外的操作系统文件（）〔选择一项〕a) CLOBb) BLOBc) CFILEd) BFILE10) Oracle中的三种系统文件分别是（）〔选择三项〕a) 数据文件b) 归档文件c) 日志文件d) 控制文件11) 下列哪项不是Oracle中常用的分区方法？（）〔选择一项〕a) 围分区b) 散列分区c) 列表分区d) 条件分区12) 查看Test中名称为P1的分区中的记录的查询语句为（）〔选择一项〕a) Select * from Test Where PartitionName=’p1’b) Select * from Test(p1)c) Select * from Test Partition(p1);d) 以上均正确13) 创建序列时，若未指定Cycle选项，则当当前值大于MaxValue时将（）〔选择一项〕a) 从MinValue重新开始循环b) 重复使用MaxValue 值c) 执行出错d) 使用随机值14) 下列哪项是创建索引组织表所必需的（）〔选一项〕a) Primary Keyb) Order Byc) Group Byd) 以上均不是15) 若表的某字段值存在大量的重复，则基于该字段适合创建哪种索引？（）〔选一项〕a) 标准索引b) 唯一索引c) 位图索引d) 分区索引16) 在PL/SQL块中定义一个名为PI值为3.14的Real型常量的语法是（）〔选一项〕a) A.Pi Const Real＝3.14;b) B.Pi Real Const ＝3.14;c) C.Constant Pi Real：＝3.14d) D.Pi Constant Real:=3.1417) 22.当Select语句没有返回行时，将引发下列哪个异常？（）[选择一项]a) A.No_rows_foundb) B.No_data_foundc) C.No_Data_rows_foundd) D.Invalid_Number18) Oracle中提供的两种游标是（）〔选择二项〕a) A.隐式游标b) B.静态游标c) C.REF游标d) D.显式游标19) 若Emp表中有14条记录，则用户执行了以下操作，结果是：（）〔选择一项〕Cursor mycur is Select * From emp；。

Oracle基础(习题卷1)第1部分：单项选择题，共63题，每题只有一个正确答案,多选或少选均不得分。

1.[单选题]以下()用户默认拥有SYSDBA权限A)A-SYSTEMB)B-SYSC)C-SAD)D-DBADMIN答案:B解析:2.[单选题]你使用 IMMEDIATE 选项关闭数据库实例。

考虑打开数据库需要执行的步骤：1. 分配 SGA2. 读取控制文件3. 读取日志文件4. 开始实例恢复5. 启动后台进程6. 检查数据文件一致性7. 读取 spfile 或者 pfile哪个选项是正确的关于这些步骤（）A)7, 1, 5, 2, 3, 6, 4B)1, 5, 7, 2, 3, 6; step 4 is not requiredC)7, 1, 5, 2, 3, 6 step 4 is not requiredD)1, 2, 3, 5, 6, 4; step 7 is not required答案:C解析:3.[单选题]执行下面的命令备份 USERS 表空间SQL> ALTER TABLESPACE users BEGIN BACKUP;ALTER TABLESPACE users BEGIN BACKUP*ERROR at line 1:ORA-01123: cannot start online backup;media recovery not enabled什么原因导致整个错误（）A)MTTR Advisor 禁用B)数据库处于 NOARCHIVELOG 模式C)表空间已经处于备份模式D)Flash Recovery Area 没有配置答案:B解析:4.[单选题]当执行介质数据库恢复时，有个归档日志文件丢失或者损坏，那么此时只能执行哪种操作？什么A)Until SCNB)Until timeC)Recover using backup control file.D)Until cancel答案:D解析:5.[单选题]oracle逻辑存储结构正确的是( )A)tablespace--segment--osblock--blockB)tablespace--segment--extent--blockC)tablespace--extent--segment--blockD)tablespace--extent--block -segment答案:B解析:6.[单选题]如何将变量v_row 定义为emp表的记录类型( )A)v_row emp%type;B)v_row emp%record;C)v_row emp%tabletype;D)v_row emp%rowtype;答案:D解析:7.[单选题]The schema SALES exists in two databases, ORCL1 and ORCL2, and has the same password,SALES123. User SALES has CREATE DATABASE LINK and CREATE SESSION privileges on bothDatabases.Eaxmine these command:Conn SALES/SALES123CREATE DATABASE LINK orcl2 USING ‘orcl12’;What is the outcome of executing these commands in the ORCL1 database?A)ORCL2 is created as a shared database link to connect multiple sessions to the SALES schema in the ORCL2 databaseB)ORCL2 database link creation failsC)ORCL2 is created as a private database link to connect to only the SALES schema in the ORCL2 databaseD)ORCL2 is created as a public link to connect a single session to the SALES schema in the ORCL2 database答案:C解析:8.[单选题]分析以下的SQL 命令:SELECT manufacturer_idFROM inventoryWHERE manufacturer_id LIKE '%N\%P\%O%' ESCAPE '\';命令执行的返回结果是( )。

简答题(4*5’)1、简述Oracle数据库体系结构的构成。

答：数据库实例，物理存储结构和逻辑存储结构。

实例包括内存结构与后台进程；物理存储结构主要用于描述Oracle数据库外部数据的存储，即在操作系统中如何组织和管理数据，与具体的操作系统有关；逻辑存储结构主要描述Oracle数据库内部数据的组织和管理方式，与操作系统没有关系。

2、简述Oracle数据库逻辑存储结构的组成及相互关系。

答：Oracle数据库逻辑存储结构包括表空间、段、区、块四种。

其中，数据块是数据库中最小I/O单位，由若干连续的数据块组成的区是数据库中最小的存储分配单元，由若干区形成的段是相同类型的数据存储分配区域，由若干段形成的表空间是最大的逻辑存储单元，所有的表空间构成一个数据库。

3、简述Oracle数据库重做日志文件的作用。

答：重做日志文件，保存了用户对数据库所作的更新操作，包含的主要信息是记录事务的开始和结束、事务中每项操作的对象和类型、更新操作前后的数据值等。

4、简述索引作用并说明索引按不同分类依据分为哪些类。

答：创建索引的作用是为了提高对表中数据的查询效率。

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

5、简述Oracle的安全控制机制有哪些？答：1）、用户管理2）、权限管理3）、角色管理4）、表空间设置和配额5）、用户资源限制6）、数据库审计6、简述Oracle数据库角色的种类、作用以及如何利用角色为用户授权。

答：SYS：是数据库中具有最高权限的数据库管理员，可以启动、修改和关闭数据库，拥有数据字典；SYSTEM：是一个辅助的数据库管理员，不能启动和关闭数据库，但可以进行其他一些管理工作，如创建用户、删除用户等。

SCOTT：是一个用于测试网络连接的用户，其口令为TIGER。

PUBLIC：实质上是一个用户组，数据库中任何一个用户都属于该组成员。

试题一一、填空题(每小题4分，共20分)1、数据库管理技术经历了人工管理、文件系统、数据库系统三个阶段2、数据库三级数据结构是外模式、模式、内模式3、Oracle数据库中，SGA由数据库缓冲区，重做日志缓冲区，共享池组成4、在Oracle数据库中，完正性约束类型有Primay key约束。

Foreign key约束，Unique约束，check约束，not need约束5、PL/SQL中游标操作包括声明游标，打开游标，提取游标，关闭游标二、正误判断题（每小题2分，共20分）1、数据库中存储的基本对象是数据（T）2、数据库系统的核心是DBMS（T）3、关系操作的特点是集合操作（T）4、关系代数中五种基本运算是并、差、选择、投影、连接（F）5、Oracle进程就是服务器进程（F）6、oraclet系统中SGA所有用户进程和服务器进程所共享（T）7、oracle数据库系统中数据块的大小与操作系统有关（T）8、oracle数据库系统中，启动数据库和第一步是启动一个数据库实例（T）9、PL/SQL中游标的数据是可以改变的（F）10、数据库概念模型主要用于数据库概念结构设计（）三、简答题（每小题7分，共35分）1、何谓数据与程序的逻辑独立性和物理独立性？2、试述关系代数中等值连接与自然连接的区别与联系？3、何谓数据库，数据库设计一般分为哪些阶段？4、简述Oracle逻辑数据库的组成？5、试任举一例说明游标的使用方法？五、设有雇员表emp(empno,ename,age,sal,tel,deptno),其中：empno-----编号，name------姓名，age -------年齡，sal-----工资，tel-----电话deptno-----部门号。

请按下列要求分别晨SQL*PLUS下编程。

（每小题3分，共15分）•1、查询家有电话的职工信息。

•2、查询工资在500至800元之间的雇员信息•3、按年龄递增顺序显示雇员编号、姓名、年龄、工资•4、求部门号为D_01的平均工资•5、查找部门号为D_01的40岁以上而工资在400元以下的雇员名单。