[数据库笔记06] SQL50题31 - 50

31、查询1990年出生的学生名单（重点year）

SELECT * FROM student
WHERE YEAR(s_birth)=1990;

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

32、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩（不重要）

SELECT s_id, AVG(s_score) AS avg_score FROM score
GROUP BY s_id HAVING avg_score >= 85;

SELECT a.s_id, a.s_name, b.avg_score FROM student AS a
INNER JOIN
(
SELECT s_id, AVG(s_score) AS avg_score FROM score
GROUP BY s_id HAVING avg_score >= 85
) AS b ON a.s_id=b.s_id;

33、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列（不重要）

SELECT s.c_id, c.`c_name`, AVG(s.s_score) AS avg_score FROM score AS s
INNER JOIN course AS c ON s.`c_id`=c.`c_id`
GROUP BY c_id 
ORDER BY avg_score ASC, c_id DESC;

34、查询课程名称为"数学"，且分数低于60的学生姓名和分数（不重点）

SELECT s.s_id, st.`s_name`, s.`s_score` FROM score AS s
INNER JOIN course AS c ON s.`c_id`=c.c_id
INNER JOIN student AS st ON s.`s_id`=st.`s_id`
WHERE c.`c_name`="数学" AND s.`s_score`<60;

35、查询所有学生的课程及分数情况（重点）

SELECT sd.`s_id`, sd.`s_name`,
MAX(CASE WHEN c.`c_name`='语文' THEN s.`s_score` ELSE NULL END) AS '语文',
MAX(CASE WHEN c.`c_name`='数学' THEN s.`s_score` ELSE NULL END) AS '数学',
MAX(CASE WHEN c.`c_name`='英语' THEN s.`s_score` ELSE NULL END) AS '英语'
FROM score AS s
INNER JOIN course AS c ON s.`c_id`=c.`c_id`
INNER JOIN student AS sd ON s.`s_id`=sd.`s_id`
GROUP BY sd.`s_id`, sd.`s_name`;

36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数（重点）

SELECT s.`s_id`, t.`s_name`, c.`c_name`, s.`s_score`
FROM score AS s
INNER JOIN course AS c ON s.`c_id`=c.`c_id`
INNER JOIN student AS t ON s.`s_id`=t.`s_id`
WHERE s.`s_score`>70;

37、查询不及格的课程并按课程号从大到小排列(不重点)

SELECT s.`s_id`, st.`s_name`, c.`c_id`, c.`c_name`, s.`s_score`
FROM score AS s
INNER JOIN course AS c ON s.`c_id`=c.`c_id`
INNER JOIN student AS st ON s.s_id=st.`s_id`
WHERE s.`s_score` < 60
ORDER BY c.`c_id` DESC;

38、查询课程编号为03且课程成绩在80分以上的学生的学号和姓名（不重要）

SELECT s.`s_id`, st.`s_name`, s.`c_id`, c.`c_name`, s.`s_score` FROM 
score AS s
INNER JOIN student AS st ON s.`s_id`=st.`s_id`
INNER JOIN course AS c ON c.`c_id`=s.`c_id`
WHERE s.`c_id`='03' AND s.`s_score`>80;

39、求每门课程的学生人数（不重要）

SELECT c.c_id, c.`c_name`, COUNT(DISTINCT s_id) FROM score AS s
INNER JOIN course AS c ON c.`c_id`=s.`c_id`
GROUP BY c.c_id;

40、查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩（重要top）

SELECT s.s_id, st.s_name, s.s_score, c.c_id, c.c_name, t.t_name
FROM score AS s
INNER JOIN course AS c ON s.`c_id`=c.`c_id`
INNER JOIN teacher AS t ON c.`t_id`=t.`t_id`
INNER JOIN student AS st ON s.`s_id`=st.`s_id`
WHERE t.`t_name`='张三' ORDER BY s.`s_score` DESC LIMIT 0,1;

41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 （重点）

42、查询每门功成绩最好的前两名（同22和25题）

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

SELECT c_id, COUNT(DISTINCT s_id) AS cnt FROM score
GROUP BY c_id HAVING cnt > 5
ORDER BY cnt DESC, c_id ASC;

44、检索至少选修两门课程的学生学号（不重要）

SELECT s_id, COUNT(DISTINCT c_id) AS cnt FROM score
GROUP BY s_id HAVING cnt >=2;

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

SELECT s_id, COUNT(DISTINCT c_id) AS cnt FROM score
GROUP BY s_id HAVING cnt=(SELECT COUNT(c_id) FROM course);

46、查询没学过“张三”老师讲授的任一门课程的学生姓名

SELECT * FROM student WHERE s_id NOT IN(
SELECT s.`s_id` FROM score AS s
INNER JOIN course AS c ON s.`c_id`=c.`c_id`
INNER JOIN teacher AS t ON c.`t_id`=t.t_id
WHERE t.t_name='张三');

47、查询两门以上不及格课程的同学的学号及其平均成绩

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

SELECT * FROM student WHERE WEEK(CONCAT('2019-', SUBSTRING(s_birth, 6, 5)), 1) = WEEK('2019-05-15', 1)+1;

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

SELECT * FROM student
WHERE MONTH(s_birth)=MONTH('2019-12-25');

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

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

SELECT * FROM student WHERE
CASE WHEN MONTH('2019-04-08')=12 THEN MONTH(s_birth)=1
ELSE MONTH(s_birth)=MONTH('2019-04-08') + 1 END;

参考：

https://zhuanlan.zhihu.com/p/43289968
https://www.bilibili.com/video/BV1q4411G7Lw