数据分析sql⾯试必会6题经典_经典SQL⾯试题及答案分析
1、学⽣表 student(s_id：学⽣id，s_name：学⽣姓名，s_birth：学⽣⽣⽇，s_sex：学⽣性别)：
2、教师表teacher(t_id：教师id，t_name：教师姓名)
3、课程表 course(c_id：课程id，c_name：课程名称，t_id：教师id)：
4、成绩表 score(s_id：学⽣id，c_id：课程id，score：分数)
初始化数据的sql附在⽂章末尾
挑战
1、查询各科成绩前三名的记录
--⽅法1：SELECT s_name,a.s_id,a.c_id,a.score FROM score a LEFT JOIN score b ON a.c_id = b.c_id AND a.score=a.score)<=2 ORDER BY a.c_id思路：
SELECT * FROM score a JOIN score b ON a.c_id=b.c_id WHERE a.c_id=‘03’ 这⼀句，形成了03课程的所有学员的两两组合(带上03，是为了简化数据，便于分析)SELECT * FROM score a JOIN score b O
N a.c_id=b.c_id WHERE a.c_id=‘03’ AND a.score
SELECT *FROM (SELECT s_id,score,c_id FROM score WHERE c_id='01') aJOIN (SELECT s_id,score,c_id FROM score WHERE c_id='02') bON a.s_id=b.s_id WHERE a.score>b.score思路：
通过SELECT s_id,score,c_id FROM score WHERE c_id='01’和SELECT s_id,score,c_id FROM score WHERE c_id='02’的两个临时表联查，得到每个学员的01、02课程成绩情况：接下来，a.score>b.score条件直接筛选就可以了。
3、查询和"04"号的同学学习的课程完全相同的其他同学的信息
SELECT score.s_id,COUNT(DISTINCT c_id) FROM student JOIN score ON student.s_id=score.s_id GROUP BY score.s_id HAVING COUNT(DISTINCT c_id)= (SELECT COUNT(c_id) FROM score WHERE s_id='04') AND s_id NOT IN -- 筛选出学过(04号同学未学课程)的学⽣，通过not in排除掉 (SELECT s_id FROM score WHERE c_id IN (SELECT DISTINCT(c_id) FROM course WHERE c_id NOT IN (SELECT c_id FROM score WHERE s_id='04'))) AND score.s_id!='04'思路：
通常思路，我们先获取04号同学的课程，再遍历其他同学，以此筛选。这种⽅式适⽤于编程，单纯的sql实现不了，我们应该从下⾯这个思路去考虑------>我和04号同学学习的课程数⽬⼀样多，且04号同学没学过的我也没学过，那么我不就是和04号同学学习的课程⼀样吗？
4、按平均成绩从⾼到低显⽰所有学⽣的所有课程的成绩以及平均成绩
SELECT a.s_id,b.s_name, MAX(CASE a.c_id WHEN '01' THEN a.score END ) 内功, MAX(CASE a.c_id WHEN '02' THEN a.score END ) 剑法, MAX(CASE a.c_id WHEN '03' THEN a.score END ) 拳法, AVG(a.score) FROM score a JOIN student b ON
a.s_id=
b.s_id GROUP BY a.s_id ORDER BY AVG(a.score) DESC思路不难，关键在于理解透彻case when语法。
SELECT a.c_id,b.c_name,MAX(score),MIN(score),ROUND(AVG(score),2), ROUND(100*(SUM(case when a.score>=60 then 1 else 0 end)/SUM(case when a.score then 1 else 0 end)),2) as 及格率, ROUND(100*(SUM(case when a.score>=70 and
a.score<=80 then 1 else 0 end)/SUM(case when a.score then 1 else 0 end)),2) as 中等率, ROUND(100*(SUM(case when
a.score>=80 and a.score<=90 then 1 else 0 end)/SUM(case when a.score then 1 else 0 end)),2) as 优良率, ROUND(100* (SUM(case when a.score>=90 then 1 else 0 end)/SUM(case when a.score the
n 1 else 0 end)),2) as 优秀率FROM score a LEFT JOIN course b ON a.c_id =
b.c_id GROUP BY a.c_id,b.c_name在case when语法的基础上，进⼀步掌握sql语句中进⾏逻辑运算的技巧
5、按各科成绩进⾏排序，并显⽰排名
SELECT a.s_id,a.c_id, @i:=@i + 1 AS 排名, @j:=(CASE WHEN @score=a.score THEN @j ELSE @i END) AS 并排排名,
@score:=a.score AS scoreFROM (SELECT s_id,c_id,score FROM score GROUP BY s_id,c_id ORDER BY score DESC) a, (SELECT @i:= 0,@j:= 0,@score:= 0) s简评：Mysql中没有rank函数，只能使⽤伪列的概念去实现排名算法。
思路：
mysql面试题大全SELECT s_id,c_id,score FROM score GROUP BY s_id,c_id ORDER BY score DESC先进⾏了排序(SELECT
@i:=0,@j:=0,@score:=0) s对@i、@j、@score进⾏了初始化，初始值都为0@i:=@i + 1，遍历每⼀⾏，
@i逐⾏⾃增@j:=(CASE WHEN @score=a.score THEN @j ELSE @i END)，遍历每⼀⾏，逻辑判断，@score与上⼀⾏的score相等则@j=@i，否则则⾃增⼀次6、查询学⽣的总成绩并进⾏排名
SELECT a.s_id, @i:=@i+1 as i, @j:=(CASE WHEN @score=a.sum_score THEN @j ELSE @i END) AS rank,
@score:=a.sum_score AS scoreFROM (SELECT s_id,SUM(score) AS sum_score FROM score GROUP BY s_id ORDER BY sum_score DESC)a, (SELECT @i:=0,@j:=0,@score:=0) s与上⼀题基本差不多，不同的是a这个临时表的数据内容⽽已。
7、查询所有课程的成绩第2名到第3名的学⽣信息及该课程成绩
SELECT student.*,c.rank,c.score,c.c_id FROM (SELECT a.s_id,a.score,a.c_id,@i:=@i+1 as rank from score a,(SELECT @i:=0)s WHERE a.c_id='01' ORDER BY a.score DESC) cLEFT JOIN student ON c.s_id=student.s_idWHERE rank BETWEEN 2 AND
3UNION ALL SELECT student.*,c.rank,c.score,c.c_id FROM (SELECT a.s_id,a.score,a.c_id,@j:=@j+1 as rank from score a, (SELECT @j:=0)s WHERE a.c_id='02' ORDER BY a.score DESC) cLEFT JOIN student ON c.s_id=student.s_idWHERE rank BETWEEN 2
正码反码补码计算器AND 3UNION ALL SELECT student.*,c.rank,c.score,c.c_id FROM (SELECT a.s_id,a.score,a.c_id,@k:=@k+1 as rank from score a,(SELECT @k:=0)s WHERE a.c_id='03' ORDER BY a.score DESC) cLEFT JOIN student ON
c.s_id=student.s_idWHERE rank BETWEEN 2 AND 3思路：
先将01课程的所有⼈的分数排序，再进⼀步根据rank BETWEEN 2 AND 3筛选出2、3名使⽤同样⽅法，筛选02、03课程数据，使⽤UNION ALL合并查询结果8、统计各科成绩各分数段⼈数：课程编号、课程名称、[80-100]，[60-80]，[0-60]及所占百分⽐
SELECT DISTINCT e.c_name,a.c_id,b.`80-100`,b.百分⽐,c.`60-80`,c.百分⽐,d.`0-60`,d.百分⽐ FROM score aLEFT JOIN (SELECT c_id,SUM(CASE WHEN score >80 AND score <=100 THEN 1 ELSE 0 END) AS `80-100`, ROUND(100*(SUM(CASE WHEN score >80 AND score <=100 THEN 1 ELSE 0 end)/count(*)),2) AS 百分⽐ FROM score GROUP BY c_id)b ON
a.c_id=
b.c_idLEFT JOIN (SELECT c_id,SUM(CASE WHEN score >60 AND score <=80 THEN 1 ELSE 0 END) AS `60-80`, ROUND(100*(SUM(CASE WHEN score >60 AND score <=80 THEN 1 ELSE 0 end)/count(*)),2) AS 百分⽐ FROM score GROUP BY c_id)c ON a.c_id=
c.c_idLEFT JOIN (SELECT c_id,SUM(CASE WHEN score >=0 AND score <=60 THEN 1 ELSE 0 END) AS `0-60`, ROUND(100*(SUM(CASE WHEN score >=0 AND score <=60 THEN 1 ELSE 0 END)/count(*)),2) AS 百分⽐FROM score GROUP BY c_id)d ON a.c_id=
文件通配符d.c_idLEFT JOIN course e ON a.c_id =
e.c_id思路：SELECT c_id,SUM(CASE WHEN score >80 AND score <=100 THEN 1 ELSE 0 END) AS80-100, ROUND(100*(SUM(CASE WHEN score >80 AND score
<=100 THEN 1 ELSE 0 END)/count(*)),2) AS 百分⽐ FROM score GROUP BY c_id得到所有课程80-100分成绩的统计临时表：然后再⽤同样的⽅式，得到0-60、60-80的统计临时表，三表联查。
9、查询学⽣平均成绩及其名次
SELECT a.s_id, @i:=@i+1 as '不保留空缺排名', @k:=(CASE WHEN @avg_score=a.avg_s THEN @k ELSE @i END) AS '保留空缺排名', @avg_score:=avg_s AS '平均分'FROM (SELECT s_id,ROUND(AVG(score),2) AS avg_s FROM score GROUP BY s_id ORDER BY avg_s DESC)a, (SELECT @avg_score:=0,@i:=0,@k:=0)b;
10、查询各学⽣的年龄
SELECT s_birth,(DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(s_birth,'%Y') - (CASE WHEN
DATE_FORMAT(NOW(),'%m%d')>DATE_FORMAT(s_birth,'%m%d') THEN 0 ELSE 1 END)) AS ageFROM student；本⾝的实现思路并不难，DATE_FORMAT掌握其⽤法即可。
11、分别查询本周、下周、本⽉、下⽉过⽣⽇的学⽣
--本周SELECT * FROM student WHERE WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))=WEEK(s_birth)--下周SELECT * FROM student WHERE WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))+1 = WEEK(s_birth)--本⽉SELECT * FROM student WHERE MONTH(DATE_FORMAT(NOW(),'%Y%m%d')) =MONTH(s_birth)--下⽉SELECT * FROM student WHERE
MONTH(DATE_FORMAT(NOW(),'%Y%m%d'))+1 =MONTH(s_birth)
进阶
1、查询及格学⽣(每门课程的分数>=60)的学⽣姓名
SELECT s_name FROM student WHERE s_id NOT IN (SELECT DISTINCT(s_id) FROM score WHERE score<60)或者：
SELECT s_name FROM student a JOIN score b ON a.s_id=b.s_id GROUP BY b.s_id HAVING MIN(score)>602、查询平均成绩⼩于60分的同学的学⽣编号和学⽣姓名和平均成绩 (包括有成绩的和⽆成绩的)
电脑中documents是什么文件夹SELECT s_name,ROUND(avg(score),2) AS avg FROM student JOIN score ON student.s_id=score.s_id GROUP BY score.s_id HAVING avg<60 UNION ALL SELECT s_name,0 AS avg FROM student WHERE s_id NOT IN(SELECT DISTINCT(s_id) FROM score)-- ⽅法2SELECT s_name,IFNULL(ROUND(avg(score),2),0) AS avg FROM student LEFT JOIN score ON
student.s_id=score.s_id GROUP BY score.s_id HAVING avg<60 OR avg is NULL3、查询学过"张三丰"⽼师授课的同学的信息
SELECT * FROM score JOIN student ON score.s_id=student.s_id WHERE c_id IN (SELECT c_id FROM course c JOIN teacher t ON c.t_id=t.t_id WHERE t_name='张三丰')4、查询没学过"张三丰"⽼师授课的同学的信息
SELECT * FROM student WHERE s_id NOT IN (SELECT s_id FROM score WHERE c_id IN (SELECT c_id FROM course c JOIN teacher t ON c.t_id=t.t_id WHERE t_name='张三丰'))5、查询没有学全所有课程的同学的信息
SELECT s_name,COUNT(c_id) AS count FROM student LEFT JOIN score ON student.s_id=score.s_id GROUP BY student.s_id HAVING count
SELECT * FROM student WHERE s_id IN (SELECT DISTINCT a.s_id FROM score a WHERE a.c_id IN (SELECT a.c_id FROM score a WHERE a.s_id='01'));7、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT a.s_id,a.s_name,ROUND(AVG(b.score)) FROM student a LEFT JOIN score b ON a.s_id = b.s_idWHERE a.s_id IN (SELECT s_id from score WHERE score<60 GROUP BY s_id HAVING count(1)>=2)GROUP BY a.s_id,a.s_name8、查询所有学⽣的课程及分数情况；
SELECT a.s_id,a.s_name, SUM(CASE c.c_name WHEN '内功' THEN b.score ELSE 0 END) AS '内功', SUM(CASE c.c_name WHEN '剑法' THEN b.score ELSE 0 END) AS '剑法', SUM(CASE c.c_name WHEN '拳法' THEN b.score ELSE 0 END) AS '拳法', SUM(b.score) as '总分'FROM student a LEFT JOIN score b ON a.s_id = b.s_id LEFT JOIN course c ON b.c_id = c.c_id GROUP BY a.s_id,a.s_name9、查询选修"张三丰"⽼师所授课程的学⽣中，成绩最⾼的学⽣信息及其成绩
SELECT s_id,MAX(score) FROM score WHERE c_id IN (SELECT c_id FROM teacher JOIN course ON teacher.t_id=course.t_id WHERE t_name='张三丰')10、查询不同课程成绩相同的学⽣的学⽣编号
、课程编号、学⽣成绩
SELECT * FROM score a,score b where a.c_id != b.c_id and a.score = b.score11、统计每门课程的学⽣选修⼈数(超过5⼈的课程才统计)。要求输出课程号和选修⼈数，查询结果按⼈数降序排列，若⼈数相同，按课程号升序排列
SELECT c_id,COUNT(*) AS total FROM score GROUP BY c_id HAVING total>4 ORDER BY total DESC,c_id ASC12、查询选修了全部课程的学⽣信息
汉字转unicode软件SELECT * FROM student WHERE s_id IN (SELECT s_id FROM score GROUP BY s_id HAVING COUNT(*)=(SELECT COUNT(*) FROM course))
基础
1、查询不及格的课程
SELECT a.s_id,a.c_id,b.c_name,a.score FROM score a LEFT JOIN course b ON a.c_id = b.c_idWHERE a.score<602、查询课程编号为01且课程成绩在80分以上的学⽣的学号和姓名
SELECT a.s_id,b.s_name FROM score a LEFT JOIN student b ON a.s_id = b.s_idWHERE a.c_id = '01' AND a.score>803、查询每个同学的姓名、选课数、总成绩
SELECT s_name,COUNT(c_id),SUM(score) FROM student a LEFT JOIN score b ON a.s_id=b.s_id GROUP BY b.s_id4、查询平均成绩⼤于等于60分的同学的学⽣编号和学⽣姓名和平均成绩
SELECT s_name,ROUND(avg(score),2) AS avg FROM student JOIN score ON student.s_id=score.s_id GROUP BY score.s_id HAVING avg>605、查询所有同学的学⽣编号、学⽣姓名、选课总数、所有课程的总成绩
SELECT student.s_name,COUNT(c_id),IFNULL(SUM(score),0) AS sum FROM student LEFT JOIN score ON
student.s_id=score.s_id GROUP BY student.s_id ORDER BY sum DESC16、查询"张"姓⽼师的数量
SELECT COUNT(t_id) FROM teacher WHERE t_name LIKE '张%'7、查询"01"课程分数⼩于60，按分数降序排列的学⽣信息
SELECT a.*,b.c_id,b.scoreFROM student a,score bWHERE a.s_id=b.s_id AND b.c_id='01' AND b.score<60ORDER BY b.score DESC;8、查询任何⼀门课程成绩在70分以上的姓名、课程名称和分数
SELECT a.s_name,b.c_name,c.score FROM course b LEFT JOIN score c ON b.c_id = c.c_idLEFT J
OIN student a ON
a.s_id=c.s_id WHERE c.score>=709、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT a.* FROM student a,score b,score c WHERE a.s_id = b.s_id AND a.s_id = c.s_id AND b.c_id='01' AND
c.c_id='02';10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
SELECT a.* FROM student a WHERE a.s_id IN (SELECT s_id FROM score WHERE c_id='01' ) AND a.s_id NOT IN(SELECT s_id FROM score WHERE c_id='02')11、查询男⽣、⼥⽣⼈数
SELECT s_sex,COUNT(s_sex) FROM student GROUP BY s_sex12、查询名字中含有"圆"字的学⽣信息
SELECT * FROM student WHERE s_name LIKE '%圆%';13、查询同名同性学⽣名单，并统计同名⼈数
SELECT a.s_name,a.s_sex,count(*) FROM student a JOIN student b ON a.s_id !=b.s_id and a.s_na
me = b.s_name AND a.s_sex = b.s_sexGROUP BY a.s_name,a.s_sex14、查询1990年出⽣的学⽣名单
SELECT s_name FROM student WHERE s_birth LIKE '1337%'15、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
SELECT c_id,ROUND(AVG(score),2) AS avg_score FROM score GROUP BY c_id ORDER BY avg_score DESC,c_id ASC16、查询平均成绩⼤于等于85的所有学⽣的学号、姓名和平均成绩
SELECT a.s_id,b.s_name,ROUND(avg(a.score),2) AS avg FROM score aLEFT JOIN student b on a.s_id=b.s_id GROUP BY s_id HAVING avg>=8517、查询课程名称为"内功"，且分数低于60的学⽣姓名和分数
SELECT a.s_name,b.score FROM score b JOIN student a ON a.s_id=b.s_id WHERE b.c_id=(SELECT c_id FROM course WHERE c_name ='内功') AND b.score<6018、求每门课程的学⽣⼈数
推荐图片素材网站SELECT c_id,count(*) FROM score GROUP BY c_id;19、检索⾄少选修两门课程的学⽣学号
SELECT s_id,count(*) AS count FROM score GROUP BY s_id HAVING count>=2数据初始化

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