数据库习题答案

1、1/ 84 2 对于教学数据库的三个基本表S( SNO,SNAME,AGE,SEX,SDEPT )SC( SNO,CNO,GRADE )C( CNO,CNAME ，CDEPT,TNAME ) 试用 SQL 的查询语句表达下列查询：1检索 LIU 老师所授课程的课程号和课程名。2检索年龄大于 23 岁的男学生的学号和姓名。3检索至少选修 LIU 老师所授课程中一门课程的女学生姓名。4检索 WANG 同学不学的课程的课程号。 检索至少选修两门课程的学生学号。 检索全部学生都选修的课程的课程号与课程名。 检索选修课程包含 LIU老师所授 (全部 )课程的学生学号。参考答案：1SELECT CNO ，

2、 CNAME FROM CWHERE TNAME = LIU ;2SELECT SNO ，SNAME FROM SWHERE AGE 23 AND SEX = M ;3SELECT SNAME (联接查询方式) FROM S，SC，CWHERE S.SNO=SC.SNO AND SC.CNO=C.CNO AND SEX= FANDTNAME= LIU ;或： SELECT SNAME(嵌套查询方式 )FROM SWHERE SEX = FAND SNO IN(SELECT SNO FROM SC WHERE CNO IN(SELECT CNOFROM CWHERE TNAME= LIU); 或

3、： SELECT SNAME(存在量词方式 )FROM SWHERE SEX = FAND EXISTS(SELECT FROM SCWHERE SC.SNO=S.SNOAND EXISTS(SELECT * FROM CWHERE C.CNO=SC.CNOAND TNAME= LIU );4SELECT CNOFROM CWHERE NOT EXISTS2/ 8(SELECT *FROM S,SCWHERE S.SNO=SC.SNO AND SC.CNO=C.CNOAND SNAME= WANG );或： SELECT CNOFROM CWHERE CNO NOT IN(SELECT SC.

4、CNOFROM S,SCWHERE S.SNO=SC.SNO AND SNAME= WANG ); 或：SELECTCNOFROM CWHERE CNO NOT IN(SELECT CNOFROM SCWHERE SNO IN(SELECT SNOFROM S WHERE SNAME= WANG );5SELECT DISTINCT X.SNOFROM SC AS X,SC AS YWHERE X.SNO=Y .SNO AND X.CNOY.CNO;或： SELECT SNOFROM SCGROUP BY SNO HAVING COUNT(CNO)=2;6SELECT CNO,CNAMEFRO

5、M CWHERE NOT EXISTS(SELECT *FROM SWHERE NOT EXISTS(SELECT *FORM SCWHERE SC.SNO=S.SNO AND SC.CNO=C.CNO);7SELECT DISTINCT SNOFROM SC AS XWHERE NOT EXISTS(SELECT *FORM CWHERE TNAME= LIU AND NOT EXISTS(SELECT *FROM SC AS YWHERE Y .SNO=X.SNO AND Y .CNO=C.CNO);3/ 84. 3 设有两个基本表 R (A , B , C)和 S ( D, E, F),

6、试用 SQL 查询语句表达下列 关系代数表达式：1SELECT A FROM R;2SELECT * FROM R WHERE B=17;3SELECT * FROM R,S;4SELECT A,F FROM R,S WHERE C=D;4. 4 设有两个基本表 R( A，B，C)和 S(A，B，C)，试用 SQL 查询语句表达下列 关系代数表达式：1. (SELECT * FROM R)UNION (SELECT * FROM S);2. (SELECT * FROM R)INTERSECT(SELECT * FROM S);3. (SELECT * FROM R)EXCEPT (SELEC

7、T * FROM S);4. (SELECT A,B FROM R)NATURAL INNER JOIN (SELECT B,C FROM S);或： SELECT R.A, R.B, S.CFROM R,S WHERE R.B=S.B4.6 试用 SQL 查询语句表达下列对教学数据库中三个基本表S、SC、C 的查询：1、 统计有学生选修的课程门数。2、 求选修 C4 课程的学生的平均年龄。3、 求 LIU 老师所授课程的每门课程的学生平均成绩。4、 统计每门课程的学生选修人数(超过10 人的课程才统计) 。要求输出课程号和 选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。5、

8、 检索学号比 WANG 同学大，而年龄比他小的学生姓名。6、 检索姓名以 WANG 打头的所有学生的姓名和年龄。7、 在 SC 中检索成绩为空值的学生学号和课程号。8、 求年龄大于女同学平均年龄的男学生姓名和年龄。9、 求年龄大于所有女同学年龄的男学生姓名和年龄。 参考答案：1、 SELECT COUNT ( DISTINCT CNO)FROM SC；2、 SELECT AVG(AGE)FROM S， SCWHERE S.SNO=SC.SNO AND CNO= C4;3、 SELECT SC.CNO, CNAME, A VG(GRADE)FROM SC,CWHERE SC.CNO=C.CNO

9、AND TNAME= LIU GROUP BY SC.CNO;4、 SELECT CNO,COUNT(SNO)FROM SCGROUP BY CNO4/ 8HAVING COUNT(*)10ORDER BY 2 DESC, 1;5、 SELECT X.SNAMEFROM S AS X,S AS YWHERE Y .SNAME= WANG AND X.SNOY .SNO AND X.AGE(SELECT SNOFROM SWHERE SNAME= WANG AND AGE( SELECT AVG (AGE )FROM SWHERE SEX = F 9、SELECT SNAME , AGEFROM

10、 SWHERE SEX = M AND AGE ALL (SELECT AGEFROM SWHERE SEX = F);或：把上述红色部分改为：AGE ( SELECT MAX (AGE )4. 7 试用 SQL 更新语句表达对教学数据库中三个基本表S、SC、C 的各个更新操作：1、 往基本表 S 中插人一个学生元组( S9, WU ,18)。2、 在基本表 S 中检索每一门课程成绩都大于等于80 分的学生学号、姓名和性别,并把检索到的值送往另一个已存在的基本表STUDENT ( SNO , SNAME ,SEX)。5/ 83、在基本表 SC 中删除尚无成绩的选课元组。4、把 WANG 同学的

11、学习选课和成绩全部删去。5、把选修 MATHS 课不及格的成绩全改为空值。6、把低于总平均成绩的女同学成绩提高5。7、在基本表 SC 中修改 C4 课程的成绩，若成绩小于等于 75 分时提高 5，若 成绩大于 75分时提高 4（用两个 UPDATE 语句实现）。参考答案：1、 INSERTINTO S(SNO，SNAME，AGE )VALUES ( S9， WU ，18);2、 INSERTINTO STUDENTSELECTSNO，SNAME ， SEXFROMSWHERE80 = ALL (SELECTGRADEFROM SCWHERE SNO = S.SNO);3、 DELETEFROM

12、SCWHEREGRADEISNULL;4、 DELETEFROMSCWHERESNO IN( SELECT SNOFROMSWHERESNAME = WANG 5、 UPDATESCSET GRADE = NULLWHEREGRADE 60ANDCNOIN ( SELECT CNOFROM CWHERE CNAME = MATHS 6、 UPDATESCSET GRADE = GRADE * 1.05WHERE SNO IN(SELECT SNOFROM SWHERE SEX = F)AND GRADE 75;UPDATE SCSET GRADE = GRADE * 1.05WHERE CNO

13、 = C4 AND GRADE 80;3SELECT SNO , AVG _GRADEFROM S_GRADEWHERE C_NUM ( SELECT C_NUMFROM S_GRADEWHERE SNO = S4);4UPDATE S_GRADESET C_NUM = C_NUM + 1WRERE SNO=S45DELETEFROM S_GRADEWHERE C_NUM 4;参考答案：1允许查询。相应操作如下：SELECT SNO, COUNT (CNO) AS C_NUM , AVG(GRADE ) ASAVG _GRADEFROM SCGROUP BY SNO;2允许查询。相应操作如下：SELECT SNO, COUNT (CNO) AS C_NUMRROM SCGROUP BY SNOHAVING AVG ( GRADE ) 80;3允许查询。相应操作如下：SELECT SNO , AVG (GRADE ) AS AVG GRADE FROM SCGROUP BY SNOHAVING COUNT (CNO)( SELECT C