第3章习题.doc

3.2 对于教学数据库的四个基本表 教师关系 T(T#，TNAME，TITLE)课程关系 C(C#，CNAME，T#)学生关系 S(S#，SNAME，AGE，SEX)选课关系 SC(S#，C#，SCORE)试用SQL的查询语句表达下列查询：解： 检索年龄小于17岁的女生的学号和姓名。SELECT S#, SNAMEFROM SWHERE SEX=F AND AGE23 AND SEX=M ; 检索男学生所学课程的任课老师的工号和姓名。SELECT T#，TNAMEFROM S, SC, C, TWHERE SEX=M AND S.S#=SC.S# AND SC.C#=C.C# AND C.T#=T.T# ;检索学号为S3学生所学课程的课程名与任课教师名。（第2版）SELECT CNAME，TNAMEFROM SC, C, TWHERE S#=S3 AND SC.C#=C.C# AND C.T#=T.T# ; 检索至少选修两门课的学生学号。SELECT DISTINCT X.S#FROM SC AS X, SC AS YWHERE X.S#=Y.S# AND X.C#Y.C# ;检索至少选修LIU老师所授课程中一门课程的女学生姓名。（第2版）（联接查询方式）SELECT SNAMEFROM S, SC, C,TWHERE S.S#=SC.S# AND SC.C#=C.C# AND C.T#=T.T# AND SEX=FAND TNAME=LIU;或：（嵌套查询方式）SELECT SNAMEFROM SWHERE SEX=FAND S# IN (SELECT S#FROM SCWHERE C# IN (SELECT C#FROM CWHERE T# IN (SELECT T#FROM TWHERE TNAME=LIU);或：（存在量词方式）SELECT SNAMEFROM SWHERE SEX=FAND EXISTS (SELECT *FROM SCWHERE SC.S#=S.S#AND EXISTS (SELECT * FROM C WHERE C.C#=SC.C#AND EXISTS (SELECT * FROM T WHERE C.T#=T.T# AND TNAME=LIU); 检索至少有学号S2和S4学生选修的课程的课程号。SELECT DISTINCT X.C#FROM SC AS X, SC AS YWHERE X.C#=Y.C# AND X.S#=S2 AND Y.S#=S4);检索WANG同学不学的课程的课程号。（第2版） SELECT C#FROM CWHERE NOT EXISTS(SELECT * FROM S, SC WHERE S.S#=SC.S# AND SC.C#=C.C# AND SNAME=WANG);或者 SELECT C#FROM CWHERE C# NOT IN (SELECT DISTINCT C# FROM S, SC WHERE S.S#=SC.S# AND SNAME=WANG)检索WANG同学不学的课程的课程号。 SELECT C#FROM CWHERE NOT EXISTS(SELECT * FROM S, SC WHERE S.S#=SC.S# AND SC.C#=C.C# AND SNAME=WANG);检索至少选修两门课程的学生学号。（第2版） SELECT DISTINCT X.S#FROM SC AS X, SC AS YWHERE X.S#=Y.S# AND X.C#Y.C#;检索全部学生都选修的课程的课程号与课程名。(双重否定：在C中检索课程号与课程名，在S中不存在一名学生，没选修该课程) SELECT C#, CNAMEFROM CWHERE NOT EXISTS(SELECT *FROM SWHERE NOT EXISTS(SELECT * FROM SC WHERE S#=S.S# AND SC.C#=C.C#);检索选修课程包含LIU老师所授课程的学生学号。(双重否定：在SC中检索一个学号，在C，T表中不存在LIU所授的课程，该学号学生没有选修) SELECT DISTINCT S#FROM SC AS XWHERE NOT EXISTS(SELECT * FROM C, T WHERE TNAME=LIU AND C.T#=T.T# AND NOT EXISTS(SELECT * FROM SC AS Y WHERE Y.S#=X.S# AND Y.C#=C.C#);3.4 设有两个基本表R（A，B，C）和S（A，B，C），试用SQL查询语句表达下列关系代数表达式： RS RS RS RSA,B(R) B,C（S） 3=3 课 1，6（3=4（RS） 1，2，3（R S） RC（S）解： (SELECT * FROM R)UNION(SELECT * FROM S)； (SELECT * FROM R)INTERSECT(SELECT * FROM S)； (SELECT * FROM R)EXCEPT(SELECT * FROM S)； SELECT *FROM R, S； SELECT R.A, R.B, S.CFROM R, SWHERE R.B=S.B； SELECT R.A, S.CFROM R, SWHERE R.C=S.A； SELECT R.*（R.*表示R中全部属性）FROM R, SWHERE R.C=S.C；RC（S）的元组表达式如下： t |（u）（v）（w）（R（u） S（v） R（w） w1=u1 w2=u2 w3=v3 t1=u1 t2=u2）据此，可写出SELECT语句：SELECT A, BFROM R RXWHERE NOT EXISTS( SELECT *FROM SWHERE NOT EXISTS( SELECT *FROM R RYWHERE RY.A=RX.A AND RY.B=RX.B AND RY.C=S.C)；3.7 试用SQL查询语句表达下列对3.2题中四个基本表T、C、S、SC的查询：解： 统计有学生选修的课程门数。SELECT COUNT(DISTINCT C#)FROM SC； 求选修C4课程的女学生的平均年龄。SELECT AVG(AGE)FROM S, SCWHERE S.S#=SC.S# AND C#=C4 AND SEX=F； 求LIU老师所授课程的每门课程的平均成绩。SELECT C.C#，AVG(SCORE)FROM SC，C，TWHERE SC.C#=C.C# AND C.T#=T.T# AND TNAME=LIUGROUP BY C.C#； 统计每门课程的学生选修人数（超过10人的课程才统计）。要求显示课程号和人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。SELECT C#, COUNT(DISTINCT S#)FROM SCGROUP BY C#HAVING COUNT(*)10ORDER BY 2 DESC, 1； 检索学号比WANG同学大，而年龄比他小的学生姓名。SELECT SNAMEFROM SWHERE S#ALL(SELECT S# FROM S WHERE SNAME=WANG) AND AGE(SELECT AVG(AGE)FROM SWHERE SEX=F)； 求年龄大于所有女同学年龄的男学生姓名和年龄。SELECT SNAME, AGEFROM SWHERE SEX=M AND AGEALL(SELECT AGEFROM SWHERE SEX=F)；3.8 对于下面的关系R和S，试求出下列各种联接操作的执行结果：R NATURAL INNER JOIN SR NATURAL RIGHT OUTER JOIN SR RIGHT OUTER JOIN S USING（C）R INNER JOIN SR FULL OUTER JOIN S ON falseRABCSBCDa1b1c1b1c1d1a2b2c2b2c2d2a3b3c3b4c4d4解：ABCDABCDAR.BCS.BDa1b1c1d1a1b1c1d1a1b1c1b1d1a2b2c2d2a2b2c2d2a2b2c2b2d2nullb4c4d4nullnullc4b4d4R INNER JOIN SR FULL OUTER JOIN S ON falseAR.BR.CS.BS.CDAR.BR.CS.BS.CDa1b1c1b1c1d1a1b1c1nullnullnulla1b1c1b2c2d2a2b2c2nullnullnulla1b1c1b4c4d4a3b3c3nullnullnulla2b2c2b1c1d1nullnullnullb1c1d1a2b2c2b2c2d2nullnullnullb2c2d2a2b2c2b4c4d4nullnullnullb4c4d4a3b3c3b1c1d1a3b3c3b2c2d2a3b3c3b4c4d43.12 试用SQL更新语句表达对3.2题教学数据库中关系S、SC、C、T的更新操作：解： 往关系C中插一个课程元组（C8，VC+，T6）。 INSERT INTO CVALUES(C8，VC+，T6)； 检索所授每门课程平均成绩均大于80分的教师姓名，并把检索到的值送往另一个已存在的表FACULTY（TNAME）。 INSERT INTO FACULTY(TNAME)SELECT DISTINCT TNAME FROM (SELECT TNAME, C.C#, AVG(SCORE)FROM C, SC, TWHERE SC.C#=C.C# AND T.T#=C.T#GROUP BY TNAME, C.C#)AS RESULT(TNAME, C#, AVG_SCORE) AS XWHERE 80=ALL(SELECT AVG_SCOREFROM RESULT AS YWHERE Y.TNAME=X.TNAME)； 在SC中删除尚无成绩的选课元组。 DELETE FROM SCWHERE SCORE IS NULL； 把选修LIU老师课程的女同学选课元组全部删去。 DELETE FROM SCWHERE S# IN(SELECT S# FROM S WHERE SEX=F)AND C# IN(SELECT C# FROM C WHERE T# IN(SELECT T# FROM T WHERE TNAME=LIU)； 把MATHS课不及格的成绩全改为60分。 UPDATE SCSET SCORE=60WHERE SCORE60AND C# IN(SELECT C# FROM C WHERE CNAME=MATHS)； 把低于所有课程总平均成绩的女同学成绩提高5。 UPDATE SCSET SCORE=SCORE*1.05WHERE S# IN(SELECT S# FROM S WHERE SEX=F)AND SCORE70；UPDATE SCSET SCORE=SCORE*1.05WHERE C#=C4 AND SCORE70 THEN 1.04ELSE 1.05ENDWHERE C#=C4； 在表SC中，当某个成绩低于全部课程的平均成绩时，提高5%。 UPDATE SCSET SCORE=SCORE*1.05WHERE SCORE80； 允许查询。相应的操作如下：SELECT S#，COUNT（C#） AS C_NUMFROM SCGROUP BY S#HAVING AVG（SCORE） 80；SELECT S#，AVG_SCOREFROM S_SCOREWHERE C_NUM (SELECT C_NUMFROM S_SCOREWHERE S#=S4)； 允许查询。相应的操作如下：SELECT S#，AVG（SCORE） AS AVG_SCOREFROM SCGROUP BY S#HAVING COUNT （C#）（SELECT COUNT（C#）FROM SC