SQL 简单表查询，刚刚入门求解这几道题

[gegeda123]

. 查询每个学生超过他选修课程平均成绩的课程号、课程名和成绩，并按照学号、课程号排序。
. 查询各系选修《数据库系统》课程的学生人数、最高分、最低分和平均成绩。
查询至少选修了学生s1选修的全部课程的学生号码(不包括s1)。
求解这三道，下图是用到的表
关于第一个我最初是这么写的
SELECT Sno,Cno,Grade,
FROM SC x
WHERE Grade>=
(
SELECT AVG(Grade)
FROM SC y
WHERE y.Sno=x.Sno)
ORDER BY Sno ,cno ;
但是发现没有课程名，如何添加上课程名呢，用到lian

[听雨停了]

--查询每个学生超过他选修课程平均成绩的课程号、课程名和成绩，并按照学号、课程号排序
SELECT a.Cno,
       c.Cname,
       a.grade
FROM   SC
       INNER JOIN (
                SELECT Sno,
                       AVG(grade) AS Avg_grade
                FROM   SC
                GROUP BY
                       Sno
            )             AS b
            ON  a.Sno = b.Sno
       INNER JOIN Course  AS c
            ON  a.Cno = c.Cno
WHERE a.grade>b.Avg_grade
ORDER BY a.Sno,a.Cno   

--查询各系选修《数据库系统》课程的学生人数、最高分、最低分和平均成绩
SELECT a.Sdept,
       COUNT(b.Cno) AS '人数',
       MAX(isnull(b.grade,0)) AS '最高分',
       MIN(isnull(b.grade,0)) AS '最低分',
       AVG(isnull(b.grade,0)) AS '平均分'
FROM   
       LEFT JOIN Sc       AS b
            ON  a.Sno = b.Sno
       INNER JOIN Course  AS c
            ON  b.Cno = c.Cno
WHERE  c.Cname = '数据库'

--查询至少选修了学生s1选修的全部课程的学生号码(不包括s1)
;WITH cte AS (
SELECT distinct a.Sno,a.Cno,COUNT(1) OVER(PARTITION BY a.Sno) AS cnt
FROM   SC                  AS a
INNER JOIN Student  AS b
    ON  a.Sno = b.Sno
WHERE  b.Sname = 'S1'	
),cte2 AS (
SELECT a.Sno,count(a.Cno) AS cnt FROM SC AS a
cte AS b ON a.Cno=b.Cno
WHERE a.Sno<>b.Sno
GROUP BY a.Sno
)
SELECT distinct a.Sno FROM cte2 AS a
LEFT JOIN cte AS b ON a.cnt=b.cnt

以上代码没有经过实际的数据表测试，还有就是以后不要发蛋分的贴