数据库习题作业

数据库习题作业重点（第2-3-4章）2.6设有关系R和S：RABCSABC367345257723723443计算R∪S，R-S，R∩S，R×S，π3,2（S）,σB’5’(R),R22S,RS。解：R∪SABCR－SABCR∩SABC367367723257257723443443345R×SR.AR.BR.CS.AS.BS.Cπ3，2（S）CB3673455436772332257345257723723345723723443345443723σB’5’（R）ABCR⋈SR.AR.BR.CS.AS.BS.C723723345443R⋈SABC7232.7设有关系R和S：RABSBCabbccbeadebd计算R⋈S，R⋈S，σA=C(R×S)，S⋉R。22BC2.8假设关系U和V分别有m个元组和n个元组，给出下列表达式中可能的最小和最大的元组数量：⑴U∩V⑵U∪V⑶U⋈V⑷σF(U)×V（F为某个条件）⑸πL(U)－V（其中L为某属性集）解：操作最小元组数最大元组数⑴U∩V0min(m,n)⑵U∪Vmax(m,n)m+n⑶U⋈V0m×n⑷σF(U)×V0m×n⑸πL(U)－V0m2.17设有三个关系：T（T#,TNMAE,TITLE）C（C#，CNAME，T#）S（S#，SNAME，AGE，SEX)SC（S#，C#，SCORE）试用关系代数表达式表示下列查询语句：①检索年龄小于17岁的女学生的学号和姓名。②检索男学生所学课程的课程号和课程名。③检索男学生所学课程的任课教师的工号和姓名。④检索至少选修两门课的学生学号。⑤检索至少有学号为S2和S4学生选修的课程的课程号。⑥检索WANG同学不学的课程的课程号。⑦检索全部学生都选修的课程的课程号与课程名。⑧检索选修课程包含LIU老师所授全部课程的学生学号。eab解：⑴π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、T中，用户有一查询语句：检索女同学选修课程的课程名和任课教师名。①试写出该查询的关系代数表达式。②画出查询表达式的语法树。③使用启发式优化算法，对语法树进行优化，并画出优化后的语法树。解：【3.2】设教学数据库中有4个关系：教师关系T(T#，TNAME，TITLE)课程关系C(C#，CNAME,T#)学生关系S(S#，SNAME,AGE,SEX)选课关系SC(S#，C#，SCORE)试用SQL查询语句表示下列查询。解：①检索年龄小于17岁的女学生的学号和姓名。SELECTS#．SNAMEFROMSWHEREAGE17ANDSEX=’F’②检索男学生所学课程的课程号和课程名。SELECTC．C#，CNAME(连接查询方式)FROMS，SC，CWHERES．S#=SC.S#ANDSC.C#=C.C#ANDSEX=’M’；③检索男学生所学课程的任课老师的工号和姓名。SELECTT.T#,TNAMEFROMS，SC，C，TWHERES.S#=SC.S#ANDSC.C#=C.C#ANDC.T#=T.T#ANDSEX=’M’;④检索至少选修两门课程的学生学号。SELECTDISTINCTX.S#FROMSCASX,SCASYWHEREX.S#=Y.S#ANDX.C#!=Y.C#;⑤检索至少有学号为S2和S4的学生选修的课程的课程号。SELECTDISTINCTX.C#FROMSCASX,SCASYWHEREX.S#=’S2’ANDY.S#=’S4’ANDX.C#=Y.C#；⑥检索WANG同学不学的课程的课程号。SELECTC#FROMCWHERENOTEXISTS(SELECT*FROMS,SCWHERES.S#=SC.S#ANDSC.C#=C.C#ANDSNAME=’WANG’)；⑦检索全部学生都选修的课程的课程号与课程名。SELECTC#,CNAMEFROMCWHERENOTEXISTS(SELECT*FROMSWHERENOTEXISTS(SELECT*FROMSCWHERES#=S.S#ANDC#=C.C#))；⑧检索选修课程包含LIU老师所授全部课程的学生学号。法一：SELECTDISTINCTS#FROMSCASXWHERENOTEXISTS(SELECT*FROMC，TWHEREC.T#=T.T#ANDTNAME=’LIU’ANDNOTEXISTS(SELECT{FROMSCASYWHEREY.S#=X.S#ANDY.C#=C.C#))；法二：SELECTDISTINCTS#FROMSCXWHERENOTEXISTS((SELECTC#FROMC,TWHEREC.T#=T.T#ANDTNAME='LIU’)EXCEPT(SELECTC#FROMSCYWHEREY.S#=X.S#))；【3．7】试用SQL查询语句表达下列对第3．2题中4个基本表T、C、S、SC的查询。①统计有学生选修的课程门数。SELECTCOUNT(DISTINCTC#)FROMSC；②求选修C4课程的女学生的平均年龄。SELECTAVG(AGE)FROMS，SCWHERES．S#=SC．S#ANDC#=’C4’ANDSEX=’F’：③求LIU老师所授的每门课程的平均成绩。SELECTC．C#．AVG(SCORE)FROMSC，C，TWHERESC．C#=C．C#ANDC．T#=T．T#ANDTNAME=’LIU’GROUPBYC．C#；④统计选修每门课程的学生人数(超过l0人的课程才统计)。要求显示课程号和人数，查询结果按人数降序排列，若人数相同，则按课程号升序排列。SELECTC#．COUNT(S#)FROMSCGROUPBYC#HAVINGCOUNT(*)10ORDERBY2DESC，1；⑤检索学号比WANG同学大，而年龄比他小的学生姓名。SELECTSNAMEFROMSWHERES#ALL(SELECTS#FROMSWHERESNAME=’WANG’)ANDAGEALL(SELECTAGEFROMSWHERESNAME=’WANG)；⑥在表SC中检索成绩为空值的学生的学号和课程号。SELECTS#,C#FROMSCWHERESCOREISNULL；⑦检索姓名以L开头的所有学生的姓名和年龄。SELECTSNAME,AGEFROMSWHERESNAMELIKE’L％’;⑧求年龄大于女同学平均年龄的男学生的姓名和年龄。SELECTSNAME,AGEFROMSWHERESEX=’M’ANDAGE(SELECTAVG(AGE)FROMSWHERESEX=’F’)；⑨求年龄大于所有女同学年龄的男学生的姓名和年龄。SELECTSNAME,AGEFROMSWHERESEX=‘M’ANDAGEALL(SELECTAGEFROMSWHERESEX=’F’)；【3．12】解：①INSERTINTOCVALUES(‘C8’，’VC++’，’T6’)；②法一：INSERTINTOFACULTY(TNAME)SELECTDISTINCTTNAMEFROM(SELECTTNAME，C．C#，AVG(SCORE)FROMT,C,SCWHERET．T#=C．T#ANDC．C#=SC．C#GROUPBYTNAME，C．C#)ASRESULT(TNAME，C#，AVG—SCORE)ASXWHERE80=ALL(SELECTAVG—SCOREFROMRESULTASYWHEREY．TNAME=X．TNAME)；法二INSERTINTOFACULTY(TNAME)SELECTDISTINCTTNAMEFROMT,C,SCWHEREC．C#=SC．C#ANDT．T#=C．T#ANDT#NOTIN(SELECTT#FROMCWHEREC#IN(SELECTC#FROMSCGROUPBYC#HAVINGAVG(SCORE)=80))③DELETEFROMSCWHERESCOREISNULL；④DELETEFROMSCWHERES#IN(SELECTS#FROMSWHERESEX=’F’)ANDC#IN(SELECTC#FROMC,TWHEREC.T#=T.T#ANDTNAME=’LIU’);⑤UPDATESCSETSCORE=60WHERESCORE60ANDC#IN(SELECTC#FROMCWHERECNAME=’MATHS’)；⑥UPDATESCSETSCORE=SCORE*1.05WHERES#IN(SELECTS#FROMSWHERESEX='F’)ANDSCORE(SELECTAVG(SCORE)FROMSC)；⑦用两个UPDATE语句实现：UPDATESCSETSCORE=SCORE*1.04WHEREC#=’C4’ANDSCORE70；UPDATESCSETSCORE=SCORE*1.05WHEREC#=’C4’ANDSCORE=70；这两个UPDATE语句的顺序不能颠倒。用一个UPDATE语句实现：UPDATESCSETSCORE=SCORE*CASEWHENSCORE70THEN1.04ELSEl.05ENDWHEREC#=’C4’;⑧UPDATESCSETSCORE=SCORE*1.05WHERESCORE(SELECTAVG(SCORE)FROMSC)；【3．13】设数据库中有3个关系：职工表EMP(E#，ENAME，AGE，SEX，ECITY)，其属性分别表示职工工号、姓名、年龄、性别和籍贯。工作表WORKS(E#，C#，SALARY)，其属性分别表示职工工号、工作的公司编号和工资。公司表COMP(C#，CNAME，CITY)，其属性分别表示公司编号、公司名称和公司所在城市。试用SQL语句写出下列操作：①用CREATETABLE语句创建上述3个表，需指出主键和外键。②检索超过50岁的男职工的工号和姓名。③假设每个职工只能在一个公司工作，检索工资超过1000元的男性职工工号和姓名。④假设每个职工可在多个公司工作，检索至少在编号为C4和C8的公司兼职的职工工号和姓名。⑤检索在联华公司工作、工资超过1000元的男性职工的工号和姓名。⑥假设每个职工可在多个公司工作，检索每个职工的兼职公司数目和工资总数，显示(E#，NUM，SUM_SALARY)，分别表示工号、公司数目和工资总数。⑦工号为E6的职工在多个公司工作，试检索至少在E6职工兼职的所有公司工作的职工工号。⑧检索联华公司中低于本公司平均工资的职工工号和姓名。⑨在每一公司中为50岁以上的职工加薪100元(若职工为多个公司工作，可重复加)。⑩在EMP表和WORKS表中删除年龄大于60岁的职工有关元组。解：①CREATETABLEEMP(E#CHAR(4)NOTNULL，ENAMECHAR(8)NOTNULL，AGESMALLINT,SEXCHAR(1)，ECITYCHAR(20)，PRIMARYKEY(E#))；CREATETABLECOMP(C#CHAR(4)NOTNULL，CNAMECHAR(20)NOTNULL，CITYCHAR(20)，PRIMARYKEY(C#))；CREATETABLEWORKS(E#CHAR(4)NOTNULL，C#CHAR(4)NOTNULL，SALARYSMALLINT，PRIMARYKEY(E#，C#)，FOREIGNKEY(E#)REFERENCESEMP(E#)，FOREIGNKEY(C#)REFERENCESCOMP(C#))；②(SELECTE#，ENAMEFROMEMPWHEREAGE50ANDSEX=’M’