【MySQL】34道SQL综合练习详解(员工表、部门表、工资等级表)

文章目录一、34道SQL综合练习二、测试使用的数据表三、创建测试表的SQL语句一、34道SQL综合练习1.查询取得每个部门最高工资的人员信息。select e.ename,t.* from emp e join (select deptno,max(sal) as maxsal from emp group by deptno) t on t.deptno=e.deptno and t.maxsa

智商三岁半i

9820人浏览 · 2021-12-10 17:10:02

智商三岁半i · 2021-12-10 17:10:02 发布

文章目录

一、34道SQL综合练习

1.查询取得每个部门最高工资的人员信息。
select e.ename,t.* from emp e join (select deptno,max(sal) as maxsal from emp group by deptno) t on t.deptno=e.deptno and t.maxsal=e.sal;
在这里插入图片描述
2.查询员工的工资在部门的平均工资以上。
select e.ename,e.sal,t.* from emp e join (select deptno,avg(sal) as avgsal from emp group by deptno) t on t.deptno=e.deptno and t.avgsal<e.sal;

3.查询部门中（所有人）的平均工资等级
select e.deptno,avg(s.grade) avggrade from emp e join salgrade s on e.sal between s.losal and hisal group by e.deptno order by e.deptno;
在这里插入图片描述
4.查询员工的最高工资（不使用max函数，使用两种以上解法）
解法一：使用limit和排序
select ename,sal from emp order by sal desc limit 1;

解法二：使用not in
select ename,sal from emp e where e.sal not in (select x.sal from emp x join emp y on x.sal<y.sal);
在这里插入图片描述
解法三：插询比第二高的员工工资
select ename,sal from emp where sal > (select sal from emp order by sal desc limit 1,1);

（还可以升序排序取最后一个，解法较多。）
5.查询平均工资最高的部门编号
解法一：使用order by 排序后limit
select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1;
在这里插入图片描述
解法二：使用max
select deptno,avg(sal) as avgsal from emp group by deptno having avgsal=(select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t);

6.查询平均工资最高的部门名称
select d.dname,avg(e.sal) as avgsal from emp e join dept d on e.deptno=d.deptno group by e.deptno order by avgsal desc limit 1;
在这里插入图片描述
7.查询平均工资最低的部门的部门名称
select t.*,s.grade from (select d.dname,avg(sal) as avgsal from emp e join dept d on e.deptno=d.deptno group by d.dname) t join salgrade s on t.avgsal between s.losal and s.hisal where s.grade=(select grade from salgrade where (select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1) between losal and hisal);
在这里插入图片描述
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.查询工资排在第6-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(*) from emp e join salgrade s on e.sal between s.losal and hisal group by s.grade;
在这里插入图片描述
13.查询工作地点在DALLAS的员工信息
select e.empno,e.ename,e.job from emp e where deptno = (select deptno from dept d where loc="DALLAS");

14.查询每个员工对对应的领导姓名
select a.ename as "员工",b.ename as "领导" from emp a left join emp b on a.mgr=b.empno;

15.查询入职日期早于其直接上级领导的员工编号、姓名、部门名称
select distinct a.ename as "员工",a.hiredate,b.ename as "领导",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;
在这里插入图片描述
16.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
select e.*,d.dname from emp e right join dept d on e.deptno=d.deptno;

17.查询至少有5个员工的所有部门
select deptno from emp group by deptno having count(*) >= 5;
在这里插入图片描述
18.查询工资比“smith”高的员工信息
select ename,sal from emp where sal > (select sal from emp where ename = "smith");

19.查询所有"CLERK"(办事员)的姓名及其部门名称，部门的人数
select t1.*,t2.deptcount from (select e.ename,d.dname,d.deptno from emp e join dept d on d.deptno=e.deptno where job="clerk") t1 join (select deptno ,count(*) as deptcount from emp group by deptno) t2 on t1.deptno=t2.deptno order by deptno;
在这里插入图片描述
20.查询最低工资大于1500的各种工作及从事此工作的全部雇员人数
select job,count(*) from emp group by job having min(sal)>1500;

21.查询在部门"SALES"<销售部>工作的员工的姓名，假定不知道销售部的部门编号
select ename from emp where deptno = (select deptno from dept where dname="sales");
在这里插入图片描述
22.查询工资高于公司平均工资的所有员工，所在部门，上级领导，雇员的工资等级.
select e.ename "员工",d.dname,l.ename "领导",s.grade from emp e join dept d on e.deptno=d.deptno left join emp l on e.mgr = l.empno join salgrade s on e.sal between losal and hisal where e.sal > (select avg(sal) from emp);
在这里插入图片描述
23.查询与"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";

24.查询工资等于部门30中员工的工资的其他员工的姓名和工资
select ename,sal from emp where sal in (select sal from emp where deptno=30) and deptno<>30;
在这里插入图片描述
25查询工资高于在部门30工作的所有员工的工资的员工姓名工资、部门名称
select e.ename,e.sal,d.dname from emp e join dept d on d.deptno=e.deptno where e.sal > (select max(sal) from emp where deptno=30);

26.查询在每个部门工作的员工数量，平均工资和平均服务期限
select d.deptno,count(e.ename) as ecount,ifnull(avg(e.sal),0) as avgsal,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;
在这里插入图片描述
27.查询所有员工的姓名、部门名称和工资
select e.ename,d.dname,e.sal from emp e join dept d on e.deptno=d.deptno;

28.查询所有部门详细信息和人数
select d.deptno,d.dname,d.loc,count(e.ename) from emp e right join dept d on e.deptno=d.deptno group by d.deptno;
在这里插入图片描述
29.查询各种工作的最低工资以及对应员工的姓名
select e.ename,t.* from emp e join (select job,min(sal) as minsal from emp group by job) t on e.job=t.job and e.sal=t.minsal;

30.查询各个部门“manager”的最低工资
select deptno,min(sal) from emp where job="manager" group by deptno;
在这里插入图片描述
31.查询所有员工的年工资，按年工资最高到低排序
select ename,(sal + ifnull(comm,0)) as yearsal from emp order by yearsal asc;

32.查询员工领导的工资超过3000的员工名称与领导
select a.ename as "员工",b.ename as "领导" from emp a join emp b on a.mgr=b.empno where b.sal > 3000;
在这里插入图片描述
33.查询部门名称中，带’s’字符的部门员工的工资合计、部门人数
select d.deptno,d.dname,d.loc,count(e.ename),ifnull(sum(e.sal),0) as sumsal from emp e right join dept d on e.deptno = d.deptno where d.dname like"%S%" group by d.deptno,d.dname,d.loc;
在这里插入图片描述
34.给任职日期超过30年的员工加新10%
update emp set sal = sal *1.1 where timestampdiff(YEAR,hiredate,now())>30;

二、测试使用的数据表

emp表（员工表）

mysql> select * from 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-04-19 | 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-05-23 | 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 |
+-------+--------+-----------+------+------------+---------+---------+--------+

dept表（部门表）

mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

salgrade表（工资等级表）

mysql> select * from salgrade;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+

三、创建测试表的SQL语句

DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEPT;
DROP TABLE IF EXISTS SALGRADE;

CREATE TABLE DEPT
       (DEPTNO int(2) not null ,
	DNAME VARCHAR(14) ,
	LOC VARCHAR(13),
	primary key (DEPTNO)
	);
	
CREATE TABLE EMP
       (EMPNO int(4)  not null ,
	ENAME VARCHAR(10),
	JOB VARCHAR(9),
	MGR INT(4),
	HIREDATE DATE  DEFAULT NULL,
	SAL DOUBLE(7,2),
	COMM DOUBLE(7,2),
	primary key (EMPNO),
	DEPTNO INT(2) 
	)
	;

CREATE TABLE SALGRADE
      ( GRADE INT,
	LOSAL INT,
	HISAL INT );

INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
10, 'ACCOUNTING', 'NEW YORK'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
20, 'RESEARCH', 'DALLAS'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
30, 'SALES', 'CHICAGO'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
40, 'OPERATIONS', 'BOSTON'); 
commit;
 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7369, 'SMITH', 'CLERK', 7902,  '1980-12-17'
, 800, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7499, 'ALLEN', 'SALESMAN', 7698,  '1981-02-20'
, 1600, 300, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7521, 'WARD', 'SALESMAN', 7698,  '1981-02-22'
, 1250, 500, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7566, 'JONES', 'MANAGER', 7839,  '1981-04-02'
, 2975, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7654, 'MARTIN', 'SALESMAN', 7698,  '1981-09-28'
, 1250, 1400, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7698, 'BLAKE', 'MANAGER', 7839,  '1981-05-01'
, 2850, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7782, 'CLARK', 'MANAGER', 7839,  '1981-06-09'
, 2450, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7788, 'SCOTT', 'ANALYST', 7566,  '1987-04-19'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7839, 'KING', 'PRESIDENT', NULL,  '1981-11-17'
, 5000, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7844, 'TURNER', 'SALESMAN', 7698,  '1981-09-08'
, 1500, 0, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7876, 'ADAMS', 'CLERK', 7788,  '1987-05-23'
, 1100, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7900, 'JAMES', 'CLERK', 7698,  '1981-12-03'
, 950, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7902, 'FORD', 'ANALYST', 7566,  '1981-12-03'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7934, 'MILLER', 'CLERK', 7782,  '1982-01-23'
, 1300, NULL, 10); 
commit;
 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
1, 700, 1200); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
2, 1201, 1400); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
3, 1401, 2000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
4, 2001, 3000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
5, 3001, 9999); 
commit;