ou want to delete rowsfrom the SALES table, where the PROMO_NAME column in the PROMOTIONS
table has either blowoutsale or everyday low price as values.
Which DELETE statementsare valid (Choose all that apply.)
A. DELETE
FROM sales
WHERE promo_id = (SELECTpromo_id
FROM promotions
WHERE promo_name ='blowout sale')
AND promo_id = (SELECTpromo_id
FROM promotions
WHERE promo_name ='everyday low price');
B. DELETE
FROM sales
WHERE promo_id = (SELECTpromo_id
FROM promotions
WHERE promo_name ='blowout sale')
OR promo_id = (SELECTpromo_id
FROM promotions
WHERE promo_name ='everyday low price');
C. DELETE
FROM sales
WHERE promo_id IN(SELECT promo_id
FROM promotions
WHERE promo_name ='blowout sale'
OR promo_name ='everyday low price');
D. DELETE
FROM sales
WHERE promo_id IN(SELECT promo_id
FROM promotions
WHERE promo_name IN('blowout sale','everyday low price'));
Answer: BCD
解析：
题意：You want to delete rows from the SALES table, where thePROMO_NAME column in the PROMOTIONS tablehas either blowout sale or everyday low price as values.
意思要求找出promo_name 为blowout sale或everyday lowprice的
所以
B，C，D选项正确
169. View the Exhibitand examine the description for the PRODUCTS and SALES table.
PROD_ID is a primary keyin the PRODUCTS table and foreign key in the SALES table. You want to
remove all the rows fromthe PRODUCTS table for which no sale was done for the last three years.
Which is the validDELETE statement
A. DELETE
FROM products
WHERE prod_id = (SELECTprod_id
FROM sales
WHERE time_id - 3*365 =SYSDATE );
B. DELETE
FROM products
WHERE prod_id = (SELECTprod_id
FROM sales
WHERE SYSDATE >=time_id - 3*365 );
C. DELETE
FROM products
WHERE prod_id IN (SELECTprod_id
FROM sales
WHERE SYSDATE - 3*365>= time_id);
D. DELETE
FROM products
WHERE prod_id IN (SELECTprod_id
FROM sales
WHERE time_id >=SYSDATE - 3*365 );
Answer: C
解析：
这里主要考察了时间的顺序，时间越大，离当前时间越近，所以需要sysdate-3*365>=time_id
170. Which twostatements are true regarding the DELETE and TRUNCATE commands (Choose two.)
A. DELETE can be used toremove only rows from only one table at a time.
B. DELETE can be used toremove only rows from multiple tables at a time.
C. DELETE can be usedonly on a table that is a parent of a referential integrity constraint.
D. DELETE can be used toremove data from specific columns as well as complete rows.
E. DELETE and TRUNCATEcan be used on a table that is a parent of a referential integrityconstraint
having ON DELETE rule .
Answer: AE
解析：
B选项，Delete操作在同一时间只能对一个表进行删除
C选项，delete还可以删除其他表
D选项，delete只能删除行级数据，不能删除列
172. The SQL statementsexecuted in a user session are as follows:
SQL> CREATE TABLEproduct
(pcode NUMBER(2),
pname VARCHAR2(10));
SQL> INSERT INTOproduct VALUES (1, 'pen');
SQL> INSERT INTOproduct VALUES (2,'pencil');
SQL> SAVEPOINT a;
SQL> UPDATE product SETpcode = 10 WHERE pcode = 1;
SQL> SAVEPOINT b;
SQL> DELETE FROMproduct WHERE pcode = 2;
SQL> COMMIT;
SQL> DELETE FROMproduct WHERE pcode=10;
Which two statementsdescribe the consequences of issuing the ROLLBACK TO SAVE POINT a
command in the session (Choose two.)
A. The rollbackgenerates an error.
B. No SQL statements arerolled back.
C. Only the DELETEstatements are rolled back.
D. Only the secondDELETE statement is rolled back.
E. Both the DELETEstatements and the UPDATE statement are rolled back.
Answer: AB
解析：
因为已经提交，所以无法做rollback操作
173. When does atransaction complete (Choose all that apply.)
A. when a DELETEstatement is executed