数据库习题答案

第二章

2.6 设有关系R和S：

R A B C S A B C

3 6 7 3 4 5 2 5 7 7 2 3 7 2 3 4 4 3

计算R∪S，R-S，R∩S，R×S，π3,2（S）,σB<’5’(R),R 2<2 S, R S。 解： R∪S A B C R－S A B C R∩S 3 6 7 3 6 7 2 5 7 2 5 7 7 2 3 4 4 3 4 4 3 3 4 5

R×S R.A R.B R.C S.A S.B S.C π3，2（S） 3 6 7 3 4 5 3 6 7 7 2 3 2 5 7 3 4 5 2 5 7 7 2 3 7 2 3 3 4 5 7 2 3 7 2 3 4 4 3 3 4 5 4 4 3 7 2 3

σB<’5’（R） A B C R?S R.A R.B R.C S.A S.B S.C 7 2 3 7 2 3 3 4 4 3 R?S A B C 7 2 3

2.7 设有关系R和S：

R A B S B C a b b c c b e a d e b d 计算R ? S，R ? S，σA=C(R×S)，S ? R。

解： R?S A B C R?S R.A R.B S.B C a b c a b b c a b d a b b d c b c c b b c c b d c b b d d e a S ? R B C b c b d

A B C 7 2 3 B 5 4 3 2 4 5 C 2.17 设有4个关系：

S（S#，SNAME，AGE，SEX) SC（S#，C#，SCORE） C（C#，CNAME，T#） T(T#,TNAME,TITLE)

试用关系代数表达式表示下列查询语句：

① 检索年龄小于17岁的女学生的学号和姓名。 ② 检索年男学生所学课程的课程号和课程名。 ③ 检索男学生所学课程的任课教师的工号和姓名。 ④ 检索至少选修两门课程的学生学号。

⑤ 检索至少有学号为S2和S4学生选修的课程的课程号。 ⑥ 检索WANG同学不学的课程的课程号。

⑦ 检索全部学生都选修的课程的课程号与课程名。

⑧ 检索选修课程包含LIU老师所授全部课程的学生学号。 解：? πS#，SNAME（σAGE<’17’ ∧SEX=’F’（S）） ? Πc#，CNAME（σSEX='M'（S?SC?C）） ? ΠT#，TNAME（σSEX='M'（S?SC?C?T）） ? π1（σ1=4 ∧ 2≠5（SC×SC））

? π2（σ1=S2 ∧4=S4∧ 2=5（SC×SC）） ? πC#（C）－πC#（σSNAME='WANG'（S?SC）） ? πC#，CNAME（C?（πS#，C#（SC）÷πS#（S））） ? πS#，C#（SC）÷πC#（σTNAME='LIU'（C?T））

2.21 在教学数据库的关系S、SC、C中，用户有一查询语句：检索女同学选修课程的课程名和任课教师名。

① 试写出该查询的关系代数表达式。 ② 画出查询表达式的语法树。

③ 使用启发式优化算法，对语法树进行优化，并画出优化后的语法树。 解：① 关系代数表达式为：

πCNAME，TEACHER（σSEX=’F’（S?SC?C?T）） 上述的关系代数表达式为：

πCNAME，TEACHER（σSEX=’F’（πL（σS.S#=SC.S# ∧ SC.C#=C.C#（（S×SC）×C）））） 此处L为S、SC、C中全部属性（公共属性只取一次）。

②上述关系代数表达式的语法树如图2.2所示。

图2.2

③ 上述的关系代数表达式为： 优化后的语法树如图2.3所示。

图2.3

第三章

3.2 对于教学数据库的4个基本表 S（S#，SNAME，AGE，SEX) SC（S#，C#，SCORE） C（C#，CNAME，T#） T(T#,TNAME,TITLE) 试用SQL的查询语句表达下列查询： 试用关系代数表达式表示下列查询语句：

① 检索年龄小于17岁的女学生的学号和姓名。 ② 检索年男学生所学课程的课程号和课程名。 ③ 检索男学生所学课程的任课教师的工号和姓名。 ④ 检索至少选修两门课程的学生学号。

⑤ 检索至少有学号为S2和S4学生选修的课程的课程号。 ⑥ 检索WANG同学不学的课程的课程号。

⑦ 检索全部学生都选修的课程的课程号与课程名。

⑧ 检索选修课程包含LIU老师所授全部课程的学生学号。

解：① SELECT S#, SNAME FROM S

WHERE AGE<23 AND SEX=’F’; ② SELECT C.C#,CNAME FROM S,SC,C

WHERE S.S#=SC.S# AND SC.C#=C.C# AND SEX=’M’; ③ SELECT T#，TEACHER FROM S,SC, C,T

WHERE S.S#=SC.S# AND SC.C#=C.C# AND C.T#=T.T# AND SEX=’M’; ④ SELECT DISTINCT X.S# FROM SC AS X, SC AS Y

WHERE X.S#=Y.S# AND X.C#!=Y.C# ⑤ SELECT DISTINCT X.C# FROM SC AS X, SC AS Y

WHERE X.S#=S2 AND Y.S#=S4 AND X.C#=Y.C#; 嵌套写法：SELECT C# FROM SC

WHERE S#=S2 AND C# IN

(SELECT C# FROM SC

WHERE SC.S#=S4); ⑥ SELECT C# FROM C

WHERE NOT EXISTS (SELECT * FROM S, SC

WHERE S.S#=SC.S# AND SC.C#=C.C# AND SNAME=’WANG’); ⑦ SELECT C#, CNAME FROM C

WHERE NOT EXISTS (SELECT * FROM S

WHERE NOT EXISTS (SELECT * FROM SC

WHERE S#=S.S# AND C#=C.C#)); ⑧ SELECT DISTINCT S# FROM SC AS X WHERE NOT EXISTS (SELECT * FROM C

WHERE TNAME=’LIU’ AND NOT EXISTS (SELECT *

FROM SC AS Y

WHERE Y.S#=X.S# AND Y.C#=C.C#));

3.7 试用SQL查询语句表达下列对3.2题中4个基本表S、SC、C、T的查询： ① 统计有学生选修的课程门数。

② 求选修C4课程的女学生的平均年龄。

③ 求LIU老师所授课程的每门课程的平均成绩。

④ 统计每门课程的学生选修人数（超过10人的学生才统计）。要求显示课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。 ⑤ 检索学号比WANG同学大，而年龄比他小的学生姓名。 ⑥ 在表SC中检索成绩为空值的学生学号和课程号。 ⑦ 检索姓名以L打头的所有学生的姓名和年龄。

⑧ 求年龄大于女同学平均年龄的男学生姓名和年龄。 ⑨ 求年龄大于所有女同学年龄的男学生姓名和年龄。 解：① SELECT COUNT(DISTINCT C#) FROM SC； ② SELECT AVG(AGE)

FROM S, SC

WHERE S.S#=SC.S# AND C#=’C4’ AND SEX=’F’； ③ SELECT C.C#，AVG(GRADE) FROM SC，C,T

WHERE SC.C#=C.C# AND SC.T#=T.T# TNAME=‘LIU’ GROUP BY C.C#；

④ SELECT C#, COUNT(S#) FROM SC GROUP BY C#

HAVING COUNT(*)>10 ORDER BY 2 DESC, 1； ⑤ SELECT SNAME FROM S

WHERE S#>ALL(SELECT S# FROM S

WHERE SNAME=’WANG’) AND AGE

WHERE SNAME=’WANG’)； ⑥ SELECT S#, C# FROM SC

WHERE GRADE IS NULL； ⑦ SELECT SNAME, AGE FROM S

WHERE SNAME LIKE ’L%’； ⑧ SELECT SNAME, AGE FROM S

WHERE SEX=’M’

AND AGE>(SELECT AVG(AGE) FROM S

WHERE SEX=’F’)； ⑨ SELECT SNAME, AGE FROM S

WHERE SEX=’M’

AND AGE>ALL(SELECT AGE FROM S

WHERE SEX=’F’)；

3.8 对于下面的关系R和S，试求出下列各种联接操作的执行结果： ① R NATURAL INNER JOIN S

② R NATURAL RIGHT OUTER JOIN S ③ R RIGHT OUTER JOIN S USING（C） ④ R INNER JOIN S

⑤ R FULL OUTER JOIN S ON false

R A B C S B C D a1 b1 c1 b1 c1 d1 a2 b2 c2 b2 c2 d2 a3 b3 c3 b4 c4 d4 解：

① A B C D ② A B C D ③ A R.B C S.B D a1 b1 c1 d1 a1 b1 c1 d1 a1 b1 c1 b1 d1 a2 b2 c2 d2 a2 b2 c2 d2 a2 b2 c2 b2 d2

null b4 c4 d4 null null c4 b4 d4

④ A R.B R.C S.B S.C D ⑤ A R.B R.C S.B S.C D

a1 b1 c1 b1 c1 d1 a1 b1 c1 null null null a1 b1 c1 b2 c2 d2 a2 b2 c2 null null null a1 b1 c1 b4 c4 d4 a3 b3 c3 null null null a2 b2 c2 b1 c1 d1 null null null b1 c1 d1

a2 b2 c2 b2 c2 d2 null null null b2 c2 d2

a2 b2 c2 b4 c4 d4 null null null b4 c4 d4

a3 b3 c3 b1 c1 d1 a3 b3 c3 b2 c2 d2 a3 b3 c3 b4 c4 d4

3.12 试用SQL更新语句表达对3.2题教学数据库中关系S、SC、C、T的更新操作： ① 往关系C中插一个课程元组（'C8'，'VC++'，'T6'）。

② 检索所授每门课程平均成绩均大于80分的教师姓名，并把检索到的值送往另一个已存在的表FACULTY（TNAME）。

③ 在SC中删除尚无成绩的选课元组。

④ 把选修LIU老师课程的女同学选课元组全部删去。 ⑤ 把MATHS课不及格的成绩全改为60分。

⑥ 把低于所有课程总平均成绩的女同学成绩提高5％。

⑦ 在表SC中修改C4课程的成绩，若成绩小于等于70分时提高5％，若成绩大于70 分时提高4％（用两种方法实现，一种方法是用两个UPDATE语句实现，另一种方法是用带CASE操作的一个UPDATE语句实现）。

⑧ 在表SC中，当某个成绩低于全部课程的平均成绩时，提高5%。 解：① INSERT INTO C

VALUES('C8'，'VC++'，'T6')； ② INSERT INTO FACULTY(TNAME) SELECT DISTINCT TNAME

FROM (SELECT TNAME, C.C#, AVG(GRADE)

FROM SC,C,T

WHERE SC.C#=C.C# AND C.T#=T.# GROUP BY T#, C.C#)

AS RESULT(TNAME, C#, AVG_GRADE) AS X WHERE 80<=ALL(SELECT AVG_GRADE FROM RESULT AS Y

WHERE Y.TEACHER=X.TEACHER)； ③ DELETE FROM SC WHERE SCORE IS NULL； ④ DELETE FROM SC

WHERE S# IN(SELECT S# FROM S WHERE SEX='F')

AND C# IN(SELECT C# FROM C,T WHERE C.T#=T.# AND TNAME='LIU')； ⑤ UPDATE SC SET SCORE=60 WHERE SCORE<60

AND C# IN(SELECT C# FROM C WHERE CNAME='MATHS')； ⑥ UPDATE SC

SET SCORE=SCORE*1.05

WHERE S# IN(SELECT S# FROM S WHERE SEX='F') AND SCORE<(SELECT AVG(SCORE) FROM SC)； ⑦ 用两个UPDATE语句实现： UPDATE SC

SET SCORE=SCORE*1.04

WHERE C#='C4' AND SCORE>70； UPDATE SC

SET SCORE=SCORE*1.05

WHERE C#='C4' AND SCORE<=70；

（这两个UPDATE语句的顺序不能颠倒。） 用一个UPDATE语句实现： UPDATE SC

SET SCORE=SCORE*CASE WHEN SCORE>70 THEN 1.04 ELSE 1.05 END

WHERE C#='C4'； ⑧ UPDATE SC

SET SCORE=SCORE*1.05

WHERE SCORE<(SELECT AVG(SCORE) FROM SC)； 3.13 设数据库中有三个关系：

职工表 EMP（E#，ENAME，AGE，SEX，ECITY）， 其属性分别表示职工工号、姓名、年龄、性别和籍贯。 工作表 WORKS（E#，C#，SALARY），

其属性分别表示职工工号、工作的公司编号和工资。

公司表 COMP（C#，CNAME，CITY），

其属性分别表示公司编号、公司名称和公司所在城市。 试用SQL语句写出下列操作：

① 用CREATE TABLE语句创建上述三个表，需指出主键和外键。 ② 检索超过50岁的男职工的工号和姓名。

③ 假设每个职工只能在一个公司工作，检索工资超过1000元的男性职工工号和姓名。 ④ 假设每个职工可在多个公司工作，检索在编号为C4和C8公司兼职的职工工号和姓名。 ⑤ 检索在“联华公司”工作、工资超过1000元的男性职工的工号和姓名。

⑥ 假设每个职工可在多个公司工作,检索每个职工的兼职公司数目和工资总数.显示（E#，NUM，SUM_SALARY），分别表示工号、公司数目和工资总数。 ⑦ 工号为E6的职工在多个公司工作，试检索至少在E6职工兼职的所有公司工作的职工工号。 ⑧ 检索联华公司中低于本公司平均工资的职工工号和姓名。

⑨ 在每一公司中为50岁以上职工加薪100元（若职工为多个公司工作，可重复加）。 ⑩ 在EMP表和WORKS表中删除年龄大于60岁的职工有关元组。 解：① CREATE TABLE EMP

( E# CHAR(4) NOT NULL, ENAME CHAR(8) NOT NULL, AGE SMALLINT, SEX CHAR(1),

ECITY CHAR(20), PRIMARY KEY(E#))； CREATE TABLE COMP

( C# CHAR(4) NOT NULL, CNAME CHAR(20) NOT NULL, CITY CHAR(20), PRIMARY KEY(C#))；

CREATE TABLE WORKS ( E# CHAR(4) NOT NULL, C# CHAR(4) NOT NULL, SALARY SMALLINT, PRIMARY KEY(E#, C#),

FOREIGN KEY(E#) REFERENCES EMP(E#), FOREIGN KEY(C#) REFERENCES COMP(C#))； ② SELECT E#, ENAME FROM EMP

WHERE AGE>50 AND SEX='M'； ③ SELECT EMP.E#, ENAME FROM EMP, WORKS

WHERE EMP.E#=WORKS.E# AND SALARY>1000； ④ SELECT A.E#, A.ENAME FROM EMP A, WORKS B, WORKS C WHERE A.E#=B.E# AND B.E#=C.E# AND B.C#='C4' AND C.C#='C8'； ⑤ SELECT A.E#, A.ENAME

FROM EMP A, WORKS B, COMP C WHERE A.E#=B.E# AND B.C#=C.C#

AND CNAME='联华公司' AND SALARY>1000 AND SEX='M'；

⑥ SELECT E#, COUNT(C#) AS NUM, SUM(SALARY) AS SUM_SALARY FROM WORKS GROUP BY E#； ⑦ SELECT X.E# FROM WORKS X WHERE NOT EXISTS (SELECT * FROM WORKS Y WHERE E#='E6' AND NOT EXISTS (SELECT * FROM WORKS Z WHERE Z.E#=X.E# AND Z.C#=Y.C#))；

⑧ SELECT A.E#, A.ENAME FROM EMP A, WORKS B, COMP C WHERE A.E#=B.E# AND B.C#=C.C# AND CNAME='联华公司'

AND SALARY<(SELECT AVG(SALARY) FROM WORKS, COMP

WHERE WORKS.C#=COMP.C# AND CNAME='联华公司'）； ⑨ UPDATE WORKS

SET SALARY=SALARY+100

WHERE E# IN (SELECT E# FROM EMP WHERE AGE>50)； ⑩ DELETE FROM WORKS

WHERE E# IN (SELECT E# FROM EMP WHERE AGE>60)； DELETE FROM EMP WHERE AGE>60；

3.14 对第3.13题中的关系建立一个有关女职工信息的视图EMP_WOMAN，属性包括（E#，ENAME，C#，CNAME，SALARY）。

然后对视图EMP_WOMAN操作，检索每一位女职工的工资总数。（假设每个职工可在多个公司兼职）

解： CREATE VIEW EMP_WOMAN

AS SELECT A.E#, A.ENAME, C.C#, CNAME, SALARY FROM EMP A, WORKS B, COMP C WHERE A.E#=B.E# AND B.C#=C.C# AND SEX='F'；

SELECT E#，SUM(SALARY) FROM EMP_WOMAN

本文来源：https://www.bwwdw.com/article/bp03.html