ORACLE11g试题答案
Oracle-11g期末考试复习题
一、选择题1、对于数据库软件的应用，主要分为哪两大块（）。
〖2个答案〗A.开发B.管理C.安装D.调试2、以下哪项不是数据库物理组件的类型（D）。
〖1个答案〗A. 数据文件B. 控制文件C. 日志文件D. 表空间3、以下哪项不是数据库的逻辑组件的类型（D）。
〖1个答案〗A. 表空间B. 段C. 扩展区D. 日志文件4、关于数据控制语言中，收回所授予的权限的语句是（B）。
〖1个答案〗A. B. C. D.5、数据库中，取余数的运算符是（B）。
〖1个答案〗A. %B.C. /D.6、数据库中，连接字符串的运算符是（ D）。
〖1个答案〗A. +B. &C.D.7、数据库中返回字符串长度的函数是（D）。
〖1个答案〗A. B. C. D.8、数据库中按指定的精度进行四舍五入的函数是（C）。
〖1个答案〗A. B. C. D.9、转换日期类型为字符串的转换函数是（C）。
〖1个答案〗A. B. C. D.10、在数据库中，对象表示（D）。
〖1个答案〗A.同义词B.表C.包D.索引11、关于通配符中的“%”，以下说法正确的两项是（）。
〖2个答案〗A. 代表任意一个字符，与结合使用B. 代表任意多个字符，与结合使用C. 代表任意一个字符，在后的表达式中只能使用一次“%”D. 代表任意多个字符，在后的表达式中可以使用多次“%”12、要统计某表中记录的总个数，以下哪项是正确的语句（C）。
〖1个答案〗A. (*) 表名B. (*) 表名C. (*) 表名D. (*) 表名13、要求表中数据的最大值，应使用什么函数（A）。
〖1个答案〗A. B. C. D.14、下列正确查询姓张的学生的语句是（B）。
〖1个答案〗A. * 表名姓名 = ‘张’B. * 表名姓名‘张%’C. * 表名姓名 = ‘%张%’D. * 表名姓名‘张’15、关于表的主键，说法正确的两项是（）。
〖2个答案〗A. 主键字段的值最多允许有一条记录为B. 主键字段的值可以重复C. 主键字段的值不能为D. 主键字段的值不能重复16、创建序列，使用（A）。
oracle11g管理与应用实践(习题及答案)(2)课案
1.6习题Oracle数据库基础一.填空题1、关系模型提供了3类完整性规则，分别是_______、_______、_______。
参考答案：实体完整性规则、参照完整性规则、用户定义的完整性规则2、RDBMS由两部分组成，即_______、_______两部分。
答案：数据库系统内核、数据字典3、关系数据库模型支持三种类型的表关联关系：_______、_______、_______。
答案：一对一、一对多以及多对多4、数据模型的种类有很多，例如_______、_______、_______和_______等。
目前理论最成熟、使用最普及的是_______。
答案：层次模型、网状模型、关系数据模型、面向对象模型。
关系数据模型二、选择题1、Oracle 11g版本号中字母“g”的含义是（）A．产品类型的“代”( generation) B．网格(gridding)C．集成（integration）D．无含义答案：B2、设计性能较优的关系模式称为规范化，规范化主要的理论依据是（）A．关系规范化理论B．关系运算理论C．关系代数理论D．数理逻辑答案：A3、消除了部分函数依赖的1NF的关系模式必定是（）A．1NF B．2NF C．3NF D．4NF答案：B4、当B属于函数依赖于A属性时，属性B与A 的联系是（）A．1对多B．多对1 C．多对多D．以上都不是答案：B5、根据关系数据库规范化理论，关系数据库中的关系要满足第一范式。
下面“部门”关系中，因哪个属性而使它不满足第一范式？部门（部门号，部门名，部门成员，部门总经理）A．部门总经理B．部门成员C．部门名D．部门号答案：B6、下列不属于Oracle数据库数据类型的是（）A．NUMBER B．FLOAT C．CLOB D．BOOLEAN 答案：D三、简答题1、简述数据库与数据库管理系统的区别。
参考答案：数据库是用来存储信息或数据的机制，是按照数据结构来组织、存储和管理数据的仓库。
ORCALE11G 期末试题及答案B卷
学年第 一 学期期末考试试题（卷）
专业： 班级： 姓名： 学号：
装 订 线 装 订 线 以 内 不 准 作 任 何 标 记 装 订 线
学院
课程考试参考答案与评分标准
2013 /2014 学年第一学期
课程名称：ORCALE 考试性质：考查试卷类型：B
考试班级：计科考试方法：命题教师：
一、选择题（每小题2分，共30分）
Ccdab CBBDC CCBDC
二、判断（每题2分，共20分）
YNNYY,NNNNY
三：简答题（每小题5分，共10分）
1、创建并启动与数据库对应的实例
为实例加载数据库
将数据库设置为打开状态
2、数值、字符、日期、LOB 、ROWID类型
四、读程题（每小题10分，共20分）
1、hello world
2、15,12,9,6,3
五、编程题（每小题10分，共20分）
1. Begin update emp
Set sal=sal*1.5;
Where empno=’7369’;
If sql%notfound then
Dbms_output.putline(”未更新任何记录”);
Else
Dbms_output.putline(”更新’|| sql%rowcount || ‘条记录”);
End if；
End；
2、
Create or replace trigger tg_emp
Before insert or update or delete on emp
Insert into emp_log（who ，when）values （user ，sysdate）；End；
/。
oracle 11g(钱慎一)课后习题答案
1.名词解释：
数据块：是数据块使用的I/O最小单元，也是最基础的存储单位，又称逻辑块或oracle块。数据块包括块头和存储区。
区：是数据存储空间分配的逻辑单元，，在一个段中可以存在多个区间，区间是数据一次性预留的一个较大的存储空间。
段：是对象在数据库中占用的空间。段和数据库对象是一一对应的，但段是从数据库存储的角度来看。一个段只能属于一个表空间，一个表空间可以有多个段。
create or replace procedure salary_add(jobid in employees.JOB_ID%type,adds in employees.SALARY%type)
2 as
3 begin
4 update employees set SALARY=SALARY+adds where JOB_ID=jobid;
A.对服务器进行正确的网络配置，并且记录IP地址、域名的网络配置信息，如果采用动态IP，需先将Microsoft LoopBack Adapter配置为系统的主网络适配
B.卸载其他的数据库管理系统
C.如果服务器上运行有其他Oracle服务，必须在安装前将他们全部停止
D.关闭Windows防火墙和某些杀毒软件
（6）a.查看COUNTRIES表的结构：DESC COUNTRIES;
b.查看COUNTRIES表的部分数据：SELECT * FROM COUNTRIES;
（7）a.查看JOB_JISTORY表的结构：DESC JOB_JISTORY;
b.查看JOB_JISTORY表的部分数据：SELECT * FROM JOB_JISTORY;
2、包有两个独立的部分：说明部分和包体部分。
3、触发器的类型包括DML触发器、INSTEAD OF触发器和系统触发器。
oracle11g数据库管理与开发第7章答案.
第7章安全管理一、填空题1.Oracle数据库用户口令认证可以采用数据库验证、外部验证、全局验证等几种方式。
2.Oracle数据库概要文件主要用于资源管理、控制口令等。
3.Oracle数据库中的权限分为系统权限和对象权限两种类型，向用户直接授权需要grant权限to用户SQL语句。
4.用户连接Oracle数据库后希望得到角色权限，这有两种实现方法：一种方法是让管理员把角色设置为用户默认角色，另一种方法是向用户授予角色，需调用的SQL语句是grant 角色to用户。
二、简答题1.简要说明在oracle数据库内普通用户口令认证和管理员口令认证都有哪些方法?答:oracle数据库普通用户口令认证有以下3种认证方法：（1）数据库认证（2）外部认证（3）全局认证管理员口令认证有以下3种认证方法：（1）口令文件认证（2）操作系统认证（3）基于网络认证服务认证2.简述用户通过默认角色和非默认角色获得权限有何异同？答：用户通过默认角色获得权限时，用户默认角色在用户连接后被自动激活，所以用户不用显式启用角色就可以立即获得它们所具有的权限。
而通过非默认角色获得权限时，必须通过命令调用为其授权，才能使非默认角色获权。
三、实训题1.请创建一个用户books_pub，要求他第一次登录时必须修改口令，将其默认表空间和默认临时表空间分别设置为books_pub和temp,并在表空间users,demots和books_pub上分别为他分配10MB,10MB和50MB的存储空间。
create tablespace books_pub//创建books_pub表datafile'f:\app\administrator\admin\orcl\hcy_1.dbf'size5M;create tablespace demots//创建demots表datafile'f:\app\administrator\admin\orcl\hcy_2.dbf'size5M;create user books_pub//创建books_pub用户identified by123password expiredefault tablespace books_pubtemporary tablespace tempquota10M on usersquota10M on demotsquota50M on books_pub;2.把创建会话的系统权限，以及scott用户dept表和emp表上的所有对象授予用户books_pub。
Oracle OCP11g考试题库
Which statement is true regarding the COALESCE function?A. It can have a maximum of five expressions in a listB. It returns the highest NOT NULL value in the list for all rowsC. It requires that all expressions in the list must be of the same data typeD. It requires that at least one of the expressions in the list must have a NOT NULL valueAnswer: C2.View the Exhibit and examine the structure of the PROMOTIONS table.Which SQL statements are valid? (Choose all that apply.)A. SELECT promo_id, DECODE (NVL(promo_cost,0), promo_cost,promo_cost * 0.25, 100) "Discount"FROM promotions;B. SELECT promo_id, DECODE (promo_cost, 10000,DECODE (promo_category, 'G1', promo_cost *.25, NULL), NULL) "Catcost"FROM promotions;C SELECT promo_id, DECODE(NULLIF(promo_cost, 10000), NULL,promo_cost*.25, 'N/A') "Catcost"FROM promotions;D. SELECTpromo_id,DECODE(promo_cost, >10000, 'High',<10000, 'Low')"Range"FROM promotions;Answer: A, BView the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. There is only one customer with the cust _last_name column having value Roberts. Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST LAST NAME is Roberts and CREDIT LIMIT is 600?A. INSERT INTO orders V ALUES (1, '10-mar-2007', 'direct',(SELECT customer_idFROM customersWHERE cust last name= 'Roberts' ANDcredit_limit=600), 1000);B. INSERT INTO orders (order_id,order_date,order_mode,(SELECT customer_idFROM customersWHERE cust last name= 'Roberts' ANDcredit_limit=600),order_total)V ALUES(1, '10-mar-2007', 'direct', &&customer_id, 1000);C. INSERT INTO(SELECT o.order_id, o.order_date,o.order_mode,c.customer_id,o.order_totalFROM orders o, customers cWHERE o.customer_id=c.customer_idAND c.cust_last_name='Roberts' ANDc.credit_limit=600 |V ALUES (1,'10-mar-2007', 'direct',(SELECT customer_idFROM customersWHERE cust_last_name= 'Roberts' AND credit_limit=600 ), 1000);D. INSERT INTO orders (order_id,order_date,order_mode,(SELECT customer_idFROM customersWHERE cust_last_name= 'Roberts' ANDcredit_limit=600),order_total)V ALUES(1,'10-mar-2007', 'direct', &customer_id, 1000);Answer: A4.View the Exhibit and examine the structure of the CUSTOMERS table.Evaluate the following SQL statementSQL> SELECT cust_city, COUNT(cust_last_name)FROM customersWHERE cust_credit_limit > 1000GROUP BY cust_cityHA VING A VG(cust_credit_limit) BETWEEN 5000 AND 6000;Which statement is true regarding the outcome of the above query?A. It executes successfullyB. It returns an error because the BETWEEN operator cannot be used in the HA VING clauseC. It returns an error because WHERE and HA VING clauses cannot be used in the same SELECT statementD. It returns an error because WHERE and HA VING clauses cannot be used to apply conditions on the same columnAnswer: AView the Exhibit and examine the structure of the PROMOTIONS table. Examine the following two SQL statements:Which statement is true regarding the above two SQL statements?A. statement 1 gives an error, statement 2 executes successfullyB. statement 2 gives an error, statement 1 executes successfullyC. statement 1 and statement 2 execute successfully and give the same outputD. statement 1 and statement 2 execute successfully and give a different output Answer: DYou created an ORDERS table with the following description:You inserted some rows in the table. After some time, you want to alter the table by creating the PRIMARY KEY constraint on the ORD_ID column. Which statement is true in this scenario?A. You cannot have two constraints on one columnB. You cannot add a primary key constraint if data exists in the columnC. The primary key constraint can be created only at the time of table creationD. You can add the primary key constraint even if data exists, provided that there are no duplicate valuesAnswer: D7.When does a transaction complete? (Choose all that apply.)A. when a DELETE statement is executedB. when a ROLLBACK command is executedC. when a PL/SQL anonymous block is executedD. when a data definition language (DDL) statement is executedE. when a TRUNCATE statement is executed after the pending transact ionAnswer: B, D, E8.You need to display the first names of all customers from the CUSTOMERS table that contain the character 'e' and have the character 'a' in the second last positionWhich query would give the required output?A. SELECT cust_first_nameFROM customersWHERE INSTR(cust_first_name, 'e') <>0 ANDSUBSTR(cust_first_name, -2, l) ='a';B. SELECT cust first nameFROM customersWHERE INSTR(cust_first_name, 'e') <>" ANDSUBSTR(cust_first_name, -2, l)='a';C. SELECT cust_first_nameFROM customersWHERE INSTR(cust_first_name, 'e') IS NOT NULL ANDSUBSTR(cust_first_name, l, -2)='a';D. SELECT cust_first_nameFROM customersWHERE INSTR(cust_first_name, 'e')<>0 ANDSUBSTR(cust_first_name, LENGTH(cust_first_name),-2)='a';Answer: A9.The ORDERS table belongs to the user OE. OE has granted the SELECT privilege on the ORDERS table to the user HR.Which statement would create a synonym ORD so that HR can execute the following query successfully?SELECT * FROM ord;A. CREATE SYNONYM ord FOR orders; This command is issued by OEB. CREATE PUBLIC SYNONYM ord FOR orders; This command is issued by OEC. CREATE SYNONYM ord FOR oe.orders; This command is issued by the database administratorD. CREATE PUBLIC SYNONYM ord FOR oe.orders; This command is issued by the database administratorAnswer: D10.View the Exhibit and examine the structure of the PRODUCTS tableYou need to generate a report in the following format:Which two queries would give the required output? (Choose two.)A. SELECT prod_name || q"'s category is ' || prod_category CATEGORIESFROM products;B. SELECT prod_name || q'['s]'category is' || prod_category CATEGORIESFROM products;C. SELECT prod_name || q'\'s\' category is '|| prod_category CATEGORIESFROM products;D. SELECT prod_name ||q'<'s>'|| 'category is ' || prod_category CATEGORIESFROM products;Answer: C, D11.Which statement is true regarding the INTERSECT operator?A. It ignores NULL valuesB. Reversing the order of the intersected tables alters the resultC. The names of columns in all SELECT statements must be identicalD. The number of columns and data types must be identical for all SELECT statements in the queryAnswer: D12.Which two statements are true regarding the USING and ON clauses in table joins? (Choose two.)A. Both USING and ON clauses can be used for equijoins and nonequijoinsB. A maximum of one pair of columns can be joined between two tables using the ON clauseC. The ON clause can be used to join tables on columns that have different names but compatible data typesD. The WHERE clause can be used to apply additional conditions in SELECT statements containing the ON or the USING clauseAnswer: C, D13.Examine the structure of the PROGRAMS tableWhich two SQL statements would execute successfully? (Choose two.)A. SELECT NVL(ADD_MONTHS(END_DATE,l),SYSDATE)FROM programs;B. SELECT TO_DATE(NVL(SYSDATE-END_DATE,SYSDATE))FROM programs;C. SELECT NVL(MONTHS_BETWEEN(start_date,end_date),'Ongoing')FROM programs;D. SELECTNVL(TO_CHAR(MONTHS_BETWEEN(start_date,end_date)),'Ongoing') FROM programs;Answer: A, D14.Where can subqueries be used? (Choose all that apply.)A. field names in the SELECT statementB. the FROM clause in the SELECT statementC. the HA VING clause in the SELECT statementD. the GROUP BY clause in the SELECT statementE. the WHERE clause in only the SELECT statementF. the WHERE clause in SELECT as well as all DML statementsAnswer: A, B, C, F15.View the Exhibits and examine the structures of the PRODUCTS, SAL ES, and CUSTOMERS tablesYou need to generate a report that gives details of the customer's last name, name of the product, and the quantity sold for all customers in 'Tokyo'Which two queries give the required result? (Choose two.)A. SELECT c.cust_last_name,p.prod_name,s.quantity_soldFROM sales s JOIN products pUSING(prod_id)JOIN customers cB. SELECT c.cust_last_name, p.prod_name, s.quantity_soldFROM products p JOIN sales s JOIN customers cON(p.prod_id=s.prod_id)ON(s.cust_id=c.cust_id)WHERE c.cust_city='Tokyo';C. SELECT c.cust_last_name, p.prod_name, s.quantity_soldFROM products p JOIN sales sON(p.prod_id=s.prod_id)JOIN customers cON(s.cust_id=c.cust_id)AND c.cust_city='Tokyo';D. SELECT c.cust_id, c.cust_last_name, p.prod_id, p.prod_name, s.quantity_soldFROM products p JOIN sales sUSING(prod_id)JOIN customers cUSING(cust_id)WHERE c.cust_city='Tokyo';Answer: A, C16.View the Exhibit and evaluate the structure and data in the CUST_STATUS table You issue the following SQL statementSQL> SELECT custno, NVL2(NULLIF(amt_spent,credit_limit), 0, 1000) "BONUS"FROM cust_status;Which statement is true regarding the execution of the above query?A. It produces an error because the AMT_SPENT column contains a null valueB. It displays a bonus of 1000forall customers whose AMT_SPENT is less than CREDIT_LIMITC. It displays a bonus of 1000 for all customers whose AMT_SPENT equals CREDIT_LIMIT, or AMT_SPENT is nullD. It produces an error because the TO_NUMBER function must be used to convert the result of the NULLIF function before it can be used by the NVL2 functionAnswer: C17.Examine the structure and data in the PRICE LIST tableYou plan to give a discount of 25% on the product price and need to display the discount amount in the same format as the PROD_PRICE.Which SQL statement would give the required result?A. SELECT TO_CHAR(prod_price* .25, '$99,999.99')FROM PRICE_LIST;B. SELECT TO_CHAR(TO_NUMBER(prod_price)* .25, '$99,999.00')FROM PRICE_LIST;C. SELECT TO_CHAR(TO_NUMBER(prod_price, '$99,999.99')* .25,'$99,999.00')FROM PRICE_LIST;D. SELECT TO_NUMBER(TO_NUMBER(prod_price,$99,999.99')* .25,'$99,999.00')FROM PRICE_LIST;Answer: C18.You need to generate a list of all customer last names with their credit limits from the CUSTOMERS table. Those customers who do not have a credit limit should appear last in the list.Which two queries would achieve the required result? (Choose two.)A. SELECT cust_last_name, cust_credit_limitFROM customersORDER BY cust_credit_limit DESC;B. SELECT cust_last_name, cust_credit_limitFROM customersORDER BY cust_credit_limit;C. SELECT cust_last_name, cust_credit_limitFROM customersORDER BY cust_credit_limit NULLS LAST;D. SELECT cust_last_name, cust_credit_limitFROM customersORDER BY cust_last_name, cust_credit_limit NULLSLAST;Answer: B, C19.Which two statements are true regarding the COUNT function? (Choose two.)A. The COUNT function can be used only for CHAR, V ARCHAR2, and NUMBER data typesB. COUNT (*) returns the number of rows including duplicate rows and rows containing NULL value in any of the columnsC. COUNT (cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID columnD. COUNT(DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT columnE. A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clauseAnswer: B, D20.Which two statements are true regarding single row functions? (Choose two.)A. They accept only a single argumentB. They can be nested only to two levelsC. Arguments can only be column values or constantsD. They always return a single result row for every row of a queried tableE. They can return a data type value different from the one that is referencedAnswer: D, E21.View the Exhibit and examine the data in the COSTS table.You need to generate a report that displays the IDs of all products in the COSTS table whose unit price is at least 25% more than the unit cost. The details should be displayed in the descending order of 25% of the unit cost.You issue the following query:SQL>SELECT prod_idFROM costsWHERE unit_price >= unit_cost * 1.25ORDER BY unit_cost * 0.25 DESC;Which statement is true regarding the above query?A. It executes and produces the required resultB. It produces an error because an expression cannot be used in the ORDER By clauseC. It produces an error because the DESC option cannot be used with an expression in the ORDER BY clauseD. It produces an error because the expression in the ORDER BY clause should also be specified in the SELECT clauseAnswer: A22.View the Exhibit and examine the structure of the CUSTOMERS tableWhich statement would display the highest credit limit available in each income level in each city in the CUSTOMERS table?A. SELECT cust_city, cust_income_level, MAX(cust_credit_limit)FROM customersGROUP BY cust_city, cust_income_level, cust_credit_limit;B. SELECT cust_city, cust_income_level, MAX(cust_credit_limit)FROM customersGROUP BY cust_city, cust_income_level;C. SELECT cust_city, cust_income_level, MAX(cust_credit_limit)FROM customersGROUP BY cust_credit_limit, cust_income_level, cust_city;D. SELECT cust_city, cust_income_level, MAX(cust_credit_limit)FROM customersGROUP BY cust_city, cust_income_level, MAX(cust_credit_limit);Answer: B23.Which two statements are true regarding subqueries? (Choose two.)A. A subquery can retrieve zero or more rowsB. Only two subqueries can be placed atone levelC. A subquery can be used only in SQL query statementsD. A subquery can appear on either side of a comparison operatorE. There is no limit on the number of subquery levels in the WHERE clause of a SELECT statementAnswer: A, D24.View the Exhibit and examine the structure of the PROMOTIONS table.Evaluate the following SQL statement:The above query generates an error on execution.Which clause in the above SQL statement causes the error?A. WHEREB. SELECTC. GROUP BYD. ORDER BYAnswer: C25.You need to create a table for a banking application One of the columns in the table has the following requirements:1) You want a column in the table to store the duration of the credit period2) The data in the column should be stored in a format such that it can be easily addedand subtracted with DATE data type without using conversion functions3) The maximum period of the credit provision in the application is 30days4) The interest has to be calculated for the number of days an individual has taken a credit for.Which data type would you use for such a column in the table?A. DATEB. NUMBERC. TIMESTAMPD. INTERV AL DAY TO SECONDE. INTERV AL YEAR TO MONTHAnswer: D26.View the Exhibit to examine the description for the SALES tableWhich views can have all DML operations performed on it? (Choose all that apply.)A. CREATE VIEW v3AS SELECT * FROM SALESWHERE cust_id = 2034WITH CHECK OPTION;B. CREATE VIEW vlAS SELECT * FROM SALESWHERE time_id <= SYSDATE - 2*365WITH CHECK OPTION;C. CREATE VIEW v2AS SELECT prod_id, cust_id, time_id FROM SALESWHERE time id <= SYSDATE -2*365WITH CHECK OPTION;D. CREATE VIEW v4AS SELECT prod_id, cust_id, SUM(quantity_sold) FROM SALESWHERE time id <= SYSDATE -2*365GROUP BY prod_id, cust_idWITH CHECK OPTION;Answer: A, B27.Which is the valid CREA TE TABLE statement?A. CREATE TABLE emp9$# (emp_no NUMBER(4));B. CREATE TABLE 9emp$# (emp_no NUMBER(4));C. CREATE TABLE emp*123 (emp_no NUMBER(4));D. CREATE TABLE emp9$# (emp_no NUMBER (4), date DATE);Answer: A28.View the Exhibit and examine the data in the PRODUCTS table.You need to display product names from the PRODUCTS table that belong to the'Software/Other' category with minimum prices as either $2000 or $4000 and no unit of measure.You issue the following query:SQL>SELECT prod_name, prod_category, prod_min_priceFROM productsWHERE prod_category LIKE '%Other%' AND (prod_min_price = 2000 OR prod_min_price = 4000) AND prod_unit_of_measure <> ";Which statement is true regarding the above query?A. It executes successfully but returns no resultB. It executes successfully and returns the required resultC. It generates an error because the condition specified forPROD_UNIT_OF_MEASURE is not validD. It generates an error because the condition specified for the PROD_CATEGORY Column is not validAnswer: AView the Exhibit to examine the description for the SALES and PRODUCTS tables. You want to create a SALE _PROD view by executing the following SQL statement:Which statement is true regarding the execution of the above statement?A. The view will be created and you can perform DML operations on the viewB. The view will be created but no DML operations will be allowed on the viewC. The view will not be created because the join statements are not allowed for creating a viewD. The view will not be created because the GROUP BY clause is not allowed for creating a viewAnswer: BWhich three statements are true regarding the data types in Oracle Database 10g/11g? (Choose three.)A. Only one LONG column can be used per tableB. A TIMESTAMP data type column stores only time values with fractional secondsC. The BLOB data type column is used to store binary data in an operating system fileD. The minimum column width that can be specified for a V ARCHAR2 data type column is oneE. The value for a CHAR data type column is blank-padded to the maximum defined column widthAnswer: A, D, E31.View the Exhibit and examine the structure of the PRODUCTS table.Evaluate the following query:What would be the outcome of executing the above SQL statement?A. It produces an errorB. It shows the names of all products in the tableC. It shows the names of products whose list price is the second highest in the tableD. It shows the names of all products whose list price is less than the maximum list priceAnswer: CView the Exhibit and examine the structure of ORD and ORD_ITEMS tables.The ORD_NO column is PRIMARY KEY in the ORD table and the ORD NO and ITEM_NO columns are composite PRIMARY KEY in the ORD ITEMS table. Which two CREATE INDEX statements are valid? (Choose two.)A. CREATE INDEX ord_idxlON ord(ord_no);B. CREATE INDEX ord_idx2ON ord_items(ord_no);C. CREATE INDEX ord_idx3ON ord_items(item_no);D. CREATE INDEX ord_idx4ON ord,ord_items(ord_no, ord_date,qty);Answer: B, C33.Which statement is true regarding subqueries?A. The LIKE operator cannot be used with single-row subqueriesB. The NOT IN operator is equivalent to IS NULL with single-row subqueriesC. =ANY and =ALL operators have the same functionality in multiple-row subqueriesD. The NOT operator can be used with IN, ANY, and ALL operators in multiple-row subqueriesAnswer: DYou are currently located in Singapore and have connected to a remote database in Chicago.You issue the following command:SQL> SELECT ROUND (SYSDATE-promo_begin_date, 0)FROM promotionsWHERE (SYSDATE-promo_begin_date)/365 >2;PROMOTIONS is the public synonym for the public database link for the PROMOTIONS table.What is the outcome?A. an error because the ROUND function specified is invalidB. an error because the WHERE condition specified is invalidC. number of days since the promo started based on the current Chicago date and timeD. number of days since the promo started based on the current Singapore date and timeAnswer: C35.View the Exhibit and examine the structure of the PRODUCTS table.Using the PRODUCTS table, you issue the following query to generate the names, current list price, and discounted list price for all those products whose list price falls below $10 after a discount of 25% is applied on it.SQL>SELECT prod_name, prod_list_price,prod_list_price-(prod_list_price*.25) "DISCOUNTED_PRICE"FROM productsWHERE discounted_price < 10;The query generates an error.What is the reason for the error?A. The parenthesis should be added to enclose the entire expressionB. The double quotation marks should be removed from the column aliasC. The column alias should be replaced with the expression in the WH ERE clauseD. The column alias should be put in uppercase and enclosed within double quotation marks in the WHERE clauseAnswer: C36.View the Exhibit for the structure of the STUDENT and FACULTY tables.You need to display the faculty name followed by the number of students handled by the faculty at the base location.Examine the following two SQL statements:Which statement is true regarding the outcome?A. Only statement 1 executes successfully and gives the required resultB. Only statement 2 executes successfully and gives the required resultC. Both statements 1 and 2 execute successfully and give different resultsD. Both statements 1 and 2 execute successfully and give the same required resultAnswer: DView the Exhibit and examine the structure of CUSTOMERS and SALES tables. Evaluate the following SQL statement:Which statement is true regarding the execution of the above UPD ATE statement? A. It would not execute because two tables cannot be used in a single UPDATE statementB. It would not execute because the SELECT statement cannot be used in place of the table nameC. It would execute and restrict modifications to only the columns specified in the SELECT statementD. It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statementAnswer: CEvaluate the following query:SQL> SELECT TRUNC(ROUND(156.00,-1),-1)FROM DUAL;What would be the outcome?A. 16B. 100C. 160D. 200E. 150Answer: C39.View the Exhibits and examine the structures of the PROMOTIONS and SALES tables.Evaluate the following SQL statement:Which statement is true regarding the output of the above query?A. It gives the details of promos for which there have been salesB. It gives the details of promos for which there have been no salesC. It gives details of all promos irrespective of whether they have resulted in a sale or notD. It gives details of product 105 that have been sold irrespective of whether they had a promo or notAnswer: C40.View the Exhibit and examine the description of SALES and PROMOTIONS tables You want to delete rows from the SALES table, where the PROMO_NAME column in the PROMOTIONS table has either blowout sale or everyday low price as values Which DELETE statements are valid? (Choose all that apply.)A. DELETEFROM salesWHERE promo_id = (SELECT promo_idFROM promotionsWHERE promo_name = 'blowout sale' ) AND promo_id = (SELECT promo_idFROM promotionsWHERE promo_name = 'everyday low price' );B. DELETEFROM salesWHERE promo_id = (SELECT promo_idFROM promotionsWHERE promo_name = 'blowout sale' ) OR promo_id = (SELECT promo_idFROM promotionsWHERE promo_name = 'everyday low price' );C. DELETEFROM salesWHERE promo_id IN (SELECT promo_idFROM promotionsWHERE promo_name = 'blowout sale' )OR promo_name = 'everyday low price' );D. DELETEFROM salesWHERE promo_id IN (SELECT promo_idFROM promotionsWHERE promo_name IN ('blowout sale', 'everyday lowprice' ));Answer: B, C, D41.View the Exhibit and examine the structure of the PROMOTIONS table.You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the' INTERNET' category.Which query would give you the required output?A. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date > ALL (SELECT MAX(promo_begin_date)FROM promotions )ANDpromo_category = 'INTERNET');B. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date IN (SELECT promo_begin_date)FROM promotionsWHERE promo_category = 'INTERNET');C. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date > ALL (SELECT promo_begin_dateFROM promotionsWHERE promo_category = 'INTERNET');D. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date > ANY (SELECT promo_begin_dateFROM promotionsWHERE promo_category = 'INTERNET'); Answer: C42.View the Exhibit and examine the data in the CUSTOMERS tableEvaluate the following query:SQL> SELECT cust_name AS "NAME", cust_credit_limit/2 AS MIDPOINT,MIDPOINT+100AS "MAX LOWER LIMIT" FROM customers;The above query produces an error on executionWhat is the reason for the error?A. An alias cannot be used in an expressionB. The alias NAME should not be enclosed within double quotation marksC. The MIDPOINT+100 expression gives an error because CUST_CREDIT _LIMIT contains NULL valuesD. The alias MIDPOINT should be enclosed within double quotation marks for the CUST_CREDIT_LIMIT/2 expressionAnswer: A43.View the Exhibit and examine the structure of the ORD tableEvaluate the following SQL statements that are executed in a user session in the specified order.What would be the outcome of the above statements?A. All the statements would execute successfully and the ORD_NO column would contain the value 2 for the CUST ID 101B. The CREATE SEQUENCE command would not execute because the minimum value and maximum value for the sequence have not been specifiedC. The CREATE SEQUENCE command would not execute because the starting value of the sequence and the increment value have not been specifiedD. All the statements would execute successfully and the ORD_NO column would have the value 20 for the CUST_ID 101 because the default CACHE value is 20Answer: A44.Using the CUSTOMERS table, you need to generate a report that shows 50% of each credit amount in each income level. The report should NOT show any repeated credit amounts in each income level.Which query would give the required result?A. SELECT cust_income_level, DISTINCT cust_credit_limit * 0.50AS "50% Credit Limit"FROM customers;B. SELECT DISTINCT cust_income_level, DISTINCT cust_credit_limit * 0.50AS "50% Credit Limit"FROM customers;C. SELECT DISTINCT cust_income level || ' ' || cust_credit_limit * 0.50AS "50% Credit Limit"FROM customers;D. S ELECT cust income level || ' ' || cust_credit_limit * 0.50 AS "50% Credit Limit"ROM customers;Answer: C45.View the Exhibit and examine the structure of the CUSTOMERS tableEvaluate the query statement:What would be the outcome of the above statement?A. It executes successfullyB. It produces an error because the condition on CUST_LAST NAME is invalidC. It executes successfully only if the CUST_CREDIT_LIMIT column does not contain any null valuesD. It produces an error because the AND operator cannot be used to combine multiple BETWEEN clausesAnswer: A。
oracle11g数据库管理与开发第5章答案.
第5章重做日志管理一、选择题1.Oracle数据库重做日志由（B）后台进程写入联机重做日志文件。
A.DBWRB.LGWRC.ARCnD.SMON2.重做日志缓冲区中的重做日志在（A、B）会被写入重做日志文件。
A.事务提交时B.重做日志缓冲区达到三分之一满，或者日志缓冲区内的日志量超过1MB时C.每3秒过后D.检查点发生时3.改变Oracle数据库归档模式时，需要把数据库启动到（B）状态。
A.NOMOUNTB.MOUNTC.OPEND.CLOSE二、简答题请简述Oracle数据库重做日志从产生到归档的过程。
答：从创建数据库时，重做日志文件产生，用户在执行数据库操作时，服务器进程把重做记录从用户内存空间拷贝到SGA，它们首先被缓存在SGA的重做日志缓冲区内，之后由ORACLE数据库的后台进程写入进程把他们写入联机重做日志文件中，一个数据库至少有两个重做日志文件，一组当前处于写入状态，另一组重做日志用于归档操作。
在oracle数据库运行在归档模式时，发三个部分日志切换后，归档进程（ARCn，n为归档进程号，它可以是0-9，a-t，oracle中可以启动多达30个归档进程）将把填充过的联机重做日志文件复制到指定的一个或者多个位置存储，为他们创建脱机副本，归档完成。
三、实训题1．练习把Oracle数据库从非归档模式修改为归档模式，之后创造条件让数据库立即归档，并检查归档是否成功。
(1)查看数据库的归档模式archive log list;（2）关闭数据库shutdown normal/immedtate;（3）吧数据库重新启动到mount状态startup mount;(4)把数据库修改为自动归档模式alter database archivelog;(5)打开数据库，供用户访问alter database open;(6)查看数据库的归档模式archive log list(7)让数据库立即自动归档archive system switch logfile(8)查看数据库是否归档archive log list；2．查看数据库当前重做日志文件组及成员的设置情况，之后为Oracle数据库添加一组重做日志。
ORCALE11G 期末试题及答案A
学年第 一 学期期末考试试题（卷）
专业： 班级： 姓名： 学号：
装 订 线 装 订 线 以 内 不 准 作 任 何 标 记 装 订 线
学院
课程考试参考答案与评分标准
学年第一学期
课程名称：ORCALE 考试性质：考查试卷类型：A
考试班级：计科考试方法：命题教师：
一、选择（每题2分，共30分）
DBBBA，DCCBD，CBCAB
二、判断（每题2分，共20分）
YNNYY，YYNNY
三：简答题（每题5分，共10分）
1、脏缓存块、空闲缓存块、命中缓存块
2、自动生成数据
强制复杂的完整性约束
自定义复杂的安全权限
提供审计和日志记录
启动复杂的业务逻辑
四、读程题（每题10分，共20分）
1、12,9,6,3
2、1的平方是1；2的平方是4；3的平方是9；4的平方是16；5的平方是25；五：（每题10分，共20分）
1．Create procedure sp_proc is
Begin
Dbms_output.putline(”hello world”);
End;
/
2、Create or replace trigger tg_md before
Update
On emp
For each row
Begin
Update DEPT
Set deptno=:new.deptno where deptno=:old.deptno;
End;。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
. .. .目录第一章Oracle 11g 介绍 (2)第二章ORACLE 11g 的体系结构 (4)第三章ORACLE 11g 的数据库管理 (8)第四章ORACLE 11g 的表空间管理 (10)第五章ORACLE 11g 的表管理 (13)第六章ORACLE 11g 的数据查询 (18)第七章ORACLE 数据的基本操作 (23)第八章索引 (28)第九章视图 (33)第十章PL/SQL基础 (37)第十一章存储过程与函数 (43)第十二章触发器 (48)第十三章游标 (51)第十四章安全管理 (54)第十五章数据库备份与恢复 (57). .资料. ..第一章Oracle 11g 介绍一、选择题1．在数据库系统中，将满足以下两个条件的基本层次联系集合称为层次模型：( B )。
①有一个结点无双亲②其它结点无双亲③有且仅有一个结点无双亲④其它结点有且仅有一个双亲⑤允许其它结点有多个双亲A．①和②B．③和④C．③和⑤D．②和⑤2．下列有关数据库的描述，正确的是( C )A.数据库是一个DBF文件B.数据库是一个关系C.数据库是一个结构化的数据集合D.数据库是一组文件3．根据关系数据基于的数据模型——关系模型的特征判断下列正确的一项：( B )A.只存在一对多的实体关系，以图形方式来表示。
B.以二维表格结构来保存数据，在关系表中不允许有重复行存在。
C.能体现一对多、多对多的关系，但不能体现一对一的关系。
D.关系模型数据库是数据库发展的最初阶段。
4．Oracle 11g 是基于（ A ）的A.关系型B.文件系统C.层次型D.网络型5. 用二维表结构表达实体集的模型是（D ）A．概念模型B．层次模型C．网状模型D．关系模型6．下列四项中说法不正确的是（C ）A.数据库减少了数据冗余B.数据库中的数据可以共享C.数据库避免了一切数据的重复D.数据库具有较高的数据独立性7．下列四项中，不属于关系数据库特点的是（D）A.数据冗余小B.数据独立性高C.数据共享性好D.多用户访问下面系统中不属于关系数据库管理系统的是（C ）A. OracleB. MS SQL ServerC. IMSD. DB29．MS SQL Server是（D ）A. 数据库B. 数据库系统C. 数据处理系统D. 数据库管理系统二、填空题1．在数据库系统中管理数据的软件称为___数据库管理系统________。
2. 数据库中数据模型可分为三种类型：层次模型、网状模型和关系模型。
3．用树型结构表示实体类型及实体间联系的数据模型称为__层次模型__。
4．数据库系统各类用户对数据库的各种操作请求(数据定义、查询、更新及各种控制)都是由2. .. .一个复杂的软件来完成的，这个软件叫做__数据库管理系统___。
三、简答题1．解释什么是Oracle Database 11g系统中关键网格技术？解：Oracle 11g中“g”是grid的缩写，表示网格，网格计算是一种技术，能对同源不同计算类型的分布式网络进行无缝地、大规模地扩展，允许不同厂商的计算机共同工作来提供无尽的共享计算机资源。
比如，有两台PC机，分别来自不同厂商，存均为516M，现系统运行要求1G的存，若PC机单独执行肯定不能满足要求，可以通过网格计算和集群技术将两台PC 机组合在一起，以满足系统运行需求。
2．解释Oracle Database 11g的基本文件目录的含义？解：在Oracle Database 11g中,Oracle的目录结构是由Oracle_Base及其子目录Oracle_Home、admin、flash_recovery_area和oradata目录构成的。
为方便讨论，用Oracle_Base代表Oracle 目录树的根，用Oracle_Home表示根目录下的主目录。
（1）Oracle_Base目录Oracle_Base代表Oracle目录树的根。
如果使用Oracle Universal Installer进行安装,则Oracle_Base是指system_drive:\oracle\product\10.2.0。
（2）Oracle_Home目录Oracle_Home主目录位于system_drive:\Oracle_Base之下，它包含与Oracle软件运行有关的子目录和网络文件以及选定的组件等；若在主机上第一次且只安装了Oracle数据库,没有其他Oracle产品，则使用默认的主目录\db_1；如果在同一台主机的同一个根目录下安装多个产品或安装了第2次，则Oracle_Home主目录会以db_n的形式出现,即db_2、db_3等。
这也是为什么在Oracle_Base目录可以有多个Oracle_Home目录的缘故。
Oracle_Home目录中包括的主要子目录有:·\BIN——主要包含用于数据库管理的各种命令等。
·\css——与Oracle Cluster Synchronization服务有关的文件。
·\dbs——存放数据库服务器端的参数文件Spfile。
·\demo——存放数据库实例模式的脚本等。
· \install——用于存储ORACLE安装后的端口号,iSQL*Plus以及Enterprise Manager Database Control启动并登录的方式等。
·\network\admin——有关监听器listener.ora和sqlnet.ora以及tnsnames.ora等。
·\sysman\config——用于与Oracle Enterprise Management有关的端口管理等。
（3）admin目录数据库管理文件均存储在oracle_base\admin\db_name目录下。
各个子目录的主要含义如下:·\bdump——后台进程跟踪文件。
·\cdump——信息转储文件（core dump）。
·\create——数据库创建文件。
. .资料. ..·\exp——数据库导出文件。
·\pfile——初始化参数文件。
·\udump——用户SQL追踪文件。
（4）Oradata目录数据库文件存储在Oracle_Base\oradata\db_name目录下,该目录主要存储数据库的控制文件、数据文件、重做日志文件。
其中*.dbf文件对应数据库中每个表空间；.ctl文件为控制文件；.log文件对应重做日志文件组及其成员。
（5）flash_recovery_area目录flash_recovery_area目录存储并管理与备份和恢复有关的文件。
它包含系统中每个数据库的子目录。
该目录可用于存储与恢复有关的文件,如控制文件、联机重做日志副本、归档日志、闪回日志以及Oracle数据库恢复管理器(RMAN)备份等。
3. 解释$ORACLE_HOME和$ORACLE_BASE的区别?解：$ORACLE_BASE下是admin和product；$ORACLE_HOME下则是ORACLE的命令、连接库、安装助手、listener等。
$ORACLE_HOME比$ORACLE_BASE目录要更深一些，ORACLE_HOME＝$ORACLE_BASE/product/version。
$ORACLE_BASE是oracle的根目录，$ORACLE_HOME是oracle产品的目录。
如果装了2个版本的oracle，那么$ORACLE_BASE 可以是一个，但$ORACLE_HOME是2个。
第二章ORACLE 11g 的体系结构一、单项选择题1．（ A ）是Oracle服务器在启动期间用来标识物理文件和数据库结构的二进制文件。
A．控制文件 B.参数文件 C.数据文件 D.日志文件2．（ B ）进程主要职责是监控服务器进程和注册数据库服务。
A.SMONB.PMONC.CHKTD.LGWR3.( C )代表了数据库中最小粒度的逻辑数据存储层次。
A.盘区B.表空间C.数据块D.数据文件4．用于在启动实例时配置数据库，确定Oracle 11g的运行环境文件是（ A ）A.参数文件B.数据文件C.可执行文件D.控制文件4. .. .5．下列选项中，哪一部分不是Oracle实例的组成部分？（ C ）A.系统全局区SGAB.PMON后台进程C.控制文件D.Dnnn调度进程6．在全局存储区SGA中，哪部分存区域是循环使用的?( B )A.数据缓冲区B.日志缓冲区C.共享池D.大池7．解析后的SQL语句在SGA的哪个区域中进行缓存？（ C ）A.数据缓冲区B.日志缓冲区C.共享池D.大池8．如果服务器进程无法在数据缓冲区中找到空闲缓存块，以添加从数据文件中读取的数据块，则将启动如下哪个进程？（ A ）A.DBWRB.LGWRC.SMOND.PMON9.如果服务器进程非正常终止，Oracle系统将使用下列哪一个进程以释放它所占用的资源？（ D ）A.DBWRB.LGWRC.SMOND.PMON10．下列哪个进程用于将修改过的数据从存保存到磁盘数据文件中？（ A ）A.DBWRB.LGWRC.RECOD.ARCH11.当数据库运行在归档模式下时，如果发生日志切换，为了保证不覆盖旧的日志信息，系统将启动哪个进程？（ D ）A.DBWRB.LGWRC.SMOND.ARCH12.下面哪个描述是正确的？（ A ）A.表空间由段组成，段由盘区组成，盘区由数据块组成。
B.段由表空间组成，表空间由盘区组成，盘区由数据块组成。
C.盘区由数据块组成,数据块由段组成，段由表空间组成。
D.数据块由段组成，段由盘区组成，盘区由表空间组成。
二、填空题1.在Oracle 11g的文件系统中包含多种类型的文件，这些文件是数据文件、控制文件、日志文件、参数文件、口令文件、跟踪文件、警告日志文件等。
2.一般地，Oracle的后台进程包括数据写入进场DBWR 、日志写入进场LGWR 、检查点进程CKPT 、系统监控进场SMON 、进程监控进场PMON 、归档进程ARCH 、恢复进程RECO、封锁进程LCKn、调度进程Dnnn、服务器进程Snnn、作业进程SNP等。
3．一个表空间物理上对应一个或多个数据文件。
4．用户对数据库的操作如果产生日志信息，则该日志信息首先存储在日志缓冲区中，随后由LGWR 进程保存到日志文件。
5．在Oralce的逻辑存储结构中，根据存储数据的类型，可将段分为数据段、索引段回退段、LOB段和临时段。
6．Oracle数据库由一个或多个称为表空间的逻辑存储单元组成。
7. 存结构是指一个进程在其中进行自身对话或与其他进程对话的存区域,Oracle使用两种类型的存结构,一种是SGA ;另一种是PGA 。