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

现有模式定义如下:

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

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

1
2
3
4
5
SELECT sname 
FROM student, sc
WHERE student.sno = sc.sno AND sc.cno IN (SELECT cno
FROM sc
WHERE sc.sno = '1802387');

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

1
2
3
4
SELECT AVG(sage)
FROM student
GROUP BY sdept
ORDER BY AVG(sage) DESC;

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

1
2
3
4
5
6
7
8
9
10
SELECT sno, sname
FROM student
WHERE NOT EXISTS ( -- 不存在未选课程
(SELECT cno
FROM course) -- 所有课程
EXCEPT
(SELECT cno
FROM sc
WHERE sno=student.sno) -- 已选课程
);

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 创建临时视图
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;