what is subquery and correlated subquery in sql code example

Example: correlated subquery

A correlated subquery is a way of reading values in each row
and comparing those values in each row with related data.

A correlated subquery is evaluated once for each row
processed by the parent query.
i.e. the subquery returns a value for each row
processed by the parent query unlike the normal subquery which executes
only once and returns the data to the parent query for processing.

SELECT last_name, salary, department_id
FROM employees outer
WHERE salary > (SELECT AVG(salary)
                FROM employees
                WHERE department_id = outer.department_id
               );

Here the inner query (correlated subquery) generates
the avg salary for each department_id processed by the outer query.