第3章課堂綜合練習(xí)題答案

1、An Introduction to Database System第三章第三章 綜合練習(xí)綜合練習(xí)設(shè)有三個關(guān)系：S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher) 試用SQL語句表示下列查詢語句3、查詢學(xué)號為查詢學(xué)號為S3學(xué)生所學(xué)課程的課程名學(xué)生所學(xué)課程的課程名與任課教師名與任課教師名4、查詢至少選修、查詢至少選修LIU老師所授課程中一老師所授課程中一門課程的女學(xué)生姓名門課程的女學(xué)生姓名5、查詢、查詢WANG同學(xué)不學(xué)的課程的課程同學(xué)不學(xué)的課程的課程號號6、查詢至少選修兩門課的學(xué)生學(xué)號、查詢至少選修兩門課的學(xué)生學(xué)號7、查詢?nèi)繉W(xué)生都

2、選修的課程的課程號、查詢?nèi)繉W(xué)生都選修的課程的課程號與課程名與課程名8、查詢選修課程包含、查詢選修課程包含LIU老師所授全部老師所授全部課程的學(xué)生學(xué)號。課程的學(xué)生學(xué)號。1、查詢、查詢LIU老師所授課程老師所授課程的課程號和課程名的課程號和課程名2、查詢年齡大于、查詢年齡大于23歲的男歲的男學(xué)生的學(xué)號和姓名學(xué)生的學(xué)號和姓名An Introduction to Database System第三章第三章 綜合練習(xí)綜合練習(xí)設(shè)有三個關(guān)系：S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher) 試用SQL語句表示下列查詢語句10、求、求LIU老

3、師所授課程的每門課程的平均老師所授課程的每門課程的平均成績成績11、檢索姓名以、檢索姓名以L打頭的所有學(xué)生的姓名和打頭的所有學(xué)生的姓名和年齡。年齡。12、求年齡大于所有女同學(xué)年齡的男學(xué)生姓、求年齡大于所有女同學(xué)年齡的男學(xué)生姓名和年齡。名和年齡。13、往關(guān)系、往關(guān)系C中插一個課程元組（中插一個課程元組（C8,VC+,BAO）14、把選修、把選修LIU老師課程的女同學(xué)選課元組老師課程的女同學(xué)選課元組全部刪去。全部刪去。15、把低于所有課程總平均成績的男同學(xué)成、把低于所有課程總平均成績的男同學(xué)成績提高績提高5% .9、統(tǒng)計每門課程的學(xué)生選修人、統(tǒng)計每門課程的學(xué)生選修人數(shù)（超過數(shù)（超過10人的課程才統(tǒng)

4、計）。人的課程才統(tǒng)計）。要求顯示課程號和人數(shù)，查詢結(jié)要求顯示課程號和人數(shù)，查詢結(jié)果按人數(shù)降序排列，若人數(shù)相同果按人數(shù)降序排列，若人數(shù)相同，按課程號升序排列。，按課程號升序排列。An Introduction to Database System綜合練習(xí)答案綜合練習(xí)答案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綜合練習(xí)答案綜合練習(xí)答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)2、查詢年齡大于、查詢年齡大于23歲的男學(xué)生的學(xué)號和姓名歲的男學(xué)生的學(xué)號和姓名Select sno,sname from S where age23 and

6、sex=M涉及到的表：S(sno,sname,sex,age)方法一：一般的查詢方法一：一般的查詢An Introduction to Database System綜合練習(xí)答案綜合練習(xí)答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)2、查詢年齡大于、查詢年齡大于23歲的男學(xué)生的學(xué)號和姓名歲的男學(xué)生的學(xué)號和姓名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綜合練習(xí)答案綜合練習(xí)答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)2、查詢年齡大于、查詢年齡大于23歲的男學(xué)生的學(xué)號和姓名歲的男學(xué)生的學(xué)號和姓名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綜合練習(xí)答案綜合練習(xí)答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)2、查詢年齡大于、查詢年齡大于23歲的男學(xué)生的學(xué)號和姓名歲的男學(xué)生的學(xué)號和姓名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綜合練習(xí)答案綜合練習(xí)答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)2、查詢年齡大于、查詢年齡大于23歲的男學(xué)生的學(xué)號和姓名歲的男學(xué)生的學(xué)號和姓名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)方法五：內(nèi)連接方法五：內(nèi)連接涉及到的表：Sy(sno,sname,sex,age)An Introduction to

10、 Database System綜合練習(xí)答案綜合練習(xí)答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)2、查詢年齡大于、查詢年齡大于23歲的男學(xué)生的學(xué)號和姓名歲的男學(xué)生的學(xué)號和姓名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綜合練習(xí)答案綜合練習(xí)答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)2、查詢年齡大于、查詢年齡大于23歲的男學(xué)生的學(xué)號和姓名歲的男學(xué)生的學(xué)號和姓名Select sno,sname from S where age23 IntersectSelect sno,sname from S where sex=男男涉及到的表：S(sno,sname,sex,age)方法七：集合查詢方法七：集合查詢An Introduction to Database System綜合練習(xí)答案綜合練習(xí)答案S(

12、sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)3、查詢學(xué)號為、查詢學(xué)號為S3學(xué)生所學(xué)課程的課程名與任課教師名學(xué)生所學(xué)課程的課程名與任課教師名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綜合練習(xí)答案綜合練習(xí)答案S(sno,sname,sex,age)SC(sno,c

13、no,grade)C(cno,cname,teacher)3、查詢學(xué)號為、查詢學(xué)號為S3學(xué)生所學(xué)課程的課程名與任課教師名學(xué)生所學(xué)課程的課程名與任課教師名Select cname ,teacher From SC inner join C On SC.cno=C.cno where sno=S3涉及到的表：SC(sno,cno,grade)C(cno,cname,teacher)方法二：內(nèi)連接查詢方法二：內(nèi)連接查詢An Introduction to Database System綜合練習(xí)答案綜合練習(xí)答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cna

14、me,teacher)3、查詢學(xué)號為、查詢學(xué)號為S3學(xué)生所學(xué)課程的課程名與任課教師名學(xué)生所學(xué)課程的課程名與任課教師名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綜合練習(xí)答案綜合練習(xí)答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)3、

15、查詢學(xué)號為、查詢學(xué)號為S3學(xué)生所學(xué)課程的課程名與任課教師名學(xué)生所學(xué)課程的課程名與任課教師名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綜合練習(xí)答案綜合練習(xí)答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)3、查詢學(xué)號為、查詢學(xué)

16、號為S3學(xué)生所學(xué)課程的課程名與任課教師名學(xué)生所學(xué)課程的課程名與任課教師名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綜合練習(xí)答案綜合練習(xí)答案4 4、查詢至少選修、查詢至少選修LIULIU老師所授課程中一門課程的女學(xué)生姓名老師所授課程中一門課程的女學(xué)生姓名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綜合練習(xí)答案綜合練習(xí)答案4 4、查詢至少選修、查詢至少選修LIULIU老師所授課程中一門課程的女學(xué)生姓名老師所授課程中一門課程的女學(xué)生姓名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方法二：內(nèi)連接查詢方法二：內(nèi)連接查詢涉及到全部的表：涉及到全部的表：S，SC，CAn Introduction to Database System綜合練習(xí)答案綜合練習(xí)答案4 4、查詢至少選修、查詢至少選修LIULIU老師所授課程中一門課程的女學(xué)生姓名老師所授課程中一門課程的女學(xué)生姓名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綜合練習(xí)答案綜合練習(xí)答案4 4、查詢至少選修、查詢至少選修LIULIU老師所授課程中一門課程的女學(xué)生姓名老師所授課程中一門課程的女學(xué)生姓名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綜合練習(xí)答案綜合練習(xí)答案4 4、查詢至少選修、查詢至少選修LIULIU老師所授課程中一門課程的女學(xué)生姓名老師所授

21、課程中一門課程的女學(xué)生姓名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綜合練習(xí)答案綜合練

22、習(xí)答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)5、查詢、查詢WANG同學(xué)不學(xué)的課程的課程號同學(xué)不學(xué)的課程的課程號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綜合練習(xí)答案綜合練習(xí)答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)5、查詢、查詢WANG同學(xué)不學(xué)的課程的課程號同學(xué)不學(xué)的課程的課程號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綜合練習(xí)答案綜合練習(xí)答案S

24、(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)5、查詢、查詢WANG同學(xué)不學(xué)的課程的課程號同學(xué)不學(xué)的課程的課程號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綜合練習(xí)答案綜合練習(xí)答案S(sno,sname,sex,age)SC(

25、sno,cno,grade)C(cno,cname,teacher)5、查詢、查詢WANG同學(xué)不學(xué)的課程的課程號同學(xué)不學(xué)的課程的課程號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綜合練習(xí)答案綜合練習(xí)答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)5、查詢、查

26、詢WANG同學(xué)不學(xué)的課程的課程號同學(xué)不學(xué)的課程的課程號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綜合練習(xí)答案綜合練習(xí)答案6 6、查詢至少選修兩門課的學(xué)生學(xué)號、查詢至少選修兩門課的學(xué)生學(xué)號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綜合練習(xí)答案綜合練習(xí)答案7 7、查詢?nèi)繉W(xué)生都選修的課程的課程號與課程名、查詢?nèi)繉W(xué)生都選修的課程的課程號與課程名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綜合練習(xí)答案綜合練習(xí)答案8 8、查詢選修課程包含、查詢選修課程包含LIULIU老師所授全部課程的學(xué)生學(xué)號老師所授全部課程的學(xué)生學(xué)號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綜合練習(xí)答案綜合練習(xí)答案9、統(tǒng)計每門課程的學(xué)生選修人數(shù)（超過、統(tǒng)計每門課程的學(xué)生選修人數(shù)（超過10人的課程才人的課程才統(tǒng)計）。要求顯示課程號和人數(shù)，查詢結(jié)果按人數(shù)降序統(tǒng)計）。要求顯示課程號和人數(shù)，查詢結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號升序排列排列，若人數(shù)相同，按課程號升序排列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綜合練習(xí)答案綜合練習(xí)答案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綜合練習(xí)答案綜合練習(xí)答案11、檢索姓名以、檢索姓名以L打頭的所有學(xué)生的姓名和年齡打頭的所有學(xué)生的姓名和年齡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綜合練習(xí)答案綜合練習(xí)答案12、求年齡大于所有女同學(xué)年齡的男學(xué)生姓名和年齡、求年齡大于所有女同學(xué)年齡的男學(xué)生姓名和年齡S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)Select sname,age from S