数据库教程:29个查询题（搬运总结）

特谢：
https://blog.csdn.net/qq_42764468/article/details/98072747

以下题目操作的数据库Scott的三个表，如下：
dept部门表
±————±————–±—————-+
| DEPTNO | DNAME | LOC |
| 部门编号 | 部门名称| 部门位置 |
±——-±——————-±—————-+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
±——-±——————±—————-+

emp雇员信息表
±———-±———–±———–±——-±—————–±——–±————±————+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
|员工编号|员工姓名 | 职位 | 经理 | 入职时间 | 工资 | 奖金 | 部门编号 |
±——±———-±————-±——-±—————±———±———-±——-+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
±——±———–±————±——-±—————±————±———±——-+
salgrade工资等级表
±——±——±——+
| GRADE | LOSAL | HISAL |
| 等级 |最低工资|最高工资|
±——±——±——-+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
±——±——±——+

1.取得平均薪水最高的部门的部门编号
第一种方法：

select * from (select avg(sal) as avg_sal,deptno from emp group by deptno) as T2 where avg_sal in (select max(avg_sal) as avg_sal from (select avg(sal) as avg_sal,deptno from emp group by deptno) as T1);  

第二种方法：

select deptno from emp group by deptno having  avg(sal) = (select max(avg_sal) from (select avg(sal) avg_sal from emp group by deptno)t ) 

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

select d.dname,avg(e.sal) as avgsal from emp e join dept d on e.deptno=d.deptno group by d.dname having avg(e.sal)=(select avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1); 

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

第一步：取得每一个部门的平均薪水 select deptno,avg(sal) avgsal from emp group by deptno; 

第二步：取得平均薪水的最大值 mysql> select avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1; 

第三步：将第一步与第二步联合（单表查询时使用having或者where） 	将第二步计算的结果子查询当做一个条件 	mysql> select deptno,avg(sal) avgsal     -> from emp     -> group by deptno     -> having     -> avgsal = (select avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1); 

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

第一步：先找出所有的领导，然后用not in排除领导即为普通员工 mysql> select distinct  a.empno,a.ename from emp a join emp b where a.empno=b.mgr; mysql> select ename,sal from emp where ename not in( select distinct a.ename from emp a jo in emp b where a.empno=b.mgr); 

第二步：找出普通员工的最高薪水 mysql> select ename,max(sal)  from emp where ename not in( select distinct a.ename from em p a join emp b where a.empno=b.mgr) ; 

第三步：找出比普通员工最高薪水高的领导员工名称 mysql> select ename,sal from emp where sal>( select max(sal)  from emp where ename not in(  select distinct a.ename from emp a join emp b where a.empno=b.mgr)); 

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

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

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

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

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

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

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

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

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

mysql> select a.ename empname,b.ename leadername from emp a join emp b on a.mgr=b.empno; 

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

第一步：列出所有受雇日期早于其直接上级的所有员工的编号，姓名 mysql> select a.empno,a.ename,a.hiredate,b.empno,b.ename,b.hiredate from emp a join emp b on a.mgr=b.empno  where a.hiredate<b.hiredate; 

第二步：列出所有受雇日期早于其直接上级的所有员工的编号，姓名，部门名称 mysql> select a.empno,a.ename,d.dname from emp a join emp b on a.mgr=b.empno join dept d o n a.deptno=d.deptno where a.hiredate<b.hiredate; 

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

mysql> select e.*,d.dname from emp e right join dept d on e.deptno = d.deptno; 

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

既然有数字就应该想到count()函数，先分组，分组之后再过滤 mysql> select deptno  from emp  group by deptno having count(*)>=5; mysql> select d.*  from emp e  join dept d  on d.deptno=e.deptno   group by deptno having count(*)>=5; 

13.列出薪金比”SMITH”多的所有员工信息.

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

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

mysql> select e.ename,d.dname ,e.deptno from emp e join dept d on e.deptno =d.deptno  wher e e.job="CLERK"; mysql> select deptno,count(*)  totalemp from emp group by deptno; mysql> select t.ename,t.dname,t1.totalemp from (select e.ename,d.dname ,e.deptno from emp e join dept d on e.deptno =d.deptno  where e.job="CLERK") t join (select deptno,count(*) totalemp from emp group by deptno) t1 on t.deptno=t1.deptno; 

15.列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数
注意：这里要求部门所有人中，最低薪金大于1500，然后查询雇员人数
第一种

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

第二种

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

错误理解示范：

select t.job,count(t.job) from (select * from emp where sal>1500) t group by t.job; 

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

mysql> select deptno from dept where dname="SALES"; mysql> select ename from emp where deptno =( select deptno from dept where dname="SALES"); 

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

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

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

mysql> select job from emp where ename="SCOTT"; mysql> select ename from emp where job =(select job from emp where ename="SCOTT"); mysql> select e.ename,d.dname   from emp e join dept d on e.deptno=d.deptno   where  job =(select job from emp where ename="SCOTT"); 

mysql> select e.ename,d.dname  from emp e join dept d on e.deptno=d.deptno  where job =(select job from emp where ename="SCOTT") and e.ename!="SCOTT"; 

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

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

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

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

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

select deptno,count(*) empcount,avg(sal) avgsal from emp group by deptno; 

第二步：在以上查询结果的基础上，按照d.deptno分组，按照e.ename计数 列出每个部门工作的员工数量，平均工资 select d.deptno,count(e.ename) countname,ifnull(avg(e.sal),0) avgsal from emp e right join dept d  on e.deptno=d.deptno group by d.deptno; 

第三步：计算每个员工的平均服务期限 mysql> select to_days(now()); mysql> select to_days(hiredate) from emp; mysql> select (to_days(now())-to_days(hiredate))/365 from emp; 

第四步：在第二步的基础上 列出每个部门工作的员工数量，平均工资和平均服务期限 mysql> select     -> d.deptno,     -> count(e.ename) countemp,     -> ifnull(avg(e.sal),0) avgsal,     -> ifnull( avg( (to_days(now())-to_days(hiredate))/365),0) avgtime     -> from emp e     -> right join dept d     -> on e.deptno=d.deptno     -> group by d.deptno; 

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

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

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

mysql> select d.*,count(e.ename)  from  emp e right join dept d  on e.deptno=d.deptno group by d.deptno; 

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

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

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

select deptno,min(sal) minsal from emp  where job="MANAGER" group by deptno; 

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

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

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

mysql> select a.ename empname,b.ename leadername     -> from emp a join emp b on a.mgr= b.empno     -> where b.sal>3000; 

28.求出部门名称中,带’S’字符的部门员工的工资合计、部门人数
写法一：

mysql> select     ->  d.dname,     ->  ifnull(sum(e.sal),0) sumsal,     ->  count(e.ename) countemp     ->  from emp e     ->  right join dept d     ->  on e.deptno=d.deptno     ->  where d.dname like '%s%'     ->  group by d.deptno; 

写法二：

mysql> select d.dname,sum(e.sal),count(e.empno)     -> from emp e     -> join dept d     -> on e.deptno=d.deptno     -> group by d.dname     -> having d.dname like '%S%'; 

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

create table emp_bak as selecm emp;  update emp_bak set sal=sal*1.1 where (to_days(now())-to_days(hiredate))/365>30; 

另一种方法;

select ename,(sal*1.1) salary_sal,timestampdiff(year,hiredate,now()) from emp where timestampdiff(year,hiredate,now()) >30; 

数据库技术:29个查询题（搬运总结）地址：https://blog.csdn.net/weixin_41311528/article/details/107280611

需要了解更多数据库技术:29个查询题（搬运总结），都可以关注数据库技术分享栏目—计算机技术网(www.ctvol.com)!