Oracle Updated 1z0-071 Exam Questions and Answers by olaf

Multiple row subqueries are subqueries that are used where a subquery is allowed, but they can return more than one row. This means they can be used with operators that allow multiple values like IN, ANY, ALL, etc.

• Option A: They can contain HAVING clauses. This is true. A multiple row subquery can use aggregate functions and, therefore, can include a HAVING clause to filter the results of these aggregates. A HAVING clause is used in a similar way to a WHERE clause, except that it is used to filter rows after an aggregation is performed.
• Option D: They can return multiple columns. This is true. A multiple row subquery can indeed return more than one column, which can be used in the outer query, depending on the context, such as in the column comparison subqueries.
• Option E: They can contain GROUP BY clauses. This is true. Since multiple row subqueries can include aggregate functions, they can also include a GROUP BY clause to specify the grouping of data on which to apply the aggregate functions.

Regarding the incorrect options:

• Option B: Two or more values are always returned from the subquery. This is false. Multiple row subqueries have the potential to return multiple rows, but they are not required to do so. They could return zero, one, or multiple rows depending on the data and the specific query.
• Option C: They cannot contain subquery. This is false. Multiple row subqueries can contain other subqueries within them, which are known as nested subqueries.

For further reading and verification of these points, one can refer to the Oracle Database SQL Language Reference 12c, specifically the sections on subquery syntax and semantics, which discuss the use of HAVING, GROUP BY, and the ability of subqueries to return multiple rows or columns.

To calculate the date from a given base date with intervals, Oracle allows you to add or subtract intervals from dates. Since the NLS_DATE_FORMAT is set to DD-MM-YYYY, the output is expected to be in that format.

Option B seems to calculate a date that is 3 months and 7 days ahead of October 29, 2019, and then subtracts 360 seconds (which is 6 minutes), resulting in a time that is still within the same day.

Here's how the calculation in option B would work out:

• Start date: 29-10-2019
• Add 3 months: 29-01-2020
• Add 7 days: 05-02-2020
• Subtract 360 seconds: Since it's only a few minutes, the date remains 05-02-2020.

However, this does not match the provided result of 02-JAN-2020. We would need to consider the exact amount of time being subtracted or added to find the correct answer.

But upon reviewing the options, they all have various syntax errors such as a missing TO_CHAR function, incorrect quotes, and date formats not matching the session's NLS_DATE_FORMAT. Therefore, we would need to correct these issues to find the right answer.

In Oracle Database 12c SQL, regarding date/time functions and considering a session where NLS_DATE_FORMAT is set to DD-MON-YYYY SH24:MI:SS:

• C. CURRENT_DATE returns the current date and time as per the session time zone. This is correct as CURRENT_DATE returns the current date and time in the time zone of the current SQL session, as set by the ALTER SESSION command.
• D. SYSDATE and CURRENT_DATE return the current date and time set for the operating system of the database server. This is partially correct. SYSDATE returns the current date and time from the operating system of the database server. However, CURRENT_DATE returns the date and time set for the client's operating system environment, adjusted to the session time zone.

Options A, B, E, and F are incorrect based on Oracle's documentation:

• A is incorrect because SYSDATE is independent of the NLS_DATE_FORMAT setting.
• B is incorrect because CURRENT_TIMESTAMP includes time zone information, which can differ from CURRENT_DATE.
• E is incorrect because CURRENT_TIMESTAMP differs from SYSDATE by including fractional seconds and time zone.
• F is incorrect as SYSDATE can be queried in any SELECT statement, not just from DUAL.

A: True. TRUNCATE is generally faster than DELETE for removing all rows from a table because TRUNCATE is a DDL (Data Definition Language) operation that minimally logs the action and does not generate rollback information. TRUNCATE drops and re-creates the table, which is much quicker than deleting rows one by one as DELETE does, especially for large tables. Also, TRUNCATE does not fire triggers.

References:

• Oracle documentation specifies that TRUNCATE is faster because it doesn't generate redo logs for each row as DELETE would.
• TRUNCATE cannot be rolled back once executed, since it is a DDL command and does not generate rollback information as DML commands do.