11g oracle OCP 047题库解析

最新推荐文章于 2024-11-01 12:20:35 发布

cke63021

最新推荐文章于 2024-11-01 12:20:35 发布

阅读量193

点赞数

文章标签：数据库

51. View the Exhibit and examine the description of the EMPLOYEES table.
You want to know the EMPLOYEE_ID and FIRST_NAME of all the records in the EMPLOYEES table
wherein the JOB_ID column has ST_CLERK or ST_MAN values,
在JOB_ID列里有ST_CLERK或ST_MAN的值
the DEPARTMENT_ID column has value 30,
DEPARTMENT_ID =30
and the SALARY column has a value greater than 3,000.
SALARY>3000
Which SQL statement would get you the desired result?
题目的要求是:
(like '%CLERK' or like '%MAN')and DEPARTMENT_ID =30 and SALARY>3000
(      a           or      b         )and       c           and       d
==>(a and c and d)or (b and c and d)
A. SELECT employee_id, first_name
FROM employees
WHERE  job_id like 'MAN%' OR job_id like 'CLERK%'
AND department_id = 30 AND salary > 3000
逻辑正确，要求含有ST_CLERK，ST_MAN，通配符为%CLERK,%MAN
B. SELECT employee_id, first_name
FROM employees
WHERE  job_id like '%MAN' OR job_id like '%CLERK'
AND (department_id = 30 OR salary > 3000)
department_id = 30，salary > 3000其一为假，结果可以为真，与题意不符
C. SELECT employee_id, first_name
FROM employees
WHERE (job_id like '%MAN' AND job_id like '%CLERK')
AND department_id = 30 OR salary > 3000?
salary > 3000皆为假，结果可以为真，与题意不符
D. SELECT employee_id, first_name (right)
FROM employees
WHERE  (job_id like '%MAN' OR job_id like '%CLERK' )
AND department_id = 30 AND salary > 3000?

52. View the Exhibit and examine the structure of the ORDERS table.
The ORDERS table belongs to the user OE. HR is another user in the database.
Evaluate the commands issued by users OE and HR in the following order:
Statement 1 by user OE: GRANT SELECT, UPDATE(customer_id, order_total) ON orders TO hr;
Statement 1 by user HR: SELECT * FROM oe.orders;

Statement 2 by user HR: UPDATE oe.orders SET order_total= 10000;
Which statement is true regarding the above commands?
A. Statement 1 by user OE would not work because the statement has to be issued by the DBA.
ORDERS的主人是oe，oe可以将该表的对象权限授予别人
B. Statement 2 by user HR would not work because the grant is only for SELECT in a subquery of update.
Oracle的新特性可以将表中某几列的对象权限授予别人，oe授予hr对customer_id, order_total两列的update权限
C. There are no errors in the statements issued by OE and HR， all the statements would execute successfully. (right)
D. Statement 1 by user HR would not work because SELECT and UPDATE privileges have been granted only on CUSTOMER_ID and ORDER_TOTAL columns.
的确UPDATE权限只给了CUSTOMER_ID和ORDER_TOTAL两列，但是select权限是给了全表的

53. View the Exhibit and examine the structure of the ORDER_ITEMS table.
You need to display the ORDER_ID of the order that has the highest total value among all the orders in
the ORDER_ITEMS table.
Which query would produce the desired output?
正确答案是:
select order_id
from order_items
group by order_id
having sum(unit_price*quantity)=(
     select max(sum(unit_price*quantity))
     from order_items
     group by order_id
     )

A. SELECT order_id
FROM order_items
WHERE(unit_price*quantity) = MAX(unit_price*quantity)
GROUP BY order_id
max()聚合函数不能用于where条件
B. SELECT order_id
FROM order_items
WHERE(unit_price*quantity) = (SELECT MAX(unit_price*quantity)
FROM order_items)
GROUP BY order_id
一个order_id有多行记录，(SELECT MAX(unit_price*quantity) FROM order_items)只能找到一行的最大值，结果没有实际意义
C. SELECT order_id
FROM order_items
WHERE (unit_price*quantity) = (SELECT MAX(unit_price*quantity)
FROM order_items
GROUP BY order_id)
子查询返回的是GROUP BY order_id以后最大的单行unit_price*quantity，与题意不符
D. SELECT order_id (right)
FROM order_items
GROUP BY order_id
HAVING SUM(unit_price*quantity) =(SELECT MAX(SUM(unit_price*quantity))
FROM order_items GROUP BY order_id)

54. Which two statements are true about sequences created in a single instance database? (Choose two.)

A. The numbers generated by a sequence can be used only for one table.
翻译: 序列产生的数字只能被用于一个表
解释: 可以用于几个标，序列不是基于表建立的。
B. DELETE  would remove a sequence from the database.
解释: 删除序列用drop sequence sequence_name
C. CURRVAL is used to refer to the last sequence number that has been generated. (right)
翻译: CURRVAL用来提交序列最后产生的数字
D. When the MAXVALUE limit for a sequence is reached, you can increase the MAXVALUE limit by using the ALTER SEQUENCE statement.  (right)
翻译: 当序列达到最大值限制时，你可以通过alter sequence语句来增加最大值限制

E. When a database instance shuts down abnormally, the sequence numbers that have been cached but not used would be available once again when the database instance is restarted.
翻译: 当数据库实例非正常关闭，进入缓存但是没有使用的序列数字可以在数据库实例重新启动后再次有效。
解释: 数据库非正常关闭会丢失已经读入缓存的序列数字，这些数字将永远丢失
序列的最大值、最小值，步进都是可以通过alter sequence更改的，但是序列的当前值不能用alter sequence更改，要多次调用sequence.netvalue来改变

55. View the Exhibit and examine the structure of the EMPLOYEES and DEPARTMENTS tables.
Which SET operator would you use in the blank space in the following SQL statement to list the
departments where all the employees have managers?
要求显示所有的雇员有经理的部门
SELECT department_id FROM departments 返回的是所有的部门
SELECT department_id FROM employees WHERE manager_id IS NULL?返回的是所有没有经理的部门
所以2个集合的差集就是所有有经理的部门
SELECT department_id
FROM departments
____
SELECT department_id
FROM employees
WHERE manager_id IS NULL?
A. UNION
交集并且去除重复记录
B. MINUS (right)
差集
C. INTERSECT
并集
D. UNION ALL
交集不去除重复记录

56. Which mandatory(强制性的) clause has to be added to the following statement to successfully create an external table called EMPDET?
CREATE TABLE empdet (
   empno CHAR(2),
   ename CHAR(5),
   deptno NUMBER(4)
   )
ORGANIZATION EXTERNAL (
   LOCATION ('emp.dat')
   );

A. TYPE
type 数据转换驱动器，oracle_loader为默认，也可以改换其他如databump
B. REJECT LIMIT
REJECT LIMIT遇到错误退出，0为默认值,可以指定值或使用unlimited
C. DEFAULT DIRECTORY (right)
DEFAULT DIRECTORY工作目录，必须显式指定外部表的目录路径，不设定工作目录是无法创建外部表的
D. ACCESS PARAMETERS
转换参数，如列分割符，错误的设置不会妨碍对外部表的创建，但是访问外部表时会出错

举例:
CREATE TABLE "USERLIST" (
    ID NUMBER,
    USERNAME VARCHAR2(30),
    EMAIL VARCHAR2(128)
    )
ORGANIZATION external (
    TYPE oracle_loader
    DEFAULT DIRECTORY TEMP
    ACCESS PARAMETERS (
       RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
       BADFILE 'TEMP':'userlist.bad'
       DISCARDFILE 'TEMP':'userlist.dis'
       LOGFILE 'TEMP':'user.log'
       READSIZE 1048576
       FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
       MISSING FIELD VALUES ARE NULL
       REJECT ROWS WITH ALL NULL FIELDS

       )
    location (
      'userlist.txt'
      )
    )
REJECT LIMIT UNLIMITED

57. View the Exhibit and examine the description of the ORDER_ITEMS and PRODUCT_INFORMATION tables.
The ORDER_ITEM table has records pertaining to details for each product in an order.
The PRODUCT_INFORMATION table has records for all the products available for ordering.
Evaluate the following SQL statement:
SELECT oi.order_id, pi.product_id
FROM order_items oi RIGHT OUTER JOIN product_information pi
ON (oi.product_id=pi.product_id);
右外连接，显示product_information表的所有记录和能够和product_information 连接的order_items 表记录
Which statement is true regarding the output of this SQL statement?
A. The query would return the ORDER_ID and PRODUCT_ID for only those products that are ordered.
翻译: 查询返回那些被订购的产品的ORDER_ID和PRODUCT_ID
解释: 应该返回所有PRODUCT_ID和被订购的ORDER_ID
B. The query would return the ORDER_ID and PRODUCT_ID for the products that are ordered as well as for the products that have never been ordered.(right)
翻译: 查询返回那些被订购和没有被订购的产品的ORDER_ID和PRODUCT_ID

C. The query would return the ORDER_ID and PRODUCT_ID for the products that are ordered but not listed in the PRODUCT_INFORMATION table.
翻译: 查询返回那些被订购的但是没有在PRODUCT_INFORMATION表中列出的产品的ORDER_ID和PRODUCT_ID
解释: 这个是左外连接的含义，题目是右外连接
D. The query would return the ORDER_ID and PRODUCT_ID for those products that are ordered as well as for the products that have never been ordered, and for the products that are not listed in the PRODUCT_INFORMATION table.
翻译: 查询返回那些被订购和没有被订购的产品，还有没有在PRODUCT_INFORMATION表中列出的产品的ORDER_ID和PRODUCT_ID
解释: 这个是全外连接的含义，题目是右外连接

58. Evaluate the following statement:
CREATE TABLE bonuses(
   employee_id NUMBER,
   bonus NUMBER DEFAULT 100
   );

The details of all employees who have made sales need to be inserted into the BONUSES table.
有销售的雇员的详细情况需要插入BONUSES表。
You can obtain the list of employees who have made sales based on the SALES_REP_ID column of the ORDERS table.
你可以用过ORDERS表的SALES_REP_ID列获得有销售的雇员的列表。
The human resources manager now decides that employees with a salary of $8,000 or less should receive a bonus.
人力资源经理现在决定有8000美元或以下工资的雇员将得到奖励
Those who have not made sales get a bonus of 1% of their salary.
那些没有销售的人获得他们工资1%的奖励
Those who have made sales get a bonus of 1% of their salary and also a salary increase of 1%.
那些有销售的人获得他们工资1%的奖励并且工资增加1%
The salary of each employee can be obtained from the EMPLOYEES table.
每个雇员的工资可以在EMPLOYEES表获得。
本题要求向bonuses表插入雇员工资1%的记录，并修改雇员的工资为原来工资的101%
只有merge可以同时insert和update
insert语句可以有选择性的插入多条记录到多张表，但是没有办法去update记录。
Which option should be used to perform this task most efficiently?
A. MERGE (right)
B. Unconditional INSERT
没有update功能
C. Conditional ALL INSERT
没有update功能
D. Conditional FIRST INSERT
没有update功能

59. Which statement is true regarding the ROLLUP operator specified in the GROUP BY clause of a SQL statement?
A. It produces only the subtotals for the groups specified in the GROUP BY clause.
翻译: 他只对在groupby条件中指定的分组产生分类汇总
解释: rollup会自右向左对每一列进行汇总
B. It produces only the grand totals for the groups specified in the GROUP BY clause.
翻译: 他只对groupby条件中指定的分组做总和
解释: rollup会自右向左对每一列进行汇总
C. It produces higher-level subtotals, moving from right to left through the list of grouping columns specified in the GROUP BY clause. (right)
翻译: rollup会自右向左对分组列表中的列进行高水平的汇总

D. It produces higher-level subtotals, moving in all the directions through the list of grouping columns specified in the GROUP BY clause.
翻译:  rollup会全方向的对分组列表中的列进行高水平的汇总
解释:  rollup只会从右向左

60. View the Exhibit and examine DEPARTMENTS and the LOCATIONS tables.
Evaluate the following SQL statement:
SELECT location_id, city
FROM locations l
WHERE NOT EXISTS (
   SELECT location_id
   FROM departments
   WHERE location_id <> l.location_id
   );
This statement was written to display LOCATION_ID and CITY where there are no departments located.
要求显示没有部门的城市
sql的流程是:
从location表中找出1条记录，
查询子查询是否有返回结果
子查询的结果是返回department表中不在外查询中得到的城市的部门信息，一般来说子查询肯定存在返回值
所以not exists一个非空集合返回false，也就是说该查询得不到任何结果
Which statement is true regarding the execution and output of the command?
A. The statement would execute and would return the desired results.
返回空集，没有得到想要的结果
B. The statement would not execute because the = comparison operator is missing in the WHERE clause of the outer query.
exists只判断子查询返回的集合是否为空，并不针对字段，所以加上等号反而出错
C. The statement would execute but it will return zero rows because the WHERE clause in the inner query should have the = operator instead of <>.(right)
等号替换掉不等号，子查询将会搜索出在location_id上的部门的集合，然后not exists就可以得到没有部门的 location_id了
D. The statement would not execute because the WHERE clause in the outer query is missing the column name for comparison with the inner query result.
exists只判断子查询返回的集合是否为空，并不针对字段，所以加上列名反而出错