MySQL查询练习--答案5

41、检索至少选修两门课程的学生学号

SELECT sno FROM sc GROUP BY sno HAVING COUNT(sno)>1;

42、查询全部学生都选修的课程的课程号和课程名

SELECT DISTINCT(c.cno),c.cname FROM course c ,sc
WHERE sc.cno=c.cno

43、查询没学过“谌燕”老师讲授的任一门课程的学生姓名

SELECT st.sname FROM student st
WHERE st.sno NOT IN
(SELECT DISTINCT sc.sno FROM sc,course c,teacher t
WHERE sc.cno=c.cno AND c.tno=t.tno AND t.tname='谌燕')

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

SELECT sno,AVG(score)FROM sc
WHERE sno IN
(SELECT sno FROM sc WHERE sc.score<60
GROUP BY sno HAVING COUNT(sno)>1
) GROUP BY sno

45、检索“c004”课程分数小于60，按分数降序排列的同学学号

SELECT sno FROM sc WHERE cno='c004' AND score<90 ORDER BY score DESC;

46、删除“s002”同学的“c001”课程的成绩

DELETE FROM sc WHERE sno='s002' AND cno='c001';