Which of the following DML SQL statements support usage of correlated sub-queries? Each correct answer
represents a complete solution. Choose all that apply.
A.
INSERT
B.
UPDATE
C.
DELETE
Explanation:
Answer B and CA correlated sub-query is a sub-query where a column of a table referred in the outer parent query is
referenced in the sub-query. Two tables are involved in a correlated sub-query and both have data in them.
The two tables are linked to each other using a common column. In the UPDATE and DELETE SQL
statements the table can have data and can be linked with a common column. However, the same is not true
for INSERT statements as in the INSERT statement the table does not contain any data, and hence, cannot be
correlated with the inner query table. The best wayto work with correlated sub-queries is to use table and/or
column aliases to clearly understand which columns are being referenced from which tables. Without aliases
Oracle will return an ambiguity error in some cases.
Answer A is incorrect. The INSERT statements do notsupport correlated sub-queries. They support othersubqueries like INSERT INTO (SELECT FROM ).
Correlated sub-queries with INSERT will not work because for a correlated sub-query to work there has to be a
common column based on which rows in the table are linked to the columns of the table used in the sub-query.
When using the INSERT state, the table does not contain any data; hence, a correlation cannot occur. You
may however, INSERT data first into a table using INSERT INTO (SELECT FROM ) and then use UPDATE
statements and correlated sub-queries to update thedata from other tables.