11g oracle 047题库解析

最新推荐文章于 2022-06-26 16:36:13 发布

cke63021

最新推荐文章于 2022-06-26 16:36:13 发布

阅读量395

点赞数

文章标签：数据库

31. Which statements are true regarding the hierarchical query(级联查询) in Oracle Database 10g? (Choose all that apply.)
A. It is possible to retrieve data only in topdown hierarchy.
翻译: 可以检索top-down结构(由根到叶)的数据
解释: 级联查询可以查询top-down结构和bottom-up结构的数据。PRIOR关键词用来指定谁是父记录谁是子记录。
B. It is possible to retrieve data in topdown or bottomup hierarchy.(right)
翻译: 可以检索top-down结构(由根到叶)或者bottom-up结构(即由叶到根)的数据
C. It is possible to remove an entire branch from the output of the hierarchical query.(right)
翻译: 可以从级联查询德输出结果中移除整个分支
解释: 并不是删除整个分支，而是在级联查询中去除，选择性的显示所需要的分支信息，可以通过指定不同的root或者父记录与子记录间的关系
D. You cannot specify conditions when you retrieve data by using a hierarchical query
翻译: 你用级联查询检索数据时不能指定条件
解释: 级联查询用START WITH指定根的条件，用CONNECT BY指定父记录与子记录之间的关系.

32. Which two statements are true regarding views? (Choose two.)
A. A simple view in which column aliases have been used cannot be updated
翻译: 一张列的别名被使用的简单视图不能进行修改
解释: 如果是简单视图的话，可以进行update操作，不管列是不是取了别名。
B. A subquery used in a complex view definition cannot contain group functions or joins.
翻译: 在一个复杂的视图定义下使用的子查询不能包含聚合函数或者连接
解释: 就是因为使用了聚合函数或者连接，所以才成为复杂的视图。所以子查询必须可以包含聚合函数或者连接
C. Rows cannot be deleted through a view if the view definition contains the DISTINCT keyword.(right)
翻译: 如果视图定义包含了DISTINCT关键字，那么不能通过这个视图删除行。
解释: 在包含DISTINCT关键字的视图上不允许DML操作。
D. Rows added through a view are deleted from the table automatically when the view is dropped.
翻译: 当视图被删除时，通过视图添加的行将自动的从表中被删除。
解释: 通过视图添加的行实际上是添加在视图所指向的表上，所以删除视图对视图基于的表没有任何关联操作。
E. The OR REPLACE option is used to change the definition of an existing view without dropping and recreating it.(right)
翻译: OR REPLACE选项使用来改变一个已经存在的视图的定义，不用删除视图再重新创建它。
F. The WITH CHECK OPTION constraint can be used in a view definition to restrict the columns displayed through the view.
翻译: WITH CHECK OPTION约束可以被使用来在一个视图定义中限制通过视图显示的列。
解释: WITH CHECK OPTION约束是限制dml操作结果必须落在视图范围，而确定视图显示的列则是在创建视图时指定的。

33. View the Exhibit and examine the details of the ORDER_ITEMS table.
Evaluate the following SQL statements:
Statement 1:
SELECT MAX(unit_price*quantity) "Maximum Order"
FROM order_items
对所有的行计算unit_price*quantity，输出最大值(1行)
Statement 2:
SELECT MAX(unit_price*quantity) "Maximum Order"
FROM order_items
GROUP BY order_id
以order_id分组，计算unit_price*quantity，输出各组最大值(3行)
Which statements are true regarding the output of these SQL statements? (Choose all that apply.)
A. Statement 1 would return only one row of output.(right)
B. Both the statements would give the same output.
翻译: 2个语句给出相同的输出
解释: 语句1有1行输出，语句2有3行输出，不相同。
C. Statement 2 would return multiple rows of output.(right)
D. Statement 1 would not return any row because the GROUP BY clause is missing.
翻译: 语句1不会返回任何行因为缺少GROUP BY条件
解释: 没有GROUP BY条件会计算unit_price*quantity，输出所有行之中的最大值，所以有1行输出
E. Both statements would ignore NULL values for the UNIT_PRICE and QUANTITY columns.(right)

34. View the Exhibit and examine the structure of the ORDERS and ORDER_ITEMS tables.
Evaluate the following SQL statement:
SELECT oi.order_id, product_id, order_date
FROM order_items oi JOIN orders o
USING(order_id)
Which statement is true regarding the execution of this SQL statement?

A. The statement would not execute because table aliases are not allowed in the JOIN clause.
翻译: 语句不会被执行，因为join条件中不允许使用表的别名
解释: 对表起别名就是在join语句中指定的。
B. The statement would not execute because the table alias prefix is not used in the USING clause.
翻译: 语句不会被执行，因为表的别名前缀没有在using条件中被使用
解释: using条件是不能用表的别名修饰列名的，只有on条件要用表的别名修饰。
C. The statement would not execute because all the columns in the SELECT clause are not prefixed with table aliases.
翻译: 语句不会被执行，因为select条件中所有的列没有表的别名作为前缀
解释: 只有多个表的同名列需要用[表名.列名]来唯一确定。
D. The statement would not execute because the column part of the USING clause cannot have a qualifier in the SELECT list.(right)
翻译: 语句不会被执行，因为using条件的列部分不能在select列表中有限定词

35. Evaluate the following SQL statements in the given order:
DROP TABLE dept
CREATE TABLE dept(
   deptno NUMBER(3) PRIMARY KEY,
   deptname VARCHAR2(10)
  )
DROP TABLE dept
FLASHBACK TABLE dept TO BEFORE DROP
闪回表遵从同名表后进先出原则
对表使用闪回要求该表的行移动为允许
alter table binzhang ENABLE ROW MOVEMENT
Which statement is true regarding the above FLASHBACK operation?
A. It recovers only the first DEPT table.
同名表后进先出原则，恢复第2个表
B. It recovers only the second DEPT table.(right)
C. It does not recover any of the tables because FLASHBACK is not possible in this case.
删除表示仅用drop是可以使用flashback恢复的，用truncate table或者purge table无法恢复，
D. It recovers both the tables but the names would be changed to the ones assigned in the RECYCLEBIN.
闪回一次恢复一个，同名表闪回要rename to新表名

FLASHBACK TABLE   [ schema. ]table     [, [ schema. ]table ]...   TO { { SCN | TIMESTAMP } expr        [ { ENABLE | DISABLE } TRIGGERS ]      | BEFORE DROP [ RENAME TO table ]      } ;
flashback table test_purge to before drop;
flashback table test_purge to before drop rename to test_purge_old;
flashback table test_purge to SCN | TIMESTAMP

36. Evaluate the following statements:
CREATE TABLE digits(
   id NUMBER(2),
   description VARCHAR2(15)
   )
创建digits表
INSERT INTO digits VALUES (1,'ONE')
插入(1,'ONE')
UPDATE digits SET description ='TWO' WHERE id=1
将(1,'ONE')改为(1,'two')
INSERT INTO digits VALUES (2,'TWO')
插入(2,'TWO')
COMMIT
digits表中记录为(1,'two'),(2,'TWO'),只有commit以后闪回版本查询才能查询到版本的更新
DELETE FROM digits
删除2行,但是没有commit,如果commit闪回版本查询比原来就会多出2行"TWO"
SELECT description FROM digits
VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
闪回版本查询会记录每次commit后各版本的差异,不提交没记录
What would be the outcome of the above query?
A. It would not display any values.
B. It would display the value TWO once.
C. It would display the value TWO twice.(right)
D. It would display the values ONE, TWO, and TWO.

37. View the Exhibit and examine the description of the ORDERS table.
Evaluate the following SQL statement:
SELECT order_id, customer_id
FROM orders
WHERE order_date > 'June 30 2001'
order_date是timestamp类型,'June 30 2001'是字符串.
Oracle不会自动转换,要显式调用to_date()或者to_char()
Which statement is true regarding the execution of this SQL statement?

A. It would not execute because 'June 30 2001' in the WHERE condition is not enclosed within double quotation marks.
翻译: 它将不会执行因为在where条件中的'June 30 2001'没有被双引号封闭
解释: sql里的字符串时用单引号修饰
B. It would execute and would return ORDER_ID and CUSTOMER_ID for all records having ORDER_DATE greater than 'June 30 2001'.
翻译: 他将执行并返回所有ORDER_DATE大于'June 30 2001'的记录的ORDER_ID和CUSTOMER_ID
解释: 本语句执行会报错,因为日期和字符串oracle不会隐式转换
C. It would not execute because 'June 30 2001' in the WHERE condition cannot be converted implicitly and needs the use of the TO_DATE conversion function for proper execution.(right)
翻译: 它将不会执行因为在where条件中的'June 30 2001'不能隐式转换并且需要使用TO_DATE转换函数恰当的执行
D. It would not execute because 'June 30 2001' in the WHERE condition cannot be converted implicitly and needs the use of the TO_CHAR conversion function for proper execution.
翻译: 它将不会执行因为在where条件中的'June 30 2001'不能隐式转换并且需要使用TO_CHAR转换函数恰当的执行
解释: 'June 30 2001'是字符串,对其使用to_char()是没有意义的,应该使用to_date(),也不能对order_date使用to_char(),因为字符串作比较是比ascii码,不会比英语单词意思的

38. Which statements are correct regarding indexes? (Choose all that apply.)
A. When a table is dropped, the corresponding indexes are automatically dropped.(right)
翻译: 当表被删除时,对应的索引也自动被删除.
B. For each DML operation performed, the corresponding indexes are automatically updated.(right)
翻译: 每条DML操作执行,对应的索引都会自动更新
C. Indexes should be created on columns that are frequently referenced as part of an expression.
翻译: 索引必须创建在频繁的作为表达式的一部份被引用的列上
解释: 建议为频繁使用的列建立索引,但是这不是强制性的,频繁使用的列可以不建索引,索引可以建在任何列上
D. A nondeferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index.(right)
翻译: 一个表中的一个不可延时的主键或者唯一键约束会自动建立一个唯一索引

39. View the Exhibit and examine the description of the PRODUCT_INFORMATION table.
Which SQL statement would retrieve(检索) from the table the number of products having LIST_PRICE as NULL?
比较null值用is NULL,任何NULL不等于其他NULL,使用count(列名)会忽略列中值为NULL的行,count(*)返回记录数,NULL也算一条记录
A. SELECT COUNT(list_price)
FROM product_information
WHERE list_price IS NULL
解释: COUNT(list_price)返回list_price不为NULL的个数
B. SELECT COUNT(list_price)
FROM product_information
WHERE list_price = NULL
解释: 值为NULL写作is NULL,不可写为= NULL
C. SELECT COUNT(NVL(list_price, 0))(right)
FROM product_information
WHERE list_price IS NULL
解释: nvl(arg,value)代表如果前面的arg的值为null那么返回的值为后面的value
D. SELECT COUNT(DISTINCT list_price)
FROM product_information
WHERE list_price IS NULL
解释: DISTINCT是去除重复的关键字,不管有没有DISTINCT关键字,count()都返回不是null的个数,

40. User OE, the owner of the ORDERS table, issues the following command:
GRANT SELECT,INSERT ON orders TO hr WITH GRANT OPTION
授予hr对表orders的SELECT,INSERT权限,并且授予hr将这些权限授予别人的权限
The user HR issues the following command:
GRANT SELECT ON oe.orders TO scott
授予scott对表oe.orders的SELECT权限
Then, OE issues the following command:
REVOKE ALL ON orders FROM hr
撤销hr对表orders的所有权限
WITH GRANT OPTION只能在赋予 object privilege 的时使用,撤销时有连带效果oe>>hr>>scott
Which statement is correct?
A. The user SCOTT loses the privilege to select rows from OE.ORDERS.(right)
B. The user SCOTT retains the privilege to select rows from OE.ORDERS.
翻译: SCOTT保留了对OE.ORDERS表的select权限
解释: 对象权限撤销时会连带撤销通过WITH GRANT OPTION传递的权限
C. The REVOKE statement generates an error because OE has to first revoke the SELECT privilege from SCOTT.
翻译: 撤销语句产生一个错误，因为OE要先撤销scott的select权限
解释: 撤销任何权限时都不会要求先撤销其他通过WITH ADMIN/GRANT OPTION获得权限的用户/角色
D. The REVOKE statement generates an error because the ALL keyword cannot be used for privileges
翻译: 撤销语句产生一个错误，因为关键词ALL不能用于权限
解释: GRANT ALL PRIVILEGES TO user/role/public [IDENTIFIED BY password] [WITH ADMIN OPTION]
      GRANT ALL PRIVILEGES ON [schema.]object TO user/role/public [WITH GRANT OPTION] [WITH HIERARCHY OPTION]