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

4．2对于教学数据库的三个基本表S（SNO,SNAME,AGE,SEX,SDEPT）SC（SNO,CNO,GRADE）C（CNO,CNAME，CDEPT,TNAME）试用SQL的查询语句表达下列查询：①检索LIU老师所授课程的课程号和课程名。②检索年龄大于23岁的男学生的学号和姓名。③检索至少选修LIU老师所授课程中一门课程的女学生姓名。④检索WANG同学不学的课程的课程号。⑤检索至少选修两门课程的学生学号。⑥检索全部学生都选修的课程的课程号与课程名。⑦检索选修课程包含LIU老师所授(全部)课程的学生学号。参考答案：①SELECTCNO，CNAMEFROMCWHERETNAME＝‘LIU’;②SELECTSNO，SNAMEFROMSWHEREAGE＞23ANDSEX＝‘M’;③SELECTSNAME（联接查询方式）FROMS，SC，CWHERES.SNO=SC.SNOANDSC.CNO=C.CNOANDSEX=’F’ANDTNAME=’LIU’;或：SELECTSNAME(嵌套查询方式)FROMSWHERESEX＝‘F’ANDSNOIN(SELECTSNOFROMSCWHERECNOIN(SELECTCNOFROMCWHERETNAME=’LIU’));或：SELECTSNAME(存在量词方式)FROMSWHERESEX＝‘F’ANDEXISTS（SELECT＊FROMSCWHERESC.SNO=S.SNOANDEXISTS(SELECT*FROMCWHEREC.CNO=SC.CNOANDTNAME=’LIU’));④SELECTCNOFROMCWHERENOTEXISTS(SELECT*FROMS,SCWHERES.SNO=SC.SNOANDSC.CNO=C.CNOANDSNAME=’WANG’);或：SELECTCNOFROMCWHERECNONOTIN(SELECTSC.CNOFROMS,SCWHERES.SNO=SC.SNOANDSNAME=’WANG’);或：SELECTCNOFROMCWHERECNONOTIN(SELECTCNOFROMSCWHERESNOIN(SELECTSNOFROMSWHERESNAME=’WANG’));⑤SELECTDISTINCTX..SNOFROMSCASX,SCASYWHEREX.SNO=Y.SNOANDX.CNOY.CNO;或：SELECTSNOFROMSCGROUPBYSNOHAVINGCOUNT(CNO)=2;⑥SELECTCNO,CNAMEFROMCWHERENOTEXISTS(SELECT*FROMSWHERENOTEXISTS(SELECT*FORMSCWHERESC.SNO=S.SNOANDSC.CNO=C.CNO));⑦SELECTDISTINCTSNOFROMSCASXWHERENOTEXISTS(SELECT*FORMCWHERETNAME=’LIU’ANDNOTEXISTS(SELECT*FROMSCASYWHEREY.SNO=X.SNOANDY.CNO=C.CNO));4．3设有两个基本表R（A，B，C）和S（D，E，F），试用SQL查询语句表达下列关系代数表达式：①SELECTAFROMR;②SELECT*FROMRWHEREB=17;③SELECT*FROMR,S;④SELECTA,FFROMR,SWHEREC=D;4．4设有两个基本表R（A，B，C）和S（A，B，C），试用SQL查询语句表达下列关系代数表达式：1.(SELECT*FROMR)UNION(SELECT*FROMS);2.(SELECT*FROMR)INTERSECT(SELECT*FROMS);3.(SELECT*FROMR)EXCEPT(SELECT*FROMS);4.(SELECTA,BFROMR)NATURALINNERJOIN(SELECTB,CFROMS);或：SELECTR.A,R.B,S.CFROMR,SWHERER.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.SNOANDCNO=’C4’;3、SELECTSC.CNO,CNAME,AVG(GRADE)FROMSC,CWHERESC.CNO=C.CNOANDTNAME=’LIU’GROUPBYSC.CNO;4、SELECTCNO,COUNT(SNO)FROMSCGROUPBYCNOHAVINGCOUNT(*)10ORDERBY2DESC,1;5、SELECTX.SNAMEFROMSASX,SASYWHEREY.SNAME=’WANG’ANDX.SNOY.SNOANDX.AGEY.AGE;或：SELECTSNAMEFROMSWHERESNO(SELECTSNOFROMSWHERESNAME=’WANG’)ANDAGE(SELECTAGEFROMSWHERESNAME=’WANG’);(此处要求学生姓名不重复)6、SELECTSNAME，AGEFROMSWHERESNAMELIKE‘WANG%’7、SELECTSNO，CNOFROMSCWHEREGRADEISNULL；8、SELECTSNAME，AGEFROMSWHERESEX＝‘M’ANDAGE＞（SELECTAVG（AGE）FROMSWHERESEX＝‘F’）9、SELECTSNAME，AGEFROMSWHERESEX＝‘M’ANDAGE＞ALL（SELECTAGEFROMSWHERESEX＝‘F’）；或：把上述红色部分改为：AGE＞（SELECTMAX（AGE）4．7试用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，SEXFROMSWHERE80＜＝ALL（SELECTGRADEFROMSCWHERESNO＝S.SNO）；3、DELETEFROMSCWHEREGRADEISNULL;4、DELETEFROMSCWHERESNOIN（SELECTSNOFROMSWHERESNAME＝‘WANG’）；5、UPDATESCSETGRADE＝NULLWHEREGRADE＜60ANDCNOIN（SELECTCNOFROMCWHERECNAME＝‘MATHS’）；6、UPDATESCSETGRADE＝GRADE＊1.05WHERESNOIN（SELECTSNOFROMSWHERESEX＝‘F’）ANDGRADE＜（SELECTAVG（GRADE）FROMSC）；7、UPDATESCSETGRADE＝GRADE＊1.04WHERECNO＝‘C4’ANDGRADE＞75；UPDATESCSETGRADE＝GRADE＊1.05WHERECNO＝‘C4’ANDGRADE＜＝75；注：这两个语句顺序不能颠倒。4．8假设某“仓库管理”关系模型有下列五个关系模式：零件PART（PNO，PNAME，COLOR，WEIGHT）项目PROJECT（JNO，JNAME，DATE）供应商SUPPLIER（SNO，SNAME，SADDR）供应P_P（JNO，PNO，TOTAL）采购P_S（PNO，SNO，QUANTITY）①试用SQLDDL语句定义上述五个基本表，并说明主键和外键。②试将PROJECT、P_P、PART三个基本表的自然联接定义为一个视图VIEW1，PART,P_S、SUPPLIER三个基本表的自然联接定义为一个视图VIEW2。③试在上述两个视图的基础上进行数据查询：i．检索上海的供应商所供应的零件的编号和名字。ii．检索项目J4所用零件的供应商编号和名字。参考答案：①CREATETABLEPART(PNOCHAR(6),PNAMECHAR(10)NOTNULL,COLORCHAR(6),WEIGHTFLOAT(6),PRIMARYKEY(PNO));CREATETABLEPROJECT(JNOCHAR(6),JNAMECHAR(12)NOTNULL,DATEDATE,PRIMARYKEY(JNO));CREATETABLESUPPLIER(SNOCHAR(8),SNAMECHAR(12)NOTNULL,SADDRVARCHAR(30),PRIMARYKEY(SNO));CREATETABLEP_P(JNOCHAR(6),PNOCHAR(6),TOTALINTEGER,PRIMARYKEY(JNO,PNO)FOREIGNKEY(JNO)REFERENCESPROJECT(JNO),FOREIGNKEY(PNO)REFERENCESPART(PNO));CREATETABLEP_S(PNOCHAR(6),SNOCHAR(6),QUANTITYINTEGER,PRIMARYKEY(PNO,SNO)FOREIGNKEY(PNO)REFERENCESPART(PNO),FOREIGNKEY(SNO)REFERENCESSUPPLIER(SNO));②CREATEVIEWVIEW1ASSELECTA.JNO,JNAME,DATE,C.PNO,PNAME,COLOR,WEIGHT,TOTALFROMPROJECTA,P_PB,PARTCWHEREA.JNO=B.JNOANDB.PNO=C.PNO;CREATEVIEWVIEW2ASSELECTA.PNO,PNAME,COLOR,WEIGHT,C.SNO,SNAME,SADDR,QUANTITYFROMPARTA,P_SB,SUPPLIERCWHEREA.PNO=B.PNOANDB.SNO=C.SNO;③i.SELECTPNO,PNAMEFROMVIEW2WHERESADDRLIKE‘上海%’;ii.SELECTSNO,SNAMEFROMVIEW1,VIEW2WHEREVIEW1.PNO=VIEW2.PNOANDJNO=’J4’;4．9对于教学数据库中基本表SC，已建立下列视图：CREATEVIEWS＿GRADE（S，C_NUM，AVG＿GRADE）ASSELECTSNO，COUNT（CNO），AVG（GRADE）FROMSCGROUPBYSNO；试判断下列查询和更新是否允许执行。若允许，写出转换到基本表SC上的相应操作：①SELECT*FROMS_GRADE②SELECTSNO，C_NUMRROMS_GRADEWHEREAVG_GRADE＞80；③SELECTSNO，AVG＿GRADEFROMS_GRADEWHEREC_NUM＞（SE