In SQL databases, what are the key properties and main types of subqueries that can be used inside SELECT, INSERT, UPDATE, and DELETE statements?

Introduction / Context:
In structured query language (SQL), subqueries are a very common tool for writing expressive and powerful database queries. This question checks your understanding of what a subquery is, how it behaves inside a main query, and what the main types of subqueries are from a theoretical and practical point of view. Knowing the properties and types helps you design efficient queries and read other people's code with more confidence.

Given Data / Assumptions:

• We are working with standard SQL on a relational database.
• Subqueries can appear in SELECT, INSERT, UPDATE, and DELETE statements.
• We want to identify the correct description of subquery properties and types.
• The main classification of subqueries is into correlated and noncorrelated (also called uncorrelated) forms.

Concept / Approach:
A subquery is a query that appears inside another query. The main or outer query uses the result of the inner subquery as part of its logic. The key property is that a subquery returns a result set that can be used as a value, a list, or a table expression inside the outer query. Subqueries are broadly divided into correlated subqueries, which depend on values from the outer query, and noncorrelated subqueries, which can run independently because they do not rely on any column from the outer query.

Step-by-Step Solution:
1. Read each option and identify how it describes subqueries and their types.2. Option A states that subqueries are nested inside a main statement and that there are correlated and noncorrelated subqueries. This matches the standard definition found in database theory and practice.3. Option B claims that subqueries are temporary tables stored on disk and always run before the main query, which is misleading. A subquery does not have to be stored permanently on disk, and the optimizer can reorder execution.4. Option C restricts subqueries to SELECT clauses and says they must always return one row and one column, which is not correct. Subqueries can be used in WHERE, FROM, and other clauses, and can return sets of rows.5. Option D confuses subqueries with stored procedures and incorrectly says they cannot reference outer query columns, which is the opposite of correlated subquery behaviour.6. Therefore, Option A is the only accurate and complete description of the properties and main types of subqueries.

Verification / Alternative check:
You can verify this by checking any standard SQL textbook or documentation, where you will find the formal distinction between correlated and noncorrelated subqueries. Example: a noncorrelated subquery might calculate a global maximum, while a correlated subquery filters rows using a value from the outer query for each iteration. Both of these patterns depend on the idea that a subquery is nested inside another query.

Why Other Options Are Wrong:
Option B is wrong because a subquery is not defined as a temporary table that must reside on disk, and optimizers can change execution order.Option C is wrong because subqueries are not limited to SELECT clauses and can return more than one row when used with operators like IN or EXISTS.Option D is wrong because correlated subqueries explicitly reference columns from the outer query, so the statement that they cannot do so is incorrect.

Common Pitfalls:
Many learners believe that subqueries always execute fully before the outer query, but in practice, the database optimizer can interleave or rewrite execution plans. Another common confusion is thinking that every subquery must return only one value. In reality, the allowed cardinality depends on the context and operators used, such as scalar subqueries, tuple subqueries, and list subqueries with IN. Students also sometimes confuse subqueries with views or stored procedures, but those are separate database objects with different lifecycles and usage patterns.

Final Answer:
Subqueries are nested queries inside a main statement and are classified into correlated and noncorrelated subqueries.