-- 查询各个课程及相应的选修人数; SELECT aa.cid,aa.cname ,bb.num FROM ( SELECT cid ,cname FROM course) aa INNER JOIN (SELECT COUNT(student_id)as num,corse_id FROM score GROUP BY corse_id) bb on aa.cid=bb.corse_id -- 查询不同课程但成绩相同的学生的学号、课程号、学生成绩; -- 查询每门课程成绩最好的前两名; SELECT * FROM( select SC.corse_id,SC.number SCORE,ROW_NUMBER() OVER(PARTITION BY SC.corse_id ORDER BY SC.number DESC) RN FROM score SC) A WHERE A.RN <=2 -- 检索至少选修两门课程的学生学号; SELECT student_id,COUNT(*)as num FROM score GROUP BY student_id HAVING num>=2 -- 查询全部学生都选修的课程的课程号和课程名; SELECT cname,cid FROM course WHERE cid in ( select corse_id as num FROM score GROUP BY corse_id HAVING num =(SELECT COUNT(*) FROM student)) -- 查询没学过“王”老师讲授的任一门课程的学生姓名 SELECT * FROM student WHERE sid not in ( SELECT student_id FROM score WHERE corse_id in( SELECT cid FROM course WHERE tearch_id in ( SELECT tid FROM teacher WHERE tname = '王老师'))) -- 查询两门以上不及格课程的同学的学号及其平均成绩; SELECT AVG(number),student_id FROM score GROUP BY student_id HAVING number<60 -- 、检索“004”课程分数小于60,按分数降序排列的同学学号; SELECT student_id FROM score WHERE corse_id=4 and number<60 ORDER BY number -- 删除“002”同学的“001”课程的成绩; DELETE FROM score WHERE student_id=2 and corse_id=1
标签:总结,问题,课程,student,sql,corse,WHERE,id,SELECT From: https://www.cnblogs.com/xxks/p/17305481.html