查询出只选修两门课程的学生学号和姓名 SELECT DISTINCT student.SId, student.Sname FROM student, ( SELECT sc.SId, COUNT(*) as a FROM sc GROUP BY(sc.SId)) as A WHERE student.SId = A.SId AND A.a = 2 SELECT Distinct student.Sid, student.Sname FROM student where student.SId in ( SELECT sc.SId FROM sc GROUP BY sc.SId having count(*)=2) 查询男生、女生人数 SELECT Ssex, count(*) FROM student GROUP BY student.Ssex 查询名字中含有「风」字的学生信息 SELECT * FROM student WHERE student.sname LIKE '%风%'; 查询同名同姓学生名单,并统计同名人数 SELECT Sname, count(*) FROM student GROUP BY Sname HAVING COUNT(*) >1; 查询 1990 年出生的学生名单 SELECT * FROM student WHERE sage LIKE '1990-%'; 查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列 SELECT sc.CId, avg(sc.score) as avgscore FROM sc GROUP BY sc.CId ORDER BY avg(sc.score) DESC, sc.CId 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩 SELECT student.Sid, student.Sname, t.avgscore FROM student, ( SELECT sc.SId, avg(sc.score) as avgscore FROM sc GROUP BY sc.SId) as t WHERE student.SId = t.SId AND t.avgscore >= 85 查询课程名称为「数学」,且分数低于 60 的学生姓名和分数 SELECT student.Sname, t.score FROM student, ( SELECT sc.SId, sc.score FROM sc WHERE sc.score < 60 AND sc.CId = (SELECT CId FROM course WHERE course.Cname='数学')) as t WHERE student.SId = t.SId 查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况) SELECT st.*, sc.CId, sc.score FROM student AS st LEFT JOIN sc ON st.SId = sc.SId ORDER BY st.SId, sc.CId; 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数 SELECT student.Sname, course.Cname, sc.score FROM student, course, sc WHERE student.SId = sc.SId AND course.CId = sc.CId AND sc.score > 70 ORDER BY sc.CId 查询不及格的课程 SELECT sc.CId, sc.score FROM sc WHERE sc.score <60 order by>