Oracle SQL 查询练习：多表查询和嵌套子查询

a) 查询从事同一种工作但不属于同一部门的员工信息:\n\nSELECT e1.empno, e1.ename, e1.job, e1.deptno\nFROM emp e1, emp e2\nWHERE e1.job = e2.job AND e1.deptno <> e2.deptno;\n\nb) 查询各个部门的详细信息以及部门人数、部门平均工资:\n\nSELECT d.deptno, d.dname, COUNT(e.empno) AS num_employees, AVG(e.sal) AS avg_salary\nFROM dept d\nLEFT JOIN emp e ON d.deptno = e.deptno\nGROUP BY d.deptno, d.dname;\n\na) 查询10号部门员工以及领导的信息:\n\nSELECT e1.empno, e1.ename, e1.job, e1.mgr\nFROM emp e1\nWHERE e1.deptno = 10;\n\nb) 查询工资为某个部门平均工资的员工信息:\n\nSELECT e.empno, e.ename, e.job, e.sal\nFROM emp e\nWHERE e.sal = (SELECT AVG(sal) FROM emp WHERE deptno = e.deptno);\n\nc) 查询工资高于本部门平均工资的员工的信息及其部门的平均工资:\n\nSELECT e.empno, e.ename, e.job, e.sal, AVG(e2.sal) AS avg_dept_salary\nFROM emp e, emp e2\nWHERE e.sal > (SELECT AVG(sal) FROM emp WHERE deptno = e.deptno)\nAND e.deptno = e2.deptno\nGROUP BY e.empno, e.ename, e.job, e.sal;