在我们学习mysql数据库时需要一些题目进行练习，所以我将一些视频中的练习题来分享给大家
库文件代码，将这段代码复制到文件中后缀名改为.sql就可以用了
drop table if exists dept;
drop table if exists salgrade;
drop table if exists emp;
create table dept(
deptno int(10) primary key,
dname varchar(14),
loc varchar(13)
);
create table salgrade(
grade int(11),
losal int(11),
hisal int(11)
);
create table emp(
empno int(4) primary key,
ename varchar(10),
job varchar(9),
mgr int(4),
hiredate date,
sal double(7,2),
comm double(7,2),
deptno int(2)
);
insert into dept(deptno,dname,loc) values(10,'ACCOUNTING','NEW YORK');
insert into dept(deptno,dname,loc) values(20,'RESEARCHING','DALLAS');
insert into dept(deptno,dname,loc) values(30,'SALES','CHICAGO');
insert into dept(deptno,dname,loc) values(40,'OPERATIONS','BOSTON');
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,5000);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(7369,'SIMITH','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,null,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);
select * from dept;
select * from salgrade;
select * from emp;一，作业
1、取得每个部门最高薪水的人员名称
第一步：先查出每个部门的最高薪水
select deptno,max(sal) as maxsal from emp group by deptno;
第二步：再用emp表与第一个查询当作表进行表连接,然后进行判断emp表中的部门编号要与第一表中的部门编号相同并且emp表中的sal等于第一个表中的部门薪水最大值
select
e.ename,e.sal
from
emp e
join
(select deptno,max(sal) as maxsal from emp group by deptno) s
on
e.deptno=s.deptno and e.sal=s.maxsal;
2,哪些人的薪水在部门的平均薪水之上
第一步：对于这个问题可以先查出各个部门的平均薪水
select deptno,avg(sal) as sal from emp group by deptno;
第二步：接下可以把第一个搜索当作一张表，再用emp与它进行表连接,然后进行判断emp表中的sal大于第一种表中的平均薪水
而且还要判断是否是自己部门的
select
e.ename,e.sal,e.deptno
from
emp e
join
(select deptno,avg(sal) as sal from emp group by deptno) d
on
e.deptno=d.deptno and e.sal>d.sal;
3,取得部门中（所有人的）平均的薪水等级，
第一步;先查出所有人的薪水等级
select e.ename,s.grade,e.deptno from emp e join salgrade s on e.sal between losal and hisal;
第二步：再用emp与第一个查询当做表进行表连接,进行分组筛选并求出部门所有人的薪水等级
select
d.deptno,avg(grade)
from
(select e.ename,s.grade,e.deptno from emp e join salgrade s
on
e.sal between losal and hisal) d group by d.deptno;
4,不准用组函数（Max），取得最高薪水（给出两种解决方案）
第一种方案
按照薪水的降序排列,取第一个
select ename,sal from emp order by sal desc limit 1;
第二个
自连接
emp表和emp表自连接：条件是a.sal<b.sal
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 avg(sal) as avgsal,deptno from emp group by deptno;
第二步,取得平均薪水的最大值降序排列去第一个
select avg(sal) avgsal,deptno from emp group by deptno order by sal desc limit 1;
第三步,为了防止有重复最大值遗漏,第一步和第二步相结合
select
e.avgsal,e.deptno
from
(select avg(sal) as avgsal,deptno from emp group by deptno) e
join
(select avg(sal) avgsal,deptno from emp group by deptno order by sal desc limit 1) d
on
e.avgsal=d.avgsal;
第二种方案：
第一步：先查出各部门的平均薪水
select avg(sal) as avgsal,deptno from emp group by deptno;
第二步：再将第一个表当作表e，然后用max()函数求出平均薪水最高的部门
select max(e.sal),e.deptno from (select avg(sal) as sal,deptno from emp group by deptno) e;
第三步：
select
avg(sal) as avgsal ,deptno
from
emp group by deptno
having
avg(sal)=(select max(e.sal) from (select avg(sal) as sal from emp group by deptno) e);
6、取得平均薪水最高的部门的部门名称
第一步：求得最高平均薪水
select max(e.sal) from (select avg(sal) as sal from emp group by deptno) e;
第二步：将emp与dept进行表连接，条件是部门的平均薪水等于最高薪水
select
avg(a.sal) as avgsal ,d.dname
from
emp a
join
dept d
on
a.deptno=d.deptno group by d.dname
having
avg(a.sal)=(select max(e.sal) from (select avg(sal) as sal from emp group by deptno) e);
7、求平均薪水的等级最高的部门的部门名称
第一步：将表emp与表dept进行表表连接，求出平均薪水与部门编号，部门名称
select
avg(t.sal) as avgsal,t.deptno,d.dname
from
emp t
join
dept d
on
t.deptno=d.deptno group by t.deptno;
第二步：求出各部门薪水等级
select
e.deptno,s.grade,e.dname
from
salgrade s
join
(select
avg(t.sal) as avgsal,t.deptno,d.dname
from
emp t
join
dept d
on
t.deptno=d.deptno group by t.deptno) e
on
e.avgsal between losal and hisal;
第三步：求出最大部门最大薪水等级
select
e.deptno,max(s.grade) as maxgrade
from
salgrade s
join
(select avg(sal) as avgsal,deptno from emp group by deptno) e
on
e.avgsal between losal and hisal ;
第四步：求出薪水等级最高的都有什么部门
select
b.grade,b.dname
from
(select
e.deptno,max(s.grade) as maxgrade
from
salgrade s
join
(select avg(sal) as avgsal,deptno from emp group by deptno) e
on
e.avgsal between losal and hisal) a
join
( select
e.deptno,s.grade,e.dname
from
salgrade s
join
(select
avg(t.sal) as avgsal,t.deptno,d.dname
from
emp t
join
dept d
on
t.deptno=d.deptno group by t.deptno) e
on
e.avgsal between losal and hisal) b
on
a.maxgrade=b.grade;
8、取得比普通员工(员工代码没有在mgr字段上出现的)的最高薪水还要高的领导人姓名
第一步,查出领导编号
select distinct mgr from emp;
第二步,查出普通员工
select empno,ename,sal from emp where empno not in(select distinct mgr from emp where mgr is not null);
第三步,查出普通员工最高薪水
select max(sal)as maxsal from emp where empno not in(select distinct mgr from emp where mgr is not null);
第四步,查出比普通员工的最高薪水还要高的领导人姓名
select
ename,sal
from
emp
where
sal>(select max(sal)as maxsal 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、取得薪水最高的第六到第十名员工
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(s.grade) from emp e join salgrade s on sal between losal and hisal group by s.grade;
14、列出所有员工及领导的姓名
将emp表当作两个表进行左外连接
select e.ename empname,ifnull(d.ename,'not') leadername from emp e left join emp d on e.mgr=d.empno;
15、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
第一步：查出受雇日期早于其直接上级的员工编号，姓名，部门编号
select e.empno,e.ename empname,e.deptno from emp e left join emp d on e.mgr=d.empno where e.hiredate<d.hiredate;
第二步：将第一步当作表a与部门进行表连接，查出员工所在的部门
select
a.empno,a.empname empname,d.dname
from
dept d
join
(select e.empno,e.ename empname,e.deptno from emp e left join emp d on e.mgr=d.empno where e.hiredate<d.hiredate)
a
on a.deptno=d.deptno;
16、列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门.
select d.dname,e.* from emp e right join dept d on e.deptno=d.deptno;
17、列出至少有5个员工的所有部门
select count(e.ename),d.dname from emp e join dept d on e.deptno=d.deptno group by d.dname having count(e.ename)>=5;
18、列出薪金比"SIMITH"多的所有员工信息.
第一步：查出SIMITH的薪水
select sal from emp where ename='SIMITH';
第二步：查出大于SIMITH薪水的员工
select ename,sal from emp where sal>(select sal from emp where ename='SIMITH');
19、列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数.
第一步：查出工作为CLERK的员工
select ename,job,deptno from emp where job='CLERK';
第二步：
select e.ename,d.dname,count(*) from dept d join
(select ename,job,deptno from emp where job='CLERK') e on e.deptno=d.deptno group by d.dname;
20、列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数.
select min(sal),job,count(ename) from emp group by job having min(sal)>1500;
21、列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部门编号.
第一步：查出部门"SALES"的部门编号
select deptno from dept where dname='SALES';
第二步：查看在部门"SALES"工作的员工
select ename from emp where deptno=(select deptno from dept where dname='SALES');
22、列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级.
将三个表进行表连接，按照条件进行判断
select
e.ename empname,a.ename leadername,d.dname,s.grade
from
emp e
left join
emp a
on
e.mgr=a.empno
join
dept d
on
e.deptno=d.deptno
join
salgrade s
on
e.sal between s.losal and s.hisal where e.sal>(select avg(sal) from emp);
23、列出与"SCOTT"从事相同工作的所有员工及部门名称.
第一步：查出SCOTT的工作
select job from emp where ename='SCOTT';
第二步：列出与"SCOTT"从事相同工作的所有员工及部门名称.
select
e.ename,d.dname
from
emp e
join
dept d
on
e.job=(select job from emp where ename='SCOTT') where e.deptno=d.deptno;
24、列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金.
第一步：查出部门30中的员工薪水
select distinct sal from emp where deptno=30;
第二步：列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金.
select ename,sal from emp where sal in(select distinct sal from emp where deptno=30) and deptno!=30;
25、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金.部门名称.
第一步：部门30中的最高薪水
select max(sal) from emp where deptno=30;
第二步：列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金.部门名称.
select e.ename,e.sal,d.dname from emp e join dept d on e.deptno=d.deptno where e.sal>(select max(sal) from emp where deptno=30) and e.deptno<>30;
26、列出在每个部门工作的员工数量,平均工资和平均服务期限.
select
d.dname,count(e.ename),ifnull(avg(e.sal),0),ifnull(avg(to_days(now())-to_days(e.hiredate))/365,0) as avgtime
from
emp e
right join
dept d
on
e.deptno=d.deptno group by d.dname;
27、列出所有员工的姓名、部门名称和工资。
select e.ename,d.dname,e.sal from emp e join dept d on e.deptno=d.deptno;
28、列出所有部门的详细信息和人数
select d.*,count(e.ename) from emp e right join dept d on e.deptno=d.deptno group by deptno;
29、列出各种工作的最低工资及从事此工作的雇员姓名
第一步：列出各种工作的最低工资
select job,min(sal) as minsal from emp group by job;
第二步：列出各种工作的最低工资及从事此工作的雇员姓名
select
d.ename,d.sal,d.job
from
emp d
join
(select job,min(sal) as minsal from emp group by job) e
on
d.job=e.job and e.minsal=d.sal;
30、列出各个部门的MANAGER(领导)的最低薪金
select deptno,min(sal) from emp where job='MANAGER' group by deptno;
31、列出所有员工的年工资,按年薪从低到高排序
select ename,(sal+ifnull(comm,0))*12 as yearsal from emp order by sal ;
32、求出员工领导的薪水超过3000的员工名称与领导名称
select e.ename empname,s.ename leadername,s.sal from emp e left join emp s on e.mgr=s.empno where s.sal>3000;
33、求出部门名称中,带'S'字符的部门员工的工资合计、部门人数.
select
d.deptno,ifnull(sum(sal),0),count(e.ename)
from
emp e
right join
dept d
on
e.deptno=d.deptno and d.dname like '%s%' group by d.deptno;
34、给任职日期超过30年的员工加薪10%.
select ename,sal*1.1 sal from emp where (to_days(now())-to_days(hiredate))/365>30;
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
