第3章课堂综合练习题答案

1、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、查询全部学生都

2、选修的课程的课程号、查询全部学生都选修的课程的课程号与课程名与课程名8、查询选修课程包含、查询选修课程包含LIU老师所授全部老师所授全部课程的学生学号。课程的学生学号。1、查询、查询LIU老师所授课程老师所授课程的课程号和课程名的课程号和课程名2、查询年龄大于、查询年龄大于23岁的男岁的男学生的学号和姓名学生的学号和姓名An Introduction to Database System第三章第三章 综合练习综合练习设有三个关系：S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher) 试用SQL语句表示下列查询语句10、求、求LIU老

3、师所授课程的每门课程的平均老师所授课程的每门课程的平均成绩成绩11、检索姓名以、检索姓名以L打头的所有学生的姓名和打头的所有学生的姓名和年龄。年龄。12、求年龄大于所有女同学年龄的男学生姓、求年龄大于所有女同学年龄的男学生姓名和年龄。名和年龄。13、往关系、往关系C中插一个课程元组（中插一个课程元组（C8,VC+,BAO）14、把选修、把选修LIU老师课程的女同学选课元组老师课程的女同学选课元组全部删去。全部删去。15、把低于所有课程总平均成绩的男同学成、把低于所有课程总平均成绩的男同学成绩提高绩提高5% .9、统计每门课程的学生选修人、统计每门课程的学生选修人数（超过数（超过10人的课程才统

4、计）。人的课程才统计）。要求显示课程号和人数，查询结要求显示课程号和人数，查询结果按人数降序排列，若人数相同果按人数降序排列，若人数相同，按课程号升序排列。，按课程号升序排列。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,teac

5、her)方法二：方法二：LIKESelect cno,cname from C where teacher like LIU方法三：方法三：INSelect cno,cname from C where teacher in (LIU)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

6、sex=M涉及到的表：S(sno,sname,sex,age)方法一：一般的查询方法一：一般的查询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)方法二：用

7、方法二：用IN嵌套查询嵌套查询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 =ANY(select sno from s where sex=男男)涉及到的表：S(sno,sname,sex,age)方法三：用方法三：用=ANY嵌套查询嵌套查询An Introduction

8、 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 xywhere sx.sno=sy.sno andsx.age23 and sy.sex=男男涉及到的表：Sx(sno,sname,sex,age)方法四：自连接方法四：自连接涉及到的表：Sy(sno,sname,sex,age)An Introduction to Da

9、tabase System综合练习答案综合练习答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)2、查询年龄大于、查询年龄大于23岁的男学生的学号和姓名岁的男学生的学号和姓名Select sx.sno, sx.sname from s sx inner join s xyon sx.sno=sy.sno wheresx.age23 and sy.sex=男男涉及到的表：Sx(sno,sname,sex,age)方法五：内连接方法五：内连接涉及到的表：Sy(sno,sname,sex,age)An Introduction to

10、 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嵌套查询嵌套查询An Introducti

11、on 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 IntersectSelect sno,sname from S where sex=男男涉及到的表：S(sno,sname,sex,age)方法七：集合查询方法七：集合查询An Introduction to Database System综合练习答案综合练习答案S(

12、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)方法一：自然连接查询方法一：自然连接查询An Introduction to Database System综合练习答案综合练习答案S(sno,sname,sex,age)SC(sno,c

13、no,grade)C(cno,cname,teacher)3、查询学号为、查询学号为S3学生所学课程的课程名与任课教师名学生所学课程的课程名与任课教师名Select cname ,teacher From SC inner join C On SC.cno=C.cno where sno=S3涉及到的表：SC(sno,cno,grade)C(cno,cname,teacher)方法二：内连接查询方法二：内连接查询An Introduction to Database System综合练习答案综合练习答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cna

14、me,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嵌套查询嵌套查询An Introduction to Database System综合练习答案综合练习答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)3、

15、查询学号为、查询学号为S3学生所学课程的课程名与任课教师名学生所学课程的课程名与任课教师名Select cname ,teacher from C where cno =ANY( select cno from SC where sno=S3 )涉及到的表：SC(sno,cno,grade)C(cno,cname,teacher)方法四：方法四：=ANY嵌套查询嵌套查询An Introduction to Database System综合练习答案综合练习答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)3、查询学号为、查询学

16、号为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嵌套查询嵌套查询An Introduction to Database System综合练习答案综合练习答案4 4、查询至少选修、查询至少选修LIULIU老师所授课程中一门课程的女学生姓名老师所授课程中一门课程的女学生姓名S(sno,sn

17、ame,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，CAn Introduction to Database System综合练习答案综合练习答案4 4、查询至少选修、查询至少选修LIULIU老师所授课程中一门课程的女学生姓名老师所授课程中一门课程的女学生姓名S(sno,sname,sex,age)

18、SC(sno,cno,grade)C(cno,cname,teacher)Select sname from S inner join SCOn S.sno=SC.sno inner join C on SC.cno=C.cno where sex=F and teacher=LIU方法二：内连接查询方法二：内连接查询涉及到全部的表：涉及到全部的表：S，SC，CAn Introduction to Database System综合练习答案综合练习答案4 4、查询至少选修、查询至少选修LIULIU老师所授课程中一门课程的女学生姓名老师所授课程中一门课程的女学生姓名S(sno,sname,sex

19、,age)SC(sno,cno,grade)C(cno,cname,teacher)Select sname from Swhere sex=F and sno in (select sno from SC where cno in (select cno from C where teacher=LIU)方法三：方法三：IN嵌套查询嵌套查询涉及到全部的表：涉及到全部的表：S，SC，CAn Introduction to Database System综合练习答案综合练习答案4 4、查询至少选修、查询至少选修LIULIU老师所授课程中一门课程的女学生姓名老师所授课程中一门课程的女学生姓名S(s

20、no,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)Select sname from Swhere sex=F and sno ANY (select sno from SC where cno =ANY (select cno from C where teacher=LIU)方法四：方法四：ANY嵌套查询嵌套查询涉及到全部的表：涉及到全部的表：S，SC，CAn Introduction to Database System综合练习答案综合练习答案4 4、查询至少选修、查询至少选修LIULIU老师所授课程中一门课程的女学生姓名老师所授

21、课程中一门课程的女学生姓名S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)Select sname from Swhere 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，CAn Introduction to Database System综合练习答案综合练

22、习答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)5、查询、查询WANG同学不学的课程的课程号同学不学的课程的课程号Select cno from Cwhere not exists (select * from S where sname=WANG and exists select * from SC where sno=SC.sno and cno=C.cno ) 涉及到全部的表：涉及到全部的表：S，SC，C方法一：方法一：NOT EXISTS嵌套查询嵌套查询An Introduction to Database Sy

23、stem综合练习答案综合练习答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)5、查询、查询WANG同学不学的课程的课程号同学不学的课程的课程号Select cno from CWhere cno not in (select cno from SC where sno in (select sno from S where sname=WANG)涉及到全部的表：涉及到全部的表：S，SC，C方法二：方法二：NOT IN嵌套查询嵌套查询An Introduction to Database System综合练习答案综合练习答案S

24、(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)5、查询、查询WANG同学不学的课程的课程号同学不学的课程的课程号Select cno from CWhere cno all(Select cno from SC where sno =any (select sno from S where sname=WANG)涉及到全部的表：涉及到全部的表：S，SC，C方法三：方法三：ANY/ALL查询查询An Introduction to Database System综合练习答案综合练习答案S(sno,sname,sex,age)SC(

25、sno,cno,grade)C(cno,cname,teacher)5、查询、查询WANG同学不学的课程的课程号同学不学的课程的课程号Select cno from CMinusSelect distinct cno from S, SC where S.sno=SC.sno and sname=WANG涉及到全部的表：涉及到全部的表：S，SC，C方法四：集合查询方法四：集合查询An Introduction to Database System综合练习答案综合练习答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)5、查询、查

26、询WANG同学不学的课程的课程号同学不学的课程的课程号Select cno from Cwhere not exists (select * from S,SC where S.sno=SC.sno and SC.cno=C.cno and sname=WANG)涉及到全部的表：涉及到全部的表：S，SC，C方法五：混合嵌套查询方法五：混合嵌套查询An Introduction to Database System综合练习答案综合练习答案6 6、查询至少选修两门课的学生学号、查询至少选修两门课的学生学号S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cnam

27、e,teacher)Select sno from SCgroup by sno having count(*)=2涉及到的表：涉及到的表：SCAn Introduction to Database System综合练习答案综合练习答案7 7、查询全部学生都选修的课程的课程号与课程名、查询全部学生都选修的课程的课程号与课程名S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)Select cno,cname from Cwhere not exists (select * from S where not exists (selec

28、t * from SC where sno=S.sno and cno=C.cno)涉及到全部的表：涉及到全部的表：S，SC，CAn Introduction to Database System综合练习答案综合练习答案8 8、查询选修课程包含、查询选修课程包含LIULIU老师所授全部课程的学生学号老师所授全部课程的学生学号S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)Select distinct sno from SC as Xwhere not exists (select * from C where teacher=

29、LIU and not exists (select * from SC as Y where Y.sno=X.sno and Y.cno=C.cno)涉及到的表：涉及到的表：SC，CAn Introduction to Database System综合练习答案综合练习答案9、统计每门课程的学生选修人数（超过、统计每门课程的学生选修人数（超过10人的课程才人的课程才统计）。要求显示课程号和人数，查询结果按人数降序统计）。要求显示课程号和人数，查询结果按人数降序排列，若人数相同，按课程号升序排列排列，若人数相同，按课程号升序排列S(sno,sname,sex,age)SC(sno,cno,gr

30、ade)C(cno,cname,teacher)Select cno,count(sno) from SC group by cno having count(*)10order by 2 desc,1涉及到的表：涉及到的表：SCAn 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,Cwhere SC

31、.cno=C.cno and teacher=LIU group by C.cno涉及到的表：涉及到的表：SC，CAn 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%涉及到的表：涉及到的表：SAn Introduction to Database System综合练习答案综合练习答案12、求年龄大于所有女同学年龄的男学生姓名和年龄、求年龄大于所有女同学年龄的男学生姓名和年龄S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)Select sname,age from S