sql 50面试题记录（T31-T50）

题目

1.查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)

SELECT * 
FROM student 
WHERE YEAR(student.s_age)='1990'

结果：

2.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号

SELECT c_id,CAST(AVG(score) as DECIMAL(5,2))  as '平均成绩' 
FROM score 
GROUP BY c_id 
ORDER BY AVG(score) desc,c_id

结果：

3.查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

SELECT score.s_id,
s_name,
CAST(AVG(score) as DECIMAL(5,2)) as '平均分数' 
FROM score LEFT JOIN student on score.s_id=student.s_id 
GROUP BY s_id 
HAVING AVG(score) >85

结果：

4.查询课程名称为"数学"，且分数低于60的学生姓名和分数

SELECT s_name,score 
from student left JOIN score on student.s_id = score.s_id
WHERE score.c_id in
(
	SELECT c_id 
	FROM course 
	WHERE course.c_name='数学'
)
and score <60

结果：

5.查询所有学生的课程及分数情况

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 '英语'
from score
GROUP BY s_id

结果：

36.查询任何一门课程成绩都在70分以上的同学姓名、课程名称和分数

SELECT DISTINCT c.s_id,c.s_name
FROM course a,score b,student c
WHERE a.c_id = b.c_id 
and b.s_id = c.s_id 
and b.s_id 
not in (
	SELECT s_id
	FROM score
	WHERE score<70
	GROUP BY s_id
)

结果：

37.查询不及格的课程

SELECT score.c_id,course.c_name,score.score
FROM score LEFT JOIN course on score.c_id = course.c_id
WHERE score.score < 60

结果：

38.查询课程编号为01且课程成绩在80分及以上的学生的学号和姓名

SELECT b.s_id,b.s_name 
from score a LEFT JOIN student b 
on a.s_id=b.s_id 
WHERE c_id = '01' AND score >=80

结果：

39.求每门课程的学生人数

SELECT c_id,COUNT(s_id) as '选修人数'
FROM score
GROUP BY c_id

结果：

40.查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

SELECT student.*,MAX(score) 
FROM student LEFT JOIN score on student.s_id =score.s_id 
WHERE c_id in(
	SELECT c_id
	FROM teacher LEFT JOIN course ON teacher.t_id = course.t_id
	WHERE t_name = '张三'
)

结果：

41.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

SELECT  a.* 
FROM score a,score b 
WHERE a.c_id =b.c_id 
and a.score =b.score 
AND a.s_id != b.s_id

结果：

42.查询每门功成绩最好的前两名

SELECT a.* 
FROM score a  
WHERE(
	SELECT COUNT(1) 
	FROM score b 
	WHERE a.c_id = b.c_id 
	and b.score>a.score
)<=1
ORDER BY c_id 

结果：

43.统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT c_id,COUNT(1) 
from score  
GROUP BY c_id 
HAVING COUNT(1)>5 
ORDER BY COUNT(1) DESC,c_id

结果：

44.检索至少选修两门课程的学生学号

select s_id 
from score 
group by s_id 
having count(c_id)>=2;

结果：

45.查询选修了全部课程的学生信息

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 )

结果：

46.查询各学生的年龄

SELECT student.*,YEAR(NOW()) - YEAR(s_age) as '年龄'
FROM student

结果：

47.查询本周过生日的学生

SELECT student.* ,ADDDATE(CURDATE(),-(DATE_FORMAT(NOW(),"%m")-1)) as '本周开始',ADDDATE(CURRENT_DATE,7-(DATE_FORMAT(NOW(),"%m"))) as '本周结束'
from student 
WHERE DATE_FORMAT(s_age,"2020-%m-%d") 
BETWEEN
ADDDATE(CURDATE(),-(DATE_FORMAT(NOW(),"%m")-1))
AND 
ADDDATE(CURRENT_DATE,7-(DATE_FORMAT(NOW(),"%m")))

结果：

48.查询下周过生日的学生

SELECT student.* ,ADDDATE(CURDATE(),-(DATE_FORMAT(NOW(),"%m")-1-7)) as '下周开始',ADDDATE(CURRENT_DATE,7+7-(DATE_FORMAT(NOW(),"%m"))) as '下周结束'
from student 
WHERE DATE_FORMAT(s_age,"2020-%m-%d") 
BETWEEN
ADDDATE(CURDATE(),-(DATE_FORMAT(NOW(),"%m")-1-7))
AND 
ADDDATE(CURRENT_DATE,7+7-(DATE_FORMAT(NOW(),"%m")))

结果：

49.查询本月过生日的学生

SELECT student.*
from student
WHERE MONTH(s_age) =MONTH(NOW())

结果：

50.查询下月过生日的学生

SELECT student.*
from student
WHERE MONTH(s_age) =MONTH(NOW())+1

结果：

总结