sql 50面试题记录（T11-T20）

题目

11.查询没有学全所有课程的同学的信息

先查询所有的课程总数
再根据学号分组查询所有选修的课程总数
select student.*
from student left join score on student.s_id = score .s_id group by s_id having count(c_id) < (select count(*) from course)

结果：

12.查询至少有一门课与学号为"01"的同学所学相同的同学的信息

先查询01同学选修的科目c_id
select DISTINCT student.*
from student left join score on student.s_id = score .s_id
WHERE c_id in ( 
	select c_id from score where s_id = '01'
) 

结果：

13.查询和"01"号的同学学习的课程完全相同的其他同学的信息

在这里插入代码片

结果：

14.查询没学过"张三"老师讲授的任一门课程的学生姓名

先查询张三老师教过什么课
通过课程号找到上过张三老师的学生
最后排除上过任意张三老师课的就是没上过张三老师的课的学生
select stundet.* from student where s_id not in (
select s_id from score where c_id in(
select c_id from teacher left join course on teacher.t_id = course.t_id where t_name = '张三'))

结果：

15.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

方法一直接将两表相连然后留下成绩低于60的
最后用分组的方式得到
select student.*,avg(score)
from student left join score on student.s_id = score.s_id
WHERE score <60
group by s_id
having count(c_id)>=2
方法二：
SELECT student.s_id,student.s_name,AVG(score)
from student LEFT JOIN score
on student.s_id = score.s_id
WHERE student.s_id in (
	SELECT score.s_id
	from score 
	WHERE score <60
	GROUP BY s_id
	HAVING COUNT(c_id) >= 2
)
GROUP BY student.s_id

结果：

16.检索"01"课程分数小于60，按分数降序排列的学生信息

方法一：直接连接两张表，根据条件排除
select student.*
from student left join score on student.s_id = score.s_id
where c_id = '01' and score < 60
order by score desc


方法二：先查询出课程01小于60分的学号和成绩作为临时表
再通过学生表和临时表连接在一起查询学生信息最后排序
SELECT student.* ,a.score
from student left JOIN(
SELECT s_id,score FROM score WHERE c_id = '01' and score<60) as a
on student.s_id=a.s_id
WHERE a.score is not null
ORDER BY a.score desc

结果：

17.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

SELECT s_id,
sum(CASE c_id  when '01' THEN score ELSE 0 END) as '语文',
sum(case c_id  when '02' THEN score ELSE 0 END) as '数学',
sum(case c_id  when '03' THEN score ELSE 0 END) as '英语',
CAST(AVG(score) as DECIMAL(5,2)) as '平均成绩'
FROM score
GROUP BY s_id
ORDER BY AVG(score) DESC

结果：

考点：case语句

18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率，及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

成绩表拆分成原数据成绩如果在分数区间就变成1
然后查询的时候根据课程取组
select a.c_id as '课程ID',
c_name as '课程name',
max(score) as '最高分',
min(score) as '最低分',
CAST(avg(score) as DECIMAL(5,2)) as '平均分',
 CONCAT(CAST(sum(pass)/count(*)*100 as DECIMAL(5,2)),'%') as '及格率',
 CONCAT(CAST(sum(medi)/count(*)*100 as DECIMAL(5,2)),'%') as '中等率',
 CONCAT(CAST(sum(good)/count(*)*100 as DECIMAL(5,2)),'%') as '优良率',
 CONCAT(CAST(sum(excellent )/count(*)*100 as DECIMAL(5,2)),'%') as '优秀率'
from
(select *,
case when score >=60 then 1 else 0 end as pass,
case when score >=70 and score < 80 then 1 else 0 end as medi,
case when score >=80 and score < 90 then 1 else 0 end as good,
case when score >=90 then 1 else 0 end as excellent 
from score) as a left join course on a.c_id = course.c_id
group by a.c_id

结果：

19.按各科成绩进行排序，并显示排名

需要在排序的时候先对成绩进行排序在进行加排名的作用，不然结果会出现排名和成绩不一致
SELECT a.*,@ran:=@ran+1 as '排名' from
(SELECT c_id,SUM(score) as '总成绩' FROM score GROUP BY c_id ORDER BY SUM(score) DESC) a,
(SELECT @ran :=0) b

结果：

20.查询学生的总成绩并进行排名

SELECT student.*,sum(score.score) as '总成绩',@ran:=@ran + 1 as '排名'
from student LEFT JOIN score
on student.s_id=score.s_id ,(SELECT @ran:=0) p
GROUP BY s_id 
ORDER BY sum(score.score) DESC

结果：

考点

case：