56.
Jim is trying to add records from the ORDER_DETAILS table to ORDER_DETAIL_HISTORY for
orders placed before the current year. Which insert statement would accomplish his task?
- [A]
INSERT INTO ORDER_DETAIL_HISTORY VALUES (SELECT * FROM ORDER_DETAIL WHERE ORDER_DATE < TRUNC(SYSDATE,'YY'));
- [B]
INSERT FROM ORDER_DETAIL INTO ORDER_DETAIL_HISTORY WHERE ORDER_DATE < TRUNC(SYSDATE,'YY');
- [C]
INSERT INTO ORDER_DETAIL_HISTORY FROM ORDER_DETAIL WHERE ORDER_DATE < TRUNC(SYSDATE,'YY');
- [D]
INSERT INTO ORDER_DETAIL_HISTORY SELECT * FROM ORDER_DETAIL WHERE ORDER_DATE < TRUNC(SYSDATE,'YY');
- Answer & Explanation
- Report
Answer : [D]
Explanation :
Explanation :
| When inserting from another table using a subquery, the VALUES clause should not be included. Options B and C are invalid syntaxes for the INSERT statement. |
57.
Which of the following statements will not implicitly begin a transaction?
- [A]INSERT
- [B]UPDATE
- [C]DELETE
- [D]SELECT FOR UPDATE
- [E]None of the above; they all implicitly begin a transaction, if not started already.
- Answer & Explanation
- Report
Answer : [E]
Explanation :
Explanation :
| If a transaction is not currently open, any INSERT, UPDATE, MERGE, DELETE, SELECT FOR UPDATE, or LOCK statement will implicitly begin a transaction. |
58.
Consider the following UPDATE statement. Which UPDATE statements from the options will
accomplish the same task? (Choose two.)
UPDATE ACCOUNTS SET LAST_UPDATED = SYSDATE, UPDATE_USER = USER;
- [A]
UPDATE ACCOUNTS SET (LAST_UPDATED, UPDATE_USER) = (SYSDATE, USER);
- [B]
UPDATE ACCOUNTS SET LAST_UPDATED = (SELECT SYSDATE FROM DUAL), UPDATE_USER = (SELECT USER FROM DUAL);
- [C]
UPDATE ACCOUNTS SET (LAST_UPDATED, UPDATE_USER) = (SELECT SYSDATE, USER FROM DUAL);
- [D]
UPDATE ACCOUNTS SET LAST_UPDATED = SYSDATE AND UPDATE_USER = USER;
- Answer & Explanation
- Report
Answer : [B, C]
Explanation :
Explanation :
| Option A will error out because when using columns in set, a subquery must be used as in option C. Option D is wrong because AND is used instead of a comma to separate columns in the SET clause. |
59.
Which of the following statements do not end a transaction? (Choose two.)
- [A]SELECT
- [B]COMMIT
- [C]TRUNCATE TABLE
- [D]UPDATE
- Answer & Explanation
- Report
Answer : [A, D]
Explanation :
Explanation :
| COMMIT, ROLLBACK, and any DDL statement end a transaction—DDL is automatically committed. INSERT, UPDATE, and DELETE statements require a commit or rollback. |
60.
Sara wants to update the SALARY column in the OLD_EMPLOYEES table with the value from
the EMPLOYEES table for employees in department 90. Which SQL will accomplish the task?
- [A]
UPDATE old_employees a SET salary = (SELECT salary FROM employees b WHERE a.employee_id = b.employee_id) WHERE department_id = 90;
- [B]
UPDATE old_employees SET salary = (SELECT salary FROM employees) WHERE department_id = 90;
- [C]
UPDATE old_employees a FROM employees b SET a.salary = b.salary WHERE department_id = 90;
- [D]
UPDATE old_employees a SET salary = (SELECT salary FROM employees b WHERE a.employee_id = b.employee_id AND department_id = 90);
- [E]e, d, g
- Answer & Explanation
- Report
Answer : [A]
Explanation :
Explanation :
| Option A uses a correlated subquery to match the correct employee. Option B selects all the rows in the subquery and hence will generate an error. Option C is not valid syntax. Option D will update all the rows in the table since the UPDATE statement does not have a WHERE clause. The WHERE clause preset belongs to the subquery. |
|
|
||||||||||||||||||||||||||||
