The provided DELETE statement uses a correlated subquery to determine which rows should be deleted from the EMPLOYEES table.
* A. The subquery is indeed executed for every row in the EMPLOYEES table. This is because it references e.employee_id, which is a column from the outer query, making it a correlated subquery.
* B. The subquery is a correlated subquery, as explained above.
* C. The subquery is executed for each row, not before the DELETE statement.
* D. Not all existing rows in the EMPLOYEES table are deleted, only those that have a corresponding employee_id in the EMP_HISTORY table.
* E. The DELETE statement executes successfully even if the subquery selects multiple rows because the EXISTS condition only checks for the presence of rows, not their count.
References:
* Oracle Database SQL Language Reference 12c Release 1 (12.1), DELETE Statement
* Oracle Database SQL Language Reference 12c Release 1 (12.1), Subquery Factoring
* Oracle Database SQL Language Reference 12c Release 1 (12.1), Correlated Subqueries