1. 顺序不能变
select top 3 "E".ename, "E".sal, "S".grade, "D".dname
from emp "E"
join dept "D"
on "E".deptno = "D".deptno
join SALGRADE "S"
on "E".sal between "S".LOSAL and "S".HISAL
where "E".ename not like '%A%'
order by "E".sal desc
2. 查找每个部门的编号 该部门所有员工的平均工资 平均工资的等级
select "T".deptno, "T"."avg_sal" as "平均工资", "S".GRADE as "工资等级"
from (
select deptno, AVG(sal) as "avg_sal"
from emp
group by deptno
) "T"
join SALGRADE "S"
on "T".avg_sal between "S".LOSAL and "S".HISAL
等价于
select "T".deptno, "T"."avg_sal" as "平均工资", "S".GRADE as "工资等级"
from SALGRADE "S"
join (
select deptno, AVG(sal) as "avg_sal"
from emp
group by deptno
) "T"
on "T".avg_sal between "S".LOSAL and "S".HISAL
或等价于
select "T".deptno, "T"."avg_sal" as "平均工资", "S".GRADE as "工资等级"
from SALGRADE "S", (
select deptno, AVG(sal) as "avg_sal"
from emp
group by deptno
) "T"
where "T".avg_sal between "S".LOSAL and "S".HISAL
