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

SELECT student.Sname , sc.SId , AVG(score) FROM student RIGHT JOIN
sc
ON student.SId = sc.SId
GROUP BY sc.SId
HAVING AVG(score) >= 85

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

SELECT student.sname, sc.score FROM student, sc, course
WHERE student.sid = sc.sid
AND course.cid = sc.cid
AND course.cname = “数学”
AND sc.score < 60;

28 . 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

SELECT student.Sname , sc.CId, sc.score FROM
student LEFT JOIN
sc
ON student.SId = sc.SId

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

SELECT student.Sname , sc.CId , sc.score
FROM student ,sc , course
WHERE student.SId = sc.SId
AND sc.score > 70
AND sc.cid = course.cid

30 . 查询不及格的课程

SELECT cid FROM sc WHERE score < ‘60’ GROUP BY cid

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

SELECT sc.SId , student.Sname FROM
sc , student
WHERE
student.SId = sc.SId
AND sc.CId = ‘01’
AND sc.score >= 80

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

SELECT sc.CId , COUNT(sc.sid) FROM sc
GROUP BY sc.cid

33 . 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

SELECT student.*, sc.score, sc.cid FROM student, teacher, course,sc
WHERE teacher.tid = course.tid
AND sc.sid = student.sid
AND sc.cid = course.cid
AND teacher.tname = “张三”

34 . 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

SELECT student., sc.score, sc.cid FROM student, teacher, course,sc
WHERE teacher.tid = course.tid
AND sc.sid = student.sid
AND sc.cid = course.cid
AND teacher.tname = “张三”
SELECT student., sc.score, sc.cid FROM student, teacher, course,sc
WHERE teacher.tid = course.tid
AND sc.sid = student.sid
AND sc.cid = course.cid
AND teacher.tname = “张三”
AND sc.score = (
SELECT MAX(sc.score)
FROM sc,student, teacher, course
WHERE teacher.tid = course.tid
AND sc.sid = student.sid
AND sc.cid = course.cid
AND teacher.tname = “张三”
);

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

SELECT a.cid, a.sid, a.score FROM sc AS a
INNER JOIN
sc AS b
ON a.sid = b.sid
AND a.cid != b.cid
AND a.score = b.score
GROUP BY cid, sid;

36 . 查询每门功成绩最好的前两名(不太理解)

SELECT a.sid,a.cid,a.score FROM sc AS a
LEFT JOIN sc AS b
ON a.cid = b.cid AND a.scoreGROUP BY a.cid, a.sid
HAVING COUNT(b.cid)<2
ORDER BY a.cid;

37 . 统计每门课程的学生选修人数（超过 5 人的课程才统计）

SELECT sc.cid ,COUNT(sc.sid) AS c
FROM sc
GROUP BY sc.cid
HAVING c > 5

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

SELECT sid ,COUNT(cid )FROM sc
GROUP BY sid
HAVING COUNT(cid)>=2

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

SELECT student. , COUNT(cid) FROM student , sc
WHERE sc.SId=student.SId
GROUP BY sc.sid
HAVING COUNT() = (SELECT DISTINCT COUNT(*) FROM course)

40 . 查询各学生的年龄，只按年份来算

SELECT * FROM student
GROUP BY sage

41 . 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一（不会）

SELECT student.SId AS 学生编号,student.Sname AS 学生姓名,
TIMESTAMPDIFF(YEAR,student.Sage,CURDATE()) AS 学生年龄
FROM student

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

SELECT *
FROM student
WHERE WEEKOFYEAR(student.Sage)=WEEKOFYEAR(CURDATE());