In Oracle Database 12c, the INTERSECT operator is used in compound queries to return only the rows that are common to both SELECT statements involved in the query. Here's a breakdown of why the correct answer is C:
A: Incorrect. The order of the tables in an INTERSECT operation does not affect the output because INTERSECT returns only the common elements between the two queries. The operation is symmetric.
B: Incorrect. While the column names themselves can differ between the two SELECT statements, the data types and the number of columns must match for INTERSECT to function correctly.
C: Correct. INTERSECT returns the intersection of the results of the two queries, which includes only rows that are found in both result sets.
D: Incorrect. The number of columns and their data types in each SELECT statement of the INTERSECT must be the same to avoid errors and to correctly perform the comparison.
E: Incorrect. INTERSECT does not ignore NULLs; rows with NULL values in corresponding columns must exactly match (both sides having NULLs in the same column) to be included in the output.