– 31、查询1990年出生的学生名单:
SELECT *
FROM student
WHERE YEAR(s_birth)=1990
– 32、查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列:
SELECT c.c_id,c.c_name,ROUND(AVG(s_score),2) avg
FROM score sc JOIN course c ON sc.c_id=c.c_id
GROUP BY c.c_id,c.c_name
ORDER BY avg DESC,c.c_id
– 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩:
SELECT s.s_id,s.s_name,ROUND(AVG(s_score),2)
FROM student s JOIN score sc ON s.s_id=sc.s_id
GROUP BY s.s_id,s.s_name HAVING AVG(s_score)>=85
– 34、查询课程名称为"数学",且分数低于60的学生姓名和分数:
SELECT s.s_name,sc.s_score
FROM course c
JOIN score sc ON c.c_id=sc.c_id AND sc.c_id='02' AND sc.s_score<60
JOIN student s ON sc.s_id=s.s_id
– 35、查询所有学生的课程及分数情况:
SELECT s.s_name,
SUM(CASE WHEN c.c_name='语文' THEN sc.s_score ELSE 0 END ) chainese,
SUM(CASE WHEN c.c_name='数学' THEN sc.s_score ELSE 0 END ) math,
SUM(CASE WHEN c.c_name='英语' THEN sc.s_score ELSE 0 END ) english
FROM student s
JOIN score sc ON s.s_id=sc.s_id
JOIN course c ON c.c_id=sc.c_id
GROUP BY s.s_name
后续部分参见: