Using subquery in SELECT Part | Oracle SQL fundamentals

Each #subquery must contain a #SELECT #clause and a #FROM clause. Subqueries can be #correlated or #uncorrelated. A subquery (or inner SELECT statement) is correlated when the value it produces depends on a value produced by the outer SELECT statement that contains it.
The following situations define the types of subqueries the #database #server supports:
A SELECT statement nested in the SELECT list of another SELECT statement
A SELECT statement nested in the #WHERE clause of another SELECT statement (or in an #INSERT, #DELETE or #UPDATE statement)
Each subquery must contain a SELECT clause and a FROM clause. Subqueries can be correlated or uncorrelated.
A subquery (or inner SELECT statement) is correlated when the value it produces depends on a value produced by the outer SELECT statement that contains it. Any other kind of subquery is considered uncorrelated.
You can construct a SELECT statement with a subquery to replace two separate SELECT statements.