关于mysql的练习习题 34道

结果都可输出，可自行优化，本人所写较为繁琐,如果输出结果不同请相信自己，本文也并非是最准确的答案

1.数据准备

create DATABASE test3

-- 创建部门表
create table dept(
	deptno int PRIMARY KEY,-- 部门编号
	dname VARCHAR(14),-- 部门名称
	loc VARCHAR(13)  -- 部门地址
);

insert into dept VALUES(10,'accounting','new york'),
                       (20,'research','dallas'),
											 (30,'sales','chicago'),
											 (40,'operations','boston');

 

-- 创建员工表
create table emp(
	empno int PRIMARY KEY,-- 员工编号
	ename VARCHAR(10),-- 员工名称
	job VARCHAR(9),  -- 员工工作
	mgr int,-- 员工直属领导编号
	hiredate date,-- 员工入职时间
	sal double,-- 薪资
	comm double,-- 奖金
	deptno int -- 对应dept表的外键
);

-- 添加部门和员工之间的主外键关系
alter table emp add constraint foreign key emp(deptno) references dept(deptno);

insert into emp VALUES(7369,'smith','clerk',7092,'1980-12-17',800,null,20);
insert into emp VALUES(7499,'allen','salesman',7698,'1981-02-20',800,300,30);
insert into emp VALUES(7521,'ward','salesman',7698,'1981-02-22',800,500,30);
insert into emp VALUES(7566,'jones','manager',7839,'1981-04-02',800,null,20);
insert into emp VALUES(7654,'martin','salesman',7698,'1981-09-28',800,1400,30);
insert into emp VALUES(7698,'blake','manager',7839,'1981-05-01',800,null,30);
insert into emp VALUES(7782,'clark','manager',7839,'1981-06-09',800,null,10);
insert into emp VALUES(7788,'scott','analyst',7566,'1981-07-03',800,null,20);
insert into emp VALUES(7839,'king','president',null,'1981-11-17',800,null,10);
insert into emp VALUES(7844,'turner','salesman',7698,'1981-09-08',800,0,30);
insert into emp VALUES(7876,'adams','clerk',7788,'1981-07-13',800,null,20);
insert into emp VALUES(7900,'james','clerk',7698,'1981-12-03',800,null,30);
insert into emp VALUES(7902,'ford','analyst',7566,'1981-12-03',800,null,20);
insert into emp VALUES(7934,'miller','clerk',7788,'1981-01-23',800,null,10);

-- 创建工资等级表
create table salgrade(
	grade int,-- 等级
	losal double,-- 最低工资
	hisal double -- 最高工资
);

insert into salgrade VALUES(1,700,1200);
insert into salgrade VALUES(2,1201,1400);
insert into salgrade VALUES(3,1401,2000);
insert into salgrade VALUES(4,2001,3000);
insert into salgrade VALUES(5,3001,9999);

 2.开始处理

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

-- 1 取得每个部门的最高薪水的人员名称
select deptno,max(sal) from emp GROUP BY deptno

select a.ename,a.deptno,a.sal from emp a
join (select deptno,max(sal) maxsal from emp GROUP BY deptno) b 
on a.deptno=b.deptno and a.sal =b.maxsal 

 

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

-- 2 哪些人的薪水在部门的平均薪水之上
select deptno,ROUND(AVG(sal)) avgsal from emp GROUP BY deptno

select ename,a.sal,a.deptno from emp a join (select deptno,ROUND(AVG(sal)) avgsal from emp GROUP BY deptno) b on a.deptno=b.deptno and a.sal >b.avgsal

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

-- 3 取得部门中（所有人的）平均的薪水等级
select a.deptno,round(avg(grade)) from emp a
join salgrade b on  a.sal>b.losal and a.sal<b.hisal
GROUP BY deptno

 -- 4 不准用组函数（max），取得最高薪水（给出两种解决方案）

-- 4 不准用组函数（max），取得最高薪水（给出两种解决方案）
select ename,sal from emp ORDER BY sal desc LIMIT 1
select ename,sal from (select empno,ename,sal,DENSE_RANK()over(ORDER BY sal desc) r from emp) a where a.r=1

 

 -- 5 取得平均薪水最高的部门部门编号（至少给出两种方案）

-- 5 取得平均薪水最高的部门部门编号（至少给出两种方案）
select deptno,round(AVG(sal)) avgsal from emp GROUP BY deptno ORDER BY avgsal desc limit 1

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

 

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

-- 6 取得平均薪水最高的部门的部门名称
select a.deptno,dname from dept a
join (select deptno,round(avg(sal)) avgsal from emp group by deptno having avgsal = (select max(t.avgsal) from (select deptno,round(avg(sal)) avgsal from emp group by deptno) t)) b on a.deptno=b.deptno

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

-- 7 求平均薪水的等级最低的部门的部门名称
select a.*,grade from dept a join (select distinct deptno,grade from emp a join salgrade b on a.sal>b.losal and a.sal<b.hisal where grade =1) b on a.deptno=b.deptno

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

-- 8 取得比普通员工（员工代码没有在mgr字段上出现的）的最高薪水还要高的领导人姓名
select empno from emp where empno not in(select DISTINCT mgr from emp where mgr is not null)

select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null)

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

 

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

-- 9 取得薪水最高的前五名员工
select empno,ename,sal,a.r from (select empno,ename,sal,ROW_NUMBER()over(ORDER BY sal desc) r from emp) a where a.r<=5

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

-- 10 取得薪水最高的第六名到第十名员工
select empno,ename,sal,a.r from (select empno,ename,sal,ROW_NUMBER()over(ORDER BY sal desc) r from emp) a where a.r BETWEEN 6 and 10

-- 11 取得最后入职的五名员工 

-- 11 取得最后入职的五名员工
select empno,ename,hiredate,a.r from (select empno,ename,hiredate,ROW_NUMBER()over(ORDER BY hiredate desc) r from emp) a where a.r<=5

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

-- 12 取得每个薪水等级有多少员工
select grade,count(*) from emp a
join salgrade b on  a.sal>b.losal and a.sal<b.hisal
GROUP BY grade

 -- 14 列出所有员工及领导姓名

-- 14 列出所有员工及领导姓名
select a.ename 员工,b.ename 领导 from emp a join emp b on a.mgr=b.empno

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

-- 15 列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称
select 
a.empno 员工编号,a.ename 员工,b.ename 领导,a.hiredate 员工入职,b.hiredate 领导入职,c.dname 部门名
from emp a 
join emp b on a.mgr=b.empno 
join dept c on a.deptno = c.deptno
where DATE(a.hiredate)<DATE(b.hiredate)

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

-- 16 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
select deptno,empno,ename,job from emp ORDER BY deptno
select b.deptno,dname,empno,ename,job from emp a RIGHT JOIN dept b on a.deptno=b.deptno
select * from emp a right join dept b on a.deptno=b.deptno

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

-- 17 列出至少有5个员工的所有部门
select deptno,count(*) from emp GROUP BY deptno HAVING count(*)>4

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

-- 18 列出薪金比“SMITH”多的所有员工信息
select * from emp where sal>(select sal from emp where ename='SMITH')

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

-- 19 列出所有“CLERK”（办事员）的姓名及其部门名称，部门人数
select a.ename,b.dname,c.countdeptno from emp a join dept b join (select count(*) countdeptno,deptno from emp group by deptno) c on a.deptno=b.deptno and c.deptno=a.deptno where a.job ='CLERK';

 -- 20 列出最低薪资大于1500的各种工作，以及从事该工作的全部雇员人数

-- 20 列出最低薪资大于1500的各种工作，以及从事该工作的全部雇员人数
select job,count(*) from emp group by job having min(sal)>1500;

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

-- 21 列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号
select ename,dname from emp a
join dept b on a.deptno =b.deptno
where dname='SALES'  #知道部门编号的情况

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

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

-- 22 列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，雇员的工资等级
select * from emp where sal > (select round(AVG(sal)) from emp)

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

-- 23 列出与“SCOTT”从事相同工作的所有员工及部门名称
select ename,dname from emp a join dept b on a.deptno=b.deptno where job =(select job from emp where ename ='scott') and ename <> 'scott'

 

-- 24 列出薪资等于（30部门中的员工的薪资）的其他员工的姓名和薪金 

-- 24 列出薪资等于（30部门中的员工的薪资）的其他员工的姓名和薪金
select distinct ename,sal from emp where sal in(select distinct sal from emp where deptno = 30) and deptno!=30

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

-- 25 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金，部门名称
select distinct ename,sal from emp where sal > all(select distinct sal from emp where deptno = 30) 

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

-- 26 列出在每个部门工作的员工数量，平均工资和平均服务期限
select dname 部门,count(*) 员工人数,round(AVG(sal)) 平均工资,AVG(YEAR(NOW())-YEAR(hiredate)) 平均服务期限 from emp a join dept b on a.deptno=b.deptno GROUP BY a.deptno

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

-- 27 列出所有员工的姓名、部门名称和工资
select ename,dname,sal from emp a join dept b on a.deptno=b.deptno

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

-- 28 列出所有部门的详细信息和人数
select b.deptno,dname,loc,count(a.ename) from emp a right join dept b on a.deptno=b.deptno GROUP BY b.deptno

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

-- 29 列出各种工作的最低工资及从事此工作的雇员姓名
select job,MIN(sal) from emp GROUP BY job

select ename,b.* from emp a join (select job,MIN(sal) minsal from emp GROUP BY job) b on a.job=b.job and a.sal = b.minsal

-- 30 列出工作名为MANAGER（领导）的最低薪金 

-- 30 列出工作名为MANAGER（领导）的最低薪金
select deptno,min(sal) from emp where job = 'MANAGER' group by deptno

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

-- 31 列出所有员工的年工资，按年薪从低到高排序
select ename,(sal*12+ifnull(comm,0)) sumsal from emp ORDER BY sumsal

-- 32 求出员工领导的薪水超过3000的员工名称与领导名称 

-- 32 求出员工领导的薪水超过3000的员工名称与领导名称
select 
a.empno 员工编号,a.ename 员工,b.ename 领导,b.sal 领导薪资,c.dname 部门名
from emp a 
join emp b on a.mgr=b.empno 
join dept c on a.deptno = c.deptno
where b.sal > 3000

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

-- 33 求出部门名称中带’S’字符,的部门员工的工资合计、部门人数
select dname,sum(sal),count(a.deptno) from emp a right join dept b on a.deptno = b.deptno where dname REGEXP 's' GROUP BY b.deptno

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

-- 34 给任职日期超过30年的员工加薪10%
select ename,sal 旧工资,round(sal*1.1) 新工资 from emp where  TimeStampDiff(YEAR,hiredate,now())>30;