一道SQL的笔试题目，大家一起提供思路

Question

有两个表：
dept(deptno(PK), dname, location)
emp(empno(PK), ename, job, salary, deptno)
第一个表为部门表，里面包括部门编号（主键），部门名称和部门位置
第二个表为员工表，里面包括员工编号（主键），员工姓名、员工工作，工资和所在部门的外键
规定每个员工最多在一个部门。1、列出所有部门的部门编号、部门名称，同时列出各个部门Job为Engineer的员工姓名和工作（提示：有的部门中并没有Eigneer工种的员工）
我是这么写的，但是这个结SQL有点问题，我不知道是MySQL的版本问题造成的还是我的SQL不正确：
select dept.deptno as 'Department No', dname as 'Department Name', ename as 'Employee Name', job as 'Job' 
from dept right outer join emp on dept.deptno=emp.deptno 
where job='Engineer'2、列出所有工资高于本部门平均工资的员工姓名、部门名称、平均工资、工资，工资升序排列
select emp_a.ename as 'Employee Name', dname as 'Department Name', avg(emp_a.salary) as 'AVG Salary', emp_a.salary as 'Salary' from emp as emp_a, dept
where emp_a.deptno=dept.deptno and emp_a.salary>(select avg(emp_b.salary) from emp as emp_b where emp_a.deptno=emp_b.deptno) 
group by emp_a.deptno order by emp_a.salary
这条也有点问题，希望高手们给修正一下！

Answer 1

1、select dept.deptno as  'Department No ', dname as  'Department Name ', ename as  'Employee Name ', job as  'Job ' 
from dept,emp where dept.deptno=emp.deptno  and job= 'Engineer ';
2、干嘛要用GROUP 呢?

Answer 2

因为第二题要求分组计算每个组的分数的平均值，当然要group了，对吧？

Answer 3

那你的GROUP 应该放在括号里面，而不是放到括号外边。

Answer 4

select a.DEPTNO,a.DNAME,b.ENAME,b.JOB
from dept a left join emp b
on a.DEPTNO=b.DEPTNO
and b.JOB='Engineer';with AA(ename,dname,salary,deptno)as
(
select b.ENAME,a.DNAME,b.SALARY,a.DEPTNO
from dept a,emp b
where a.DEPTNO=b.DEPTNO
)
select 
ename,deptno,salary,
(select avg(salary) from AA b where b.deptno = AA.deptno group by deptno) as avg_dsalary
from AA
where salary>(select avg(salary) from AA b where b.deptno = AA.deptno group by deptno)
order by salary asc;

Answer 5

提交之后才发现有了where条件其实可以不用group by语句；