《数据库》第4章习题答案.doc

42 对于教学数据库的三个基本表 S（SNO,SNAME,AGE,SEX,SDEPT） SC（SNO,CNO,GRADE） C（CNO,CNAME，CDEPT,TNAME） 试用SQL的查询语句表达下列查询： 检索LIU老师所授课程的课程号和课程名。 检索年龄大于23岁的男学生的学号和姓名。 检索至少选修LIU老师所授课程中一门课程的女学生姓名。 检索WANG同学不学的课程的课程号。 检索至少选修两门课程的学生学号。 检索全部学生都选修的课程的课程号与课程名。 检索选修课程包含LIU老师所授(全部)课程的学生学号。参考答案： SELECTCNO，CNAMEFROMCWHERETNAMELIU; SELECTSNO，SNAMEFROMSWHEREAGE23ANDSEXM; SELECTSNAME（联接查询方式）FROMS，SC，CWHERES.SNO=SC.SNO AND SC.CNO=C.CNOAND SEX=F AND TNAME=LIU;或：SELECT SNAME (嵌套查询方式)FROMSWHERESEXF AND SNO IN(SELECT SNO FROM SCWHERE CNO IN (SELECT CNO FROM C WHERE TNAME=LIU);或：SELECT SNAME (存在量词方式)FROMSWHERESEXFANDEXISTS（SELECTFROMSCWHERESC.SNO=S.SNO AND EXISTS(SELECT * FROM C WHERE C.CNO=SC.CNO AND TNAME=LIU); SELECT CNOFROM CWHERE NOT EXISTS(SELECT * FROM S,SC WHERE S.SNO=SC.SNO AND SC.CNO=C.CNO AND SNAME=WANG);或：SELECT CNOFROM C WHERE CNO NOT IN (SELECT SC.CNO FROM S,SC WHERE S.SNO=SC.SNO AND SNAME=WANG);或：SELECT CNO FROM C WHERE CNO NOT IN (SELECT CNO FROM SC WHERE SNO IN (SELECT SNO FROM S WHERE SNAME=WANG); SELECT 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; SELECT CNO,CNAMEFROM CWHERE NOT EXISTS(SELECT * FROM SWHERE NOT EXISTS (SELECT * FORM SC WHERE SC.SNO=S.SNO AND SC.CNO=C.CNO); SELECT DISTINCT SNOFROM SC AS XWHERE NOT EXISTS(SELECT * FORM CWHERE TNAME=LIU AND NOT EXISTS(SELECT * FROM SC AS Y WHERE Y.SNO=X.SNO AND Y.CNO=C.CNO);43 设有两个基本表R（A，B，C）和S（D，E，F），试用SQL查询语句表达下列关系代数表达式： SELECT A FROM R; SELECT * FROM R WHERE B=17; SELECT * FROM R,S; SELECT A,F FROM R,S WHERE C=D; 44 设有两个基本表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(SELECT * FROM S); 4.(SELECT A,B FROM R)NATURAL INNER JOIN(SELECT B,C FROM S);或：SELECT R.A, R.B, S.C FROM R,S WHERE R.B=S.B4.6 试用 SQL查询语句表达下列对教学数据库中三个基本表 S、SC、C的查询： 1、统计有学生选修的课程门数。2、求选修 C4课程的学生的平均年龄。 3、求 LIU老师所授课程的每门课程的学生平均成绩。 4、统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。5、检索学号比WANG同学大，而年龄比他小的学生姓名。6、检索姓名以WANG打头的所有学生的姓名和年龄。7、在SC中检索成绩为空值的学生学号和课程号。8、求年龄大于女同学平均年龄的男学生姓名和年龄。9、求年龄大于所有女同学年龄的男学生姓名和年龄。参考答案：1、 SELECTCOUNT（DISTINCTCNO）FROMSC；2、 SELECTAVG（AGE）FROMS，SCWHERES.SNO=SC.SNO AND CNO=C4;3、 SELECTSC.CNO, CNAME, AVG(GRADE)FROM SC,CWHERE SC.CNO=C.CNO AND TNAME=LIUGROUP BY SC.CNO;4、 SELECT CNO,COUNT(SNO)FROM SC GROUP BY CNOHAVING COUNT(*)10ORDER BY 2 DESC, 1;5、 SELECT X.SNAMEFROM S AS X,S AS YWHERE Y.SNAME=WANGAND X.SNOY.SNO AND X.AGE(SELECT SNO FROM S WHERE SNAME=WANG)AND AGE(SELECT AGE FROM S WHERE SNAME=WANG); (此处要求学生姓名不重复)6、 SELECTSNAME，AGEFROMSWHERESNAMELIKEWANG%7、 SELECTSNO，CNOFROMSCWHEREGRADEISNULL；8、 SELECTSNAME，AGEFROMSWHERESEXMANDAGE（SELECTAVG（AGE）FROMSWHERESEXF）9、 SELECTSNAME，AGEFROMSWHERESEXMANDAGEALL（SELECTAGEFROMSWHERESEXF）；或：把上述红色部分改为：AGE（SELECTMAX（AGE）47 试用SQL更新语句表达对教学数据库中三个基本表S、SC、 C的各个更新操作： 1、往基本表S中插人一个学生元组（S9，WU，18）。 2、在基本表S中检索每一门课程成绩都大于等于80分的学生学号、姓名和性别，并把检索到的值送往另一个已存在的基本表STUDENT（SNO，SNAME，SEX）。 3、在基本表SC中删除尚无成绩的选课元组。 4、把WANG同学的学习选课和成绩全部删去。 5、把选修MATHS课不及格的成绩全改为空值。 6、把低于总平均成绩的女同学成绩提高5。 7、在基本表SC中修改 C4课程的成绩，若成绩小于等于75分时提高 5，若成绩大于 75分时提高4（用两个UPDATE语句实现）。参考答案：1、 INSERTINTOS（SNO，SNAME，AGE）VALUES（S9，WU，18）；2、 INSERTINTOSTUDENTSELECTSNO，SNAME，SEXFROMSWHERE80ALL（SELECTGRADEFROMSCWHERESNOS.SNO）；3、 DELETEFROMSCWHEREGRADE IS NULL;4、 DELETEFROMSCWHERESNOIN（SELECTSNOFROMSWHERESNAMEWANG）；5、 UPDATESCSETGRADENULLWHEREGRADE60ANDCNOIN（SELECTCNOFROM CWHERECNAMEMATHS）；6、 UPDATESCSETGRADEGRADE1.05WHERESNOIN（SELECTSNOFROMSWHERESEXF）ANDGRADE（SELECTAVG（GRADE）FROMSC）；7、 UPDATESCSETGRADEGRADE1.04WHERECNOC4ANDGRADE75；UPDATESCSETGRADEGRADE1.05WHERECNOC4ANDGRADE75；注：这两个语句顺序不能颠倒。 48 假设某“仓库管理”关系模型有下列五个关系模式： 零件PART（PNO，PNAME，COLOR，WEIGHT） 项目PROJECT（JNO，JNAME，DATE） 供应商SUPPLIER（SNO，SNAME，SADDR） 供应P_P（JNO，PNO，TOTAL） 采购 P_S（PNO，SNO，QUANTITY） 试用 SQL DDL语句定义上述五个基本表，并说明主键和外键。 试将 PROJECT、P_P、PART三个基本表的自然联接定义为一个视图 VIEW1，PART,P_S、SUPPLIER三个基本表的自然联接定义为一个视图VIEW2。 试在上述两个视图的基础上进行数据查询： i 检索上海的供应商所供应的零件的编号和名字。 ii检索项目J4所用零件的供应商编号和名字。参考答案： CREATE TABLE PART(PNO CHAR(6),PNAME CHAR(10) NOT NULL,COLOR CHAR(6),WEIGHT FLOAT(6),PRIMARY KEY(PNO);CREATE TABLE PROJECT (JNO CHAR(6), JNAME CHAR(12) NOT NULL, DATE DATE, PRIMARY KEY(JNO);CREATE TABLE SUPPLIER (SNO CHAR(8), SNAME CHAR(12) NOT NULL, SADDR VARCHAR(30), PRIMARY KEY(SNO);CREATE TABLE P_P (JNO CHAR(6), PNO CHAR(6), TOTAL INTEGER, PRIMARY KEY(JNO,PNO) FOREIGN KEY(JNO) REFERENCES PROJECT(JNO), FOREIGN KEY(PNO) REFERENCES PART(PNO);CREATE TABLE P_S (PNO CHAR(6), SNO CHAR(6), QUANTITY INTEGER, PRIMARY KEY(PNO,SNO) FOREIGN KEY(PNO) REFERENCES PART(PNO), FOREIGN KEY(SNO) REFERENCES SUPPLIER(SNO); CREATE VIEW VIEW1AS SELECT A.JNO,JNAME,DATE,C.PNO,PNAME,COLOR, WEIGHT,TOTAL FROM PROJECT A, P_P B, PART C WHERE A.JNO=B.JNO AND B.PNO=C.PNO; CREATE VIEW VIEW2AS SELECT A.PNO, PNAME,COLOR,WEIGHT,C.SNO,SNAME, SADDR,QUANTITY FROM PART A, P_S B,SUPPLIER C WHERE A.PNO=B.PNO AND B.SNO=C.SNO; i. SELECT PNO,PNAMEFROM VIEW2WHERE SADDR LIKE 上海%; ii. SELECT SNO,SNAMEFROM VIEW1,VIEW2WHERE VIEW1.PNO=VIEW2.PNO AND JNO=J4;49 对于教学数据库中基本表SC，已建立下列视图： CREATE VIEW SGRADE（S，C_NUM，AVGGRADE） AS SELECT SNO，COUNT（CNO），AVG（GRADE） FROM SC GROUP BY SNO； 试判断下列查询和更新是否允许执行。若允许，写出转换到基本表SC上的相应操作： SELECT * FROM S_GRADE SELECT SNO，C_NUMRROM S_GRADE WHERE AVG_GRADE80； SELECT SNO，AVGGRADE FROM S_GRADE WHERE C_NUM （SELECT C_NUM FROM S_GRADE WHERE SNOS4）; UPDATE S_GRADE SET C_NUMC_NUM 1 WRERE SNOS4； DELETE FROM S_GRADE WHERE C_NUM4； 参考答案：允许查询。相应操作如下：SELECTSNO，COUNT（CNO）ASCNUM，AVG（GRADE）ASAVGGRADEFROMSCGROUPBYSNO； 允许查询。相应操作如下：SELECT SNO，COUNT（CNO）ASC_NUMRROM SC GROUPBYSNOHAVING AVG（GRADE）80； 允许查询。相应操作如下：SELECT SNO，AVG（GRADE）ASAVGGRADE FROM SC GROUPBYSNO HAVINGCOUNT（CNO） （SELECT COUNT（CNO） FROM SCGROUPBYSNO HAVING SNOS4）; 不允许。CNUMJ是对SC中的学生选修门数进行统计，在未更改SC表时，要在视图SGRADE中更改门数，是不可能的。 不允许。在SGRADE中删除选修门数在4门以上的学生元组，势必造成SC中这些学生学习元组的删除，这未必是用户的原意，因此使用分组和聚合操作的视图，不允许用户执行更新操作。