MySql经典语句练习50题 ---- 40 ~ 50题

目录

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

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

42、查询每门课成绩最好的前两名

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

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

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

46、查询各学生的年龄

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

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

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

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

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

SELECT student.s_id, s_name, s_birth, s_sex, s_score
FROM student, teacher, score, (SELECT c_id, MAX(s_score) max
							   FROM score
							   GROUP BY c_id) AS sc
WHERE t_name = "张三" AND teacher.t_id = sc.c_id AND score.s_id = student.s_id AND score.c_id = sc.c_id AND score.s_score = max;

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

SELECT sc1.*
FROM score sc1
WHERE EXISTS (SELECT *
			  FROM score sc2
		      WHERE sc1.s_id = sc2.s_id AND sc1.c_id != sc2.c_id 
			  AND sc1.s_score = sc2.s_score);

42、查询每门课成绩最好的前两名

SELECT c_id, s_score 
FROM score sc1
WHERE (SELECT COUNT(*)
	   FROM score sc2
	   WHERE sc1.c_id = sc2.c_id AND sc1.s_score < sc2.s_score) < 2
ORDER BY sc1.c_id, sc1.s_score DESC;

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

SELECT c_id, COUNT(s_id) num
FROM score
GROUP BY c_id
HAVING COUNT(s_id) > 5
ORDER BY COUNT(s_id) DESC, c_id;

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

SELECT s_id
FROM score
GROUP BY s_id
HAVING COUNT(c_id) >= 2;

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

SELECT *
FROM student
WHERE NOT EXISTS (SELECT *
            FROM course
			WHERE NOT EXISTS (SELECT *
							FROM score
							WHERE student.s_id = score.s_id AND course.c_id = score.c_id));

46、查询各学生的年龄

SELECT *, 2023 - s_birth age
FROM student;

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

SELECT *
FROM student
WHERE WEEK(s_birth) = WEEK(NOW());

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

SELECT *
FROM student
WHERE WEEK(s_birth) = WEEK(NOW()) + 1;

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

SELECT *
FROM student
WHERE MONTH(s_birth) = MONTH(NOW());

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

SELECT *
FROM student
WHERE MONTH(s_birth) = MONTH(NOW()) + 1;