sql语句
列出薪金高于平均薪金的所有员工的信息、所在部门名称、上级领导、工资等级
分析过程:
需要的表
|- emp:查询平均薪金(姓名,薪金)
|-dept:部门名称
|-emp:上级领导
|-salgrade:工资等级
关联条件
e.deptno = d.deptno
m.mgr = e.empno
e.sal between losal and hisal
步骤1:查询大于平均薪金的员工信息
SELECT e.empno, e.ename, e.sal, e.hiredate
FROM emp e
WHERE e.sal > ALL(SELECT avg(sal)
FROM emp );
步骤2:查查步骤1中人的部门名称
SELECT e.empno, e.ename, e.sal, e.hiredate, d.dname
FROM emp e, dept d
WHERE e.sal > ALL(SELECT avg(sal)
FROM emp )
AND e.deptno = d.deptno;
步骤3:查查步骤1中人上级领导名字
SELECT e.empno, e.ename, e.sal, e.hiredate, d.dname, m.ename mgr
FROM emp e, dept d, emp m
WHERE e.sal > ALL(SELECT avg(sal)
FROM emp )
AND e.deptno = d.deptno
AND m.empno(+) = e.mgr;
步骤3:查查步骤1中人的工资等级
SELECT e.empno, e.ename, e.sal, e.hiredate, d.dname, m.ename mgr, s.grade salgrade
FROM emp e, dept d, emp m, salgrade s
WHERE e.sal > ALL(SELECT avg(sal)
FROM emp )
AND e.deptno = d.deptno
AND m.empno(+) = e.mgr
AND e.sal between s.losal and s.hisal;
查询结果截图