阿里巴巴Oracle-DBA笔试题及答案

合集下载

OracleDBA数据库结构试题及答案

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。

OracleDBA数据库结构试题选1_Oracle认证_

Oracle DBA 数据库结构试题选1注：答案在选项后面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 above2[NextPage]Q. 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笔试题目带答案

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数据类型。

Oracle 笔试题目带答案

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

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

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

Oracle数据库笔试面试试题及答案

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笔试题库附参考答案

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）。

oracle考试试题及答案.doc

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笔试题及答案

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 DBA笔试题及参考答案

阿里巴巴的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 DBA笔试题参考答案

5:如何跟踪某个session的SQL
程序代码
exec dbms_system.set_sql_trace_in_session(sid,serial#,&sql_trace);
select sid,serial# from v$session where sid = (select sid from v$mystat where rownum = 1);
explain plan set statement_id = &item_id for &sql;
select * from table(dbms_xplan.display);
/ ... /b10752/ex_plan.htm
4:本地管理表空间和字典管理表空间的特点，ASSM有什么特点
5:回滚段的作用是什么
6:日志的作用是什么
7:SGA主要有那些部分，主要作用是什么
8:Oracle系统进程主要有哪些，作用是什么
三:备份恢复类
1:备份如何分类
2:归档是什么含义
该数据块从freelist中去掉,当块由于dml操作free space大于pct_used设置的空间时,该数据库块将
被添加在freelist链表中。
2:简单描述table / segment / extent / block之间的关系
table创建时,默认创建了一个data segment,
参考答案：
一：SQL tuning 类
1:列举几种表连接方式
程序代码
hash join/merge join/nest loop(cluster join)/index join

oracleDBA笔试经典试题

普通类一、选择题（单选题）1、在下列哪一种情况下用户不能被删除( )。

A. 不拥有任何模式对象的用户B. 当前正处于连接状态的用户C. 拥有只读表的用户D. 所有的用户都可以任何时刻删除2、分析以下的SQL 命令:SELECT manufacturer_idFROM inventoryWHERE manufacturer_id LIKE '%N\%P\%O%' ESCAPE '\';命令执行的返回结果是（）A、所有包含'NPO'的纪录B 、所有包含'N\P\O'的纪录C 、所有包含'N%P%O'的纪录D 、所有包含'%N\P\O%'的纪录3、游标的哪一种属性指示fetch语句是否从活动集中返回行，如未能返回行，则此属性的值为true ？A、%FOUNDB、%NOTFOUNDC、%ROWCOUNTD、%ISOPEN4、下列哪个集合操作符返回两个查询所选择的所有不同的行。

A、UnionB、Union allC、Union onlyD、Intersect5、如何删除sales表中产品类型为toys的profits列的列值?A、UPDATE sales SET profits = NULL WHERE product_type = 'TOYS'B、DELETE profits FROM sales WHERE product_type = 'TOYS'C、DELETE FROM sales WHERE product_type = 'TOYS'D、DELETE FROM sales6、参数maxtrans指定每个( D )上允许的最大并发的事务数。

A、tableB、segmentC、extent D. block7、在Oracle PL/SQL中，执行动态SQL的语句是A、NXECUTEB、STARTC、RUND、EXECUTE IMMEDIATE8、下面哪一个SQL语句将USER表的名称更改为USERINFO ？A、ALTER TABLE USER RENAME AS USERINFO;B、RENAME TO USERINFO FROM USER;C、RENAME USER TO USERINFO;D、RENAME USER AS USERINFO;9、在Oracle中，下面哪条语句当COMM字段为空时显示0，不为空时显示COMM的值A、select ename, nvl(comm.,0) from emp;B、 select ename, null(comm.,0) from emp;C、SELECT ename,NULLIF(comm,0)FROM emp;D、SELECT ename,DECODE(comm.,NULL,0)FROM emp;10、如果服务器进程无法在数据缓冲区中找到空闲块以添加从数据文件中读出的数据块则系统会启动如下哪一个进程 _________A、CKPTB、SMONC、LGWRD、DBWR11、在默认情况下检查点的发生频率至少与下列哪个事件的发生次数一致A、重做日志切换B、执行UPDATE 语句C、执行INSERTD、SMON 合并表空间中的碎片二、选择题（多选题）1、Delete和 truncate 都可以用来删除表内容，以下描述正确的是？A、truncate 不需要 rollbacksegmentB、delete 需要rollbacksegmentC、truncate在提交commit之前仍可回滚D、truncate还可以删除表结构2、Student表的表结构sid_id numberstart_date dateend_date date在start_date列上使用哪些函数是正确的？A、sum(start_date)B、count(start_date)C、avg(start_date,end_date)D、min(start_date)3、下面对视图的作用描述正确的是：A、视图可以加速数据访问B、视图可以屏蔽掉对部分原始数据的访问C、视图可以降低查询复杂度D、视图可以代替原始数据表4、检查下列EMPLOYEES表中的数据。

(完整word版)oracle考试试题及答案,推荐文档

试题一一、填空题(每小题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元以下的雇员名单。

Oracle笔试题-参考答案

f（考试时间：180分钟试卷满分：300分）注意事项：1.答卷前，请关闭手机，禁止使用设备对试卷进行拍照。

2.请务必将个人相关信息填写在相应位置。

3.请将答案填写在相应位置，否则无效。

第一部分单选题（每题2分，满分80分）做题时，先将答案标在试卷上，录音结束后，你将有两分钟的时间将试卷上的答案转涂到答题卡上。

一、单选题（每题2分 * 40 = 80分）1. 使用Oracle数据库，必须启动哪个服务（）A．OracleHOME_NAMETNSListener B．OracleServiceSIDC．OracleMTSRecoveryService D．OracleJobSchedulerSID2. 在Windows操作系统中，Oracle的（）服务监听并接受来自客户端应用程序的连接请求。

A．OracleHOME_NAMETNSListener B．OracleServiceSIDC．OracleHOME_NAMEAgent D．OracleHOME_NAMEHTTPServer3. 用二维表结构表达实体集的模型是（）A．概念模型 B．层次模型 C．网状模型D．关系模型4. Oracle 11g 是基于（）的A.关系型B.文件系统C.层次型D.网络型5. ( )代表了数据库中最小粒度的逻辑数据存储层次。

A.盘区B.表空间C.数据块D.数据文件6. 你使用下列命令连接数据库：sqlplusscott/***************:1522/orcl.目标数据库是哪一个？（）A. B.tigerC. orclD. scott7. 设有一个关系：DEPT（DNO，DNAME,LOC），如果要找出倒数第三个字母为W，并且至少包含4个字母的DNAME，则查询条件子句应写成WHERE DNAME LIKE （）A.‘_ _ W _ %’B.‘_ % W _ _’C. ‘_ W _ _’D. ‘_ W _ %’8. 下列解锁scott账户的命令正确的是（）A、update user scottaccount unlock;B、alter user scott account unlock;C、alter user scott unlock;9. having , where , group by 的正确执行顺序是（）A、having,where,group byB、group by,having,whereC、where,having,group byD、where ,group by,having10. 分析下面两个SQL语句，选项中说法正确的有( )SELECT last_name, salary , hire_dateFROM EMPLOYEESORDER BY salary DESC;SELECT last_name, salary , hire_dateFROM EMPLOYEESORDER BY 2 DESC;A、两个SQL语句的结果完全相同B、第二个SQL语句产生语法错误C、没有必要指定排序方式为desc，因为默认的排序方式是降序排序D、可以通过为第二个SQL语句的salary列添加列别名来使两个SQL语句得到相同的结果注意：Order by group by 后跟数字，代表select后的字段的位置11. 数据库设计中用关系模型表示实体和实体之间的联系。

一道Oracle笔试题附网友答案

一道Oracle笔试题附网友答案问题：一道Oracle笔试题附网友答案回答：考试总分为100分，共8题，时间为1小时。

表结构说明：create table employee(id number(10) not null, 员工工号salary number(10,2) default 0 not null, 薪水name varchar2(24) not null 姓名);1.创建序列seq_employee，该序列每次取的时候它会自动增加，从1开始计数，不设最大值，并且一直累加，不循环。

（10分）2.写一个PL/SQL块，插入表user.employee中100条数据。

插入该表中字段id用序列seq_employee实现，薪水和姓名字段可以任意填写。

（15分）6.写一个匿名语句块，用于执行函数f_employee，并打印执行该函数的结果。

（8分）7.创建存储过程p_create_emp，用于判断表employee是否存在，如果存在则删除该表。

（15分）8.写一个匿名语句块，用于执行存储过程p_create_emp。

（7分）答案如下：SQL> create table employee(2 id number(10) not null, 员工工号3 salary number(10,2) default 0 not null, 薪水4 name varchar2(24) not null 姓名5 );表已创建。

第一题答案：SQL> Create sequence seq_employee increment by 1 start with 1 nomaxvalue nocycle;序列已创建。

第二题答案：SQL> declare i number;2 begin3 for i in 1 .. 1004 loop5 insert into employee6 values(seq_employee.nextval,1950+i, 王明||to_char(i));7 commit;8 end loop;9 end;10 /PL/SQL 过程已成功完成。

Oracle数据库笔试面试试题及答案

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

阿里巴巴Oracle-DBA笔试题及答案

OracleDBA数据库结构试题及答案

OracleDBA数据库结构试题选1_Oracle认证_

Oracle笔试题目带答案

Oracle 笔试题目带答案

Oracle数据库笔试面试试题及答案

最新Oracle笔试题及答案

Oracle笔试题库附参考答案

oracle考试试题及答案.doc

oracle笔试题及答案

阿里巴巴校园招聘Oracle DBA笔试题及参考答案

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

oracleDBA笔试经典试题

(完整word版)oracle考试试题及答案,推荐文档

Oracle笔试题-参考答案

一道Oracle笔试题 附网友答案

Oracle数据库笔试面试试题及答案

一道Oracle笔试题附网友答案