A subquery must be closed in parentheses, also it can be nested inside another subquery.
Given the ER Diagram, I will demonstrate an example of a very basic implementation of subquery below:
In the following example the subquery will return all office codes of the offices located in London.
The outer query selects the last name and the first name of employees who work in the offices whose office code is matched to the results of the inner query.
A correlated subquery is a subquery that uses the data from the outer query, depending on it because it is evaluated once for each row in the outer query.
Let's dive to an example given the following ER Diagram:
In this example, we select product, name, line, and price for products where the average price is greater than the buy price for products that have the same product line.