最完整的sql练习+答案

1、最完整的sql练习+答案练习题一create database mydbgouse mydbcreate table stude nt(-学号sno varchar(3) not n ull primary key,-姓名sn ame varchar(4) not n ull,-性别ssex varchar(2) not n ull,-出生年月sbirthday datetime,-所在班级class varchar(5)create table teacher(-教工编号tno varchar(3) not null primary key,-教工姓名tn ame varchar(4) no

2、t n ull,-教工性别tsex varchar(2) not n ull,-教工出生日期tbirthday datetime,-职称prof varchar(6),-所在部门depart varchar(10)create table course(-课程号eno varchar(5) not n ull primary key,-课程名称cn ame varchar(10) not nu II,-教工编号tno varchar(3) references teacher(tno) create table score(-学号sno varchar(3) not n ull refere

3、ncesstude nt(s no),-课程号eno varchar(5) not n ull refere ncescourse(c no),-成绩degree decimal(4,1)in sert into stude ntvalues(108,曾华,男,1977-09-01,95033)in sert into stude ntvalues(105,匡明,男,1975-10-02,95031)in sert into stude ntvalues(107,王丽:女,1976-01-23,95033)in sert into stude ntvalues(101,李军,男,1976-02

4、-20,95033)in sert into stude ntvalues(109,王芳,女,1975-02-10,95031) in sert into stude ntvalues（1O3,陆君,男,1974-06-03,95031）in sert into teachervalues（804,李诚,男,1958-12-02,副教授, 计算机系）in sert into teachervalues（856,张旭,男,1969-03-12,讲师,电 子工程系）in sert into teachervalues（825,王萍,女,1972-05-05,助教,计算机系）in sert into

5、 teachervalues（831,刘冰,女,1958-08-14,助教,电 子工程系）in sert into coursevalues（3-105,计算机导论,825）in sert into coursevalues（3-245,操作系统,804）in sert into coursevalues（6-166,数字电路,856）in sert into course values(9-888,高等数学,831)insert into scorevalues(103,3-245,86)insert into scorevalues(105,3-245,75)insert into sco

6、revalues(109,3-245,68)insert into scorevalues(103,3-105,92)insert into scorevalues(105,3-105,88)insert into scorevalues(109,3-105,76)insert into scorevalues(101,3-105,64)insert into scorevalues(107,3-105,91)insert into scorevalues(108,3-105,78)in sert into scorevalues(101,6-166,85) insert into score

7、 values(107,6-166,79) insert into score values(108,6-166,81) select * fromstude nt口结果1消息|sno |sname |ssex |sbirthday| classr1L101J李军男1976-02-20 口00000 000950332103陆君更197463 00 00 00 000950313105匡明男19751002 00:00:00.000950314107王丽女1976-01-23 OttOaOO OOO950335103昔华更1977-09-01 00:0000000950336109土芳女197

8、5-02-10 0&00 00 00口95031select * from teacher结果i ）消息itnotnamel$ex |(birthdayprofdeparti004李诚男195B-12-02 00：a0:0tt000副教授计算机系2325王萍女1 S72 054J5 00:00:00.000助教计算机系.3Q31刘冰女195G 0S 14 加:口O.COO肋數电子工程系4B5G张旭196903-12 oo aaoaooo讲师电子工程采select * from courseenotnoJ_3-105计算机导论B25_2_S245操作系统60436-166数子电路85649-88

9、8高等数学831结果I 消息|select * from score5HOenodegreeJ_rios3-245_2_1053-24575.0J_1093-24568.0F1033-10592 051053-10583 061093-10576.071013-10564.0E1C73-10591.010B3-10578.0J0101616685.011107616679.012ice616681.0-1、查询 Student表中的所有记录的Sname、Ssex 和 Class 列。二结果|上消息|namecla1李军1男950332_男95031匡明男95031_4_王丽玄950335曾华男

10、95033G壬芳女95031selectsname , ssex , class from student-2、查询教师所有的单位即不重复的Depart列。selectdisti netdepart from teacher口结果ID消息IdepartJ_电子工程系-2计颐系-3、 查询Student表的所有记录select * from studenti结果|捎息|snosnamesbirthdavc尿1Hoi李军男197602-20 00:00:00.000950332103陆君男1974-06-03 00:00:00.00095031_3_105匡明男1975-10-02 00:00:0

11、0.00095031斗107王丽女1976-01-23 00:00:00.000950335ioe曾华男197709-01 QO:OttOQOOO950336109壬芳女1975-02-10 00:00:00.00035031-4、查询Score表中成绩在60到80之间的 所有记录。select * from score where degreebetween 60 and 80I结果|匕消息|nocna | degreeJ_io?324575.0_2_1093245 68,031093-105 76.041013-105 64.051083-105 78.0E1076-166 79.0-5、

12、 查询Score表中成绩为85, 86或88的 记录。select * from score where degree =85 or degree =86 or degree =88號|一J消息丨snocno degree1i 1033245 86.021053-105 88 031C16-166 050-6、查询Student表中“95031班或性别为女”的同学记录select * from studentwhere class =95031 or ssex =女结果b消息Isno嚣翎J伽hd越|J_j 103陆君i男1974-06-C3 00:00:00.000950312105匡明男 1

13、975-10 02 00 00:00.000 95031_3_107王丽女 197S-01 -23 00:00:00 00096033_4_109壬芳立 19702-10 00:00:00 000 96031-7、以Class降序查询Student表的所有记录select * from studentorder by class desc结果二消息.|snosname |ssexbirthdayclass李军男1976-02-20 Ott 00:00.000950332_1107壬丽1976-0V23 00:00:00.00095033103晋华男1977-09-01 00:00:00.000

14、950334109王芳女1975-02-10 00:00:00.000960315103陆君男1974-06-(13 Ott 00:00.000950316105匡明男1975-1002 00:00:00.00095031-8、以Cno升序、Degree降序查询Score表的所有记录selectfrom score order by cno , degree desc二结果消息IsnoenodegreeJ_；1O33-10592.021073-10591.03105310583.04108310570051093-105760&10131054 07_1033-2458S0_8_1063-24

15、5750_9_1093-245&301010185011103&1EE81 012107616B790-9、查询“95031班的学生人数select count ( sno ) from studentwhere class =95031口结果| J消虑氏列名|1冷iRkmuiuujisiiBJMiiBJM iul-10、查询Score表中的最高分的学生学号和课程号select sno , eno , degree from score where degree in( select max (degree ) from score )结果消息1snoenodegree1i 103丨 3-10

16、59Z0where eno =3-105-11、查询305 号课程的平均分select avg ( degree ) from score结果1山消息1氏列名1! 81.500000 1-12、查询Score表中至少有5名学生选修的并 以3开头的课程的平均分数。select avg (degree ) from score where eno like 3% and eno in (select eno from score group by eno having count ( eno ) 5)叵结果1消息1优列名111 31.500000 !: -13、查询最低分大于70，最高分小于90的

17、Sno 列。select sno from score where degreebetween 70 and 90-14、查询所有学生的 Sname、Cno和Degree.sno 二score . sno列。selectsname , cno , degree from score , student where student|E1结果匕消息|sriameenodegree1風君3-24586.02_匡明3-24575.0J_王芳3-24563.04時君310592.05匡明釦05ee.o6王芳3-1057607李军3-105&40g王丽3-10591.09曾华3-10578010李军850

18、11壬丽E-166?ao12曾华E16681.0-15、查询所有学生的Sno、Cname 和 Degree列。selectcname , student . sno , degreefrom score , student,course where.eno =course . enostudent . sno =score . sno and score庫结果|由稍息|cnamesnodegree11038S02操作系统1057503操作系魏1094计算机导论10392.0.5计算机导论10583.06计算机导论10976.07计算机导论10164.0_e计算机导论10791.D9计算机导论1

19、0879.010数宇电路10195.011数宇电路10779012数宇电路10881 0-16、查询所有学生的 Sname、Cname和 Degree 列selectsname , cname , degree from score , student , coursewherestudent . sno =score . sno and score . eno =course . eno丄结果IJ消息Icnamedegree18G.02匡明操作系统7503王芳操作系蜿G8.04陆君计豊机号论92.05匡明计算机导论88.08王芳计算机辱论760丁李军计負机导论G408王丽计篦机导论91 0g

20、曾华计負机辱论78010李军数字电路85.011壬丽数字电路79012曾华数字电路81.0-17、查询“95033班所选课程的平均分select 平均分=avg (degree ) from course , student , score where class =95033and course . eno =score . eno and student. sno =score . sno结果消息平均分179.6666BE .-.J.-.-. .-18、假设使用如下命令建立了一个grade表:-create table grade(low in t,upp in t,ra nk varch

21、ar(1)-i nsert into grade values(90,100,A)-i nsert into grade values(80,89,B)-i nsert into grade values(70,79,C)-i nsert into grade values(60,69,D)-i nsert into grade values(0,59,E)-现查询所有同学的Sno、Cno和rank列。select student . sno , cno , rank from score , student , grade wherestudent . sno =score . sno an

22、d degree between low and upp-19、查询选修“305”课程的成绩高于“109” 号同学成绩的所有同学的记录。无关子查询select score . sno , sname , ssex , sbirthday,class,score . eno , cname , degreefrom score , student,course where student.sno =score . sno andscore . eno =course . eno and o =3-105and degree n结果血消息1snesbirthday沁冷cnocnam

23、edegree1103陆君男1974-06-03 00:00:00.000950313-105计算机导论9202105匡明男1975-1(X)2 00:0Q 00.000950313-105计篡机导论8803107王丽女1976-01-23 OOtO&OaOOO950333-105计亶机导论91.04103曾华男1977-09-01 OftOOOOOOO950333-105计算机导论780(select degree from scorewhere sno =109 and cno =3-105)-20、查询score中选学多门课程的同学中分数为非最高分成绩的记录select sno , cn

24、o , degree from score where degree not in (select max( degree ) from score group by cno ) order by snoJ结果1消息|enocnadegree1*21C13-10564 01053-24575 031053-10538.041C73-10591.0_5_107616679.0108618681.01103-1057B.0Fs-1093-10576.091093-24563 0109、课程号为-21、查询成绩高于学号为from score where“3 05”的成绩的所有记录select * f

25、rom score where degree ( select degreesno =109 and eno =3-105)二錯果 J消息丨snoenodegreeJ_j 1033-24586.02isr豹0592.031053-105eao41073-10551.051063-105780&1016-1668507_1078-1 G79,0SioeG-16681.0-22、查询和学号为108的同学同年出生的所有 学生的 Sno、Sname 和 Sbirthday 列。select sno , sname , sbirthday from studentwhere year (sbirthda

26、y )=(select year ( sbirthday ) from studentwhere sno =108)3结果_j消息1snosname曲Mhday10al曾华1977-09-01 00:00:00.000-23、查询张旭教师任课的学生成绩select sno , score . eno , degree from score , course , teacher wherescore . eno =course . eno and course . tno =teacher . tno and tname =张旭二I结果匕消息-24、查询选修某课程的同学人数多于5人的教师姓名se

27、lecttnamefrom teacher , course where teacher . tno =course . tno andcount ( sno ) 5)course . eno in ( select eno from score group by enohaving-25、查询95033班和95031班全体学生的记录。select * from studentwhere class =95033 union select * from studentwhere class =95031口结果.消息snosnamessexsbirthdqyclass1iioi1李军男1976-

28、02-20 00:00:00.000950332107壬丽197G-0V23 00:00:00.000950333108曽华男1977-09-01 00:00:00.000350334103陆君男19740603 00:00:00000950315105匡明男1975-10-02 00:00:0000095031E1091375 OMO 00:00:00 00095031-26、查询存在有85分以上成绩的课程Cno.selectdisti neteno from score where degree 85二结果_j消息cnoi13-1051!23-245-27、查询出 计算机系 教师所教课程的

29、成绩select score . sno , score . eno , degreeteacher . tno =course . tno and courseorder by snofrom teacher , course , score where.eno =score . eno and depart=计算机系结果U消息SHOenodecree1丨1011.1 3-10564.021033-2458&.031033-10592.041053-105SB.051053-24575.061073-10591.071033-1057B.081093-245E&091093-10576.0-

30、28、查询计算机系”与电子工程系不同职称 的教师的Tname和Prof。selecttname , prof from teacher where depart =计算机系and prof not in(select prof from teacher where depart =电子工程系)unionselecttname , prof from teacherin (select prof from teacherwhere depart =电子工程系and prof notwhere depart =计算机系)曲结果匕消息|trame prof1 1李诚丨副教授hminrHmrHimai

31、ri*2 张旭讲师305课程且成绩至少-29、查询选修编号为高于选修编号为“-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。select cno , sno , degree from score where cno =3-105 and degree any ( select degree from score where cno =3-245) order by degree descJ结果少息cnosnodegree13-10510332033-10510530-30、查询

32、选修编号为“ -105 ”且成绩高于选修 编号为“-245”课程的同学的Cno、Sno和Degree.select cno , sno , degree from score where cno =3-105 and degree all ( select degree from score where cno =3-245)-31、查询所有教师和同学的name、sex 和錯果血消息|erasno |dsgrse1| 310510392.02310510560from teacher union selectstude ntbirthday.selectname =

33、tname , sex =tsex , birthday =tbirthday name =sname , sex =ssex , birthday =sbirthday from二结舉J消息namebirthday1|匡明1975-10-02 00:00:00.0002李诚男1958-12 02 00:00:00.0003李军男1976-02-20 00:00:00.0004刘冰女1958-08-14 00:00:00.0005陆君男1974-06-03 00:00:00.000S王芳女197502-10 00:00:00.0001王丽女1976-01-23 00:00:00.000a王萍女

34、1972-05-05 00:00:00.000a曾华男1977-09-01 00:00:00.00010张旭男1969-03-12 00:00:00.000-32、查询所有 女”教师和 女”同学的name、sex 和 birthday.selectname =tname , sex =tsex , birthday =tbirthday from teacher where tsex =女 union selectname =sname , sex =ssex , birthday =sbirthday from studentwhere ssex =女二1结果消息name$6Xbirthd

35、甲1刘冰195S-0B-14 00:00:00.0002壬芳1975-02-10 0000:00.0003王丽女1976-01-23 OQOO:00.0004王萍女1972-05-05 oao&oaooo-33、查询成绩比该课程平均成绩低的同学的成绩表select * from score where degree = 2结果_J消息clasoJ_295031hia an ibiui aid95033-37、查询Student表中不姓 王”勺同学记录J结果吉消息snamessex出 irthd 刖dassi Im李军男197602-20 00:00:00 0003503321D3陆君男1974

36、 06 03 00:00:00 000950313105匡明男197510 02 00:00:00.00095031A1PQ里iQT7.nQ.m nn nn mnnn口 Rrm-38、查询Student表中每个学生的姓名和年) from stude ntselectsname , sage =( 2011 - year (sbirthdayJ结果 j消息Lsname阮列名|李军| 352陆君373匡明3S4王丽355曾华34g王芳36-39、查询Student表中最大和最小的 Sbirthday 日期值。select max( sbirthday ) from student union se

37、lect min (sbirthday ) from stude ntJ结果_j消息_1；197W03 00:00:00.000 I 2197709-01 00:00:00 000-40、以班号和年龄从大到小的顺序查询Student表中的全部记录。select sno , sname , ssex , class , sage =( 2011 - year (sbirthday ) from student order by class desc ,( 2011 - sbirthday ) desc结果J消息srbosnamessexclasssageJ_王丽女95033352101李军男9503335310G曾华男95033344103吐君男35031375109王芳玄9503136E105匡明男950313G-41、查询 男”教师及其所上的课程。selecttname , tsex,cname , depart fromteacher , course wherecourse. tno =teacher.tno and tsex=男结果“乩消息tsexcnamedepart1