见sql问题总结四

-- 查询各个课程及相应的选修人数；
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