6数据库习题作业

SET SCORE=SCORE * 1.05 WHERE SCORE<(SELECT AVG(SCORE) FROM SC)；
【3．13】设数据库中有 3 个关系： 职工表 EMP(E#，ENAME，AGE，SEX，ECITY)，其属性分别表示职工工号、姓名、年 龄、性别和籍贯。 工作表 WORKS(E#，C#，SALARY)，其属性分别表示职工工号、工作的公司编号和工资。 公司表 COMP(C#，CNAME，CITY)，其属性分别表示公司编号、公司名称和公司所在城 市。 试用 SQL 语句写出下列操作： ①用 CREATE TABLE 语句创建上述 3 个表，需指出主键和外键。 ②检索超过 50 岁的男职工的工号和姓名。 ③假设每个职工只能在一个公司工作，检索工资超过 1 000 元的男性职工工号和姓名。 ④假设每个职工可在多个公司工作，检索至少在编号为 C4 和 C8 的公司兼职的职工工号 和姓名。 ⑤检索在联华公司工作、工资超过 1 000 元的男性职工的工号和姓名。 ⑥假设每个职工可在多个公司工作，检索每个职工的兼职公司数目和工资总数，显示 (E#，NUM，SUM_SALARY)，分别表示工号、公司数目和工资总数。 ⑦工号为 E6 的职工在多个公司工作，试检索至少在 E6 职工兼职的所有公司工作的职工 工号。 ⑧检索联华公司中低于本公司平均工资的职工工号和姓名。 ⑨在每一公司中为 50 岁以上的职工加薪 100 元(若职工为多个公司工作，可重复加)。 ⑩在 EMP 表和 WORKS 表中删除年龄大于 60 岁的职工有关元组。 解： ①CREATE TABLE EMP
2.21 在教学数据库的关系 S、SC、C、T 中，用户有一查询语句：检索女同 学选修课程的课程名和任课教师名。
① 试写出该查询的关系代数表达式。 ② 画出查询表达式的语法树。 ③ 使用启发式优化算法，对语法树进行优化，并画出优化后的语法树。 解：
【3.2】设教学数据库中有 4 个关系： 教师关系 T(T#，TNAME，TITLE) 课程关系 C(C#，CNAME,T#) 学生关系 S(S#，SNAME,AGE,SEX) 选课关系 SC(S#，C#，SCORE) 试用 SQL 查询语句表示下列查询。 解： ①检索年龄小于 17 岁的女学生的学号和姓名。 SELECT S#．SNAME FROM S WHERE AGE<17 AND SEX=’F’ ②检索男学生所学课程的课程号和课程名。 SELECT C．C#，CNAME (连接查询方式) FROM S，SC，C WHERE S．S#=SC.S# AND SC.C#=C.C# AND SEX=’M’； ③检索男学生所学课程的任课老师的工号和姓名。
【3．12】 解： ①INSERT INTO C VALUES(‘C8’，’VC++’，’T6’)； ② 法一： INSERT INTO FACULTY(TNAME) SELECT DISTINCT TNAME FROM (SELECT TNAME，C．C#，AVG(SCORE)
FROM T, C, SC WHERE T．T#=C．T# AND C．C#=SC．C# GROUP BY TNAME，C．C#) AS RESULT(TNAME，C#，AVG—SCORE) AS X WHERE 80<=ALL(SELECT AVG—SCORE
【3．7】试用 SQL 查询语句表达下列对第 3．2 题中 4 个基本表 T、C、S、SC 的查询。 ①统计有学生选修的课程门数。 SELECT COUNT(DISTINCT C#) FROM SC； ②求选修 C4 课程的女学生的平均年龄。 SELECT AVG(AGE) FROM S，SC WHERE S．S#=SC．S# AND C#=’C4’ AND SEX=’F’： ③求 LIU 老师所授的每门课程的平均成绩。 SELECT C．C#．AVG(SCORE) FROM SC，C，T WHERE SC．C#=C．C# AND C．T#=T．T# AND TNAME=’LIU’ GROUP BY C．C#； ④统计选修每门课程的学生人数(超过 l0 人的课程才统计)。要求显示课程号和人数，查询结 果按人数降序排列，若人数相同，则按课程号升序排列。 SELECT C#．COUNT(S#) FROM SC GROUP BY C# HAVING COUNT(*)>10 ORDER BY 2 DESC，1； ⑤检索学号比 WANG 同学大，而年龄比他小的学生姓名。 SELECT SNAME FROM S WHERE S#>ALL(SELECT S# FROM S
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#))；
⑧检索选修课程包含 LIU 老师所授全部课程的学生学号。 法一： SELECT DISTINCT S# FROM SC AS X WHERE NOT EXISTS (SELECT * FROM C，T WHERE C.T#=T.T# AND TNAME=’LIU’ AND NOT EXISTS (SELECT{ FROM SC AS Y WHERE Y.S#=X.S# AND Y.C#=C.C#))； 法二： SELECT DISTINCT S# FROM SC X WHERE NOT EXISTS ((SELECT C# FROM C,T WHERE C.T#=T.T# AND TNAME='LIU’) EXCEPT (SELECT C# FROM SC Y WHERE Y.S#=X.S#))；
2.17 设有三个关系： T（T#,TNMAE,TITLE） C（C#，CNAME，T#） S（S#，SNAME，AGE，SEX) SC（S#，C#，SCORE）
试用关系代数表达式表示下列查询语句： ① 检索年龄小于 17 岁的女学生的学号和姓名。 ② 检索男学生所学课程的课程号和课程名。 ③ 检索男学生所学课程的任课教师的工号和姓名。 ④ 检索至少选修两门课的学生学号。 ⑤ 检索至少有学号为 S2 和 S4 学生选修的课程的课程号。 ⑥ 检索 WANG 同学不学的课程的课程号。 ⑦ 检索全部学生都选修的课程的课程号与课程名。 ⑧ 检索选修课程包含 LIU 老师所授全部课程的学生学号。
最小和最大的元组数量：
⑴ U∩V
பைடு நூலகம்
⑵ U∪V
⑶ U⋈V
⑸ πL(U)－V 解：
操作
⑷ σF(U)×V （其中 L 为某属性集）
（F 为某个条件）
最小元组数
最大元组数
⑴ U∩V ⑵ U∪V ⑶ U⋈V ⑷σF(U)×V ⑸πL(U)－V
0 max(m,n) 0 0 0
min(m,n) m+n m×n m×n m
SELECT T.T#, TNAME 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#; ⑤检索至少有学号为 S2 和 S4 的学生选修的课程的课程号。 SELECT DISTINCT X.C# FROM SC AS X, SC AS Y WHERE X.S#=’S2’ AND Y.S#=’S4’ AND X.C#=Y.C#； ⑥检索 WANG 同学不学的课程的课程号。 SELECT C# FROM C WHERE NOT EXISTS (SELECT * FROM S, SC WHERE S.S#=SC.S# AND SC.C#=C.C# AND SNAME=’WANG’)； ⑦检索全部学生都选修的课程的课程号与课程名。
解：⑴ π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））
WHERE SNAME=’WANG’) AND AGE<ALL( SELECT AGE FROM S WHERE SNAME=’WANG)； ⑥在表 SC 中检索成绩为空值的学生的学号和课程号。 SELECT S#, C# FROM SC WHERE SCORE IS NULL； ⑦检索姓名以 L 开头的所有学生的姓名和年龄。 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’)；
数据库习题作业重点（第 2-3-4 章）
2.6 设有关系 R 和 S：
RABC 367 257 723 443
S ABC 345 723
计算 R∪S，R-S，R∩S，R×S，π （S）,σ (R),R S, R S。
3,2
B<’5’