What is the difference between sub query and co-related subquery

Sub Query:
Here inner query gets executed first and based on the result of it the outer query is executed.

This follows BOTTOM_UP approach, first BOTTOM query (inner query) executes and then UPper query (outer query) executes.

Problem Statement: Get all the employees whose department name is ‘A’
Tables are:
Employee_Table: emp_id_col, emp_name_col, emp_sal_col, emp_dept_id_col
Dept_table: dept_id_col, dept_name_col, dept_loc_col
Here dept_id the foreign key

select * from Employee_Table where 
      empt_dept_id_col = 
      (select dept_id_col from Dept_table where dept_name_col='A')

Correlated Sub Query:
I would say this as a kind of SELF JOIN. First outer query executes and then inner query and inner query gets evaluated for each row processed by the outer query.

This follows TOP-BOTTOM approach
Problem Statement: Get the list of employees whose salary is greater than the average salary of every department.
Tables are:
Employee_Table: emp_id_col, emp_name_col, emp_sal_col, emp_dept_id_col

select * from Employee_Table e where emp_sal_col > 
   (select avg(emp_sal_col) from Employee_Table m 
        where e.emp_dept_id_col = m.emp_dept_id_col)

Here in outer query the table alias ‘e’ gets evaluated before the inner query executes.

Find nth maximum salary of an employee.

This is another example of co-related sub query and most frequently asked interview question.

select emp_sal_col from Employee_Table e1 
  where 
    N-1 
    = 
    (select count(distinct(emp_sal_col)) from Employee_Table e2 
     where e1.emp_sal_col > e2.emp_sal_col)
// Where N is nth highest salary that we want to find.

Explanation:
As correlated subquery operates in top_bottom approach, first outer query executes and then inner query. Here, for every row of outer query, inner query is executed.
Let’s say we want to find 3rd highest salary(i.e, 200)
: First row with employee id ‘1’ and sal ‘100’ from outer query is passed to the inner query. In inner query we check count(distinct(sal)) which is ‘4’ where outerEmployeeTableView.sal > innerEmployeeTableView.sal (it means salaries greater than ‘100’) which is ‘3’. Since N-1, that is (3-1 = 2), does not match we go for next row.
: Second row with employee id ‘2’ and sal ‘200’ from outer query is passed to the inner query. In inner query we check count(distinct(sal)) which is ‘4’ where outerEmployeeTableView.sal > innerEmployeeTableView.sal (it means salaries greater than ‘200’) which is ‘2’. Since N-1, that is (3-1 = 2), matches, outer query returns the salary ‘200’ as output which is 3rd highest salary in the table.