Get Oracle 1Z0-071 Exam Practice Questions - Real and Updated

Answer : A, E

Non-equi joins are joins where the join condition is based on something other than equality. In Oracle SQL, you can perform non-equi joins using various clauses:

A . The ON clause can be used: True, the ON clause can specify any condition for the join, including non-equijoins. It is not limited to equijoins.

E . The Oracle join syntax can be used: The traditional Oracle join syntax, which uses the WHERE clause to specify the join condition, can be used for all types of joins, including non-equijoins.

Oracle Database SQL Language Reference 12c, especially the sections on join clauses including the ON clause and Oracle's proprietary join syntax.

Answer : C, E

The INTERSECT operator in SQL returns the results that are common to both of the SELECT statements. It functions similarly to a set intersection in mathematics. When comparing rows for the INTERSECT operation, Oracle Database uses all the expressions in the SELECT lists to derive the result set. NULL values are considered equal for the INTERSECT operator.

Evaluating the provided data from the ORDERS and INVOICES tables, let's see which rows have the same ORDER_ID and ORDER_DATE in both tables:

A: Order ID 3 has a NULL order date in the ORDERS table and does not match with any row in the INVOICES table, so it will not be returned.

B: Order ID 2 has a NULL order date in the ORDERS table but has a non-NULL order date in the INVOICES table, so it will not be returned.

C: Order ID 1 has a NULL order date in both tables, but INTERSECT considers NULLs as equal, so this will be returned.

D: Order ID 5 has a date of 01-MAR-2019 in the ORDERS table and 01-APR-2019 in the INVOICES table, so it will not be returned since the dates do not match.

E: Order ID 4 has a date of 01-FEB-2019 in both tables, so this row will be returned as it matches in both.

F: Order ID 3 has a NULL order date in the ORDERS table but has 01-JAN-2019 in the INVOICES table, so it will not be returned.

Based on this analysis, the query will return:

Order ID 1 with a NULL order date.

Order ID 4 with an order date of 01-FEB-2019.

So the correct answer is:

Answer : B, C, D

B: True. The Oracle Database MERGE statement can use a subquery that involves views to generate the source data for the merge operation. This allows for greater flexibility in specifying the data to be merged.

C: True. MERGE can conditionally combine rows from one or more source tables (or views, or the results of a subquery) to insert or update rows in a single target table based on whether or not a match exists.

D: True. MERGE can indeed use subqueries to produce source rows. The source data for a merge operation can be a table, view, or the result of a subquery.

The MERGE statement is a powerful SQL operation in Oracle that allows for conditional INSERT or UPDATE operations in one statement, often referred to as an 'upsert' operation. It can handle complex scenarios where the decision to insert or update is based on whether the data matches between the target and source datasets.

Reference: Oracle SQL documentation on the MERGE statement provides information on how it can use different data sources, including subqueries and views, and perform conditional operations on the target table.

Answer : A

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.

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.

Answer : A, D, F