-- 1、查询emp中最高薪水人的名字
select max(SAL) from EMP;
select ENAME from EMP where
SAL=(select max(SAL) from EMP);
--
-- 2、查询每个部门中的最高薪水人的名字和所在的部门编号
select max(SAL),DEPTNO from EMP
GROUP BY DEPTNO;
select t1.ename,t1.deptno from EMP t1
INNER JOIN(select max(SAL) max_sal,DEPTNO from EMP group by deptno)t2
on t1.deptno=t2.deptno and sal=t2.max_sal
-- 3、查询薪水在平均薪水之上的雇员的名字
select AVG(SAL) from EMP;
select ENAME from EMP where SAL>(select AVG(SAL) from EMP);
-- 4、查询雇员的名字和所在部门的名字
select t1.ENAME,t2.DNAME from EMP t1,DEPT t2
where t1.deptno=t2.deptno;
-- 5、查询薪水在在本部门平均薪水之上的雇员的名字
select t1.ENAME,t1.deptno from EMP t1
INNER JOIN (select avg(sal) avg_sal,deptno from EMP GROUP BY deptno)t2
on t1.deptno=t2.deptno and sal>t2.avg_sal;
-- 6、查询每个员工的薪水的等级,员工的姓名
select * from EMP;
select * from SALGRADE;
select t1.ename,t2.grade from EMP t1,SALGRADE t2
where t1.sal BETWEEN t2.LOSAL and t2.HISAL;
-- 7、查询每个部门的平均薪水的等级,部门的编号
select avg(sal),deptno from EMP GROUP BY deptno;
select t1.grade,t2.deptno from SALGRADE t1,(select avg(sal) avg_sal,deptno from EMP GROUP BY deptno) t2
where t2.avg_sal BETWEEN t1.losal and t1.hisal;
-- 8、查询雇员的名字，所在部门的名字，工资的等级
select t1.ENAME,t2.DNAME,t3.grade from EMP t1,DEPT t2,SALGRADE t3
where t1.deptno=t2.deptno
and t1.sal BETWEEN t3.LOSAL and t3.HISAL;
-- 9、查询雇员的名字和其经理的名字
select t1.ENAME,t2.JOB from EMP t1,EMP t2
where t1.EMPNO=t2.MGR;
-- 10、查询雇员中是经理人的名字
select distinct mgr from EMP;
select ename from EMP where empno in(select distinct mgr from EMP)
-- 14、查询薪水最高的前5名雇员编号，名称，薪水
select empno,ename,sal from EMP
order by sal desc
limit 5;
-- 15、查询薪水最高的第6名到第10名雇员编号，名称，薪水
select empno,ename,sal from EMP
order by sal desc
limit 5,5;
