2.查询员⼯的⼯资在部门的平均⼯资以上。
3.查询部门中（所有⼈）的平均⼯资等级
4.查询员⼯的最⾼⼯资（不使⽤max函数，使⽤两种以上解法）
解法三：插询⽐第⼆⾼的员⼯⼯资
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.查询平均⼯资最⾼的部门名称
wireless output什么意思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 ade=(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.查询每个⼯资等级各有多少员⼯
ade,count(*) from emp e join salgrade s on e.sal between s.losal and hisal group ade;
13.查询⼯作地点在DALLAS的员⼯信息
ame,e.job from emp e where deptno = (select deptno from dept d where loc="DALLAS");
14.查询每个员⼯对对应的领导姓名
ame as "员⼯",b.ename as "领导" from emp a left join emp b =b.empno;
15.查询⼊职⽇期早于其直接上级领导的员⼯编号、姓名、部门名称
select ame as "员⼯",a.ame as "领导",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 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 (ame,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的各种⼯作及从事此⼯作的全部雇员⼈数
mysql面试题34道经典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");

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。