33道数据库题目

准备三张表

1. emp–员工表
   
2. dept–部门表
   
3. salgrade–工资等级表
   

题目

1. 取得每个部门最高薪水的人员名称
   (方法1)

select ename, sal, deptno from emp where sal in (select max(sal) from emp group by deptno);

(方法2）

select e.ename, e.sal, e.deptno from emp e join (select deptno, max(sal) max_sal from emp group by deptno) t on e.deptno = t.deptno where e.sal = t.max_sal;

2. 哪些人的薪水在部门的平均薪水之上

select e.ename, e.sal, e.deptno from emp e join (select avg(sal) avg_sal, deptno from emp group by deptno) t on e.deptno = t.deptno where e.sal > t.avg_sal;

3. 取得部门中（所有人的）平均的薪水等级

select e.deptno, avg(grade) from emp e join salgrade s on e.sal between s.losal and hisal group by deptno;

4. 不准用组函数（Max），取得最高薪水（给出两种解决方案）
   (方法1- - -order by & limit)

select sal from emp order by sal desc limit 1;

(方法2- - -自连接)

select sal from emp where sal not in (select distinct a.sal from emp a join emp b on a.sal < b.sal);

5. 取得平均薪水最高的部门的部门编号（至少给出两种解决方案）
   (方法1- - -having)

select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal = (select max(t.avgsal) from (select avg(sal) avgsal from emp group by deptno) t);

(方法2- - -order by & limit)

select deptno, avg(sal) avg_sal from emp group by deptno order by avg_sal desc limit 1;

6. 取得平均薪水最高的部门的部门名称

select a.deptno, d.dname, a.avg_sal from (select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal = (select max(t.avgsal) from (select avg(sal) avgsal from emp group by deptno) t)) a join dept d on a.deptno=d.deptno;

或者

select d.dname, avg(e.sal) avg_sal from emp e join dept d on e.deptno=d.deptno group by d.dname having avg_sal = (select max(t.avgsal) from (select avg(sal) avgsal from emp group by deptno) t);

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

select t.*, s.grade from (select d.dname, avg(sal) avg_sal from emp e join dept d on e.deptno = d.deptno group by d.dname) t join salgrade s on t.avg_sal between s.losal and s.hisal where s.grade = (select grade from salgrade where (select avg(sal) avgsal from emp group by deptno order by avgsal asc limit 1) between losal and hisal);

(方法2- - -忽略等级，平均工资最低等级也就最低)

select dname, avg_sal from (select d.dname, avg(sal) avg_sal from emp e join dept d on e.deptno = d.deptno group by d.dname) t where t.avg_sal = (select min(avgsal) from (select avg(sal) avgsal from emp group by deptno) a);

(方法3- - -忽略等级，平均工资最低等级也就最低)

select dname, avg_sal from (select d.dname, avg(sal) avg_sal from emp e join dept d on e.deptno = d.deptno group by d.dname) t where t.avg_sal = (select avg(sal) avgsal from emp group by deptno order by avgsal asc limit 1);

8. 取得比普通员工(员工代码没有在 mgr 字段上出现的)的最高薪水还要高的
   领导人姓名

select ename, sal from emp where sal > (select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null));

9. 取得薪水最高的前五名员工

select ename, sal from emp order by sal desc limit 5;

10. 取得薪水最高的第六到第十名员工

select ename, sal from emp order by sal desc limit 5,5;

11. 取得最后入职的 5 名员工

select ename, hiredate from emp order by hiredate desc limit 5;

12. 取得每个薪水等级有多少员工

select s.grade, count(empno) from emp e join salgrade s on e.sal between s.losal and s.hisal group by s.grade order by s.grade asc;

13. 列出所有员工及领导的姓名

select a.ename, ifnull(b.ename,'没有') mgr from emp a left join emp b on a.mgr = b.empno;

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

select a.empno, a.ename, a.hiredate, b.ename, b.hiredate, d.dname from emp a join emp b on a.mgr = b.empno join dept d on a.deptno = d.deptno where a.hiredate < b.hiredate;

15. 列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门

select d.dname, e.* from dept d left join emp e on d.deptno = e.deptno;

16. 列出至少有 5 个员工的所有部门

select d.dname from emp e join dept d on e.deptno = d.deptno group by d.dname having count(e.empno) >= 5;

17. 列出薪金比"SMITH"多的所有员工信息

select * from emp where sal > (select sal from emp where ename = 'smith');

18. 列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数

select e.ename, d.dname, b.count_emp from emp e join dept d on e.deptno = d.deptno join (select deptno, count(empno) count_emp from emp group by deptno) b on e.deptno = b.deptno where e.job = 'clerk';

19. 列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数

select job, count(*), min(sal) from emp group by job having min(sal) > 1500;

20. 列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部
    门编号

select ename from emp where deptno = (select deptno from dept where dname='sales');

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

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

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

select e.ename, e.job, d.dname 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';

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

select ename, sal from emp where sal in (select sal from emp where deptno = 30) and deptno <> 30;

24. 列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金.部门名
    称

select ename, sal, dname from emp e join dept d on e.deptno = d.deptno where sal > (select max(sal) from emp where deptno = 30);

25. 列出在每个部门工作的员工数量,平均工资和平均服务期限

select d.deptno, count(e.ename) ecount, ifnull(avg(e.sal), 0) as avg_sal, ifnull(avg(timestampdiff(YEAR, hiredate, now())), 0) as avgtime from emp e right join dept d on e.deptno = d.deptno group by d.deptno;

26. 列出所有员工的姓名、部门名称和工资

select e.ename, d.dname, e.sal from emp e join dept d on e.deptno = d.deptno;

27. 列出所有部门的详细信息和人数

select d.deptno, d.dname, d.loc, count(e.empno) from dept d left join emp e on d.deptno = e.deptno group by d.deptno,d.dname,d.loc;

28. 列出各种工作的最低工资及从事此工作的雇员姓名

select e.job, e.ename, e.sal from emp e join (select job, min(sal) min_sal from emp group by job) t on e.job = t.job and e.sal = t.min_sal;

29. 列出各个部门的 MANAGER(领导)的最低薪金

select deptno, min(sal) from emp where job = 'manager' group by deptno;

30. 列出所有员工的年工资,按年薪从低到高排序

select ename, (sal+ifnull(comm, 0)) * 12 as balance from emp order by balance asc;

31. 求出员工领导的薪水超过 3000 的员工名称与领导名称

select e.ename emp, a.ename leader from emp e join emp a on e.mgr = a.empno where a.sal > 3000;

32. 求出部门名称中,带’S’字符的部门员工的工资合计、部门人数

select d.dname, count(e.sal), count(e.empno) from dept d left join emp e on d.deptno = e.deptno where d.dname like '%s%' group by d.dname;

33. 给任职日期超过 30 年的员工加薪 10%

update emp set sal = sal * 1.1 where timestampdiff(YEAR, hiredate, now()) > 30;