Solution

The correct answer is C, D, E Only.

Key Points

​Let's evaluate each SQL statement to find the correct combination:

A. The HAVING CLAUSE is an optional clause which tells Oracle to group rows...

Incorrect. The GROUP BY clause is what groups the rows based on distinct values. The HAVING clause is used after grouping to filter those specific groups based on aggregate conditions.

B. Unions can be used in subqueries.

Contextually skipped. While many modern relational database systems (including newer versions of Oracle) do allow UNION operations inside inline views or subqueries, introductory database theory and older SQL standards often place strict limitations on them. Because Statement A is definitively false, we must choose between the remaining option groupings.

C. The intersect clause outputs only rows produced by both the queries intersected.

Correct. By definition, the INTERSECT operator compares the result sets of two queries and returns only the distinct rows that are present in both.

D. The statement containing a subquery is called a parent statement.

Correct. In standard SQL terminology, the outer query that encapsulates a subquery is referred to as the "parent statement" or "outer query."

E. If a select statement is defined as a subquery, the innermost select statement gets executed first.

Correct. For standard (non-correlated) nested subqueries, the execution evaluates from the bottom up. The innermost query runs first, and its result set is passed to the parent query above it.

Correct Answer: Option 2 (C, D, E Only)