根据emp,dept,salgrade表进行的sql查询语句(2)

-- 1.取得每个部门最高薪水的人员名称
SELECT
ename AS '姓名'
FROM
emp
JOIN ( SELECT deptno, max( sal ) AS max_sal FROM emp GROUP BY deptno ) AS b ON emp.deptno = b.deptno AND emp.sal = b.max_sal;

-- 2.哪些人的薪水在部门的平均薪水之上
select ename as '姓名' from emp
JOIN ( SELECT deptno, avg( sal ) AS avg_sal FROM emp GROUP BY deptno ) AS b ON emp.deptno = b.deptno AND emp.sal > b.avg_sal;

-- 3.取得部门中（所有人的）平均的薪水等级
select ename,deptno,grade from emp join salgrade on sal between losal and hisal;

-- 4.不准用组函数（Max），取得最高薪水
SELECT * FROM emp ORDER BY sal DESC LIMIT 0,1;

-- 5.取得平均薪水最高的部门的部门编号。
select deptno from emp group by deptno order by avg(sal) desc limit 0,1;

-- 6.取得平均薪水最高的部门的部门名称***
select dname from dept where deptno = (select deptno from emp group by deptno order by avg(sal) desc limit 0,1);

-- 7.求平均薪水的等级最低的部门的部门名称。

select e.deptno ,avg(e.grade),d.dname from (select ename,deptno,grade from emp join salgrade on sal between losal and hisal) as e, dept d where d.deptno=e.deptno group by deptno order by avg(e.grade) asc limit 0,1;

-- 8.取得比普通员工（员工代码没有在mgr字段上出现的）的最高薪水还要高的经理人姓名

select * from emp where job='MANAGER' and sal>(
select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null ));

-- 9.取得薪水最高的前五名员工
select * from emp order by sal desc limit 0,5;

-- 10.取得薪水最高的第六到第十名员工
select * from emp order by sal desc limit 5,10;

-- 11.取得最后入职的5名员工
select * from emp order by hiredate desc limit 0,5;

-- 12.取得每个薪水等级有多少员工
①. select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
②. select s.grade,count() from emp e join salgrade s on e.sal between s.losal and s.hisal group by s.grade;
③. select s.grade,count() from emp e join salgrade s on e.sal between s.losal and s.hisal group by s.grade;

-- 13.列出所有员工及直接上级的姓名
select e.ename '员工姓名',p.ename '领导姓名' from emp e,emp p where e.mgr = p.empno;

-- 14.列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称

1. 求出受雇日期遭遇上级的员工编号和姓名
   SELECT E.empno,E.ename FROM EMP E,EMP M WHERE E.hiredate<M.hiredate AND E.MGR=M.empno
   2.列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
   SELECT E.empno,E.ename,DNAME FROM EMP E,EMP M,DEPT WHERE E.hiredate<M.hiredate AND E.MGR=M.empno AND E.deptno=dept.DEPTNO

-- 15.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门
select d.dname ,e.* from dept d left join emp e on d.deptno=e.deptno;

-- 16.列出至少有一个员工的所有部门 ，
select distinct d.* from dept d,emp as p where d.deptno = p.deptno;

-- 17.列出薪金比"SMITH"多的所有员工信息.
select * from emp where sal>(select sal from emp where ename='SMITH');

-- 18.列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数.
SELECT COUNT() S,DEPTNO FROM EMP GROUP BY DEPTNO;
select e.ename,e.deptno,d.dname,n.S from emp e,dept d,(SELECT COUNT() S,DEPTNO FROM EMP GROUP BY DEPTNO) n where e.deptno=d.deptno and e.deptno=n.deptno and job='CLERK'

-- 19.列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数.
select distinct e.job ,n.s from emp e ,(select count(*) s, job from emp group by job) n where e.job=n.job and e.sal>1500;

-- 20.列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部门编号.
select ename from emp where deptno = (select deptno from dept where dname ='SALeS');

-- 21.列出与"SCOTT"从事相同工作的所有员工及部门名称. 两种方法
-- 1.
select * from emp,dept where emp.deptno=dept.deptno and emp.job=(select job from emp where ename='SCOTT');
-- 2.
select * from emp left join dept on emp.deptno=dept.deptno where emp.job=(select job from emp where ename='SCOTT');
--3.
select * from emp right join dept on emp.deptno=dept.deptno where emp.job=(select job from emp where ename='SCOTT');

-- 22.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金.
SELECT SAL FROM EMP WHERE DEPTNO=30
SELECT ENAME,SAL FROM EMP WHERE SAL IN (SELECT SAL FROM EMP WHERE DEPTNO=30) AND DEPTNO!=30

-- 23.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金.部门名称. 三种方法
select e.ename,e.sal,d.dname from emp e,dept d where e.sal >(select max(sal) from emp where deptno=30)and e.deptno=d.deptno;

-- 24.列出在每个部门工作的员工数量,平均工资和平均服务期限.
select e.deptno, m.s,round(avg(sal)) '平均工资', round(avg(datediff(now(),hiredate)/365)) '平均服务年限'from emp e,(select count(*) s,deptno from emp group by deptno) m where e.deptno=m.deptno group by deptno;

-- 25.列出所有员工的姓名、部门名称和工资。
select e.ename '姓名', d.dname '部门名称', e.sal '工资' from emp e,dept d where e.deptno=d.deptno;

-- 26.列出所有部门的详细信息和人数
select d.,if(isnull(e.cou),0,e.cou) '人数' from (select count() cou,deptno from emp group by deptno) e right join dept d on (e.deptno=d.deptno);
-- 27.列出各种工作的最低工资及从事此工作的雇员姓名
select job,min(sal),ename from emp group by job;

-- 28。列出各个部门的MANAGER(经理)的最低薪金
select job,ename,min(sal) from emp where job='MANAGER' group by deptno;

-- 29.列出所有员工的年工资,按年薪从低到高排序
select ename as '姓名', (sal12)+(ifnull(comm,0)12) as '年薪' from emp ORDER BY 年薪 asc;

-- 30.查出某个员工的上级主管，并要求出这些主管中的薪水超过3000.
select e.ename '员工姓名', m.ename '员工上级姓名', m.sal from emp e,emp m where e.mgr=m.empno and m.sal>3000;

-- 31.求出部门名称中,带'S'字符的部门员工的工资合计、部门人数.
select deptno from dept where dname like '%S%';
select s.dname,sum(sal) ,count(empno) from (select deptno,dname from dept where dname like '%S%') s left join emp e on e.deptno=s.deptno group by e.deptno;

-- 32.给任职日期超过25年的员工加薪10%.
select ename, sal*1.1 from emp where extract(year from now())-25 >extract(year from hiredate);