- Home
- Database
- Oracle 12 C
- OCA Oracle 12 C SQl Fundamentals(1Z0-061)
41.
Consider the following UPDATE statement. Which UPDATE statements from the following
options will accomplish the same task? (Choose two.)
UPDATE ACCOUNTS
SET LAST_UPDATED = SYSDATE,
UPDATE_USER = USER;
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 columns are used 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. |
42.
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. |
43.
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 code 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);
- 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, therefore, will generate an error. Option C is not valid syntax. Option D will update all the rows in the table because the UPDATE statement does not have a WHERE clause. The WHERE clause preset belongs to the subquery. |
44.
Review the following code snippet. Which line has an error?
1. UPDATE EMPLOYEES
2. WHERE EMPLOYEE_ID = 127
3. SET SALARY = SALARY * 1.25,
4. COMMISSION_PCT = 0
1. UPDATE EMPLOYEES
2. WHERE EMPLOYEE_ID = 127
3. SET SALARY = SALARY * 1.25,
4. COMMISSION_PCT = 0
- A.1
- B.2
- C.4
- D.There is no Error
- Answer & Explanation
- Report
Answer : [B]
Explanation :
Explanation :
| In an UPDATE statement, the WHERE clause should come after the SET clause. |
45.
Jim executes the following SQL statement. What will be the result?
DELETE salary, commission_pct
FROM employees
WHERE department_id = 30;
DELETE salary, commission_pct
FROM employees
WHERE department_id = 30;
- A.The salary and commission_pct columns for all records with department_id 30 will be deleted (changed to NULL).
- B.All the rows belonging to department_id 30 will be deleted from the table.
- C.The salary and commission_pct columns will be deleted from the employees table.
- D.The statement will produce an error.
- Answer & Explanation
- Report
Answer : [D]
Explanation :
Explanation :
| When deleting a row from a table, do not use column names. To change column values to NULL, use the UPDATE statement. |
|
|
||||||||||||||||||||||||||||
