Subqueries vs CTEs: When, Why, How.

With a background in Python programming, Learning SQL started feeling like a step back, every line is independent of the next and so far nothing seemed interconnected or Programming-like, until I hit a wall: Picture this : You have a table employees and you need to find employees who earn more than the average salary of their own department, not the company average. This Problem requires two queries, one to get the average salary of each department and another to find the employee whose salary is above that average. Such a problem requires thinking like a programmer and the solution is either a subquery or a CTE. But what are they. Subqueries A subquery (or inner query or nested query) is a SELECT statement embedded inside another SQL statement (SELECT, INSERT, UPDATE, DELETE). It answers a question within a question. SELECT name, salary, department_id FROM employees e1 WHERE salary > ( SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = e