Mysql34题

最新推荐文章于 2022-07-27 16:40:34 发布

让时间来沉淀吧

最新推荐文章于 2022-07-27 16:40:34 发布

阅读量340

点赞数

分类专栏： MYSQL

本文链接：https://blog.csdn.net/qq_34488912/article/details/107729246

版权

MYSQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

用到的表

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;

emp员工表：
在这里插入图片描述
dept部门表:

salgrade工资等级表：

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

SELECT e.DEPTNO,e.ENAME,e.SAL
from emp e
JOIN
(select max(e.SAL) as maxsal,e.DEPTNO 
from emp as e 
GROUP BY DEPTNO) as t
on
	t.DEPTNO = e.DEPTNO
where e.SAL=t.maxsal
ORDER BY e.DEPTNO

在这里插入图片描述
思路：第一步先查出每个部门的最高薪水，作为中间表t，然后查询员工姓名，员工部门号，薪水，emp e表join t表，连接条件是t.DEPTNO = e.DEPTNO ，筛选条件e.SAL=t.maxsal。
2.哪些人的薪水在部门平均薪水之上

SELECT e.DEPTNO,e.ENAME,e.SAL,t.avgsal
from emp e
JOIN
(select AVG(e.SAL) as avgsal,e.DEPTNO 
from emp as e 
GROUP BY DEPTNO) as t
on
	t.DEPTNO = e.DEPTNO
where e.SAL>t.avgsal
ORDER BY e.SAL

在这里插入图片描述
思路：第一步先查出每个部门的平均薪水，作为中间表t，然后查询员工姓名，员工部门号，薪水，部门平均薪水，emp e表join t表，连接条件是t.DEPTNO = e.DEPTNO ，筛选条件e.SAL>t.avgsal。

3.取得部门中（所有人的）平均薪水等级
题目意思：取得每个部门中所有人的平均薪水等级

SELECT avg(t.egrade),t.DEPTNO
from
(SELECT g.GRADE as egrade,e.SAL,e.DEPTNO
from salgrade as g 
JOIN emp as e
on e.SAL BETWEEN g.LOSAL AND g.HISAL) as t
GROUP BY t.DEPTNO

在这里插入图片描述
思路：先求出每个人的工资等级，再按部门分组求出平均等级。

4.不准用组函数（MAX），取得最高薪水（给出两种解决方案）
第一种方法

select SAL FROM emp ORDER BY SAL DESC LIMIT 1;

在这里插入图片描述

第二种方法

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

5.取得平均薪水最高的部门的部门编号

select t.DEPTNO
from
(select avg(sal) as avgsal,e.DEPTNO from emp as e GROUP BY e.DEPTNO) as t
ORDER BY avgsal desc LIMIT 1

在这里插入图片描述
思路：先按部门分组查出各组的平均成绩，作为中间表，然后该表按照平均成绩降序取最高的平均成绩的编号。

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

select d.DNAME 
FROM dept d
join 
(select t.DEPTNO
from
(select avg(sal) as avgsal,e.DEPTNO from emp as e GROUP BY e.DEPTNO) as t
ORDER BY avgsal desc LIMIT 1)as s
on d.DEPTNO=s.DEPTNO

在这里插入图片描述
思路：在5的基础上，联合dept表，连接条件d.DEPTNO=s.DEPTNO查出部门名称。

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

select DNAME from dept as d
join (select avg(sal) as avgsal ,DEPTNO from emp group by DEPTNO ORDER BY avgsal asc limit 1)as t
where d.DEPTNO=t.DEPTNO

在这里插入图片描述
思路：
第一步，求平均薪水最低的部门ID

select avg(sal) as avgsal ,DEPTNO from emp group by DEPTNO ORDER BY avgsal asc limit 1;

第二步，求平均薪水的等级最低的部门的部门名称
select DNAME from dept as d
join (select avg(sal) as avgsal ,DEPTNO from emp group by DEPTNO ORDER BY avgsal asc limit 1)as t
where d.DEPTNO=t.DEPTNO

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

select e.ENAME from emp as e
where e.EMPNO in (select DISTINCT mgr from emp where mgr is not NULL) 
and e.SAL>(select max(sal) as maxsal
from emp as e
where e.EMPNO not in(select DISTINCT mgr from emp where mgr is not NULL)
)

在这里插入图片描述
思路：
第一步，查询出非普通员工的员工号
select DISTINCT mgr from emp where mgr is not NULL

第二步，查出普通员工的最高工资
select max(sal) as maxsal
from emp as e
where e.EMPNO not in(select DISTINCT mgr from emp where mgr is not NULL);

第三步，查出比普通员工最高工资高的经理人姓名
select e.ENAME from emp as e
where e.EMPNO in (select DISTINCT mgr from emp where mgr is not NULL)
and e.SAL>(select max(sal) as maxsal
from emp as e
where e.EMPNO not in(select DISTINCT mgr from emp where mgr is not NULL)
)

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

SELECT SAL ,EMPNO,ENAME from emp ORDER BY SAL DESC LIMIT 0,5;

在这里插入图片描述

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

SELECT SAL ,EMPNO,ENAME from emp ORDER BY SAL DESC LIMIT 5,5;

在这里插入图片描述
11.取得最后入职的5名员工

SELECT * FROM `emp` ORDER BY HIREDATE desc LIMIT 5;

在这里插入图片描述

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

select s.GRADE ,count(*)as num from emp as e
join salgrade as s
on e.SAL BETWEEN s.LOSAL and s.HISAL
GROUP BY GRADE

在这里插入图片描述
思路：先查出每个员工的工资等级，然后按工资等级分组

14.列出所有员工及领导的名字

select e1.ENAME as yuangong,e2.ENAME as lingdao from emp as e1
left join emp as e2
on e1.mgr=e2.EMPNO

在这里插入图片描述

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

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

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

18.列出薪水比“SMITH”多的所有员工信息

19.列出所有“CLERK”（办事员）的姓名及其部门名称，部门人数

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

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

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

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

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

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

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

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

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

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

30.列出各个部门MANAGER的最低薪金

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

32.求出员工领导的薪水超过3000的员工名称和领导名称

33.求部门名称中带“S”字符的部门员工的工资合计、部门人数

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

让时间来沉淀吧

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql34题

用到的表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 ,
复制链接

扫一扫