数据库原理第五章课后答案.pdf

《数据库原理第五章课后答案.pdf》由会员分享，可在线阅读，更多相关《数据库原理第五章课后答案.pdf（12页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、 数据库原理第五章课后答案 本页仅作为文档封面，使用时可以删除 This document is for reference only-rar21year.March 对于教学数据库的三个基本表 学生 S(S#,SNAME,AGE,SEX)学习 SC(S#,GRADE)课程 C(C#,CNAME,TEACHER)试用 SQL 的查询语句表达下列查询：（1）检索 LIU 老师所授课程的课程号和课程名。SELECT C#，CNAME FROM C WHERE TEACHER=LIU （2）检索年龄大于 23 岁的男学生的学号和姓名。SELECT S#，SNAME FROM S WHERE(AGE2

2、3)AND(SEX=M)（3）检索至少选修 LIU 老师所授课程中一门课程的女学生姓名。SELECT SNAME FROM S WHERE SEX=F AND S#IN (SELECT S#FROM SC WHERE C#IN (SELECT C#FROM C WHERE TEACHER=LIU)NOTICE：有多种写法，比如联接查询写法：SELECT SNAME FROM S,SC,C WHERE SEX=F AND#=#AND#=#AND TEACHER=LIU 但上一种写法更好一些。（4）检索 WANG 同学不学的课程的课程号。SELECT C#FROM C WHERE C#NOT IN

3、 (SELECT C#FROM SC WHERE S#IN (SELECT S#FROM S WHERE SNAME=WANG)（5）检索至少选修两门课程的学生学号。SELECT DISTINCT FROM SC X,SC Y WHERE=AND Notice:对表 SC 进行自连接，X，Y 是 SC 的两个别名。（6）检索全部学生都选修的课程的课程号与课程名。SELECT C#,CNAME FROM C WHERE NOT EXISTS (SELECT*FROM S WHERE S#NOT IN (SELECT*FROM SC WHERE#=#)要从语义上分解：（1）选择课程的课程号与课程名

4、，不存在不选这门课的同学。其中，“不选这门课的同学”可以表示为：SELECT*FROM S WHERE S#NOT IN （SELECT*FROM SC WHERE#=#)或者 SELECT*FROM S WHERE NOT EXISTS （SELECT*FROM SC WHERE#=#AND#=#)（7）检索选修课程包含 LIU 老师所授课的学生学号。SELECT DISTINCT S#FROM SC WHERE C#IN (SELECT C#FROM C WHERE TEACHER=LIU)（8）统计有学生选修的课程门数。SELECT COUNT(DISTINCT C#)FROM SC （

5、9）求选修 C4课程的学生的平均年龄。SELECT AVG(AGE)FROM S WHERE S#IN (SELECT S#FROM SC WHERE C#=C4)或者，SELECT AVG(AGE)FROM S，SC WHERE#=#AND C#=004 （10）求 LIU 老师所授课程的每门课程的学生平均成绩。SELECT CNAME,AVG(GRADE)FROM SC,C WHERE#=#AND TEACHER=LIU GROUP BY C#（11）统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。SEL

6、ECT DISTINCT C#,COUNT(S#)FROM SC GROUP BY C#HAVING COUNT(S#)10 ORDER BY 2 DESC,C#ASC （12）检索学号比 WANG 同学大，而年龄比他小的学生姓名。SELECT FROM S AS X,S AS Y WHERE=WANG AND#AND(SELECT AVG(AGE)FROM S AS Y WHERE=女)（16）求年龄大于所有女同学年龄的男学生姓名和年龄。SELECT SNAME,AGE FROM S AS X WHERE=男 AND ALL(SELECT AGE FROM S AS Y WHERE=女)试用

7、 SQL 更新语句表达对教学数据库中三个基本表 S、SC、C 的各个更新操作：（1）往基本表 S 中插入一个学生元组（S9，WU，18）。INSERT INTO S(S#,SNAME,AGE)VALUES(59,WU,18)（2）在基本表 S 中检索每一门课程成绩都大于等于80分的学生学号、姓名和性别，并把检索到的值送往另一个已存在的基本表 STUDENT（S，SANME，SEX）。INSERT INTO STUDENT(S#,SNAME,SEX)SELECT S#,SNAME,SEX FROM S WHERE NOT EXISTS (SELECT*FROM SC WHERE GRADE80

8、AND#=#)（3）在基本表 SC 中删除尚无成绩的选课元组。DELETE FROM SC WHERE GRADE IS NULL （4）把 WANG 同学的学习选课和成绩全部删去。DELETE FROM SC WHERE S#IN (SELECT S#FROM S WHERE SNAME=WANG)（5）把选修 MATHS 课不及格的成绩全改为空值。UPDATE SC SET GRADE=NULL WHERE GRADE60 AND C#IN (SELECT C#FROM C WHERE CNAME=MATHS)（6）把低于总平均成绩的女同学成绩提高5%。UPDATE SC SET GRAD

9、E=GRADE*WHERE GRADE(SELECT AVG(GRADE)FROM SC)AND S#IN(SELECT S#FROM S WHERE SEX=F)（7）在基本表 SC 中修改 C4课程的成绩，若成绩小于等于75分时提高5%，若成绩大于75分时提高4%（用两个 UPDATE 语句实现）。UPDATE SC SET GRADE=GRADE*WHERE C#=C4 AND GRADE75 在第1章例中提到“仓库管理”关系模型有五个关系模式：零件 PART（P，PNAME，COLOR，WEIGHT）项目 PROJECT（J，JNAME，DATE）供应商 SUPPLIER（S，SNAM

10、E，SADDR）供应 PP（J，P，TOTOAL）采购 PS（P，S，QUANTITY）（1）试用 SQL DDL 语句定义上述五个基本表，并说明主键和外键。CREATE TABLE PART (P#CHAR(4)NOT NULL,PNAME CHAR(12)NOT NULL,COLOR CHAR(10),WEIGHT REAL,PRIMARY KEY(P#)CREATE TABLE PROJECT (J#CHAR(4)NOT NULL,JNAME CHAR(12)NOT NULL,DATE DATE,PRIMARY KEY(J#)CREATE TABLE SUPLIER (S#CHAR(4)

11、NOT NULL,SNAME CHAR(12),SADDR VARCHAR(20),PRIMARY KEY(S#)CREATE TABLE P_P (J#CHAR(4),P#CHAR(4),TOTAL INTEGER,PRIMARY KEY(J#,P#),FOREIGN KEY(J#)REFERENCE PROJECT(J#),FOREIGN KEY(P#)REFERENCE PART(P#)CREATE TABLE P_S (P#CHAR(4),S#CHAR(4),QUANTITY INTEGER,PRIMARY KEY(P#,S#),FOREIGN KEY(P#)REFERENCE PAR

12、T(P#),FOREIGN KEY(S#)REFERENCE SUPLIER(S#)（2）试将 PROGECT、PP、PART 三个基本表的自然联接定义为一个视图 VIEW1，PART、PS、SUPPLIER 三个基本表的自然联接定义为一个视图 VIEW2。CREATE VIEW VIEW1(J#,JNAME,DATE,P#,PNAME,COLOR,WEIGHT,TOTAL)AS SELECT#,JNAME,DATE,#,PNAME,COLOR,WEIGHT,TOTAL FROM PROJECT,PART,P_P WHERE#=#AND#=#CREATE VIEW VIEW2(P#,PNAME

13、,COLOR,WEIGHT,S#,SNAME,SADDR,QUANTITY)AS SELECT#,PNAME,COLOR,WEIGHT,#,SNAME,SADDR,QUANTITY FROM PART,P_S,SUPPLIER WHERE#=#AND#=#（3）试在上述两个视图的基础上进行数据查询：1）检索上海的供应商所供应的零件的编号和名字。SELECT P#,PNAME FROM VIEW2 WHERE SADDR=SHANGHAI 2）检索项目 J4所用零件的供应商编号和名字。SELECT S#,SNAME FROM VIEW2 WHERE P#IN(SELECT P#FROM VIEW

14、1 WHERE J#=J4)对于教学数据库中基本表 SC，已建立下列视图：CREATE VIEW SGRADE（S，CNUM，AVGGRADE）AS SELECT S，COUNT(C)，AVG（GRADE）FROM SC GROUP BY S 试判断下列查询和更新是否允许执行。若允许，写出转换到基本表SC 上的相应操作。（1）SELECT*FROM SGRADE 允许 SELECT S#,COUNT(C#),AVG(GRADE)FROM SC GROUP BY S#（2）SELECT S，CNUM FROM SGRADE WHERE AVGGRADE80 允许 SELECT S#,COUNT(C#)FROM SC WHERE AVG(GRADE)80 （3）SELECT S，AVGGRADE FROM SGRADE WHERE CNUM(SELECT CNUM FROM SGRADE WHERE SS4)允许 SELECT S#,AVG(GRADE)FROM SC AS X WHERE COUNT#)(SELECT COUNT#)FROM SC AS Y WHERE#=S4)GROUP BY S#（4）UPDATE SGRADE SET CNUMCNUM1 WHERE SS4 不允许 （5）DELETE FROM SGRADE WHERE CNUM4 不允许