Mysql练习题13道（21-33题）

列出薪金高于公司平均薪金的所有员工, 所在部门, 上级领导, 雇员的工资等级

select e.ename '员工',d.dname,t.ename '领导',s.grade from emp e join dept d on e.deptno=d.deptno left join emp t on e.mgr=t.empno join salgrade s on e.sal between s.losal and s.hisal where e.sal > (select avg(sal) from emp);

+--------+------------+--------+-------+
| 员工   | dname      | 领导   | grade |
+--------+------------+--------+-------+
| FORD   | RESEARCH   | JONES  |     4 |
| SCOTT  | RESEARCH   | JONES  |     4 |
| CLARK  | ACCOUNTING | KING   |     4 |
| BLAKE  | SALES      | KING   |     4 |
| JONES  | RESEARCH   | KING   |     4 |
| KING   | ACCOUNTING | NULL   |     5 |
+--------+------------+--------+-------+

列出与"SCOTT" 从事相同工作的所有员工及部门名称

select e.ename,d.dname,e.job from emp e join dept d on e.deptno = d.deptno where e.job = (select job from emp where ename='scott') and e.ename <> 'scott';

+-------+----------+---------+
| ename | dname    | job     |
+-------+----------+---------+
| FORD  | RESEARCH | ANALYST |
+-------+----------+---------+

列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金

select e.ename,e.sal from emp e join (select distinct sal from emp where deptno = 30) t on e.sal = t.sal and e.deptno <> 30;