CORRELATED SUBQUERIES

SubQuery : 
Simple subquery doesn't use values from the outer query and is being calculated only once:

SELECT id, first_name FROM student_details
WHERE id IN (SELECT student_id FROM student_subjects

WHERE subject= 'Science');

 
CoRelated Subquery  -
Query To Find all employees whose salary is above average for their department

SELECT employee_number, name FROM employees emp
WHERE salary > ( SELECT AVG(salary) FROM employees

WHERE department = emp.department);