动⼒节点Mysql练习题34道
有三张表
1、部门表 dept
2、员⼯表 emp
3、薪⽔等级表 salgrade
1、 取得每个部门最⾼薪⽔⼈员的名称
ame,t.*
from(select deptno,max(sal)as msal from emp group by deptno)as t
join emp e
on e.sal = t.msal and e.deptno = t.deptno;
2、哪些⼈的薪⽔在部门的平均薪⽔之上
feignclientame,t.*
from emp e
join(select deptno,avg(sal) avgsal from emp group by deptno)t
自己可以开发小程序吗on e.deptno = t.deptno and e.sal > t.avgsal;
3、取得部门中所有⼈平均的薪⽔等级
select e.deptno,ade) agrade
from emp e
join salgrade s
on e.sal between s.losal and s.hisal
group by e.deptno;
路由器管家app下载4、不⽤组函数（max），取得最⾼薪⽔
select ename,sal from emp order by sal desc limit1;
5、取得平均薪⽔最⾼部门的部门编号；
select deptno,avg(sal) avgsal
from emp
group by deptno
order by avgsal
desc limit1;
6、取得平均薪⽔最⾼的部门的部门名称
select d.dname,avg(e.sal) avgsal
from emp e
join dept d
on e.deptno = d.deptno
group by d.dname
order by avgsal desc
limit1;
7、求平均薪⽔的等级最低的部门的部门名称(P82)
8、取得⽐普通员⼯最⾼薪⽔还要⾼的领导⼈姓名
第⼀步：出普通员⼯的最⾼薪⽔
select ename,max(sal)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)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 limit5;
10、取得薪⽔最⾼的第6到第10名员⼯
select ename,sal from emp order by sal desc limit5,5;//第6位开始，查5个11、取得最后⼊职的五个员⼯
select ename,hiredate from emp order by hiredate desc limit5;
12、取得每个薪⽔等级有多少个员⼯
ade,count(*)as count
from emp e join salgrade s on
e.sal between s.losal and s.hisal
group ade;
14、列出所有员⼯及领导的姓名
ame'员⼯',b.ename'领导'
from emp a left join emp b
= b.empno;会员登录界面设计
15、列出受雇⽇期早于其直接上级的所有员⼯编号，姓名，部门名称
ame'员⼯',a.ame'领导',b.hiredate,d.dname
from emp a
join emp b
= b.empno
join dept d
on a.deptno = d.deptno
where a.hiredate < b.hiredate;
16、列出部门名称和这些部门的员⼯信息，同时列出那些没有员⼯的部门
select d.dname,e.*
from dept d
left join emp e
on d.deptno = e.deptno;
17、列出⾄少有五个员⼯的所有部门
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”（办事员）的姓名及其部门名称，部门的⼈数
第⼀步：CLERK的姓名及其部门名称 t1 表
ame,e.job,d.dname,d.deptno
from emp e join dept d
on e.deptno = d.deptno
where e.job ='CLERK';
第⼆步：部门⼈数 t2 表
select deptno,count(*)as deptcount
from emp group by deptno;
第三部：表连接
select t1.*,t2.deptcount
from
(ame,e.job,d.dname,d.deptno
from emp e join dept d
on e.deptno = d.deptno
where e.job ='CLERK')t1
join
(select deptno,count(*)as deptcount
from emp group by deptno)t2
on t1.deptno = t2.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、列出薪⽔⾼于公司平均⽔平的所有员⼯，所在部门、上级领导、雇员的⼯资等级
ame'员⼯',d.ame'领导',s.grade
from emp e
join dept d
on e.deptno = d.deptno
left join
emp l
= l.empno
join salgrade s
on e.sal between s.losal and s.hisal
where e.sal >(select avg(sal)from emp);
23、列出与“scott”从事相同⼯作的所有员⼯及部门名称
ame,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')
ame <>'SCOTT';
24、列出薪⽔等于部门30中员⼯的薪⽔的其他员⼯的姓名和薪⽔
select ename,sal
from emp
where sal in(select sal from emp where deptno =30)
and deptno <>30;
25、列出薪⽔⾼于在部门30⼯作的所有员⼯的薪⽔的员⼯姓名和薪⽔、部门名称
ame,e.sal,d.dname
from emp e
join dept d
on e.deptno = d.deptno
where sal >(select max(sal)from emp where deptno =30);
26、列出在每个部门⼯作的员⼯数量，平均⼯资和平均服务期限(P92)
27、列出所有员⼯的姓名、部门名称和⼯资
ame,d.dname,e.sal
from emp e
join dept d
on e.deptno = d.deptno;
28、列出所有部门的详细信息和⼈数
select d.*,ame)
from emp e
right join dept d
on e.deptno = d.deptno
group by d.deptno;
29、列出各种⼯作的最低⼯资以及从事此⼯作的雇员姓名
select min(sal) minsal from emp group by job;
ame,e.job,t.*
from emp e
join(select min(sal) minsal from emp group by job)t
on e.sal = t.minsal;
30、列出各个部门的manager(领导)的最低薪⽔
select deptno,min(sal) minsal from emp
where job ='manager'
group by deptno;
31、列出所有员⼯的年⼯资，按年薪从低到⾼排序
select ename,(sal + ifnull(comm,0))*12as yearsal
from emp
order by sal;
32、求出员⼯领导的薪⽔超过3000的员⼯名称与领导
ame'员⼯',b.ename'领导'
from emp a
join emp b
= b.empno
where b.sal >3000;
tkinter可视化设计工具33、求出部门名称中，带“S”字符的部门员⼯的⼯资合计、部门⼈数
select d.deptno,d.dname,d.loc,ame),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、给任职⽇期超过三⼗年的员⼯加薪10%
update emp set sal = sal*1.1where timestampdiff(year,hiredate,now())>30;
Mysql基础
1、创建表
create table if not exists'runoob'(
mysql面试题34道经典'runoob_id'int unsigned not null auto_increment,
'runoob_title'varchar(100)not null,
'runoob_author'varchar(40)not null,
primary key('runoob_id')
)engine=innodb default charset= utf8;
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论