《SQL语句课堂练习题及答案.ppt》由会员分享，可在线阅读，更多相关《SQL语句课堂练习题及答案.ppt（27页珍藏版）》请在金锄头文库上搜索。
1、AnIntroductiontoDatabaseSystem第三章第三章 综合练习综合练习设有三个关系：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岁的男岁的男学生的学号和姓名学生的学号和姓名Evaluation only.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Copyright 2004-2011 Aspose Pty Ltd.AnIntroductiontoDatabaseSyste
3、m第三章第三章 综合练习综合练习设有三个关系：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老师课程的女同学选课元组老师课程的女同学
4、选课元组全部删去。全部删去。15、把低于所有课程总平均成绩的男同学成、把低于所有课程总平均成绩的男同学成绩提高绩提高5% .9、统计每门课程的学生选修人、统计每门课程的学生选修人数（超过数（超过10人的课程才统计）。人的课程才统计）。要求显示课程号和人数，查询结要求显示课程号和人数，查询结果按人数降序排列，若人数相同，果按人数降序排列，若人数相同，按课程号升序排列。按课程号升序排列。Evaluation only.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Copyright 2004-2011 Aspose P
5、ty Ltd.AnIntroductiontoDatabaseSystem综合练习答案综合练习答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)1、查询、查询LIU老师所授课程的课程号和课程名老师所授课程的课程号和课程名Select cno,cname from Cwhere teacher=LIU涉及到的表：C(cno,cname,teacher)Evaluation only.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Copyright 2
6、004-2011 Aspose Pty Ltd.AnIntroductiontoDatabaseSystem综合练习答案综合练习答案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)方法一：一般的查询方法一：一般的查询Evaluation only.Created with Aspose.Slides for .N
7、ET 3.5 Client Profile 5.2.0.0.Copyright 2004-2011 Aspose Pty Ltd.AnIntroductiontoDatabaseSystem综合练习答案综合练习答案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,s
8、ex,age)方法二：用方法二：用IN嵌套查询嵌套查询Evaluation only.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Copyright 2004-2011 Aspose Pty Ltd.AnIntroductiontoDatabaseSystem综合练习答案综合练习答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)2、查询年龄大于、查询年龄大于23岁的男学生的学号和姓名岁的男学生的学号和姓名Select sx.sno, sx.sna
9、me 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嵌套查询嵌套查询Evaluation only.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Copyright 2004-2011 Aspose Pty Ltd.AnIntroductiontoDatabaseSystem综合练习答案综合练习答案S(
10、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)Evaluation only.Created with Aspose.Slides for .NET 3.
11、5 Client Profile 5.2.0.0.Copyright 2004-2011 Aspose Pty Ltd.AnIntroductiontoDatabaseSystem综合练习答案综合练习答案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
12、,age)方法五：集合查询方法五：集合查询Evaluation only.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Copyright 2004-2011 Aspose Pty Ltd.AnIntroductiontoDatabaseSystem综合练习答案综合练习答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)3、查询学号为、查询学号为S3学生所学课程的课程名与任课教师名学生所学课程的课程名与任课教师名Select cname ,teach
13、er from SC,C where SC.cno=C.cno and sno=S3涉及到的表：SC(sno,cno,grade)C(cno,cname,teacher)方法一：连接查询方法一：连接查询Evaluation only.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Copyright 2004-2011 Aspose Pty Ltd.AnIntroductiontoDatabaseSystem综合练习答案综合练习答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,
14、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嵌套查询嵌套查询Evaluation only.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Copyright 2004-2011 Asp
15、ose Pty Ltd.AnIntroductiontoDatabaseSystem综合练习答案综合练习答案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)方法三：方法三：EX
16、IST嵌套查询嵌套查询Evaluation only.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Copyright 2004-2011 Aspose Pty Ltd.AnIntroductiontoDatabaseSystem综合练习答案综合练习答案4 4、查询至少选修、查询至少选修LIULIU老师所授课程中一门课程的女学生姓名老师所授课程中一门课程的女学生姓名S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)Select sname from S
17、,SC,C where S.sno=SC.sno and SC.cno=C.cno and sex=F and teacher=LIU方法一：连接查询方法一：连接查询涉及到全部的表：涉及到全部的表：S，SC，CEvaluation only.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Copyright 2004-2011 Aspose Pty Ltd.AnIntroductiontoDatabaseSystem综合练习答案综合练习答案4 4、查询至少选修、查询至少选修LIULIU老师所授课程中一门课程的女学生姓
18、名老师所授课程中一门课程的女学生姓名S(sno,sname,sex,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，CEvaluation only.Created with Aspose.Slides for .NET 3.5 Client Profile 5.
19、2.0.0.Copyright 2004-2011 Aspose Pty Ltd.AnIntroductiontoDatabaseSystem综合练习答案综合练习答案4 4、查询至少选修、查询至少选修LIULIU老师所授课程中一门课程的女学生姓名老师所授课程中一门课程的女学生姓名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
20、SC where SC.sno=S.sno and SC.cno=C.cno )方法三：方法三：EXISTS嵌套查询嵌套查询涉及到全部的表：涉及到全部的表：S，SC，CEvaluation only.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Copyright 2004-2011 Aspose Pty Ltd.AnIntroductiontoDatabaseSystem综合练习答案综合练习答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)5、查
21、询、查询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方法一：方法一：NOT EXISTS嵌套查询嵌套查询Evaluation only.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Copyright 2004-2011 Aspose Pty Ltd.AnI
22、ntroductiontoDatabaseSystem综合练习答案综合练习答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)5、查询、查询WANG同学不学的课程的课程号同学不学的课程的课程号Select cno from CExceptSelect distinct cno from S, SC where S.sno=SC.sno and sname=WANG涉及到全部的表：涉及到全部的表：S，SC，C方法二：集合查询方法二：集合查询Evaluation only.Created with Aspose.Slides for
23、 .NET 3.5 Client Profile 5.2.0.0.Copyright 2004-2011 Aspose Pty Ltd.AnIntroductiontoDatabaseSystem综合练习答案综合练习答案6 6、查询至少选修两门课的学生学号、查询至少选修两门课的学生学号S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)Select sno from SCgroup by sno having count(*)=2涉及到的表：涉及到的表：SCEvaluation only.Created with Aspose.Sl
24、ides for .NET 3.5 Client Profile 5.2.0.0.Copyright 2004-2011 Aspose Pty Ltd.AnIntroductiontoDatabaseSystem综合练习答案综合练习答案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
25、t * from SC where sno=S.sno and cno=C.cno)涉及到全部的表：涉及到全部的表：S，SC，CEvaluation only.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Copyright 2004-2011 Aspose Pty Ltd.AnIntroductiontoDatabaseSystem综合练习答案综合练习答案8 8、查询选修课程包含、查询选修课程包含LIULIU老师所授全部课程的学生学号老师所授全部课程的学生学号S(sno,sname,sex,age)SC(sno,
26、cno,grade)C(cno,cname,teacher)Select distinct sno from SC as Xwhere 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，CEvaluation only.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Copyright 2004-2011
27、 Aspose Pty Ltd.AnIntroductiontoDatabaseSystem综合练习答案综合练习答案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(
28、*)10order by 2 desc,1涉及到的表：涉及到的表：SCEvaluation only.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Copyright 2004-2011 Aspose Pty Ltd.AnIntroductiontoDatabaseSystem综合练习答案综合练习答案10、求、求LIU老师所授课程的每门课程的平均成绩老师所授课程的每门课程的平均成绩S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)Select C.cn
29、o,avg(grade) from SC,Cwhere SC.cno=C.cno and teacher=LIU group by C.cno涉及到的表：涉及到的表：SC，CEvaluation only.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Copyright 2004-2011 Aspose Pty Ltd.AnIntroductiontoDatabaseSystem综合练习答案综合练习答案11、检索姓名以、检索姓名以L打头的所有学生的姓名和年龄打头的所有学生的姓名和年龄S(sno,sname,sex,
30、age)SC(sno,cno,grade)C(cno,cname,teacher)Select sname,age from S where sname like L%涉及到的表：涉及到的表：SEvaluation only.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Copyright 2004-2011 Aspose Pty Ltd.AnIntroductiontoDatabaseSystem综合练习答案综合练习答案12、求年龄大于所有女同学年龄的男学生姓名和年龄、求年龄大于所有女同学年龄的男学生姓名和年龄S
31、(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)Select sname,age from Swhere sex=M and ageall (select age from S where sex=F)涉及到的表：涉及到的表：S方法一：方法一：allEvaluation only.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Copyright 2004-2011 Aspose Pty Ltd.AnIntroductiontoDatabaseSyste
32、m综合练习答案综合练习答案12、求年龄大于所有女同学年龄的男学生姓名和年龄、求年龄大于所有女同学年龄的男学生姓名和年龄S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)Select sname,age from Swhere sex=M and age (select max(age) from S where sex=F)涉及到的表：涉及到的表：S方法二：方法二：maxEvaluation only.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Co
33、pyright 2004-2011 Aspose Pty Ltd.AnIntroductiontoDatabaseSystem综合练习答案综合练习答案1313、往关系、往关系C C中插一个课程元组中插一个课程元组（C8,VC+,BAOC8,VC+,BAO）S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)Insert into C values(C8,VC+,BAO)Evaluation only.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Copy
34、right 2004-2011 Aspose Pty Ltd.AnIntroductiontoDatabaseSystem综合练习答案综合练习答案1414、把选修、把选修LIULIU老师课程的女同学选课元组全部删去老师课程的女同学选课元组全部删去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)Evaluation o
35、nly.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Copyright 2004-2011 Aspose Pty Ltd.AnIntroductiontoDatabaseSystem综合练习答案综合练习答案1515、把低于所有课程总平均成绩的男同学成绩提高、把低于所有课程总平均成绩的男同学成绩提高5%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 age(grade) from SC)Evaluation only.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Copyright 2004-2011 Aspose Pty Ltd.
