Day 09 - 数据库70题：综合题 22-33

参考重点：教材第 2 章关系演算，第 3 章 SQL。SQL 均按 SQL Server 写法。

22. 对于上题的关系 S、C 和 SC，试用元组演算表达式表示查询。

使用谓词：S(s)、C(c)、SC(sc)，属性写作 s.S#、c.C# 等。

22.1 检索选修课程号为 K5 的学生学号和成绩。

答案：

{ <x, g> | EXISTS sc (SC(sc) AND sc.C#='K5' AND x=sc.S# AND g=sc.GRADE) }

22.2 检索选修课程号为 K8 的学生的学号和姓名。

答案：

{ <x, n> | EXISTS s EXISTS sc (S(s) AND SC(sc) AND s.S#=sc.S# AND sc.C#='K8' AND x=s.S# AND n=s.SNAME) }

22.3 检索选修课程名为“C语言”的学生学号和姓名。

答案：

{ <x, n> | EXISTS s EXISTS c EXISTS sc (S(s) AND C(c) AND SC(sc) AND c.CNAME='C语言' AND sc.C#=c.C# AND s.S#=sc.S# AND x=s.S# AND n=s.SNAME) }

22.4 检索选修课程号为 K1 或 K5 的学生学号。

答案：

{ <x> | EXISTS sc (SC(sc) AND x=sc.S# AND (sc.C#='K1' OR sc.C#='K5')) }

22.5 检索选修课程号为 K1 和 K5 的学生学号。

答案：

{ <x> | EXISTS sc1 EXISTS sc2 (SC(sc1) AND SC(sc2) AND sc1.S#=x AND sc2.S#=x AND sc1.C#='K1' AND sc2.C#='K5') }

22.6 检索不选修 K8 课程的学生姓名和年龄。

答案：

{ <n, a> | EXISTS s (S(s) AND n=s.SNAME AND a=s.AGE AND NOT EXISTS sc (SC(sc) AND sc.S#=s.S# AND sc.C#='K8')) }

22.7 检索选修全部课程的学生姓名。

答案：

{ <n> | EXISTS s (S(s) AND n=s.SNAME AND FORALL c (C(c) -> EXISTS sc (SC(sc) AND sc.S#=s.S# AND sc.C#=c.C#))) }

讲解：FORALL c 表示对每一门课程，都存在该学生的选课记录。这是关系除法在元组演算中的表达。

22.8 检索所选修课程包含 1 号学生所选课程的学生学号。

答案：

{ <x> | EXISTS s (S(s) AND x=s.S# AND FORALL sc1 ((SC(sc1) AND sc1.S#='1') -> EXISTS sc2 (SC(sc2) AND sc2.S#=x AND sc2.C#=sc1.C#))) }

讲解：对 1 号学生选过的每一门课，结果学生 x 都必须也选过。

23. 对于上题的关系 S、C 和 SC，试用域演算表达式表示查询。

使用谓词：S(sno, sname, age, sex)，C(cno, cname, teacher)，SC(sno, cno, grade)。

23.1 检索选修课程号为 K5 的学生学号和成绩。

答案：

{ <sno, grade> | SC(sno, 'K5', grade) }

23.2 检索选修课程号为 K8 的学生学号和姓名。

答案：

{ <sno, sname> | EXISTS age EXISTS sex EXISTS grade (S(sno, sname, age, sex) AND SC(sno, 'K8', grade)) }

25. 叙述使用 SQL 语言实现各种关系运算的方法。

答案：

讲解：SQL Server 没有教材中某些系统的 NATURAL JOIN，自然连接要显式写 JOIN ... ON 并选择需要的列。

26. 设有关系 A、B、AB，试用 SQL 写查询并给出结果。

题面关系：

A

B

AB

注意：原表中 AB.A# 出现 206 和 602，但 A 表没有这两个商店；题目第 3 问又说“代号为 256 的商店”。按常见教材题意，206 很可能是 256 的误写。下面 SQL 按题意写，结果说明会标注假设。

26.1 找出店员人数不超过 100 人或者在长沙的所有商店的代号和商品名。

SELECT DISTINCT a.[A#], b.BName
FROM dbo.A AS a
JOIN dbo.AB AS ab
  ON ab.[A#] = a.[A#]
JOIN dbo.B AS b
  ON b.[B#] = ab.[B#]
WHERE a.WQty <= 100
   OR a.City = N'长沙';

按原表中能与 A 表连接上的记录，结果为：

26.2 找出供应书包的商店名。

SELECT DISTINCT a.AName
FROM dbo.A AS a
JOIN dbo.AB AS ab
  ON ab.[A#] = a.[A#]
JOIN dbo.B AS b
  ON b.[B#] = ab.[B#]
WHERE b.BName = N'书包';

结果：

讲解：602 在 A 表中没有对应商店，所以按内连接不会出现在结果中。

26.3 找出至少供应代号为 256 的商店所供应的全部商品的商店名和所在城市。

SELECT a.AName, a.City
FROM dbo.A AS a
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.AB AS base_ab
    WHERE base_ab.[A#] = 256
      AND NOT EXISTS (
          SELECT 1
          FROM dbo.AB AS ab
          WHERE ab.[A#] = a.[A#]
            AND ab.[B#] = base_ab.[B#]
      )
);

如果把原表中的 206 按题意修正为 256，则 256 供应商品 1 和 2，结果为：

讲解：这是除法查询。逻辑是“不存在 256 供应的某个商品，而当前商店没有供应它”。

27. 有图书登记表 TS(BNO, BC, BNA, AU, PUB)，按要求用 SQL 语句设计。

27.a 按图书编号 BNO 建立 TS 表的索引 ITS。

CREATE INDEX ITS ON dbo.TS(BNO);

27.b 查找按出版社统计其出版图书总数。

SELECT PUB, COUNT(*) AS BookCount
FROM dbo.TS
GROUP BY PUB;

27.c 删除索引 ITS。

DROP INDEX ITS ON dbo.TS;

讲解：SQL Server 删除索引的语法是 DROP INDEX 索引名 ON 表名，不同于 Oracle 的 DROP INDEX 索引名。

28. 已知三个关系 R、S 和 T，试用 SQL 建立视图并分组求平均值。

题面关系：

R

S

T

28.1 将 R、S、T 按关联属性建立视图 R-S-T。

CREATE VIEW dbo.[R-S-T]
AS
SELECT r.A, r.B, r.C, s.D, s.E, t.F
FROM dbo.R AS r
JOIN dbo.S AS s
  ON s.A = r.A
JOIN dbo.T AS t
  ON t.D = s.D;

讲解：题中 T 表第一行写作 D2，而 S 表为 d2。SQL Server 默认排序规则通常大小写不敏感，若数据库使用大小写敏感排序规则，需要统一为 d2。

28.2 对视图按 A 分组，求 C 和 E 的平均值。

SELECT A,
       AVG(CAST(C AS decimal(10, 2))) AS AvgC,
       AVG(CAST(E AS decimal(10, 2))) AS AvgE
FROM dbo.[R-S-T]
GROUP BY A;

在大小写不敏感且 D2=d2 的假设下，结果为：

29. 设有关系 R 和 S，试用 SQL 实现查询和更新。

题面关系：

R

S

29.1 查询属性 C > 50 时，R 中与之相关联的属性 B 之值。

SELECT r.B
FROM dbo.R AS r
JOIN dbo.S AS s
  ON s.A = r.A
WHERE s.C > 50;

结果：b3。

29.2 当属性 C = 40 时，将 R 中与之相关联的属性 B 值修改为 B4。

UPDATE r
SET B = 'B4'
FROM dbo.R AS r
JOIN dbo.S AS s
  ON s.A = r.A
WHERE s.C = 40;

讲解：SQL Server 的连接更新使用 UPDATE r SET ... FROM R AS r JOIN ...。

30. 已知关系 R 和 S，执行给定 SQL，给出视图 H 内容和查询结果。

题面关系：

R

S

题面 SQL：

CREATE SQL VIEW H(A, B, C, D, E)
AS SELECT A, B, R.C, D, E
FROM R, S
WHERE R.C = S.C;

SELECT B, D, E
FROM H
WHERE C = 'C2';

答案：

原题 SQL 有明显笔误：S 表没有 C 列，因此 WHERE R.C = S.C 在题面关系上无法执行；并且题面 R.C 的值为 20, 22, 18, a2，与查询条件 C='C2' 也不一致。

按最常见修正，把连接条件改为 R.A = S.A，SQL Server 写法为：

CREATE VIEW dbo.H(A, B, C, D, E)
AS
SELECT r.A, r.B, r.C, s.D, s.E
FROM dbo.R AS r
JOIN dbo.S AS s
  ON s.A = r.A;

则视图 H 为：

若仍按题面查询 WHERE C='C2'，结果为空。若老师原意是 WHERE C=22 或原表 C 值应为 C2，对应结果为：

讲解：复习时要会判断题面是否类型和属性一致。关系模式不含某属性时，SQL 不能执行；不能为了凑答案忽略这个错误。

31. 已知关系 R，按 A 分组求 C 的最大值和最小值，并置于视图 RVE 中；再查询 A='98' 的记录。

题面关系：

答案：

CREATE VIEW dbo.RVE
AS
SELECT A,
       MAX(C) AS MaxC,
       MIN(C) AS MinC
FROM dbo.R
GROUP BY A;

SELECT A, MaxC, MinC
FROM dbo.RVE
WHERE A = '98';

视图 RVE：

查询结果：

32. 已知学生表 S(SNO, SN, SD, PROV) 和学生选课表 SC(SNO, CN, GR)，用 SQL 实现查询。

32.1 查询“信息系”的学生来自哪些省区。

SELECT DISTINCT PROV
FROM dbo.S
WHERE SD = N'信息系';

32.2 按分数降序排序，输出“英语系”的学生选修了“计算机”课程的学生姓名和分数。

SELECT s.SN, sc.GR
FROM dbo.S AS s
JOIN dbo.SC AS sc
  ON sc.SNO = s.SNO
WHERE s.SD = N'英语系'
  AND sc.CN = N'计算机'
ORDER BY sc.GR DESC;

讲解：DISTINCT 用于去重省区；排序只能保证最终查询结果顺序，不应依赖表内物理顺序。

33. 设学生表 S(SNO, SN) 和选课表 SC(SNO, CNO, CN, G)，完成视图和查询。

33.1 建立视图 V-SSC(SNO, SN, CNO, CN, G)，并按 CNO 升序排序。

SQL Server 写法：

CREATE VIEW dbo.[V-SSC]
AS
SELECT s.SNO, s.SN, sc.CNO, sc.CN, sc.G
FROM dbo.S AS s
JOIN dbo.SC AS sc
  ON sc.SNO = s.SNO;

SELECT SNO, SN, CNO, CN, G
FROM dbo.[V-SSC]
ORDER BY CNO ASC;

讲解：SQL Server 普通视图定义中不能为了显示顺序直接写 ORDER BY。视图只定义结果集合，排序应在查询视图时写。

33.2 从视图 V-SSC 上查询平均成绩在 90 分以上的 SN、CN 和 G。

WITH StudentAvg AS (
    SELECT SNO
    FROM dbo.[V-SSC]
    GROUP BY SNO
    HAVING AVG(CAST(G AS decimal(5, 2))) >= 90
)
SELECT v.SN, v.CN, v.G
FROM dbo.[V-SSC] AS v
JOIN StudentAvg AS a
  ON a.SNO = v.SNO;

讲解：题面说“平均成绩在 90 分以上”，通常按学生全部课程平均分理解。先按学生分组筛出平均分达标的学生，再返回这些学生的课程名和成绩。