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.
