【诗檀学院 Oracle OCP认证专题】Oracle 11g OCP 考题讲解051

wangtong

169：
View the Exhibit and examine the description for the PRODUCTS and SALES table.
PROD_ID is a primary key in the PRODUCTS table and foreign key in the SALES table. You want to
remove all the rows from the PRODUCTS table for which no sale was done for the last three years.
Which is the valid DELETE statement?

A. DELETE FROM products
WHERE prod_id =
       (SELECT prod_id FROM sales WHERE time_id - 3 * 365 = SYSDATE);

B. DELETE FROM products
WHERE prod_id =
       (SELECT prod_id FROM sales WHERE SYSDATE >= time_id - 3 * 365);

C. DELETE FROM products
WHERE prod_id IN
       (SELECT prod_id FROM sales WHERE SYSDATE - 3 * 365 >= time_id);

D. DELETE FROM products
WHERE prod_id IN
       (SELECT prod_id FROM sales WHERE time_id >= SYSDATE - 3 * 365);

169：
查看下表并检查PRODUCTS和SALES表的详细信息
PROD_ID是PRODUCTS表的主键，是SALES表的外键。你想移除PRODUCTS表过去没有销售过的产品所在的行。
哪一个DELETE语句是有效的？
A. DELETE FROM products
WHERE prod_id =
       (SELECT prod_id FROM sales WHERE time_id - 3 * 365 = SYSDATE);

B. DELETE FROM products
WHERE prod_id =
       (SELECT prod_id FROM sales WHERE SYSDATE >= time_id - 3 * 365);

C. DELETE FROM products
WHERE prod_id IN
       (SELECT prod_id FROM sales WHERE SYSDATE - 3 * 365 >= time_id);

D. DELETE FROM products
WHERE prod_id IN
       (SELECT prod_id FROM sales WHERE time_id >= SYSDATE - 3 * 365);

答案：C
子查询SELECT prod_id FROM sales WHERE SYSDATE - 3 * 365 >= time_id选出的是过去三年没销售过的产品ID。

wangtong

170：
Which two statements are true regarding the DELETE and TRUNCATE commands? (Choose two.)
A. DELETE can be used to remove only rows from only one table at a time.
B. DELETE can be used to remove only rows from multiple tables at a time.
C. DELETE can be used only on a table that is a parent of a referential integrity constraint.
D. DELETE can be used to remove data from specific columns as well as complete rows.
E. DELETE and TRUNCATE can be used on a table that is a parent of a referential integrity constraint having ON DELETE rule .

170：
哪两句是正确的关于DELETE和TRUNCATE命令？（选择两个）
A.        DELETE可以被用于一次移除一个表的一行
B.        DELETE可以被用于一次移除多个表的一行
C.        DELETE可以被用于删除有引用完整性约束的父表
D.        DELETE可以被用于删除指定列以及完整的行
E.        DELETE和TRUNCATE可以用于有引用完整性约束ON DELETE规则的父表

答案：AE
对于E选项，表可以使用ON DELETE CASCADE选项（当删除父表数据时，子表数据也一起删除）或ON DELETE CASCAD SET NULL选项（当删除父表数据时，子表相关的列设置为NULL）,有这些选项，就可以在父表中直接删除相关的行，而不会报外键约束错误。

wangtong

171：
Which three statements/commands would cause a transaction to end? (Choose three.)
A. COMMIT
B. SELECT
C. CREATE
D. ROLLBACK
E. SAVEPOINT

171：
哪三个语句会导致事务结束？（选择三个）
A. COMMIT
B. SELECT
C. CREATE
D. ROLLBACK
E. SAVEPOINT

答案：ACD
A选项正确，commit会提交事务，事务结束。
B选项不正确，select语句是DML语句，不影响事务。
C选项正确，create语句是DDL语句，DDL语句会自动提交事务，事务结束。
D选项正确，rollback回滚事务，事务结束。
E选项不正确，savepoint是保存某个点，然后在回滚的时候可以回滚到某个点，但它不影响事务。

wangtong

172:
The SQL statements executed in a user session are as follows:
SQL> CREATE TABLE product
(pcode NUMBER(2),
pname VARCHAR2(10));
SQL> INSERT INTO product VALUES (1, 'pen');
SQL> INSERT INTO product VALUES (2,'pencil');
SQL> SAVEPOINT a;
SQL> UPDATE product SET pcode = 10 WHERE pcode = 1;
SQL> SAVEPOINT b;
SQL> DELETE FROM product WHERE pcode = 2;
SQL> COMMIT;
SQL> DELETE FROM product WHERE pcode=10;
Which two statements describe the consequences of issuing the ROLLBACK TO SAVE POINT a command
in the session? (Choose two.)
A. The rollback generates an error.
B. No SQL statements are rolled back.
C. Only the DELETE statements are rolled back.
D. Only the second DELETE statement is rolled back.
E. Both the DELETE statements and the UPDATE statement are rolled back.

172:
SQL语句执行在一个用户会话如下：
SQL> CREATE TABLE product
(pcode NUMBER(2),
pname VARCHAR2(10));
SQL> INSERT INTO product VALUES (1, 'pen');
SQL> INSERT INTO product VALUES (2,'pencil');
SQL> SAVEPOINT a;
SQL> UPDATE product SET pcode = 10 WHERE pcode = 1;
SQL> SAVEPOINT b;
SQL> DELETE FROM product WHERE pcode = 2;
SQL> COMMIT;
SQL> DELETE FROM product WHERE pcode=10;
哪两句关于在上述会话中执行ROLLBACK TO SAVE POINT a命令后的描述是正确的？（选择两个）
A.        rollback报错
B.        没有SQL语句回滚
C.        只有DELETE语句回滚
D.        只有第二个DELETE语句回滚
E.        DELETE和UPDATE语句回滚

答案：AB
Commit之后，事务就结束了，a是三个结束事务的回滚点，执行ROLLBACK SAVE POINT a会报错。

wangtong

173：
When does a transaction complete? (Choose all that apply.)
A. when a DELETE statement is executed
B. when a ROLLBACK command is executed
C. when a PL/SQL anonymous block is executed
D. when a data definition language ( DDL) statement is executed
E. when a TRUNCATE statement is executed after the pending transaction

173：
事务什么时候完成？（选择所有合适的）
A.        当执行一个DELETE语句
B.        当执行一个ROLLBACK命令
C.        当执行一个PL/SQL匿名块
D.        当执行一个数据定义语言（DDL）
E.        当事务挂起后执行一个TRUNCATE语句

答案：BDE
A选项，Delete是DML语句，不会结束事务
B选项，rollback回滚事务，事务结束
C选项，PL/SQL匿名块不影响事务
D选项，DDL语句会自动提交事务
E选项，TRUNCATE也是DDL语句

wangtong

174：
Which statement is true regarding transactions? (Choose all that apply.)
A. A transaction can consist only of a set of DML and DDL statements.
B. A part or an entire transaction can be undone by using ROLLBACK command .
C. A transaction consists of a set of DML or DCL statements.
D. A part or an entire transaction can be made permanent with a COMMIT.
E. A transaction can consist of only a set of queries or DML or DDL statements.

174：
哪些语句是正确的关于事务？（选择所有合适的）
A.        一个事务只能由一组DML和DDL语句组成
B.        部分或整个事务可以使用ROLLBACK命令回滚
C.        一个事务可以由一组DML或DCL语句组成
D.        部分或整个事务可以使用COMMIT命令提交
E.        一个事务只能由一组查询或DML或者DDL语句组成

答案：BC
A选项，只能不对，还可有有DCL语句
B正确，
C正确，
D不正确，commit提交整个事务
E不正确，还可以有DCL

wangtong

175：
Which two statements are true regarding savepoints? (Choose two.)
A. Savepoints are effective only for COMMIT.
B. Savepoints may be used to ROLLBACK.
C. Savepoints can be used for only DML statements.
D. Savepoints are effective for both COMMIT and ROLLBACK.
E. Savepoints can be used for both DML and DDL statements.

175：
哪两句是正确的关于savepoints？
A.        Savepoints只对COMMIT有影响
B.        Savepoints可以用于ROLLBACK
C.        Savepoints只能用于DML语句
D.        Savepoints对COMMIT和ROLLBACK均有效
E.        Savepoints可以用于DML和DDL语句

答案：
savepoint 表示保存某个点，可以rollback到这个点，也可以commit提交整个事务，但对于commit来说，这个savepoint没有任何意义。所以A和D不正确，B正确。
DML语句会开始一个事务，在这事务中，可以使用savepoint保存点，但DDL不像DML那样，它不会开始一个事务，也不用提交事务，所以DDL不需要保存点。
所以，C正确，D不正确