sql 50面试题记录（T21-T30）

题目

21.查询不同老师所教不同课程平均分从高到低显示

SELECT teacher.t_id,teacher.t_name,score.c_id,CAST(AVG(score.score) as DECIMAL(5,2)) as '平均成绩' FROM
score,teacher,course
WHERE score.c_id = course.c_id 
and teacher.t_id = course.t_id
GROUP BY score.c_id
ORDER BY AVG(score.score) desc

结果：

22.查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

在这里插入代码片

23.统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

select a.c_id as '课程编号',course.c_name as '课程名称',
sum(level1) as '[100-85]人数', CONCAT(CAST(sum(level1)/count(1)*100 as DECIMAL(5,2)),'%') as '[100-85]占比',
sum(level2) as '[85-70]人数', CONCAT(CAST(sum(level2)/count(1)*100 as DECIMAL(5,2)),'%') as '[85-70]占比',
sum(level3) as '[70-60]人数', CONCAT(CAST(sum(level3)/count(1)*100 as DECIMAL(5,2)),'%') as '[70-60]占比',
sum(level4) as '[0-60]人数', CONCAT(CAST(sum(level4)/count(1)*100 as DECIMAL(5,2)),'%') as '[0-60]占比' from
(select *,
(case when score between 85 and 100 then 1 else 0 end) as 'level1',
(case when score between 70 and 84 then 1 else 0 end) as 'level2',
(case when score between 60 and 69 then 1 else 0 end) as 'level3',
(case when score between 0 and 59 then 1 else 0 end) as 'level4'
from score) a
left join course on a.c_id=course.c_id
group by a.c_id;

结果：

24.查询学生平均成绩及其名次

SELECT a.*,@ran :=@ran+1 as '排名'
FROM ( 
	SELECT s_id,CAST(AVG(score) as DECIMAL(5,2)) as '平均成绩'  
	from score 
	GROUP BY s_id 
	ORDER BY AVG(score) DESC
) a ,
(
	SELECT @ran:=0
) b

结果：

25.查询各科成绩前三名的记录

排除每条成绩，在（相同科目里面比这条成绩大的记录总数）不超过三个
select *
from score a
where (
select count(*) 
	from score b 
	where a.c_id = b.c_id 
	and a.score > b.score
) < 3
order by a.c_id,a.score DESC

结果：

26.查询每门课程被选修的学生数

SELECT c_id,COUNT(s_id)
from score
GROUP BY c_id

结果:

27.查询出只有两门课程的全部学生的学号和姓名

SELECT * 
FROM student 
WHERE s_id in
(
	SELECT s_id
	FROM score
	GROUP BY s_id
	HAVING count(c_id) = 2
)

结果：

28.查询男生、女生人数

SELECT s_sex,COUNT(*) 
FROM student 
GROUP BY s_sex

结果：

29.查询名字中含有"风"字的学生信息

SELECT * 
from student 
WHERE s_name LIKE '%风%'

结果：

30.查询同名同姓学生名单，并统计同名人数

SELECT s_name,COUNT(s_id)-1 as '同名人数' 
FROM student 
GROUP BY s_name

结果：

总结