课堂综合练习题答案市公开课一等奖百校联赛优质课金奖名师赛课获奖课件.ppt

,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,An Introduction to Database System,本资料仅供参考，不能作为科学依据。谢谢。本资料仅供参考，不能作为科学依据。本资料仅供参考，不能作为科学依据。谢谢。本资料仅供参考，不能作为科学依据。,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,An Introduction to Database System,*,本资料仅供参考，不能作为科学依据。谢谢。本资料仅供参考，不能作为科学依据。本资料仅供参考，不能作为科学依据。谢谢。本资料仅供参考，不能作为科学依据。,An Introduction to Database System,第三章 综合练习,设有三个关系：,S(sno,sname,sex,age),SC(sno,cno,grade),C(cno,cname,teacher),试用,SQL语句,表示以下查询语句,3,、,查询学号为,S3,学生所学课程课程名与任课教师名,4,、查询最少选修,LIU,老师所讲课程中一门课程女学生姓名,5,、查询,WANG,同学不学课程课程号,6,、查询最少选修两门课学生学号,7,、查询全部学生都选修课程课程号与课程名,8,、查询选修课程包含,LIU,老师所授全部课程学生学号。,1,、查询,LIU,老师所讲课程课程号和课程名,2,、查询年纪大于,23,岁男学生学号和姓名,1/27,An Introduction to Database System,第三章 综合练习,设有三个关系：,S(sno,sname,sex,age),SC(sno,cno,grade),C(cno,cname,teacher),试用,SQL语句,表示以下查询语句,10、求LIU老师所讲课程每门课程平均成绩,11,、检索姓名以,L打头全部学生姓名和年纪。,12,、求年纪大于全部女同年纪男学生姓名和年纪。,13,、往关系,C中插一个课程元组（C8,VC+,BAO）,14,、把选修,LIU老师课程女同学选课元组全部删去。,15,、把低于全部课程总平均成绩男同学成绩提升,5%.,9,、统计每门课程学生选修人数（超出,10人课程才统计）。要求显示课程号和人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。,2/27,An Introduction to Database System,综合练习答案,S(sno,sname,sex,age),SC(sno,cno,grade),C(cno,cname,teacher),1,、查询,LIU,老师所讲课程课程号和课程名,Select cno,cname,from C,where teacher=LIU,包括到表：,C(cno,cname,teacher),3/27,An Introduction to Database System,综合练习答案,S(sno,sname,sex,age),SC(sno,cno,grade),C(cno,cname,teacher),2,、查询年纪大于,23,岁男学生学号和姓名,Select sno,sname,from S,where age23 and sex=M,包括到表：,S(sno,sname,sex,age),方法一：普通查询,4/27,An Introduction to Database System,综合练习答案,S(sno,sname,sex,age),SC(sno,cno,grade),C(cno,cname,teacher),2,、查询年纪大于,23,岁男学生学号和姓名,Select sno,sname from S,where age23 and sno in,(select sno from s,where sex=,男,),包括到表：,S(sno,sname,sex,age),方法二：用,IN,嵌套查询,5/27,An Introduction to Database System,综合练习答案,S(sno,sname,sex,age),SC(sno,cno,grade),C(cno,cname,teacher),2,、查询年纪大于,23,岁男学生学号和姓名,Select sx.sno,sx.sname from s sx,where sx.age23 and exists,(select*from s sy,where sy.sex=,男,and sy.sno=sx.sno),包括到表：,S(sno,sname,sex,age),方法三：用,EXISTS,嵌套查询,6/27,An Introduction to Database System,综合练习答案,S(sno,sname,sex,age),SC(sno,cno,grade),C(cno,cname,teacher),2,、查询年纪大于,23,岁男学生学号和姓名,Select sx.sno,sx.sname,from s sx,s xy,where sx.sno=sy.sno and,sx.age23 and sy.sex=,男,包括到表：,Sx(sno,sname,sex,age),方法四：自连接,包括到表：,Sy(sno,sname,sex,age),7/27,An Introduction to Database System,综合练习答案,S(sno,sname,sex,age),SC(sno,cno,grade),C(cno,cname,teacher),2,、查询年纪大于,23,岁男学生学号和姓名,Select sno,sname from S,where age23,Intersect,Select sno,sname from S,where,sex=,男,包括到表：,S(sno,sname,sex,age),方法五：集合查询,8/27,An Introduction to Database System,综合练习答案,S(sno,sname,sex,age),SC(sno,cno,grade),C(cno,cname,teacher),3,、查询学号为,S3,学生所学课程课程名与任课教师名,Select cname,teacher,from SC,C,where SC.cno=C.cno and sno=S3,包括到表：,SC(sno,cno,grade),C(cno,cname,teacher),方法一：连接查询,9/27,An Introduction to Database System,综合练习答案,S(sno,sname,sex,age),SC(sno,cno,grade),C(cno,cname,teacher),3,、查询学号为,S3,学生所学课程课程名与任课教师名,Select cname,teacher from C,where cno in,(select cno from SC,where sno=S3),包括到表：,SC(sno,cno,grade),C(cno,cname,teacher),方法二：,IN,嵌套查询,10/27,An Introduction to Database System,综合练习答案,S(sno,sname,sex,age),SC(sno,cno,grade),C(cno,cname,teacher),3,、查询学号为,S3,学生所学课程课程名与任课教师名,Select cname,teacher from C,where exists,(select*from SC,where sno=S3 and SC.cno=C.cno),包括到表：,SC(sno,cno,grade),C(cno,cname,teacher),方法三：,EXIST,嵌套查询,11/27,An Introduction to Database System,综合练习答案,4,、查询最少选修,LIU,老师所讲课程中一门课程女学生姓名,S(sno,sname,sex,age),SC(sno,cno,grade),C(cno,cname,teacher),Select sname from S,SC,C,where S.sno=SC.sno and SC.cno=C.cno,and sex=F,and teacher=LIU,方法一：连接查询,包括到全部表：,S,，,SC,，,C,12/27,An Introduction to Database System,综合练习答案,4,、查询最少选修,LIU,老师所讲课程中一门课程女学生姓名,S(sno,sname,sex,age),SC(sno,cno,grade),C(cno,cname,teacher),Select sname from S,where sex=F and sno in,(select sno from SC,where cno in,(select cno from C,where teacher=LIU),方法二：,IN,嵌套查询,包括到全部表：,S,，,SC,，,C,13/27,An Introduction to Database System,综合练习答案,4,、查询最少选修,LIU,老师所讲课程中一门课程女学生姓名,S(sno,sname,sex,age),SC(sno,cno,grade),C(cno,cname,teacher),Select sname from S,where sex=F and exists,(select*from C,where teacher=LIU,and exists,(select*from SC,where SC.sno=S.sno,and SC.cno=C.cno),方法三：,EXISTS,嵌套查询,包括到全部表：,S,，,SC,，,C,14/27,An Introduction to Database System,综合练习答案,S(sno,sname,sex,age,),SC(sno,cno,grade),C(cno,cname,teacher),5,、查询,WANG,同学不学课程课程号,Select cno from C,where not exists,(select*from S,SC,where S.sno=SC.sno,and SC.cno=C.cno,and sname=WANG),包括到全部表：,S,，,SC,，,C,方法一：,NOT EXISTS,嵌套查询,15/27,An Introduction to Database System,综合练习答案,S(sno,sname,sex,age,),SC(sno,cno,grade),C(cno,cname,teacher),5,、查询,WANG,同学不学课程课程号,Select cno from C,Except,Select distinct cno from S,SC,where S.sno=SC.sno,and sname=WANG,包括到全部表：,S,，,SC,，,C,方法二：集合查询,16/27,An Introduction to Database System,综合练习答案,6,、查询最少选修两门课学生学号,S(sno,sname,sex,age),SC(sno,cno,grade),C(cno,cname,teacher),Select sno,from SC,group by sno having count(*)=2,包括到表：,SC,17/27,An Introduction to Database System,综合练习答案,7,、查询全部学生都选修课程课程号与课程名,S(sno,sname,sex,age),SC(sno,cno,grade),C(cno,cname,teacher),Select cno,cname,from C,where not exists,(select*from S,where not exists,(select*from SC,where sno=S.sno and cno=C.cno),包括到全部表：,S,，,SC,，,C,18/27,An Introduction to Database System,综合练习答案,8,、查询选修课程包含,LIU,老师所授全部课程学生学号,S(sno,sname,sex,age),SC(sno,cno,grade),C(cno,cname,teacher),Select distinct sno,from SC as X,where not exists,(select*from C,where teacher=LIU,and not exists,(select*from SC as Y,where Y.sno=X.sno,and Y.cno=C.cno),包括到表：,SC,，,C,19/27,An Introduction to Database System,综合练习答案,9,、统计每门课程学生选修人数（超出,10人课程才统计）。要求显示课程号和人数，查询结果按人数降序排列，若人数相同，按课程号升序排列,S(sno,sname,sex,age),SC(sno,cno,grade),C(cno,cname,teacher),Select cno,count(sno),from SC,group by cno having count(*)10,order by 2 desc,1,包括到表：,SC,20/27,An Introduction to Database System,综合练习答案,10、求LIU老师所讲课程每门课程平均成绩,S(sno,sname,sex,age),SC(sno,cno,grade),C(cno,cname,teacher),Select C.cno,avg(grade),from SC,C,where SC.cno=C.cno,and teacher=LIU,group by C.cno,包括到表：,SC,，,C,21/27,An Introduction to Database System,综合练习答案,11,、检索姓名以,L打头全部学生姓名和年纪,S(sno,sname,sex,age),SC(sno,cno,grade),C(cno,cname,teacher),Select sname,age,from S,where sname like L%,包括到表：,S,22/27,An Introduction to Database System,综合练习答案,12,、求年纪大于全部女同年纪男学生姓名和年纪,S(sno,sname,sex,age),SC(sno,cno,grade),C(cno,cname,teacher),Select sname,age,from S,where sex=M,and ageall(select age,from S,where sex=F),包括到表：,S,方法一：,all,23/27,An Introduction to Database System,综合练习答案,12,、求年纪大于全部女同年纪男学生姓名和年纪,S(sno,sname,sex,age),SC(sno,cno,grade),C(cno,cname,teacher),Select sname,age,from S,where sex=M,and age(select max(age),from S,where sex=F),包括到表：,S,方法二：,max,24/27,An Introduction to Database System,综合练习答案,13,、往关系,C中插一个课程元组（,C8,VC+,BAO,）,S(sno,sname,sex,age),SC(sno,cno,grade),C(cno,cname,teacher),Insert into C values(C8,VC+,BAO),25/27,An Introduction to Database System,综合练习答案,14,、把选修,LIU老师课程女同学选课元组全部删去,S(sno,sname,sex,age),SC(sno,cno,grade),C(cno,cname,teacher),Delete from SC,where sno in,(select sno,from S,where sex=F),and cno in,(select cno,from C,where teacher=LIU),26/27,An Introduction to Database System,综合练习答案,15,、把低于全部课程总平均成绩男同学成绩提升,5%,S(sno,sname,sex,age),SC(sno,cno,grade),C(cno,cname,teacher),Update SC,set grade=grade*1.05,where sno in,(select sno,from S,where sex=F),and grade (select avg(grade),from SC),27/27,