欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页  >  IT编程

29个查询题（搬运总结）

程序员文章站 2022-03-23 19:02:01
以下题目操作的数据库Scott的三个表，如下：dept部门表±------------±--------------±----------------+| DEPTNO | DNAME | LOC || 部门编号 | 部门名称| 部门位置 |±-------±-------------------±----------------+| 10 | ACCOUNTING | NEW YORK || 20 | RESEARCH...

特谢：
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);

29个查询题（搬运总结）
第二种方法：

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);

29个查询题（搬运总结）
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;

29个查询题（搬运总结）
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;

29个查询题（搬运总结）

第二步：在以上查询结果的基础上，按照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;

29个查询题（搬运总结）

第三步：计算每个员工的平均服务期限
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个查询题（搬运总结）
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;

29个查询题（搬运总结）
另一种方法;

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

上一篇： js利用点击事件做一个简单的计算器

下一篇： 荐 SpringBoot2 整合JTA组件，多数据源事务管理

推荐阅读