怎样嵌套查询mysql

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2),
    department_id INT
);

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

SELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

SELECT e.name, e.salary, d.name AS department_name,
       (SELECT AVG(e2.salary)
        FROM employees e2
        WHERE e2.department_id = e.department_id) AS department_average_salary
FROM employees e
JOIN departments d ON e.department_id = d.id;

SELECT e.name, e.salary, d.name AS department_name,
       AVG(e2.salary) OVER (PARTITION BY e.department_id) AS department_average_salary
FROM employees e
JOIN departments d ON e.department_id = d.id;