In SQL and Oracle Database, what is a subquery and which option correctly describes its common types used inside other SQL statements?

Introduction / Context:
This question focuses on the definition of a subquery and the typical categories used when discussing SQL subqueries in Oracle and other relational databases. Subqueries are extremely powerful tools that allow you to embed one query inside another to filter, calculate, or compare values. Interviewers often test whether candidates know both the general idea of a subquery and the common classifications such as single row and correlated subqueries.

Given Data / Assumptions:

- The context is SQL and Oracle Database.
- A subquery appears within another SQL statement, commonly in SELECT, INSERT, UPDATE, or DELETE.
- The candidate is expected to know basic types of subqueries used in practice.

Concept / Approach:
A subquery is defined as a query nested inside another query. It is often enclosed in parentheses and may appear in the WHERE, FROM, or SELECT clause. Subqueries are commonly categorized as single row subqueries, which return exactly one row; multiple row subqueries, which return multiple rows; multiple column subqueries, which return more than one column; and correlated subqueries, which reference columns from the outer query and execute repeatedly for each row of the outer query.

Step-by-Step Solution:
Step 1: Recall the fundamental definition of a subquery as a nested query inside an outer SQL statement. Step 2: Remember standard categories such as single row, multiple row, multiple column, and correlated subqueries used in Oracle training material. Step 3: Evaluate option A and see that it states exactly that definition and lists the standard types. Step 4: Consider option B, which incorrectly describes subqueries as temporary tables stored on disk; that behavior is more similar to global temporary tables or materialized views. Step 5: Option C falsely equates subqueries with views, which are stored query definitions with their own names, while subqueries are anonymous and embedded. Step 6: Option D mislabels joins as subqueries, whereas inner and outer are join types, not subquery types.

Verification / Alternative check:
To verify, you can consult any standard SQL reference. You will see that subqueries are described as queries within queries and often grouped into single row, multiple row, multiple column, and correlated subqueries. Examples include queries using IN, ANY, ALL, or EXISTS predicates. None of the references define subqueries as physical tables, materialized views, or join conditions, which confirms that options B, C, and D are misleading.

Why Other Options Are Wrong:
Option B introduces incorrect terminology about permanent and temporary subqueries and incorrectly states that they are stored on disk. Option C suggests that subqueries and views are the same, which ignores the naming and persistence differences. Option D uses the language of joins, not subqueries, and therefore confuses two distinct SQL features. Only option A accurately captures both the definition and the key types of subqueries.

Common Pitfalls:
Students often confuse correlated subqueries with joins, because both may involve relationships between two sets of rows. Another pitfall is not understanding when a subquery must return a single row and when multiple rows are allowed, which can lead to runtime errors such as ORA errors in Oracle. It is also common to forget that subqueries can appear in the FROM clause as inline views, expanding the power of SQL for complex reports.

Final Answer:
A subquery is a query nested inside another query, and common types include single row, multiple row, multiple column, and correlated subqueries.