SQL及实践答案
例1．关系代数
(1). R1=∏Y ,T (R) (2). R2=σP>5 ∧ T=e (R) (3). R3=R
W
(4). R4=∏[2],[1],[6](σ[3]=[5](R D)) (5). R5=R D
R
P
Q
T
Y
2 b c d 9 a e f 9 a d e 2 b e f 7 g e f 7 g c d
W
T
Y
B
c d m
c d n d f n
D
T
Y c d e f
R1
Y T d c f e e d
R2
P
Q
T
Y
9 a e f 7 g e f
R3
P
Q
T
Y
2 b c d m
B
2 b c d n 7 g c d m 7 g c d n
R4
Q
P
Y
b 2 d a 9 f b 2 f g 7 f g 7 d
R5
P Q 2 b 7 g
例2.设有职工关系模式如：people(pno, pname, sex, job, wage, dptno), 其中，pno 为职工号，pname 为职工姓名，sex 为性别，job 为职业，wage 为工资，dptno 为所在部门号。请写出下列查询的SQL 语句：
(1).查询工资大于“赵明华”工资的所有职工信息；
(2).查询工资比其所在部门平均工资高的所有职工信息。
解：(1). SELECT * FROM people WHERE wage > (SELECT wage FROM people
WHERE pname=“赵明华” ) (2). SELECT *
FROM people R WHERE wage >
( SELECT A VG(wage) FROM people S
WHERE S.dptno = R.dptno )
例3. 设有如下关系表R,S 和T 。
R(BH, XM, XB, DWH) S(DWH, DWM)
T(BH, XM, XB, DWH)
写出以下关系代数表达式的SQL 语句。
(1). R ?T (2).σDWH=100(R) (3).∏XM,XB (R) (4). ∏XM,XB (σXB=’女’(R))
(5).R S (6). ∏XM,XB,DWM (σXB=’男’(R S))
解(1).SELECT * FROM R UNION SELECT * FROM T
(2).SELECT * FROM R WHERE DWH=100 (3).SELECT XM, XB FROM R
(4).SELECT XM, XB FROM R WHERE XB=‘女’ (5).SELECT BH, XM, XB, R.DWH, DWM FROM R, S
WHERE R.DWH=S.DWH
(6).SELECT XM, XB, S.DWH FROM R, S
WHERE R.DWH=S.DWH AND XB=‘男’
例 4. 设有如下图所示的三个基本表。表中各个属性的含义如下：A# (商店代号)，ANAME （商店品），WQTY （店员人数）、
CITY （所在城市）、B#（商品号）、BNAME （商品名称）、PRICE
（价格）、QTY （商品数量）。试用SQL 语句写出下列查询：
（1）.找出店员人数不超过100人或者位于上海的商店的代号和商店名；
（2）.找出供应书包的商店名。
（3）.找出至少供应256号商店所供应某种商品的商店名和所在城市。
（4）.找出至少供应256号商店所供应全部商品的商店名。
解：（1）. SELECT A#，ANAME
FROM A
WHER WQTY<=100 OR CITY=‘上海’
(2). SELECT A.ANAME
FROM A, B, AB
WHERE A.A#=AB.A# AND B.B#=AB.B# AND
B.BNAME=‘书包’
(3).SELECT A.ANAME, A.CITY
FROM A, AB
WHERE A.A#=AB.A# AND AB.B# IN (
SELECT AB.B#
FROM AB
WHERE A#=‘256’)
(4).
SELECT A.ANAME, A.CITY
FROM A
WHERE NOT EXISTS (
SELECT B#
FROM B B1
WHERE EXISTS (
SELECT *
FROM AB
WHERE B# = B1.B# AND A#= ‘256’
AND NOT EXISTS (
SELECT *
FROM AB
WHERE B# = B1.B# AND A#= A.A# ))
)
5．给定选课关系SC，分别用关系代数与SQL语句写出查询：(1).求选修了至少3门课的学生学号；(2).求仅选修了1门课的学生学号；（1）∏R.s# (σR.s#=S.s#∧R.s#=T.s#∧S.s#=T.s#∧R.c#
≠S.c#∧R.c#≠T.c#∧S.c#≠T.c#
(ρR(sc)×ρS(sc)×ρT(sc))
SELECT R.S#
FROM SC AS R, SC AS S, SC AS T
WHERE R.S#= S.S#AND R.S#= T.S#AND S.S#= T.S# AND R.C#<> S.C# AND R.C#<> T.C#AND S.C#<> T.C#
或Select s# from sc group by s# having count(c#)>=3 (2)∏S#(SC)-∏R.s#(σR.s#=S.s#∧R.c#≠S.c# (ρR(sc)×ρS(sc))
Select s# from sc group by s# having count(c#) =1
6．阐述关系模式的完整性。
关系模式的完整性主要包括实体完整性、参照完整性和用户定义的完整性。实体完整性是指关系的主码中的属性值不能为空值，由于实体是相互可区分的，通过主码来唯一标识，若主码为空，则出现不可标识的实体，这是不容许的参照完整性是指如果关系R2的外码Fk与关系R1的主码Pk相对应，则R2中的每一个元组的Fk值或者等于R1 中某个元组的Pk 值，或者为空值；用户定义的完整性是指用户针对具体应用环境定义的完整性约束条件。实体完整性和参照完整性由系统自动支持，系统提供定义和检验用户定义的完整性的机制。7．阐述外键约束下，删除基本关系元组与修改基本关系主码时所采用几种方式。
作为主码的关系称为基本关系，作为外码的关系称为依赖关系。删除基本关系元组：
RESTRICT方式只有当依赖关系中没有一个外码值与要删除的基本关系的主码值相对应时，才可以删除该元组，否则系统拒绝此删除操作
CASCADE方式将依赖关系中所有外码值与基本关系中要删除的主码值所对应的元组一起删除
SET NULL方式删除基本关系中元组时，将依赖关系中与基本关系中被删主码值相对应的外码值置为空值
对于这三种方式，选择哪一种，要视应用环境的语义而定。
修改基本关系的主码值时，依赖关系可以采取的做法与上述删除操作类似。
8．阐述存取控制技术中角色的意义。
角色是一组相关权限的结合，即将多个不同的权限集合在一起就形成了角色。便于给用户一组权限，便于用户操作
9．举例说明相关子查询的执行机制。
in后的子查询与外层查询无关，每个子查询执行一次，而exists后的子查询与外层查询有关，需要执行多次，称之为相关子查询
列出选修了001号和002号课程的学生的学号
select S#
from SC SC1
where SC1.C# = 001
and exists
(select S#
from SC SC2
where SC2. C# = 002
and SC2.S# = SC1.S#)