- Home
- Database
- Oracle 12 C
- OCA Oracle 12 C SQl Fundamentals(1Z0-061)
91.
Consider the following three SQL statements. Choose the most appropriate option.
1. DELETE FROM CITY WHERE CNT_CODE = 1;
2. DELETE CITY WHERE CNT_CODE = 1;
3. DELETE (SELECT * FROM CITY WHERE CNT_CODE = 1);
1. DELETE FROM CITY WHERE CNT_CODE = 1;
2. DELETE CITY WHERE CNT_CODE = 1;
3. DELETE (SELECT * FROM CITY WHERE CNT_CODE = 1);
- A.Statements 1 and 2 will produce the same result; statement 3 will error out.
- B.Statements 1 and 2 will produce the same result; statement 3 will produce a different result.
- C.Statements 1, 2, and 3 will produce the same result.
- D.Statements 1, 2, and 3 will produce different results.
- Answer & Explanation
- Report
Answer : [C]
Explanation :
Explanation :
| The FROM keyword in the DELETE statement is optional. Statement 3 first builds a subquery with the necessary condition and then deletes the rows from the subquery. |
92.
Consider the following code segment. How many rows will be in the CARS table after
all these statements are executed?
- A.30
- B.29
- C.28
- D.32
- Answer & Explanation
- Report
Answer : [B]
Explanation :
Explanation :
| When two savepoints are created with the same name, Oracle overwrites the older savepoint; therefore, only the newer savepoint will be available. In the code segment, the DELETE and the first INSERT are not rolled back. |
93.
Jim noticed that the HIRE_DATE and START_DATE columns in the EMPLOYEES table had date
and time values. When he tries to find employees hired on a certain date, he does not get
the desired results. Which SQL statement will update all the rows in the EMPLOYEES table
with no time portion in the HIRE_DATE and START_DATE columns (00:00:00)?
- A.UPDATE EMPLOYEES SET HIRE_DATE = TRUNC(HIRE_DATE) AND START_DATE = TRUNC(START_DATE);
- B.UPDATE TABLE EMPLOYEES SET TRUNC(HIRE_DATE) AND TRUNC(START_DATE);
- C.UPDATE EMPLOYEES SET HIRE_DATE = TRUNC(HIRE_DATE), START_DATE = TRUNC(START_DATE);
- D.UPDATE HIRE_DATE = TRUNC(HIRE_DATE), START_DATE = TRUNC(START_DATE) IN EMPLOYEES;
- Answer & Explanation
- Report
Answer : [C]
Explanation :
Explanation :
| When updating more than one column in a single UPDATE statement, separate the columns by a comma; do not use the AND operator. |
94.
Sara wants to update the SALARY column in the EMPLOYEE table from the SALARIES
table, based on the JOB_ID value for all employees in department 22. The SALARIES
table and the EMPLOYEE table have the following structure. Which of the following
options is the correct UPDATE statement?
- A.UPDATE SALARIES A SET SALARY = (SELECT SALARY FROM EMPLOYEES B WHERE A.JOB_ID = B.JOB_ID WHERE DEPT_ID = 22);
- B.UPDATE EMPLOYEE E SET SALARY = (SELECT SALARY FROM SALARIES S WHERE E.JOB_ID = S.JOB_IB AND DEPT_ID = 22);
- C.UPDATE EMPLOYEE E SET SALARY = (SELECT SALARY FROM SALARIES S WHERE E.JOB_ID = S.JOB_IB) AND DEPT_ID = 22;
- D.UPDATE EMPLOYEE E SET SALARY = (SELECT SALARY FROM SALARIES S WHERE E.JOB_ID = S.JOB_IB) WHERE DEPT_ID = 22;
- Answer & Explanation
- Report
Answer : [D]
Explanation :
Explanation :
| Option A updates the wrong table. Option B has the right syntax but will update all the rows in the EMPLOYEE table because there is no WHERE clause for the UPDATE statement. Because the WHERE clause is in the subquery, all the rows that do not belong to department 22 will be updated with a NULL . Options C and D are similar, except for the AND keyword instead of WHERE . |
95.
The FIRED_EMPLOYEE table has the following structure:
EMPLOYEE_ID NUMBER (4)
FIRE_DATE DATE
How many rows will be counted from the last SQL statement in the code segment?
EMPLOYEE_ID NUMBER (4)
FIRE_DATE DATE
How many rows will be counted from the last SQL statement in the code segment?
- A.109
- B.106
- C.105
- D.107
- Answer & Explanation
- Report
Answer : [D]
Explanation :
Explanation :
| The first INSERT statement and the last INSERT statement will be saved in the database. The ROLLBACK TO A statement will undo the second and third inserts. |
|
|
||||||||||||||||||||||||||||
