Subqueries cannot:
(a) Use group by or group functions
(b) Retrieve data from a table different from the one in the outer query
(c) Join tables
(d) Appear in select, update, delete, insert statements.
The question was posed to me in unit test.
This intriguing question originated from Aggregate Functions and Nested Subqueries in division SQL : Queries, Constraints and Triggers of Database Management