1. You need to load information about new customers from the NEW_CUST table into the tables CUST and CUST_SPECIAL. If a new customer has a credit limit greater than 10,000, then the details have to be inserted into CUST_SPECIAL. All new customer details have to be inserted into the CUST table. Which technique should be used to load the data most efficiently?
A. external table
B. the MERGE command
C. the multitable INSERT command
D. INSERT using WITH CHECK OPTION
答案: C
分析: 本题考点是multitable INSERT
题目要求将NEW_CUST表上所有的记录插入CUST表，credit limit大于10000的记录要同时插入CUST_SPECIAL表，应该使用有条件的insert命令，sql如下:
insert all
when credit_limit>=10000 into CUST_SPECIAL
when 1=1 into CUST
A. 外部表是只读的，不能进行插入操作
B. merge命令只能针对一张表进行插入或者修改操作，不能针对多张表进行插入操作
D. insert命令没有with check option选项，with check option选项是create view时使用的
2. View the Exhibit and examine the description of the CUSTOMERS table. You want to add a constraint on the CUST_FIRST_NAME column of the CUSTOMERS table so that the value inserted in the column does not have numbers. Which SQL statement would you use to accomplish the task?
A. ALTER TABLE CUSTOMERS ADD CONSTRAINT cust_f_name CHECK(REGEXP_LIKE(cust_first_name,'^AZ')) NOVALIDATE;
B. ALTER TABLE CUSTOMERS ADD CONSTRAINT cust_f_name CHECK(REGEXP_LIKE(cust_first_name,'^[09]')) NOVALIDATE;
C. ALTER TABLE CUSTOMERS ADD CONSTRAINT cust_f_name CHECK(REGEXP_LIKE(cust_first_name,'[[:alpha:]]'))NOVALIDATE;
D. ALTER TABLE CUSTOMERS ADD CONSTRAINT cust_f_name CHECK(REGEXP_LIKE(cust_first_name,'[[:digit:]]'))NOVALIDATE;
答案: C
分析: 本题考点是正则表达式/REGEXP
要在表CUSTOMERS的字段CUST_FIRST_NAME建个约束，使这个字段不能包含数字
A. 匹配开头是AZ的字符串。
B. 匹配开头是0或者是9的字符串。
C. 匹配包含字母的字符串。
D. 匹配包含数字的字符串。
3. Which three tasks can be performed using regular expression support in Oracle Database 10g? (Choose three.)
A. It can be used to concatenate two strings.
B. It can be used to find out the total length of the string.
C. It can be used for string manipulation and searching operations.
D. It can be used to format the output for a column or expression having string data.
E. It can be used to find and replace operations for a column or expression having string data.
答案: CDE
分析: 本题考点是正则表达式/REGEXP
Oracle 10g支持正则表达式的函数主要有REGEXP_LIKE、REGEXP_INSTR、REGEXP_SUBSTR、REGEXP_REPLACE，用来寻找或替换匹配的字符串
A. 连接两个字符串用符号||，正则表达式没有这个功能。
B. 字符串的长度是用length()函数，正则表达式没有这个功能。
4. View the Exhibit and examine the structure of the EMP table which is not partitioned and not an indexorganized table. Evaluate the following SQL statement:
ALTER TABLE emp DROP COLUMN first_name;
Which two statements are true regarding the above command? (Choose two.)
A. The FIRST_NAME column would be dropped provided it does not contain any data.
B. The FIRST_NAME column would be dropped provided at least one or more columns remain in the table.
C. The FIRST_NAME column can be rolled back provided the SET UNUSED option is added to the above SQL statement.
D. The FIRST_NAME column can be dropped even if it is part of a composite PRIMARY KEY provided the CASCADE option is used.
答案: BD
分析: 本题考点是set unused
EMP表不是分区表也不是所引组织表，drop columns是物理删除，set unused是逻辑删除，两者都不可以通过rollback恢复，所有引用到该列的对象都会失败。
A. 不管列上有没有数据，删除列的命令都可以正常执行。
C. set unused的列无法访问，无法使用rollback撤销unused设置
5. Evaluate the CREATE TABLE statement:
CREATE TABLE products (product_id NUMBER(6) CONSTRAINT prod_id_pk PRIMARY KEY, product_name VARCHAR2(15));
Which statement is true regarding the PROD_ID_PK constraint?
A. It would be created only if a unique index is manually created first.
B. It would be created and would use an automatically created unique index.
C. It would be created and would use an automatically created nonunique index.
D. It would be created and remains in a disabled state because no index is specified in the command.
答案: B
分析: 本题考点是主键约束和唯一性约束自动建立索引
Oracle在创建主键约束或唯一性约束时，会自动检测该列是否创建过唯一性索引,如果有则不创建而直接使用,如果没有则自动隐式创建唯一性索引。
A. 创建主键约束不需要首先手动创建唯一性索引，Oracle会自动隐式创建唯一性索引
C. 创建主键约束时，Oracle会自动隐式创建的索引是唯一性索引
D. 创建主键约束时没有指定索引，Oracle会自动寻找唯一性索引，找不到则会自动创建唯一性索引。所有约束创建时默认都是自动生效的
6. Which two statements are true? (Choose two.)
A. The USER_SYNONYMS view can provide information about private synonyms.
B. The user SYSTEM owns all the base tables and useraccessible views of the data dictionary.
C. All the dynamic performance views prefixed with V$ are accessible to all the database users.
D. The USER_OBJECTS view can provide information about the tables and views created by the user.
E. DICTIONARY is a view that contains the names of all the data dictionary views that the user can access.
答案: AE
分析: 本题考点是数据字典/data dictionary
B. system用户是操作系统管理员，sys才是数据库管理员，数据字典的所有基表和视图都属于sys用户。
C. v$为前缀的动态性能视图要有DBA权限才能访问。
D. USER_OBJECTS视图不仅包含用户的表、视图信息，还包括了其他对象如触发器、索引、过程等等。
7. View the Exhibit and examine the description of the ORDERS table. Which two WHERE clause conditions demonstrate the correct usage of conversion functions? (Choose two.)
A. WHERE order_date > TO_DATE('JUL 10 2006','MON DD YYYY')
B. WHERE TO_CHAR(order_date,'MON DD YYYY') = 'JAN 20 2003'
C. WHERE order_date > TO_CHAR(ADD_MONTHS(SYSDATE,6),'MON DD YYYY')
D. WHERE order_date IN ( TO_DATE('Oct 21 2003','Mon DD YYYY'), TO_CHAR('NOV 21 2003','Mon DD YYYY') )
D. WHERE order_date IN ( TO_DATE('Oct 21 2003','Mon DD YYYY'), TO_CHAR('NOV 21 2003','Mon DD YYYY') )
答案: AB
分析: 本题考点是日期格式/date type
C. order_date是timestamp with local timezone数据类型，TO_CHAR(ADD_MONTHS(SYSDATE,6),'MON DD YYYY')返回的是字符串，如果不符合系统时间格式则Oracle无法隐式转换，两者比较时会出错。
D. in是集合操作符，集合中TO_DATE('Oct 21 2003','Mon DD YYYY')返回date格式，TO_CHAR('NOV 21 2003','Mon DD YYYY')返回字符串，数据类型不一致。
8. View the Exhibit and examine the description of the EMPLOYEES table. Your company decided to give a monthly bonus of $50 to all the employees who have completed five years in the company. The following statement is written to display the LAST_NAME, DEPARTMENT_ID, and the total annual salary:
SELECT last_name, department_id, salary+50*12 "Annual Compensation" FROM employees WHERE MONTHS_BETWEEN(SYSDATE, hire_date)/12 >= 5;
When you execute the statement, the "Annual Compensation" is not computed correctly. What changes would you make to the query to calculate the annual compensation correctly?
A. Change the SELECT clause to SELECT last_name, department_id, salary*12+50 "Annual Compensation".
B. Change the SELECT clause to SELECT last_name, department_id, salary+(50*12) "Annual Compensation".
C. Change the SELECT clause to SELECT last_name, department_id, (salary+50)*12 "Annual Compensation".
D. Change the SELECT clause to SELECT last_name, department_id, (salary*12)+50 "Annual Compensation".
答案: C
分析: 本题要求为每个工作满5年的员工的月工资增加$50，返回他们的LAST_NAME、DEPARTMENT_ID和年薪，年薪=(salary+50)*12。
9. Evaluate the following CREATE SEQUENCE statement:
CREATE SEQUENCE seq1 START WITH 100 INCREMENT BY 10 MAXVALUE 200 CYCLE NOCACHE;
The sequence SEQ1 has generated numbers up to the maximum limit of 200. You issue the following SQL statement:
SELECT seq1.nextval FROM dual;
What is displayed by the SELECT statement?
A. 1
B. 10
C. 100
D. an error
答案: A
分析: 本题考点是序列/SEQUENCE
START WITH 100指定序列第一次使用是从100开始，INCREMENT BY 10指定序列每次使用增加10，MAXVALUE 200指定序列最大值为200，CYCLE指定序列可以循环使用，NOCACHE指定序列不使用缓存。序列没有设置minvalue参数，所以这个序列第一次使用从100开始，每用1次增加10，达到最大值200后循环到minvalue，由于没有设置minvalue，所以使用默认值1。
10. View the Exhibit and examine the description of the EMPLOYEES table. You want to display the EMPLOYEE_ID, FIRST_NAME, and DEPARTMENT_ID for all the employees who work in the same department and have the same manager as that of the employee having EMPLOYEE_ID 104. To accomplish the task, you execute the following SQL statement:
SELECT employee_id, first_name, department_id FROM employees WHERE (manager_id, department_id) =(SELECT department_id, manager_id FROM employees WHERE employee_id = 104) AND employee_id <> 104
When you execute the statement it does not produce the desired output. What is the reason for this?
A. The WHERE clause condition in the main query is using the = comparison operator, instead of EXISTS.
B. The WHERE clause condition in the main query is using the = comparison operator, instead of the IN operator.
C. The WHERE clause condition in the main query is using the = comparison operator, instead of the = ANY operator.
D. The columns in the WHERE clause condition of the main query and the columns selected in the subquery should be in the same order.
答案: D
分析: 本题考点是组合列
where子句的(manager_id, department_id)作为组合列，要求子查询返回的也是一个结构相同的组合列，而不是(department_id, manager_id)
11. View the Exhibit and examine the descriptions of ORDER_ITEMS and ORDERS tables. You want to display the CUSTOMER_ID, PRODUCT_ID, and total (UNIT_PRICE multiplied by QUANTITY) for the order placed. You also want to display the subtotals for a CUSTOMER_ID as well as for a PRODUCT_ID for the last six months.
Which SQL statement would you execute to get the desired output?
A. SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi.quantity) "Total" FROM order_items oi JOIN orders o ON oi.order_id=o.order_id GROUP BY ROLLUP (o.customer_id,oi.product_id) WHERE MONTHS_BETWEEN(order_date, SYSDATE) <= 6
B. SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi.quantity) "Total" FROM order_items oi JOIN orders o ON oi.order_id=o.order_id GROUP BY ROLLUP (o.customer_id,oi.product_id) HAVING MONTHS_BETWEEN(order_date, SYSDATE) <= 6
C. SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi.quantity) "Total" FROM order_items oi JOIN orders o ON oi.order_id=o.order_id GROUP BY ROLLUP (o.customer_id, oi.product_id) WHERE MONTHS_BETWEEN(order_date, SYSDATE) >= 6
D. SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi.quantity) "Total" FROM order_items oi JOIN orders o ON oi.order_id=o.order_id WHERE MONTHS_BETWEEN(order_date, SYSDATE) <= 6 GROUP BY ROLLUP (o.customer_id, oi.product_id)
答案: D
分析: 本题考点是WHERE与HAVING的区别
A. where要写在group by之前
B. having子句中的列要在select子句中出现
C. where要写在group by之前
12. View the Exhibit and examine the structure of the EMPLOYEES table. You want to retrieve(检索) hierarchical(分层) data of the employees using the top-down
hierarchy(自上而下的层级). Which SQL clause would let you choose the direction to walk through the hierarchy tree?
A. WHERE
B. HAVING
C. GROUP BY
D. START WITH
E. CONNECT BY PRIOR
答案: E
分析: 本题考点是递归查询
本题要求检索顺序是top-down，即自上而下，从上级到下级的顺序，控制递归查询遍历顺序的是CONNECT BY子句，通过PRIOR关键字的位置来确定遍历方向,PRIOR运算符在一侧表示父节点，在另一侧表示子节点，从而确定查找树结构是的顺序是自顶向下还是自底向上。
A. where用来限制结果集的范围。
B. having用来限制分组后的结果集的范围。
C. GROUP BY用来限制结果集的范围。
D. START WITH用来指定遍历的起始位置
13. Which two statements are true regarding(关于) the execution of the correlated(相关) subqueries? (Choose two.)
A. The nested query executes after the outer query returns the row.
B. The nested query executes first and then the outer query executes.
C. The outer query executes only once for the result returned by the inner query.
D. Each row returned by the outer query is evaluated for the results returned by the inner query.
答案: AD
分析: 本题考点是相关子查询/correlated subquery
相关子查询的执行流程是:
1. 外查询上拿一行
2. 用外查询的候选行的值做评估后，内查询返回了记录
3. 判断是否符合外查询的where条件
4. 反复执行一直到最后
14. OE and SCOTT are the users in the database. The ORDERS table is owned by OE. Evaluate(评价) the statements issued(发行) by the DBA in the following sequence:
CREATE ROLE r1;
GRANT SELECT, INSERT ON oe.orders TO r1;
GRANT r1 TO scott;
GRANT SELECT ON oe.orders TO scott;
REVOKE SELECT ON oe.orders FROM scott;
What would be the outcome after executing the statements?
A. SCOTT would be able to query the OE.ORDERS table.
B. SCOTT would not be able to query the OE.ORDERS table.
C. The REVOKE statement would remove the SELECT privilege from SCOTT as well as from the role R1.
D. The REVOKE statement would give an error because the SELECT privilege has been granted to the role R1.
答案: A
分析: 本题考点是权限/privilege
对象权限可以直接授予用户，也可以授予角色后将角色授予用户，结果是用户都可以使用该权限。但是用户通过角色获得的权限不可以被单独撤销，需要撤销角色来撤销权限。
B. SCOTT拥有r1角色，该角色有OE.ORDERS表的select权限，所以SCOTT仍然能正常查询该表。
C. revoke命令不会在撤销一个用户的权限时，将用户所属角色的相同权限一并撤销，撤销角色权限必须显式声明。
D. revoke命令撤销的是SCOTT用户通过GRANT SELECT ON oe.orders TO scott得到的权限，不是通过r1角色得到的权限，所以不会出错。
15. Evaluate the following SQL statement:
ALTER TABLE hr.emp (HR用户下的employees表)
SET UNUSED (mgr_id) （manager_id）
Which statement is true regarding the effect of the above SQL statement?
A. Any synonym existing on the EMP table would have to be recreated.
B. Any constraints defined on the MGR_ID column would be removed by the above command.
C. Any views created on the EMP table that include the MGR_ID column would have to be dropped and recreated.
D. Any index created on the MGR_ID column would continue to exist until the DROP UNUSED COLUMNS command is executed.
答案: B
分析: 本题考点是set unused
drop columns是物理删除，set unused是逻辑删除，两者都不可以通过rollback恢复，所有引用到该列的对象都会失败。
A. 当列被drop或者被set unused时，所有基于该列的对象失效。表的同义词基于的是表不是列，所以不会失效。
C. 含有被set unused的列的视图会失效，但不会自动删除并重建，由于表结构改变，视图已经无法重建，应该称为新建视图。
D. 当set unused或者drop列时，会删除表的结构被依赖的 index/constrain/trigger，依赖于该表的 procedure/function 将保留，但是变为 invalid 状态
16. EMPDET is an external table containing the columns EMPNO and ENAME. Which command would work in relation to the EMPDET table?
外部数据表都是只读的，因此在外部表不能够执行DML操作，也不能创建索引
A. UPDATE empdet SET ename = 'Amit' WHERE empno = 1234;
B. DELETE FROM empdet WHERE ename LIKE 'J%';
C. CREATE VIEW empvu AS SELECT * FROM empdept;
D. CREATE INDEX empdet_idx ON empdet(empno);
答案: C
分析: 本题考点是外部表/external table
外部数据表是只读的，只能对外部表执行select操作，insert, update,delete不能执行。外部表的数据不保存在数据库里，所以不能为外部表创建索引。ANALYZE语句不支持采集外部表的统计数据，应该使用DMBS_STATS包来采集外部表的统计数据。
17. View the Exhibit and examine the structure of the MARKS_DETAILS and MARKStables. Which is the best method to load data from the MARKS_DETAILS table to the MARKStable?
A. Pivoting INSERT
B. Unconditional INSERT
C. Conditional ALL INSERT
D. Conditional FIRST INSERT
答案: A
分析: 本题考点是multitable INSERT
multitable INSERT语句中的Pivoting INSERT方式可以将数据从非关系型数据库导入到关系型数据库
18. View the Exhibit and examine the data in ORDERS and ORDER_ITEMS tables. You need to create a view that displays the ORDER ID, ORDER_DATE, and the total number of items in each order. Which CREATE VIEW statement would create the view successfully?
A. CREATE OR REPLACE VIEW ord_vu (order_id,order_date) AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id) "NO OF ITEMS" FROM orders o JOIN order_items i ON (o.order_id = i.order_id) GROUP BY o.order_id,o.order_date
B. CREATE OR REPLACE VIEW ord_vu AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id) "NO OF ITEMS" FROM orders o JOIN order_items i ON (o.order_id = i.order_id) GROUP BY o.order_id,o.order_date
C. CREATE OR REPLACE VIEW ord_vu AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id) FROM orders o JOIN order_items i ON (o.order_id = i.order_id) GROUP BY o.order_id,o.order_date
D. CREATE OR REPLACE VIEW ord_vu AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)||' NO OF ITEMS' FROM orders o JOIN order_items i ON (o.order_id = i.order_id) GROUP BY o.order_id,o.order_date WITH CHECK OPTION
答案: B
分析: 本题考点是视图/view
创建视图的命令格式为:
create or replace view view_name as (select...from...)
A. 视图的列名是通过select语句指定的。而不是直接指定的。
C. 没有为COUNT(i.line_item_id)列取别名，别人查看视图时无法了解该列的含义。
D. ||用来连接2个字符串
19.View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables. You executed the following query to display PRODUCT_NAME and the number of times the product has been ordered:
SELECT p.product_name, i.item_cnt FROM (SELECT product_id, COUNT (*) item_cnt FROM order_items GROUP BY product_id) i RIGHT OUTER JOIN products p ON i.product_id = p.product_id;
What would happen when the above statement is executed?
A. The statement would execute successfully to produce the required output.
B. The statement would not execute because inline views and outer joins cannot be used together.
C. The statement would not execute because the ITEM_CNT alias cannot be displayed in the outer query.
D. The statement would not execute because the GROUP BY clause cannot be used in the inline view.
答案: A
分析: 本题考点是inline view
inline view是指在另一个select语句的from子句中的select语句，In-line views常常通过去除join操作符和将许多单独的查询凝聚在一个简单的查询里以此来简化复杂的查询。
B. inline views可以和outer joins同时使用。
C. 外查询可以通过表名.列名的形式来调用item_cnt列。
D. inline views中可以使用group by。
20. In which scenario would you use the ROLLUP operator for expression or columns within a GROUP BY clause?
A. to find the groups forming the subtotal in a row
B. to create groupwise grand totals for the groups specified within a GROUP BY clause
C. to create a grouping for expressions or columns specified within a GROUP BY clause in one direction, from right to left for calculating the subtotals
D. to create a grouping for expressions or columns specified within a GROUP BY clause in all possible directions, which is crosstabular report for calculating the subtotals
答案: C
分析: 本题考点是ROLLUP
用了rollup的group by子句所产生的所谓的超级聚合就是指在在产生聚合时会从右向左逐个对每一列进行小结，并在结果中生成独立的一行，同时也会对聚合列生成一个合计列。
A. ROLLUP会为每个分组进行汇总，不只一行。
B. 会从右向左逐个对每一列进行汇总，不仅仅只有GROUP BY子句中指定的列。
D. 在产生聚合时会从右向左逐个对每一列进行汇总，而不是所有可能的方向。
21. View the Exhibit and examine the details of the EMPLOYEES table.
Evaluate the following SQL statement:
SELECT phone_number,
REGEXP_REPLACE(phone_number,'([[:digit:]]{3}).([[:digit:]]{3}).([[:digit:]]{4})', '(1) 2-3')
"PHONE NUMBER"
FROM employees?
The query was written to format the PHONE_NUMBER for the employees. Which option would be the
correct format in the output?
REGEXP_REPLACE( 字符串 , 对字符串进行匹配的正则表达式 , 对应输出格式的正则表达式)
(650.507.9833, '([[:digit:]]{3}).([[:digit:]]{3}).([[:digit:]]{4})', '(1) 23' )
( [[:digit:]] {3} ) .
子表达式开始 匹配任何数字 出现3次 子表达式结束 转义字符'.' 第一部分
650 .
( [[:digit:]] {3} ) .
子表达式开始 匹配任何数字 出现3次 子表达式结束 转义字符'.' 第二部分
507 .
( [[:digit:]] {4} )
子表达式开始 匹配任何数字 出现4次 子表达式结束 第三部分
9833
( 1 ) 2 - 3
(前面匹配的第一部分) [空格] 前面匹配的第一部分 - 前面匹配的第一部分
( 650 ) 507 - 9833
A. xxx-xxx-xxxx
B. (xxx) xxxxxxx
C. (xxx) xxx-xxxx(right)
'(1) 2-3'这个格式很说明问题
D. xxx-(xxx)-xxxx
22. Which statement correctly grants a system privilege?
A. GRANT EXECUTE ON proc1 TO PUBLIC
授予所有用户执行过程proc1的权限,这是对象权限不是系统权限
B. GRANT CREATE VIEW ON table1 TO user1
create view是系统权限,没有在某个表上的创建视图的权限,得到create view权限和select on table对象权限就可以创建到其他用户的表的视图
C. GRANT CREATE TABLE TO user1,user2(right)
GRANT 权限名 TO 用户(角色)1,用户(角色)2
D. GRANT CREATE SESSION TO ALL
要想所有用户授权是to public不是to all
23. View the Exhibit and examine the structure of the CUST table.
Evaluate the following SQL statements executed in the given order:
ALTER TABLE cust
ADD CONSTRAINT cust_id_pk PRIMARY KEY(cust_id) DEFERRABLE INITIALLY DEFERRED
INSERT INTO cust VALUES (1,'RAJ'); --row1
INSERT INTO cust VALUES (1,'SAM'); --row2
COMMIT;
此时PRIMARY KEY状态DEFERRABLE INITIALLY DEFERRED,在commit是检查约束,row1,row2一起提交,同时失败
SET CONSTRAINT cust_id_pk IMMEDIATE;
此时PRIMARY KEY状态DEFERRABLE INITIALLY IMMEDIATE,发出命令后立即检查约束
INSERT INTO cust VALUES (1,'LATA'); --row3
发出命令检查约束,执行成功
INSERT INTO cust VALUES (2,'KING'); --row4
发出命令检查约束,执行成功
COMMIT;
提交成功
Which rows would be made permanent in the CUST table?
A. row 4 only
B. rows 2 and 4
C. rows 3 and 4(right)
D. rows 1 and 4
24. View the Exhibit and examine the structure of the ORDERS table:
The ORDER_ID column has the PRIMARY KEY constraint and CUSTOMER_ID has the NOT NULL
constraint.
Evaluate the following statement:
INSERT INTO (
SELECT order_id,order_date,customer_id
FROM ORDERS
WHERE order_total = 1000
WITH CHECK OPTION
)
VALUES (13, SYSDATE, 101)
What would be the outcome of the above INSERT statement?
语句可理解为INSERT INTO(view) values()
当view带有WITH CHECK OPTION时,要求对视图的操作结果也要在视图的范围内,即(13,sysdate,101)要符合view各列的顺序并且order_total = 1000
所以(13,sysdate,101)对应order_id,order_date,customer_id导致order_total没有赋值,插入结果也就不会落在视图范围,违反了WITH CHECK OPTION约束,该语句将报错
解决该错误的方法有:
1. 将where条件从order_total改为order_id,order_date,customer_id三者中的一个或几个
2. 将order_total加入view中,使得insert时对应的order_total值为1000
A. It would execute successfully and the new row would be inserted into a new temporary table created by the subquery.
本语句不会正确执行,并且即使执行了,修改的也是view所基于的表,而不是这个子查询所创建的临时表
B. It would execute successfully and the ORDER_TOTAL column would have the value 1000 inserted automatically in the new row.
本语句不会正确执行,并且当插入数据时某一列没有被指定,也没有设置默认值时,系统不会将其设为特定值而是设为null
C. It would not execute successfully because the ORDER_TOTAL column is not specified in the SELECT list and no value is provided for it.(right)
D. It would not execute successfully because all the columns from the ORDERS table should have been included in the SELECT list and values should have been provided for all the columns.
view的WITH CHECK OPTION约束仅要求结果落在视图范围,并不要求视图选择表的全部列
25. View the Exhibit and examine the description of the EMPLOYEES table.
Your company wants to give 5% bonus to all the employees on their annual salary(年工资增加5%). The SALARY column stores the monthly salary(月工资) for an employee. To check the total for annual salary and bonus amount for each employee, you issued the following SQL statement:
SELECT first_name, salary, salary*12+salary*12*.05 "ANNUAL SALARY + BONUS"
FROM employees
Which statement is true regarding the above query?
A. It would execute and give you the desired output.(right)
B. It would not execute because the AS keyword is missing between the column name and the alias.
as关键字在ansi sql中被要求输入,pl/sql中可以输入也可以省略
C. It would not execute because double quotation marks are used instead of single quotation marks for assigning alias for the third column.
对列起别名时是使用双引号来修饰而不是单引号
D. It would execute but the result for the third column would be inaccurate(不准确的) because the parentheses(括号) for overriding the precedence of the operator are missing
翻译: 它能否被执行,但是第三列的结果将不准确,因为为了重写优先级的运算符的括号没有了
解释: x*1.05=x+x*0.05,显然不用括号调整优先级
26. Which statement is true regarding external tables?
A. The default REJECT LIMIT for external tables is UNLIMITED.
翻译: 外部表的REJECT LIMIT默认值为UNLIMITED
解释: 外部表的默认的REJECT LIMIT值为0
B. The data and metadata for an external table are stored outside the database.
翻译: 外部表的数据和元数据储存在数据库之外.
解释: 外部表的数据的确储存在数据库之外,但是外部表的元数据储存在数据字典中才使得你可以访问外部表
C. ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an external table.
翻译: ORACLE_LOADER和ORACLE_DATAPUMP有完全一样的功能,当用于外部表的时候
解释: ORACLE_LOADER可以加载所有数据,ORACLE_DATAPUMP只能加载oracle特定的2进制文件*.dmp
D. The CREATE TABLE AS SELECT statement can be used to unload data into regular table in the database from an external table(right)
翻译: CREATE TABLE AS SELECT语句可以用来将外部表的数据卸载到数据库中正常的表里
解释: CREATE TABLE...ORGANIZATION EXTERNAL用来定义外部表,CREATE TABLE AS SELECT则将定义的外部表的数据装载到数据库内部的表,这样就可以对表进行insert,update,delete了
27. View the Exhibit and examine the structure of the PRODUCT_INFORMATION table.
You want to see the product names and the date of expiration(到期) of warranty(保修) for all the products, if the product is purchased(购买) today.
你想看product names和所有产品的保修到期日,假设产品是今天购买的.
The products that have no warranty should be displayed at the top and the products with maximum warranty period should be displayed at the bottom.
没有保修的产品要显示在顶端,保修期最大的产品显示在底端.
product_information表warranty_period(保修期)列类型为interval year(2)tomonth
INTERVAL数据类型用来存储两个时间戳之间的时间间隔。可以指定years and months等
INTERVAL '10-2' YEAR(2) TO MONTH意思是10年2个月
本题考点就是排序
要求没有保修的产品要显示在顶端,保修期最大的产品显示在底端.
就是以warranty_period进行升序排序,sysdate都是一样的,所以warranty_period+sysdate也可以
Which SQL statement would you execute to fulfill this requirement?
A. SELECT product_name, category_id, SYSDATE+warranty_period AS "Warranty expire date"
FROM product_information
ORDER BY SYSDATE-warranty_period
解释:应该按warranty_period升序排序,如果是按SYSDATE-warranty_period的话,要降序排序,使用desc后缀
B. SELECT product_name, category_id, SYSDATE+warranty_period AS "Warranty expire date"
FROM product_information
ORDER BY SYSDATE+warranty_period
right
C. SELECT product_name, category_id, SYSDATE+warranty_period AS "Warranty expire date"
FROM product_information
ORDER BY SYSDATE
解释: SYSDATE是一致的,所以以SYSDATE排序没有任何意义
D. SELECT product_name, category_id, SYSDATE+warranty_period "Warranty expire date"
FROM product_information
WHERE warranty_period >SYSDATE?
解释:SYSDATE是当前时间戳,warranty_period是时间跨度,比较大小没有意义
28. Which two statements are true regarding the EXISTS operator used in the correlated subqueries?
(Choose two.)
A. The outer query stops evaluating the result set of the inner query when the first value is found.(right)
翻译: 外查询停止评估内查询的结果集，当第一个值被发现
B. It is used to test whether the values retrieved by the inner query exist in the result of the outer query.
翻译: 他被用以试验被内查询检索的值是否在外查询的结果集中存在
C. It is used to test whether the values retrieved by the outer query exist in the result set of the inner query.(right)
翻译: 他被用以试验被外查询检索的值是否在内查询的结果集中存在
D. The outer query continues evaluating the result set of the inner query until all the values in the result
翻译: 外查询继续评估内查询的结果集，直到评估完结果中全部的值
举例:
select id, name,salary
from employees
where exists (
select id
from employees
where id in (1,2,3)'
)
当内查询select id from employees where id in (1,2,3)搜索不到id为1或者2或者3的记录时，内查询返回false到外查询
当内查询select id from employees where id in (1,2,3)搜索到第一个id为1或者2或者3的记录时，内查询直接弹回true外查询
所以称exists为半查询
29. A noncorrelated(不相关的) subquery(子查询)can be defined()定义 as ____.
A. a set of sequential queries, all of which must always return a single value
翻译: 一个由连续的查询组成的集合，所有的查询必须总是返回一个值。
解释: 如果外部查询时in关键字，返回多个值也是可以的。
B. a set of sequential queries, all of which must return values from the same table
翻译: 一个连续的查询集，所有的查询必须返回来自同一个表的值。
解释: 不要求查询的结果来自同一个表
C. a SELECT statement that can be embedded in a clause of another SELECT statement only
翻译: 一个select语句，只能被嵌入另一个条件的select语句
解释: 一个select语句嵌套在另一个select语句就是嵌套查询，两条语句条件一样无非就是一个语句执行两次而已
D. a set of one or more sequential queries in which generally the result of the inner query is used as the search value in the outer query.(right)
翻译: 一个由一个或多个连续的查询组成的集合，通常内查询的结果被用来作为外查询搜索的值。
本题用排除法，A,B,C都有错误所以选D，实际上对于Correlated Subqueries和Noncorrelated Subqueries来说D都是正确的，因为D只是解释了Subquery。
30. You need to create a table for a banking(金融) application(应用) with the following considerations(注意事项):
1) You want a column in the table to store the duration(持续时间) of the credit(贷款) period(时期).
你希望表中的一列储存贷款期的持续时间(这是一个时间的跨度)
2) The data in the column should be stored in a format such that it can be easily added and subtracted with
列中的数据要以容易做加减的形式保存
3) date type data without using the conversion functions.
日期类型的数据不使用转换函数
4) The maximum period of the credit provision in the application is 30 days.
程序中提供的贷款最大期限为30天(时间跨度不超过30天，year to month太大，要使用day to second)
5) 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. INTERVAL YEAR TO MONTH
时间跨度太大，题目要求不超过30天
B. INTERVAL DAY TO SECOND(right)
C. TIMESTAMP WITH TIME ZONE
TIMESTAMP是时间c戳，不是时间跨度
D. TIMESTAMP WITH LOCAL TIME ZONE
TIMESTAMP是时间c戳，不是时间跨度
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]
41. View the Exhibit and examine the structure of the EMP table.
You executed the following command to add a primary key to the EMP table:
ALTER TABLE emp ADD CONSTRAINT emp_id_pk PRIMARY KEY (emp_id)
USING INDEX emp_id_idx.
Which statement is true regarding the effect of the command?
A. The PRIMARY KEY is created along with a new index.
B. The PRIMARY KEY is created and it would use an existing unique index.
C. The PRIMARY KEY would be created in a disabled state because it is using
an existing index.
D. The statement produces an error because the USING clause is permitted only
in the CREATE TABLE
command.
Answer: B
The USING INDEX clause only works for PRIMARY KEY and UNIQUE constraints.
It can be appended to any PRIMARY KEY or UNIQUE constraint specification.
including the in-line anonymous, in-line named, and out-of-line syntax.
The USING INDEX clause for creating indices can be used to specify an
existing index by appending a constraint specification with “USING INDEX
index_name” and nothing else.
42. SCOTT is a user in the database.
Evaluate the commands issued by the DBA:
1 -CREATE ROLE mgr.
2 -GRANT CREATE TABLE, SELECT ON oe.orders TO mgr.
3 -GRANT mgr, create table TO SCOTT.
Which statement is true regarding the execution of the above commands?
A. Statement 1 would not execute because the WITH GRANT option is missing.
B. Statement 1 would not execute because the IDENTIFIED BY <password> clause
is missing.
C. Statement 3 would not execute because role and system privileges cannot
be granted together in a single GRANT statement.
D. Statement 2 would not execute because system privileges and object
privileges cannot be granted together in a single GRANT command.
Answer: D
43. Which statement best describes the GROUPING function?
A. It is used to set the order for the groups to be used for calculating the
grand totals and subtotals.
B. It is used to form various groups to calculate total and subtotals created
using ROLLUP and CUBE operators.
C. It is used to identify if the NULL value in an expression is a stored NULL
value or created by ROLLUP or CUBE.
D. It is used to specify the concatenated group expressions to be used for
calculating the grand totals and subtotals.
Answer: C
The GROUPING function identifies superaggregate or aggregate rows produced by
a ROLLUP or CUBE operation in a SELECT . . . GROUP BY statement. It returns a value
of the NUMBER datatype, and its value is either a one (1) or a zero (0).The GROUPING
function is only valid in a SELECT statement that uses a GROUP BY clause. While GROUPING
may be used in a GROUP BY that doesn’t include the ROLLUP or CUBE operation, it doesn’t
produce anything meaningful without those operators—it will always return a zero if
ROLLUP and CUBE are absent from the statement.
SQL> select grouping(division_id),division_id,sum(salary)
from employees2
group by rollup(division_id)
order by division_id;
GROUPING(DIVISION_ID) DIV SUM(SALARY)
--------------------- --- -----------
0 BUS 1610000
0 OPE 1320000
0 SAL 4936000
0 SUP 1015000
1 8881000
可以看到，为空的地方返回1，非空的地方返回0。
44. View the Exhibit and examine the structure of ORD and ORD_ITEMS tables.
In the ORD table, the PRIMARY KEY is ORD_NO and in the ORD_ITEMS tables the
composite
PRIMARY KEY is (ORD_NO, ITEM_NO).
Which two CREATE INDEX statements are valid? (Choose two.)
A. CREATE INDEX ord_idx
ON ord(ord_no);
B. CREATE INDEX ord_idx
ON ord_items(ord_no);
C. CREATE INDEX ord_idx
ON ord_items(item_no);
D. CREATE INDEX ord_idx
ON ord,ord_items(ord_no, ord_date,qty);
Answer: BC
建立主键的时候会自动在其上建立唯一索引
SQL> create table temp(a int not null primary key,b int,c int);
SQL> create index temp_ind on temp(a);
ORA-01408: 此列列表已索引
45. View the Exhibit and examine the structure of the CUSTOMERS table.
CUSTOMER_VU is a view based on CUSTOMERS_BR1 table which has the same
structure as CUSTOMERS table. CUSTOMERS needs to be updated to reflect the
latest information about the customers.
What is the error in the following MERGE statement?
MERGE INTO customers c
USING customer_vu cv
ON (c.customer_id = cv.customer_id)
WHEN MATCHED THEN
UPDATE SET
c.customer_id = cv.customer_id,
c.cust_name = cv.cust_name,
c.cust_email = cv.cust_email,
c.income_level = cv.income_level
WHEN NOT MATCHED THEN
INSERT VALUES(cv.customer_id,cv.cust_name,cv.cust_email,cv,income_level)
WHERE cv.income_level >’100000’;
A. The CUSTOMER_ID column cannot be updated.
B. The INTO clause is misplaced in the command.
C. The WHERE clause cannot be used with INSERT.
D. CUSTOMER_VU cannot be used as a data source.
Answer: A
Reflect:反映
46. Which two statements are true regarding operators used with subqueries?
(Choose two.)
A. The NOT IN operator is equivalent to IS NULL.
B. The <ANY operator means less than the maximum.
C. =ANY and =ALL operators have the same functionality.
D. The IN operator cannot be used in singlerow subqueries.
E. The NOT operator can be used with IN, ANY and ALL operators.
Answer: BE
Equivalent:相当 functionality:功能
其实就是把NOT放到整个表达式之前：如果是NOT ANY, NOT ALL是行不通的。
WHERE NOT col IN (SELECT ...)
WHERE NOT col = ANY (SELECT ...)
WHERE NOT col = ALL (SELECT ...)
找出员工中，只要比部门号为10的员工中的任何一个员工的工资高的员工的姓名个工资。
也就是说只要比部门号为10的员工中的那个工资最少的员工的工资高就满足条件。
select ename,sal From emp Where sal > any(select sal from emp where deptno = 10);
下面的SQL语句的意义与前面的就完全不一样了，其意义是找到比部门号为20的员工的所有
员工的工资都要高的员工，也就是比那个工资最高的员工的还要高的员工
select ename,sal From emp Where sal > all(select sal from emp where deptno = 20);
All：所有的
如>all则需要大于所有的，<all则需小于所有的
Any:任何一个
如>any则需要大于其中任何一个，<any则小于其中任何一个。
对应any, 小于对应最大值，大于对应最小值
<ANY means less than the maximum. >ANY means more than the minimum. =ANY is
equivalent to IN.
对应all, 小于对应最小值，大于对应最大值(sg有误)
<ALL means less than the minimum. >ALL means more than the maximum.
另外关于子查询的结论:
-- 子查询中有空值，其结果也返回空值
-- 子查询中不能使用order by
-- null是个不容易对付的家伙，问下面SQL的返回值是多少？
select case when null=null then 'ok' else 'err' end from dual; 答案: err
select case when null is null then 'ok' else 'err' end from dual; 答案: ok
-- WHERE 子句中的子查询可以嵌套的层数是255
-- 在关联子查询中，内部查询对外部查询处理的每一行执行一次
47. Given below are the SQL statements executed in a user session:
CREATE TABLE product
(pcode NUMBER(2),
pname VARCHAR2(10));
INSERT INTO product VALUES(1, 'pen');
INSERT INTO product VALUES (2,'pencil');
SAVEPOINT a;
UPDATE product SET pcode = 10 WHERE pcode = 1;
SAVEPOINT b;
DELETE FROM product WHERE pcode = 2;
COMMIT;
DELETE FROM product WHERE pcode=10;
ROLLBACK TO SAVEPOINT a;
Which statement describes the consequences?
A. No SQL statement would be rolled back
B. Both the DELETE statements would be rolled back.
C. Only the second DELETE statement would be rolled back.
D. Both the DELETE statements and the UPDATE statement would be rolled back.
Answer: A
试验证明在 ROLLBACK TO SAVEPOINT前不能有COMMIT命令,否则报错: ORA-01086:
从未创建保存点 'A'
要仔细看题
48. Evaluate the following CREATE TABLE command:
CREATE TABLE order_item
(order_id NUMBER(3),item_id NUMBER(2),qty NUMBER(4),
CONSTRAINT ord_itm_id_pk PRIMARY KEY (order_id,item_id)
USING INDEX
(CREATE INDEX ord_itm_idx ON order_item(order_id,item_id)));
Which statement is true regarding the above SQL statement?
A. It would execute successfully and only ORD_ITM_IDX index would be created.
B. It would give an error because the USING INDEX clause cannot be used on
a composite primary key.
C. It would execute successfully and two indexes ORD_ITM_IDX and
ORD_ITM_ID_PK would be
created.
D. It would give an error because the USING INDEX clause is not permitted
in the CREATE TABLE
command.
Answer: A
指定了索引创建的话,建主键不会再自动建索引
49. View the Exhibit and examine the description of EMPLOYEES and DEPARTMENTS
tables.You want to display the EMPLOYEE_ID, LAST_NAME, and SALARY for the
employees who get the maximum salary in their respective departments. The
following SQL statement was written:
WITH
SELECT employee_id, last_name, salary
FROM employees
WHERE (department_id, salary) = ANY (SELECT * FROM dept_max)
dept_max as ( SELECT d.department_id, max(salary)
FROM departments d JOIN employees j
ON (d.department_id = j.department_id)
GROUP BY d.department_id);
Which statement is true regarding the execution and the output of this
statement?
A. The statement would execute and give the desired results.
B. The statement would not execute because the = ANY comparison operator is
used instead of =.
C. The statement would not execute because the main query block uses the query
name before it is even created.
D. The statement would not execute because the comma is missing between the
main query block and the query name.
Answer: C
50. View the Exhibit and examine the data in the DEPARTMENTS tables.
Evaluate the following SQL statement:
SELECT department_id "DEPT_ID", department_name , 'b'
FROM departments WHERE department_id=90
UNION
SELECT department_id, department_name DEPT_NAME, 'a'
FROM departments WHERE department_id=10
Which two ORDER BY clauses can be used to sort the output of the above
statement? (Choose two.)
A. ORDER BY 3;
B. ORDER BY 'b';
C. ORDER BY DEPT_ID;
D. ORDER BY DEPT_NAME;
Answer: AC
Eg:
1: select pcode "tt",pname,'b' from product
union
select pcode ,pname ee,'a' from product
order by 'b';
ORA-01785: ORDER BY 项必须是 SELECT-list 表达式的数目
2: select pcode "tt",pname,'b' from product
union
select pcode ,pname ee,'a' from product
order by ee;
ORA-00904: "EE": 标识符无效
3: select pcode "tt",pname,'b' from product
union
select pcode ,pname ee,'a' from product
order by "tt"; ok的
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 <sequencename> 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只判断子查询返回的集合是否为空，并不针对字段，所以加上列名反而出错