2试用SQL语言完成下列查询多表查询：每题2分共4分 a查询从事同一种工作但不属于同一部门的员工信息 b查询各个部门的详细信息以及部门人数、部门平均工资

a)查询从事同一种工作但不属于同一部门的员工信息:

SELECT e1.employee_id, e1.employee_name, d1.department_name, e1.job_title FROM employees e1 JOIN departments d1 ON e1.department_id = d1.department_id JOIN employees e2 ON e1.job_title = e2.job_title WHERE e1.department_id <> e2.department_id;

b)查询各个部门的详细信息以及部门人数、部门平均工资:

SELECT d.department_id, d.department_name, COUNT(e.employee_id) AS department_size, AVG(e.salary) AS average_salary FROM departments d JOIN employees e ON d.department_id = e.department_id GROUP BY d.department_id, d.department_name