阿里巴巴的Oracle DBA笔试题及参考答案

1.( )程序包用于读写操作系统文本文件。

（选一项）A、Dbms_outputB、Dbms_lobC、Dbms_randomD、Utl_file2.( )触发器允许触发操作的语句访问行的列值。

（选一项）A、行级B、语句级C、模式D、数据库级3.( )是oracle在启动期间用来标识物理文件和数据文件的二进制文件。

（选一项）A、控制文件B、参数文件C、数据文件D、可执行文件4.CREATE TABLE 语句用来创建（选一项）A、表B、视图C、用户D、函数5.imp命令的哪个参数用于确定是否要倒入整个导出文件。

（选一项）A、constranintsB、tablesC、fullD、file6.ORACLE表达式NVL(phone，'0000-0000')的含义是（选一项）A、当phone为字符串0000-0000时显示空值B、当phone为空值时显示0000-0000C、判断phone和字符串0000-0000是否相等D、将phone的全部内容替换为0000-00007.ORACLE交集运算符是（选一项）A、intersectB、unionC、setD、minus8.ORACLE使用哪个系统参数设置日期的格式（选一项）A、nls_languageB、nls_dateC、nls_time_zoneD、nls_date_format9.Oracle数据库中，通过（）访问能够以最快的方式访问表中的一行（选一项）A、主键B、RowidC、唯一索引D、整表扫描10.Oracle数据库中，下面（）可以作为有效的列名。

（选一项）A、ColumnB、123_NUMC、NUM_#123D、#NUM12311.Oracle数据库中，以下（）命令可以删除整个表中的数据，并且无法回滚（选一项）A、dropB、deleteC、truncateD、cascade12.Oracle中, ( )函数将char或varchar数据类型转换为date数据类型。

Oracle精选笔试试题(doc 8页)Oracle笔试试题1. SQL必备<选择题每空 1 分共 14题>1. 如果在where子句中有两个条件要同时满足，应该用以下哪个逻辑符来连接( )A.ORB.NOTC.ANDD.NONE2. 外连接的条件可以放在以下的那一个子句中( )A.FROMB.WEHREC.SELECTD.HAVINGE.GROUP BYF.ORDER BY3. 在从两个表中查询数据时，连接条件要放在哪个子句中( )A.FROMB.WHEREC.SELECTD.HAVINGE.GROUP BY4. 用以下哪个子句来限制分组统计结果信息的显示( )A.FROMB.WEHREC.SELECTD.HAVINGE.GROUP BYF.ORDER BY5. 以下需求中哪个需要用分组函数来实现( )A.把ORDER表中的定单时间显示成 'DD MON YYYY' 格式B.把字符串 'JANUARY 28, 2000' 转换成日期格式C.显示PRODUCT 表中的COST 列值总量D.把PRODUCT表中的DESCRIPTION列用小写形式显示6. 以下那些命令可以暗含提交操作( )A.GRANTB.UPDATEC.SELECTD.ROLLBACK7.RDBMS是下列哪一项的缩写( )A. Relational DataBase Management System(关系数据库管理系统)B. Relational DataBase Migration System(关系数据库移植系统)C. Relational Data Migration System(关系数据移植系统)D. Relational DataBase Manage System(关系数据库管理系统)8.INSERT 是( )A. DML语句B. DDL语句C. DCL语句D. DTL语句9.SELECT CHR(66) FROM DUAL的结果是( )A. ZB. SC. BD. 都不是10.函数返回一个值除以另一个值后的余数( )A. MODB. ABSC. CEILD. 以上都不是11. 什么锁用于锁定表，仅允许其他用户查询表中的行，行不允许插入，更新，或删除行( )A. 共享B. 排他C. 共享更新D. 以上都不是12.什么是oracle提供的一个对象，可以生成唯一的连续的整数( )A. 同义词B. 序列C. 视图D. 没有13. 那种类型的约束可以自动创建索引( )A.CHECKB.UNIQUEC.NOT NULLD.PRIMART KEY14. 哪中类型的约束只能定义在列级( )A.CHECKB.UNIQUEC.NOT NULLD.PRIMART KEYE.FOREIGN KEY<简答题>（以下每题 2 分共 20 题）1. 简述SUBSTR和LENGTH的主要功能？2. 分析以下的SQL命令:SELECT *FROM productWHERE LOWER(description) = 'CABLE';命令能否执行？是否有结果返回？为什么？3. 在PLAN表中有一列为SPECIES_ID ，该列与SPECIES 表的ID列对应，但后者包含空值，要想在显示结果中包含SPECIES 表的ID列为空值的行的信息，用那种连接条件可以实现？4. 分析以下的 SQL 命令:SELECT i.id_number, m.manufacturer_idFROM inventory i, inventory mWHERE i.manufacturer_id = m.region_id_number;该命令中使用的连接条件是什么？5 .外连接的符号可以放在连接条件的哪边，可以两边同时使用吗？6. 如果用等值连接来查询５张表内的信息，至少需要几个连接条件？7. 分析以下的 SQL 命令:SELECT i.id_number, m.id_numberFROM inventory i, manufacturer mWHERE i.manufacturer_id = m.id_numberORDER BY 1;该命令执行结果的排序是按那一列的值排序的？8. 数据库中的 TEACHER 表的结果如下：ID NUMBER(7) PKSALARY NUMBER(7,2)SUBJECT_ID NUMBER(7)至少有两行以上的SUBJECT_ID值是不同的分析以下的 SQL命令:1、 SELECT ROUND(SUM(salary),-4)FROM teacher;2、 SELECT subject_id, ROUND(SUM(salary),-2)FROM teacherGROUP BY subject_id;两个语句的显示结果相同吗？哪个命令显示的结果会多些？9. 如果想对分组统计的结果信息进行筛选，用where 条件子句能实现吗？如果不行该用什么子句实现？10. EMPLOYEE 表包含以下的列:EMP_ID NUMBER(9)NAME VARCHAR2(25)BONUS NUMBER(5,2)DEPT_ID NUMBER(9)如果想计算所有具有bonus的职员的bonus平均值，不考虑该列包含空值的那些雇员，用什么功能函数可以实现？11. 写出包含 SELECT，FROM ，HAVING ，WHERE，GROUP BY ，ORDER BY子句的书写顺序12. 分析以下的SQL命令SELECT id_number "Part Number", SUM(price) "Price"FROM inventoryWHERE price > 5.00GROUP BY "Part Number"ORDER BY 2;哪一个子句会产生错误?如何修改？13. 分析以下的 SQL 命令:SELECT id_numberFROM inventoryWHERE manufacturer_id IN(SELECT manufacturer_idFROM inventoryWHERE price < 1.00 OR price > 6.00);该子查询的能否执行，是否有语法错误？14. 分析以下SQL命令:SELECT employee_id, nameFROM employeeWHERE employee_id NOT IN (SELECT employee_idFROM employeeWHERE department_id = 30 AND job = 'CLERK');如果子查询返回空值，结果会是什么样？15. 如果用户用UPDATE 命令修改了表中的数据值，是否可以恢复回来？16. 表PERSONNEL 的结构如下:ID NUMBER(9)LAST_NAME VARCHAR2(25)FIRST_NAME VARCHAR2(25)MANAGER_ID NUMBER(9)在这里, 部门的管理者也看作是雇员,分析以下两个命令:SELECT st_name, p.first_name, st_name, m.first_name FROM personnel p, personnel mWHERE m.id = p.manager_id;SELECT st_name, p.first_name, st_name, m.first_name FROM personnel p, personnel mWHERE m.manager_id = p.id;两个的执行结果一样吗？17. 分析以下的SQL命令CREATE SEQUENCE line_item_idSTART WITH 10001MAXVALUE 999999999NOCYCLE;请问这个序列NOCYCLE关键字的含义是什么？18. 表 TEACHER 的结构如下:Name Null ? Type---------------------------------------TEACHER_ID NOT NULL NUMBER(9)NAME VARCHAR2(25)SALARY NUMBER(7,2)SUBJECT_ID NOT NULL NUMBER(3)SUBJECT_DESCRIPTION VARCHAR2(2)编写一个SQL命令，实现以下要求，给所有的science teachers 增加8%的工资 The SUBJECT_ID for science teachers 的SUBJECT_ID 值是011.19.ORACLE数据库有哪几种约束类型？20.在建表时如果希望某列的值在一定的范围内，应建什么样的约束？（以下每题 3 分共 4 题）21.比较truncate和delete 命令?22.使用索引的理由?23.创建一张表，要求与EMP表具有相同的表结构，但是不要记录(即空表)？24．显示所有薪金高于各自部门平均薪金的人？（以下每题 5 分共 2 题）25．查找出数据库中表S_EMP表所有的约束类型？26. 给自己在数据库中已有的表添加一个主键约束？2. DBA必备<选择题每空 1 分共 5 题>1. 以下权限哪个时系统权限( )A.ALTERB.EXECUTEC.PREFERENCESD.BACKUP ANY TABLE2. 以下哪个权限时对象权限( )A.INDEXB.DROP USERC.CREATE SESSIOND.BACKUP ANY TABLE3. 以下哪个视图可以查到用户具有使用权限的表的信息( )ER_VIEWSER_TABLESC.ALL_OBJECTSER_OBJECTS4. ROWID的作用是( )A．唯一标识表中的一条记录B．这是一个伪列，用户一般无法使用，是由Oracle自身引用的C．表示了数据的物理存储方式D．没有作用5.关于索引的说法错误的是( )A．索引对于表来说，可有可无B．索引是用来提高查询速度的C．索引是用来装饰表，是表格好看一点D．索引会影响更新的速度3. PL/SQL必备<选择题每空 1 分共 8 题>1. 什么引擎执行PL/SQL块( )A．SQLB．PL/SQLC．ORACLED．都不对2. PLSQL块是由哪几个部分组成( )A. DECLARE BEGIN ENDB. BEGIN ENDC. EXCEPTION BEGIN ENDD. DECLARE BEGIN EXCEPTION END3. 使用游标的步骤，有哪几步( )A. 打开游标、使用游标、关闭游标B. 定义游标、打开游标、使用游标、关闭游标C. 定义游标、使用游标、关闭游标D. 定义游标、打开游标、使用游标、4. 游标有哪几种类型( )A. 静态游标、动态游标B. 隐式游标、显示游标C. 变量游标、常量游标D. 参数游标、ref 游标5. 在存储过程中，参数模式有哪几种( )A. IN、OUTB. IN、OUT、IN OUTC. INPUT、OUTPUTD. OUT INOUT6. 存储过程和函数的区别是( )A. 过程可以返回多个值，而函数只能返回一个值B. 函数可以作为PLSQL表达式的一部分，而过程不能C. 函数可以返回多个值，过程只能返回一个D. 函数和过程都必须包含RETURN语句7. 下面关于包的说法错误的是( )A.有包头，就必须有包体B.包可分为包头和包体两部分，但包体不是必须的C.如果只用函数和过程，则可以只有包体，没有包头D.包可以继承8. 触发器有哪些级别( )A. 行级触发器和字段级触发器B. 行级触发器C. 语句及触发器D. 行级触发器和语句及触发器<简述题本题 11 分共 1 题>解释FUNCTION,PROCEDURE和PACKAGE区别?。

OracleDBA数据库结构试题及答案Oracle DBA数据库结构精选试题及答案Q. 1 : Physical Disk Resources in an Oracle Database are1. Control Files2. Redo Log Files3. Data Files4. All of the above4Q. 2 : What is a Schema1. A Physical Organization of Objects in the Database2. A Logical Organization of Objects in the Database3. A Scheme Of Indexing4. None of the above2Q. 3 : An Oracle Instance is1. Oracle Memory Structures2. Oracle I/O Structures3. Oracle Background Processes4. All of the Above4Q. 4 : The SGA Consists of the Following Items1. Buffer Cache2. Shared Pool3. Redo Log Buffer4. All of the Above4Q. 5 : The area that stores the blocks recently used by SQL statements is called1. Shared Pool2. Buffer Cache3. PGA4. UGA2Q. 6 : Which of the following is not a Background Server Process in an Oracle Server1. DB Writer2. DB Reader3. Log Writer4. SMON2Q. 7 : Which of the following is a valid background server processes in Oracle1. ARCHiver2. LGWR ( Log Writer )3. DBWR ( Dbwriter )4. All of the above4Q. 8 : The process that writes the modified blocks to the data files is1. DBWR2. LGWR3. PMON4. SMON1 : Oracle does not modify the data in data file. Once the server process makes a change in the Memory, DBWR writes the modified blocks back to disk.Q. 9 : The process that records information about the changes made by all transactions that commit is1. DBWR2. SMON3. CKPT4. None of the above4 : LGWR process records the information about changes to databaseQ. 10 : Oracle does not consider a transaction committed until1. The Data is written back to the disk by DBWR2. The LGWR successfully writes the changes to redo3. PMON Process commits the process changes4. SMON Process Writes the data2Q. 11 : The process that performs internal operations like Tablespace Coalescing is1. PMON2. SMON3. DBWR4. ARCH2Q. 12 : The process that manages the connectivity of user sessions is1. PMON2. SMON3. SERV4. NET81Q. 13 : The ARCH process is enabled when the database runs in a1. PARALLEL Mode2. ARCHIVE LOG Mode3. NOARCHIVELOG Mode4. None of the above2Q. 14 : What performs the Check Point in the absence of a CKPT Process1. DBWR2. LGWR3. PMON4. SMON2 : At a check point dbwr writes all data to data files from memory. At this time the datafile headers have to be updated by LGWR in the absence of a CKPT processQ. 15 : If an application requests data that is already in the memory, it is referred to as a1. Cache Read2. Cache Hit3. Cache Miss4. Cache Latch2Q. 16 : If the data requested is in the memory but had to be reloaded due to aging, it is referred to as1. CACHE HIT2. CACHE REFRESH3. CACHE RELOAD4. None of the above3Q. 17 : If the data requested is not in the servers memory, it is referred to as1. CACHE DISK2. CACHE MISS3. CACHE READ4. None of the above2Q. 18 : You can Dynamically resize the following Parameters in the SGA1. Buffer Cache2. Library Cache3. Dictionary Cache4. None of the above4Q. 19 : The memory area that stores the parsed representation of most recently executed Statements is1. BUFFER CACHE2. LIBRARY CACHE3. DICTIONARY CACHE4. NONE OF THE ABOVE2Q. 20 : The Most recently used data dictionary information is stored in1. DATA DICTIONARY CACHE2. SHARED CACHE3. BUFFER CACHE4. NONE OF THE ABOVE1Q. 21 : The server memory that holds session-specific information is referred to as1. Program or Private Global Area2. Session Global Area3. Temp Space4. None of the above1Q. 22 : The area of memory used by the server as temporary area for sorting is called1. TEMP SPACE2. SORT AREA3. REDO BUFFER4. SORT BUFFER2Q. 23 : The fundamental unit of storage in a data file is1. BYTE2. BIT3. BLOCK4. None of the above3Q. 24 : The process that resolves the in-doubt transactions ina distributed environment is1. ARCH2. PROC3. RECO4. NONE OF THE ABOVE3Q. 25 : The size of each buffer in the database is set using this parameter1. DB_BLOCK_BUFFERS2. DB_BLOCK_SIZE3. DB_BYTE_SIZE4. NONE OF THE ABOVE2Q. 26 : The number of Block Buffers in the database is set in the init.ora using1. DB_BLOCK_SIZE2. DB_BLOCK_BUFFERS3. DB_BUFFER_CACHE4. NONE OF THE ABOVE2Q. 27 : The Parameter that sets the size of the shared SQL Area is1. SHARED_SQL_AREA2. SHARED_POOL_SIZE3. SHARED_CACHE_SIZE4. NONE OF THE ABOVE2。

Oracle笔试题目带答案1.( )程序包用于读写操作系统文本文件。

（选一项）A、Dbms_outputB、Dbms_lobC、Dbms_randomD、Utl_file2.( )触发器允许触发操作的语句访问行的列值。

（选一项）A、行级B、语句级C、模式D、数据库级3.( )是oracle在启动期间用来标识物理文件和数据文件的二进制文件。

（选一项）A、控制文件B、参数文件C、数据文件D、可执行文件4.CREATE TABLE 语句用来创建（选一项）A、表B、视图C、用户D、函数5.imp命令的哪个参数用于确定是否要倒入整个导出文件。

（选一项）A、constranintsB、tablesC、fullD、file6.ORACLE表达式NVL(phone，'0000-0000')的含义是（选一项）A、当phone为字符串0000-0000时显示空值B、当phone为空值时显示0000-0000C、判断phone和字符串0000-0000是否相等D、将phone的全部内容替换为0000-00007.ORACLE交集运算符是（选一项）A、intersectB、unionC、setD、minus8.ORACLE使用哪个系统参数设置日期的格式（选一项）A、nls_languageB、nls_dateC、nls_time_zoneD、nls_date_format9.Oracle数据库中，通过（）访问能够以最快的方式访问表中的一行（选一项）A、主键B、RowidC、唯一索引D、整表扫描10.Oracle数据库中，下面（）可以作为有效的列名。

（选一项）A、ColumnB、123_NUMC、NUM_#123D、#NUM12311.Oracle数据库中，以下（）命令可以删除整个表中的数据，并且无法回滚（选一项）A、dropB、deleteC、truncateD、cascade12.Oracle中, ( )函数将char或varchar数据类型转换为date数据类型。

1.( )程序包用于读写操作系统文本文件。

（选一项）A、Dbms_outputB、Dbms_lobC、Dbms_randomD、Utl_file2.( )触发器允许触发操作的语句访问行的列值。

（选一项）A、行级B、语句级C、模式D、数据库级3.( )是oracle在启动期间用来标识物理文件和数据文件的二进制文件。

（选一项）A、控制文件B、参数文件C、数据文件D、可执行文件4.CREATE TABLE 语句用来创建（选一项）A、表B、视图C、用户D、函数5.imp命令的哪个参数用于确定是否要倒入整个导出文件。

（选一项）A、constranintsB、tablesC、fullD、file6.ORACLE表达式NVL(phone，'0000-0000')的含义是（选一项）A、当phone为字符串0000-0000时显示空值B、当phone为空值时显示0000-0000C、判断phone和字符串0000-0000是否相等D、将phone的全部内容替换为0000-00007.ORACLE交集运算符是（选一项）A、intersectB、unionC、setD、minus8.ORACLE使用哪个系统参数设置日期的格式（选一项）A、nls_languageB、nls_dateC、nls_time_zoneD、nls_date_format9.Oracle数据库中，通过（）访问能够以最快的方式访问表中的一行（选一项）A、主键B、RowidC、唯一索引D、整表扫描10.Oracle数据库中，下面（）可以作为有效的列名。

（选一项）A、ColumnB、123_NUMC、NUM_#123D、#NUM12311.Oracle数据库中，以下（）命令可以删除整个表中的数据，并且无法回滚（选一项）A、dropB、deleteC、truncateD、cascade12.Oracle中, ( )函数将char或varchar数据类型转换为date数据类型。

Oracle数据库笔试面试试题及答案一、基础概念1. 列举几种表连接方式Answer：等连接（内连接）、非等连接、自连接、外连接（左、右、全）Or hash join/merge join/nest loop(cluster join)/index join ？？ORACLE 8i，9i 表连接方法。

一般的相等连接： select * from a, b where a.id = b.id; 这个就属于内连接。

对于外连接：Oracle中可以使用“(+) ”来表示，9i可以使用LEFT/RIGHT/FULL OUTER JOINLEFT OUTER JOIN：左外关联SELECT st_name, e.department_id, d.department_nameFROM employees eLEFT OUTER JOIN departments dON (e.department_id = d.department_id);等价于SELECT st_name, e.department_id, d.department_nameFROM employees e, departments dWHERE e.department_id=d.department_id(+)结果为：所有员工及对应部门的记录，包括没有对应部门编号department_id的员工记录。

RIGHT OUTER JOIN：右外关联SELECT st_name, e.department_id, d.department_nameFROM employees eRIGHT OUTER JOIN departments dON (e.department_id = d.department_id);等价于SELECT st_name, e.department_id, d.department_nameFROM employees e, departments dWHERE e.department_id(+)=d.department_id结果为：所有员工及对应部门的记录，包括没有任何员工的部门记录。

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 DBA笔试题及参考答案- 数据库基本概念类1:pctused and pctfree 表示什么含义有什么作用pctused与pctfree控制数据块是否出现在freelist中,pctfree控制数据块中保留用于update的空间,当数据块中的free space小于pctfree设置的空间时,该数据块从freelist中去掉,当块由于dml操作free space大于pct_used设置的空间时,该数据库块将被添加在freelist链表中。

2:简单描述table / segment / extent / block之间的关系table创建时,默认创建了一个data segment,每个data segment含有min extents指定的extents数,每个extent据据表空间的存储参数分配一定数量的blocks3:描述tablespace和datafile之间的关系一个tablespace可以有一个或多个datafile,每个datafile只能在一个tablespace内,table中的数据,通过hash算法分布在tablespace中的各个datafile中,tablespace是逻辑上的概念,datafile则在物理上储存了数据库的种种对象。

4:本地管理表空间和字典管理表空间的特点，ASSM有什么特点本地管理表空间(Locally Managed Tablespace简称LMT)8i以后出现的一种新的表空间的管理模式，通过位图来管理表空间的空间使用。

字典管理表空间(Dictionary-Managed Tablespace简称DMT)8i以前包括以后都还可以使用的一种表空间管理模式，通过数据字典管理表空间的空间使用。

动段空间管理(ASSM)，它首次出现在Oracle920里有了ASSM，链接列表freelist被位图所取代，它是一个二进制的数组，能够迅速有效地管理存储扩展和剩余区块(free block)，因此能够改善分段存储本质，ASSM表空间上创建的段还有另外一个称呼叫Bitmap Managed Segments(BMB 段)。

Oracle笔试题库附参考答案1．下列不属于ORACLE的逻辑结构的是（C）1. 区2. 段3. 数据⽂件4. 表空间2. 下⾯哪个⽤户不是ORACLE缺省安装后就存在的⽤户(A)A . SYSDBAB. SYSTEMC. SCOTTD. SYS3 下⾯哪个操作会导致⽤户连接到ORACLE数据库，但不能创建表(A)1. 授予了CONNECT的⾓⾊，但没有授予RESOURCE的⾓⾊2. 没有授予⽤户系统管理员的⾓⾊3. 数据库实例没有启动4. 数据库监听没有启动1. ( )函数通常⽤来计算累计排名，移动平均数和报表聚合。

A . 汇总B. 分析C 分组、D 单⾏1. 带有（B）字句的SELECT语句可以在表的⼀⾏或多⾏放置排他锁。

A . FOR INSERTB. FOR UPDATEC. FOR DELETED. FOR REFRESH1. 在Oracle中，你以SYSDBA登录，CUSTOMER表位于Mary⽤户⽅案中，下⾯哪条语句为数据库中的所有⽤户创建CUSTOMER表的同义词（B）。

1. CREATE PUBLIC SYNONYM cust ON mary.customer;2. CREATE PUBLIC SYNONYM cust FOR mary.customer;3. CREATE SYNONYM cust ON mary.customer FOR PUBLIC;4. 不能创建CUSTOMER的公⽤同义词。

5.7. 在Oracle中，当FETCH语句从游标获得数据时，下⾯叙述正确的是（C）。

1. 游标打开2. 游标关闭3. 当前记录的数据加载到变量中4. 创建变量保存当前记录的数据8. 在Oracle中，下⾯关于函数描述正确的是（AD）。

1. SYSDATE函数返回Oracle服务器的⽇期和时间2. ROUND数字函数按四舍五⼊原则返回指定⼗进制数最靠近的整数3. ADD_MONTHS⽇期函数返回指定两个⽉份天数的和4. SUBSTR函数从字符串指定的位置返回指定长度的⼦串9. 阅读下⾯的PL/SQL程序块：BEGININSERT INTO employee(salary,last_name,first_name)VALUES(35000,’Wang’,'Fred’);SAVEPOINT save_a;INSERT INTO employee(salary,last_name,first_name)VALUES(40000,’Woo’,'David’);SAVEPOINT save_b;DELETE FROM employee WHERE dept_no=10;SAVEPOINT save_c;INSERT INTO employee(salary,last_name,first_name)VALUES(25000,’Lee’,'Bert’);ROLLBACK TO SAVEPOINT save_c;VALUES(32000,’Chung’,'Mike’);ROLLBACK TO SAVEPOINT save_b;COMMIT;END;运⾏上⾯的程序，哪两个更改永久保存到数据库（CD）。