Sub Query in SELECT

SELECT employee_id, salary, (SELECT AVG(salary) FROM employee_salary) AS all_avg_salary
FROM employee_salary

Sub Query in WHERE

SELECT employee_id, salary
FROM employee_salary
WHERE employee_id IN (SELECT employee_id
                      FROM employee_demographics
                      WHERE age > 30)