【分享经典MySQL面试题一】

最新推荐文章于 2022-06-12 11:55:20 发布

置顶跟老程一起学编程

最新推荐文章于 2022-06-12 11:55:20 发布

阅读量682

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/future277809183/article/details/88666068

版权

数据库专栏收录该内容

6 篇文章 2 订阅

订阅专栏

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

-- 第一步：取得每个部门最高薪水【按照部门分组求最大值】
select deptno ,max(sal) as maxsal from emp group by deptno;
-- 第二步：将上面的查询结果当作临时表题，T表和emp e表进行表连接，
-- 条件：t.deptno=e.deptno and t.maxsal=e.sal;
select t.*,e.name
from emp e join (select deptno ,max(sal) as maxsal from emp group by deptno) t
on t.deptno=e.deptno and t.maxsal=e.sal;

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

-- 第一步：取得部门的平均薪水 【按照部门编号分组求平均值】
select deptno avg(sal)  as avgsal from emp group by deptno;
-- 第二部：将上面的你是表当作临时表t,t表和emp e  表进行连接:
-- 条件是：t.deptno=e.deptno and e.avg>t.avg
select e.ename,e.sal,t.*
from    emp e
join
(select deptno avg(sal)  as avgsal from emp group by deptno) t
on    t.deptno=e.deptno and e.avg>t.avg

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

-- 第一步：取得部门的平均薪水
select deptno ,avg(sal)  as avgsal from emp group by deptno;
-- 第二步：将上面的查询结果当作临时表t ,t  表和salgrade s表进行连接，
-- 条件是：t.avgsal between s.losal and s.hisal ;
select t.*,s.grade
from salgrade s
join
(select deptno ,avg(sal)  as avgsal from emp group by deptno)t
on r.avgsal between s.losal and s.hisal;

-- 第一步；每一个员工的薪水等级
select
    e.ename, e.sal ,e.deptno, s.grade
from   emp e
join salgrade s
on    e.sal between s.losal and s.hisal;
-- 第二部：在以上的SQL语句基础上，继续以部门编号分组，求等级的平均值
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  sal FROM emp    ORDER BY DESC LIMIT 1;

-- 第二种方案：自连接
-- select sal from emp ;<表 a>
-- select sal from emp;<表b>
-- a表和表进行表连接：条件是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 deptno ,avg(sal) as avgsal from emp group by deptno；
-- 第二步：取得平均薪水的最大值
select avg(sal) avgsal from emp group by deptno  order by avgsal desc limit 1;
-- 第三步：第一步和第三步联合
select
    deptno,avg(sal) as avgsal
from emp
group by  deptno
having avg(sal)=(select avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1);

第二种方法

select
    deptno,avg(sal) as avgsal
from  emp
group by deptno
having avg(sal)=(select acg(sal)avgsal from emp group by
deptno order by avgsal desc limit1);

-- 【6.**取得平均薪水最高的部门的部门名称】

select
    d.dname,avg(e.sal) as avgsal
from emp e
join   dept  d
on  e.deptno=d.deptno group by . d.dnam
having
avg(e.sal)=(select avg(sal) avgsal from emp group by   deptno order by avgsal desc limit 1);

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

 select a.deptno, c.dname, b.grade from (select deptno, avg(sal) as avg_sal 
from emp group by deptno) a, grade b, dept c where a.deptno = c.deptno 
and a.avg_sal between b.losal and b.hisal order by b.grade limit 1;

-- 【8.取得比普通员工(员工代码没有在mgr上出现的)最高薪水还要高的经理人姓名】

-- 第一步：找出普通员工
select * from emp where empno not in (select distinct mgr from emp);
-- 第二步：找出普通员工最高薪水
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. 取得薪水最高的前五名员工】

select * from emp order by sal desc limit 5;

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

 select * from emp order by sal desc limit 5, 5;

-- 【11. 取得最后入职的5名员工】

select * from emp order by hiredate desc limit 5;

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

select grade, count(em.empno) from emp em, grade 
where em.sal between losal and hisal group by grade;

--【13. 面试题】

有3个表S(学生表)，C（课程表），SC（学生选课表） S（SNO，SNAME）代表（学号，姓名） C（CNO，
CNAME，CTEACHER）代表（课号，课名，教师） SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）

DROP TABLE IF EXISTS SC;
CREATE TABLE SC(
SNO VARCHAR(200),
CNO VARCHAR(200),
SCGRADE VARCHAR(200));


DROP TABLE IF EXISTS S;
CREATE TABLE S(
SNO VARCHAR(200 ),
SNAME VARCHAR(200)
);

DROP TABLE IF EXISTS C;
CREATE TABLE C(
CNO VARCHAR(200),
CNAME VARCHAR(200),
CTEACHER VARCHAR(200)
);

INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '1', '诧文', '张');
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '2', '政治', '王');
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '3', '英诧', '李');
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '4', '数学', '赵');
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '5', '物理', '黎明');

INSERT INTO S ( SNO, SNAME ) VALUES ( '1', '学生1');
INSERT INTO S ( SNO, SNAME ) VALUES ( '2', '学生2');
INSERT INTO S ( SNO, SNAME ) VALUES ( '3', '学生3');
INSERT INTO S ( SNO, SNAME ) VALUES ( '4', '学生4');

INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '1', '40');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '2', '30');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '3', '20');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '4', '80');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '5', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '1', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '2', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '3', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '4', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '5', '40');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '1', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '3', '80');
commit;

-- 【找出没选过“黎明”老师的所有学生姓名】

select  sno,sname
from s
where sno not in(select sno from sc where cno = (select cno from c where cteacher='黎明'));

-- 【列出2门以上（含2门）不及格学生姓名及平均成绩】

SELECT t1.sno,t1.sname,t2.scscore
FROM (SELECT  sc.sno,s.sname FROM sc JOIN s ON sc.sno=s.sno 
WHERE sc.scgrade<60 GROUP BY sc.sno,s.sname HAVING COUNT(*)>2)t1
JOIN (SELECT sno,AVG(scgrade) scscore FROM sc GROUP BY sno)t2 ON t1.sno=t2.sno;

-- 【即学过1号课程又学过2号课所有学生的姓名】

SELECT sc.sno,s.sname FROM sc JOIN s ON sc.sno=s.sno 
WHERE sc.cno=1 AND sc.sno IN(SELECT sno FROM sc WHERE cno=2);

--【 14. 列出所有员工及领导的名字】

SELECT e.ename,m.ename leadername FROM emp e  LEFT JOIN emp m ON e.mgr=m.empno;

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

SELECT e.empno,e.ename,e.`hiredate` empHiredate,m.empno leaderNo,
m.ename leaderName,m.hiredate leaderHiredate,d.dname 
FROM emp e  LEFT JOIN emp m ON e.mgr=m.empno
JOIN dept d ON e.deptno=d.deptno AND e.`hiredate`<m.`hiredate`;

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

SELECT d.deptno,d.dname,e.`ename` FROM emp e RIGHT JOIN dept d ON e.`deptno`=d.`deptno`;

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

SELECT e.`deptno`,d.`dname`,d.`loc`,COUNT(e.`ename`)
FROM emp e JOIN dept d ON e.`deptno`=d.`deptno`
GROUP BY e.`deptno`,d.`dname`,d.`loc`
HAVING COUNT(e.`empno`)>=5;

--【 18. 列出薪水比“SMITH”多的所有员工信息】

SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='SMITH');

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

SELECT e.ename,d.dname,t.totalEmp
FROM dept d JOIN emp e ON e.`deptno`=d.`deptno`
JOIN (SELECT deptno,COUNT(*) AS totalEmp FROM emp GROUP BY deptno)t
ON job='CLERK';

--【 20. 列出最低薪水大亍1500的各种工作及从事此工作的全部雇员人数】

SELECT job,MIN(sal),COUNT(1) FROM emp GROUP BY job HAVING MIN(sal)>1500;

--【 21. 列出在部门“SALES”<销售部>工作的员工的姓名，假定丌知道销部门的部门编号】

SELECT e.`ename` FROM emp e WHERE e.deptno=(SELECT deptno FROM dept WHERE dname='SALES');

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

SELECT d.dname,e.ename,b.ename leadername,s.grade 
FROM emp e JOIN dept d ON e.deptno=d.deptno 
LEFT JOIN emp b ON e.mgr=b.empno JOIN salgrade s ON e.sal BETWEEN s.`losal` AND s.`hisal` 
AND e.sal>(SELECT AVG(sal) avgSal FROM emp);

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

SELECT e.ename,d.dname FROM emp e JOIN dept d ON e.`deptno`=d.`deptno`
AND e.job=(SELECT job FROM emp WHERE ename='SCOTT');

--【 24. 列出薪金等于部门30中员工的薪金的其它员工的姓名和薪金】

SELECT ename,sal,deptno FROM emp WHERE sal IN(SELECT sal FROM emp WHERE deptno=30) AND deptno!=30;

--【 25. 列出薪金高亍在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称】

SELECT e.ename,e.sal,d.dname FROM emp e JOIN dept d ON e.deptno=d.`deptno`
AND e.sal>(SELECT MAX(sal) FROM emp WHERE deptno=30) AND e.deptno<>30;

--【 26. 列出在每个部门工作的员工数量、平均工资和平均服务期限】**

SELECT d.deptno,
       COUNT(e.ename) AS totalEmp,
       IFNULL(AVG(e.sal),0) AS avgsal,
       IFNULL(AVG((TO_DAYS(NOW())-TO_DAYS(hiredate))/365),0) AS avgtime
FROM emp e
RIGHT JOIN dept d
ON e.deptno=d.deptno
GROUP BY d.deptno;

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

SELECT e.`ename`,e.sal,d.`dname` FROM emp e JOIN dept d ON e.`deptno`=d.`deptno`;

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

SELECT d.`deptno`,d.`dname`,d.`loc`,COUNT(e.ename) totalEmp 
FROM emp e RIGHT JOIN dept d ON d.`deptno`=e.`deptno` GROUP BY d.`deptno`,d.`dname`,d.`loc`;

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

SELECT e.ename,e.job FROM emp e JOIN (SELECT job,MIN(sal) minSal 
FROM emp GROUP BY job)t ON e.job=t.job AND 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))*12 yearSal FROM emp ORDER BY yearsal ASC;

--【 32. 求出员工领导的薪水超过3000的员工名称和领导名称】

SELECT a.`ename` empname,a.sal empsal,b.`ename` leadername,b.sal leadersal 
FROM emp a JOIN emp b ON a.`mgr`=b.`empno` WHERE b.sal>3000;

--【 33. 求部门名称中带“S”字符的部门员工的工资合计、部门人数】

SELECT d.dname,IFNULL(SUM(e.sal),0) sumSal,COUNT(e.ename) totalEmp 
FROM emp e RIGHT JOIN dept d ON e.`deptno`=d.`deptno` 
AND d.dname LIKE '%s%' GROUP BY d.dname;

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

DROP TABLE IF EXISTS empbak;
CREATE TABLE empbak AS SELECT * FROM emp;
UPDATE empbak SET sal=sal*1.1 WHERE (TO_DAYS(NOW())-TO_DAYS(hiredate))/365>30;

跟老程一起学编程

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
打赏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录