查询出只选修两门课程的学生学号和姓名 SELECT DISTIN...
查询出只选修两门课程的学生学号和姓名 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>