mysql笔试题18道
部门表、员工表、薪水等级表
1.取得每个部门最高薪水人员名称
第一步:取得每个部门最高薪水作为临时表t
select deptno,max(sal) as maxSal from emp group by deptno
第二步:临时表t与emp表连接条件 e.deptno=t.deptno and e.sal=t.maxSal
select e.ename,t.* from emp e join (select deptno,max(sal) as maxSal from emp group by deptno order by deptno) t on e.deptno=t.deptno and e.sal=t.maxSal
2.哪些人的薪水在部门的平均薪水之上
第一步:取得每个部门平均薪水作为临时表t
SELECT DEPTNO, AVG(SAL) FROM emp GROUP BY DEPTNO;
第二步:临时表t与emp表连接条件 e.deptno=t.deptno and e.sal>t.avgSal
SELECT t.*,e.ENAME,e.SAL FROM emp e JOIN (SELECT DEPTNO, AVG(SAL) AS avgSal FROM emp GROUP BY DEPTNO) t ON e.DEPTNO=t.DEPTNO AND e.SAL>t.avgSal ORDER BY DEPTNO;
3.取得部门中(所有人)平均的薪水等级
平均的薪水等级:先求出每个薪水对应的等级,然后找出薪水等级的平均值
平均薪水的等级:先求出每个部门的平均薪水,再根据平均薪水划分等级
SELECT e.ENAME,e.DEPTNO,e.SAL,s.GRADE FROM emp e JOIN salgrade s ON e.SAL BETWEEN s.LOSAL AND s.HISAL ORDER BY DEPTNO;
SELECT e.DEPTNO,AVG(s.GRADE) FROM emp e JOIN salgrade s ON e.SAL BETWEEN s.LOSAL AND s.HISAL GROUP BY e.DEPTNO;
4. 不准用Max函数取得最高薪水(两种做法)
第一种方案:
SELECT ENAME,SAL FROM emp ORDER BY SAL DESC LIMIT 0,1;
第二种方案:表的自连接
SELECT SAL FROM emp WHERE SAL NOT IN(SELECT DISTINCT a.SAL FROM emp a JOIN emp b ON a.SAL
5.取得平均薪水最高的部门的部门编号(两种做法)
第一种方案:降序取第一个
第一步:找出每个部门的平均薪水
SELECT DEPTNO,AVG(SAL) AS avgSAL FROM emp GROUP BY DEPTNO;
第二步:降序找出第一个
SELECT DEPTNO FROM emp GROUP BY DEPTNO ORDER BY AVG(SAL) DESC LIMIT 1;
第二种方案: max函数
第一步同上
第二步:max函数找到最高平均薪水
SELECT MAX(t.avgSAL) FROM (SELECT AVG(SAL) AS avgSAL FROM emp GROUP BY DEPTNO) t
第三步:查询完部门编号和平均薪水然后根据条件等于最大平均薪水显示
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);
取得平均薪水最高的部门的部门名称
第一步: 找出平均薪水最高的部门
SELECT DEPTNO,AVG(SAL) AS avgsal FROM emp GROUP BY DEPTNO ORDER BY avgsal DESC LIMIT 1;
第二步:与部门表连接条件部门编号
SELECT d.DNAME,AVG(e.SAL) AS avgsal FROM emp e JOIN dept d ON e.DEPTNO=d.DEPTNO GROUP BY d.DNAME ORDER BY avgsal DESC LIMIT 1;
7.求平均薪水的等级最低的部门的部门名称(注意这里不能使用limit,存在局限性如果等级最低出现多个部门)
第一步:求各个部门平均薪水
SELECT e.DEPTNO,AVG(e.SAL) AS avgsal FROM emp e GROUP BY DEPTNO ORDER BY avgsal ASC;
第二步:结果与部门表连接条件,查询等级结果
SELECT t.*,s.GRADE FROM (SELECT d.DNAME,AVG(e.SAL) AS avgsal FROM emp e JOIN dept d ON e.DEPTNO=d.DEPTNO GROUP BY d.DNAME ORDER BY avgsal ASC) tJOIN salgrade s ON t.avgsal BETWEEN s.LOSAL AND s.HISAL;
第三步:查询平均薪水最低对应的等级
SELECT GRADE FROM salgrade WHERE (SELECT AVG(e.SAL) AS avgsal FROM emp e GROUP BY DEPTNO ORDER BY avgsal ASC LIMIT 1) BETWEEN LOSAL AND HISAL; //3
第四步:第二步与第三步连接条件显示结果
SELECT t.*,s.GRADE FROM (SELECT d.DNAME,AVG(e.SAL) AS avgsal FROM emp e JOIN dept d ON e.DEPTNO=d.DEPTNO GROUP BY d.DNAME ORDER BY avgsal ASC) tJOIN salgrade s ON t.avgsal BETWEEN s.LOSAL AND s.HISAL WHERE s.GRADE=(SELECT GRADE FROM salgrade WHERE (SELECT AVG(e.SAL) AS avgsal FROM emp e GROUP BY DEPTNO ORDER BY avgsal ASC LIMIT 1) BETWEEN LOSAL AND HISAL);
8.取得比普通员工(员工代码没有在mgr字段上出现)的最高薪水还要高的领导人姓名
第一步:找出普通员工的最高薪水
SELECT 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.取出薪水最高的前5名员工(这里不需要考虑第6名与第5名工资一样的情况)
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 e.ENAME,e.SAL,s.GRADE FROM emp e JOIN salgrade s ON e.SAL BETWEEN s.LOSAL AND s.HISAL;
第二步:根据其等级进行分组并计数
SELECT s.GRADE,COUNT(*) FROM emp e JOIN salgrade s ON e.SAL BETWEEN s.LOSAL AND s.HISAL GROUP BY s.GRADE;
13.列出所有员工及其领导的名字
SELECT a.ENAME '员工',b.ENAME '领导' FROM emp a LEFT JOIN emp b ON a.MGR=b.EMPNO;
14.列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
SELECT a.EMPNO '员工号',a.ENAME '员工',a.HIREDATE,b.ENAME '领导',b.HIREDATE,d.DNAME '部门名称' FROM emp a JOIN emp b ON a.MGR=b.EMPNO JOIN dept d ON a.DEPTNO=d.DEPTNO WHERE a.HIREDATE
15.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门(右外连接)
SELECT e.*,d.DNAME FROM emp e RIGHT JOIN dept d ON e.DEPTNO=d.DEPTNO;
16.列出至少有5个员工的所有部门
SELECT e.DEPTNO,d.DNAME FROM emp e JOIN dept d ON e.DEPTNO=d.DEPTNO GROUP BY DEPTNO HAVING COUNT(*)>=5;
17.列出薪资比“Smith”高的所有员工信息
SELECT ENAME,SAL FROM emp WHERE SAL >(SELECT SAL FROM emp WHERE ENAME='SMITH');
18.列出所有(“CLERK”)办事员的姓名及其部门名称,部门的人数
第一步:列出所有(“CLERK”)办事员的姓名及其部门名称 临时表t1
SELECT e.ENAME,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(*) FROM emp GROUP BY DEPTNO;
第三步:两张临时表连接
SELECT t1.*,t2.deptCount FROM (SELECT e.ENAME,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;
来源地址:https://blog.csdn.net/bubbleJessica/article/details/129432250
免责声明:
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341