数据库-第四章习题参考解答

第四章习题参考解答4．2题对于教学数据库的3个基本表：S（SNO，SNAME，AGE，SEX，SDEPT）SC（SNO，CNO，GRADE）C（CNO，CNAME，CDEPT，TNAME）试用SQL的查询语句表达下列查询：（1）检索LIU老师所授课程的课程号和课程名。（2）检索年龄大于23岁的男同学的学号和姓名。（3）检索学号为S3的学生呢个所学课程的课程名和任课教师名。（4）检索至少选修LIU老师所授课程中一门课程的女学生姓名。（5）检索WANG同学不学的课程的课程号。（6）检索至少选修两门课程的学生学号。（7）检索全部学生都选修的课程的课程号与课程名。（8）检索选修课程包含LIU老师所授课程的学生学号。解：（1）SELECTCNO，CNAMEFROMCWHERETNAME=‘LIU’；（2）SELECTSNO，SNAMEFROMSWHEREAGE23ANDSEX=‘M’；（3）SELECTCNAME，TNAMEFROMCWHERECNOIN（SELECECNOFROMSCWHERESNO=’S3’））；（4）联接查询方式SELECTSNAMEFROMS，SC，CWHERES.SNO=SC.SNOANDSC.CNO=C.CNOANDSEX=’F’ANDTNAME=’LIU’；嵌套查询方式：SELECTSANMEFROMSWHERESEX=’F’ANDSNOIN（SELECTSNOFROMSCWHERECNOIN（SELECTCNOFROMCWHERETNAME=’LIU’））；存在量词方式：SELECTSNAMEFROMSWHERESEX=’F’ANDEXISTS（SELECT*FROMSCWHERESNO=S.SNOANDEXISTS（SELECT*FROMCWHERECNO=SC.CNOANDTNAME=’LIU’））；（5）SELECTCNOFROMCWHERECNONOTIN（SELECTCNOFROMSCWHERESNOIN（SELECTSNOFROMSWHERESNAME=’WANG’））；（6）方法（一）：SELECTDISTINCTX.SNOFROMSCXWHERESNOIN（SELECTSNOFROMSCYWHEREX.SNO=Y.SNOANDX.CNO=Y.CNO）；方法（二）：SELECTSNOFROMSCGROUPBYSNOHAVINGCOUNT（*）=2；（7）SELECTCNO，CNAMEFROMCWHERENOTEXISTS（SELECT*FROMSWHERENOTEXISTS（SELECT*FROMSCWHERESC.SNO=S.SNOANDSC.CNO=C.CNO））；（8）SELECTDISTINCTSNOFROMSCXWHERENOTEXISTS（SELECT*FROMCWHERETNAME=’LIU’ANDNOTEXISTS（SELECT*FROMSCYWHEREX.SNO=Y.SNOANDC.CNO=Y.CNO））；4．6题试用SQL查询语句表达下列对数据库中3个基本表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)FROMSWHERESNOIN（SELECTSNOFROMSCWHERECNO=‘C4’）；（3）SELECTCNO，AVG（GRADE）FROMSCWHERECNOIN（SELECTCNOFROMCWHERETNAME=‘LIU’）GROUPBYCNO（4）SELECTCNO，COUNT（SNO）FROMSCGROUPBYCNOHAVINGCOUNT（*）10ORDERBY2DESC，CNO；（5）方法（一）：SELECTX.SNAMEFROMSX，SYWHEREY.SNAME=‘WANG’ANDX.SNOY.SNOANDX.AGEY.AGE；方法（二）：SELECTSNAMEFROMSWHERESNO(SELECTSNOFROMSWHERESNAME=’WANG’)ANDAGE(SELECTAGEFROMSWHERESNAME=‘WANG’)；（6）SELECTSNAME,AGEFROMSWHERESNAMELINGK‘WANG%’；（7）SELECTSNO，CNOFROMSCWHEREGRADEISNULL；（8）SELECTSNAME,AGEFROMSWHERESEX=‘M’ANDAGE(SELECTAVG(AGE)FROMSWHERESEX=‘F’)；（9）SELECTSNAME，AGEFROMSWHERESEX=‘M’ANDAGEALL(SELECTAGEFROMSWHERESEX=‘F’)；4.7题试用SQL更新语句表达对教学数据库中基本表S、SC、C的各个更新操作。（1）往基本表S中插入一个学生元组（‘S9’，‘WU’，18）。（2）在基本表S中检索每一个成绩都大于等于80分的学生学号、姓名和性别，并把检索到的值送往另一个已存在的基本表STUDENT（S#，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，SEXFROMSWHERESNONOTIN（SELECTSNOFROMSCWHEREGRADE80)；（3）DELETEFROMSCWHEREGRADEISNULL；（4）DELETEFROMSCWHERESNOIN(SELECTSNOFROMSWHERESNAME=‘WANG’)；（5）UPDATESCSETGRADE=NULLWHEREGRADE60ANDCNOIN(SELECTCNOFROMCWHERECNAME=‘MATHS’)；（6）UPDATESCSETGRADE=GRADE*1.04WHERESNOIN(SELECTSNOFROMSWHERESEX=‘F’)ANDGRADE(SELECTAVG(GRADE)FROMSC)；（7）UPDATESCSETGRADE=GRADE*1.04WHERECNO=‘C4’ANDGRADE75；UPDATESCSETGRADE=GRADE*1.05WHERECNO=‘C4’ANDGRADE=75；这两个UPDATE语句的顺序不能颠倒。