数据库系统概念 - 期中 SQL 专项练习

现有模式定义如下：

student (sno, sname, sage, ssex, sdept)
dourse (cno, cname, ccredit)
sc (sno, cno, grade)

(1) 查询选修了与学号为“1802387”相同课程的学生姓名【签到题1】

SELECT sname 
FROM student, sc 
WHERE student.sno = sc.sno AND sc.cno IN (SELECT cno
                                          FROM sc
                                          WHERE sc.sno = '1802387');

(2) 查询每个学院的学生平均年龄，按平均年龄降序返回【签到题2】

SELECT AVG(sage)
FROM student
GROUP BY sdept
ORDER BY AVG(sage) DESC;

(3) 查询选修了所有课程的学生学号与姓名

SELECT sno, sname
FROM student
WHERE NOT EXISTS (  -- 不存在未选课程
    (SELECT cno
     FROM course)  -- 所有课程
    EXCEPT
    (SELECT cno
     FROM sc
     WHERE sno=student.sno)  -- 已选课程
);

(4) 查询超过该门课程平均分的选课记录

-- 创建临时视图
WITH avg_view(cno, avg_grade) AS
    SELECT cno, AVG(grade)
    FROM sc
    GROUP BY cno;
-- 或创建一个视图
CREATE avg_view(cno, avg_grade) AS
    SELECT cno, AVG(grade)
    FROM sc
    GROUP BY cno;
-- 查询
SELECT sno, cno, grage
FROM sc, avg_view
WHERE sc.cno = avg_view.cno AND grade > avg_grade;