最完整的sql練習(xí)+答案

1、最完整的sql練習(xí)+答案練習(xí)題一create database mydbgouse mydbcreate table stude nt(-學(xué)號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(-學(xué)號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,計算機導(dǎo)論,825）in sert into coursevalues（3-245,操作系統(tǒng),804）in sert into coursevalues（6-166,數(shù)字電路,856）in sert into course values(9-888,高等數(shù)學(xué),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口結(jié)果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結(jié)果i ）消息itnotnamel$ex |(birthdayprofdeparti004李誠男195B-12-02 00：a0:0tt000副教授計算機系2325王萍女1 S72 054J5 00:00:00.000助教計算機系.3Q31劉冰女195G 0S 14 加:口O.COO肋數(shù)電子工程系4B5G張旭196903-12 oo aaoaooo講師電子工程采select * from courseenotnoJ_3-105計算機導(dǎo)論B25_2_S245操作系統(tǒng)60436-166數(shù)子電路85649-88

9、8高等數(shù)學(xué)831結(jié)果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 列。二結(jié)果|上消息|namecla1李軍1男950332_男95031匡明男95031_4_王麗玄950335曾華男

10、95033G壬芳女95031selectsname , ssex , class from student-2、查詢教師所有的單位即不重復(fù)的Depart列。selectdisti netdepart from teacher口結(jié)果ID消息IdepartJ_電子工程系-2計頤系-3、 查詢Student表的所有記錄select * from studenti結(jié)果|捎息|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結(jié)果|匕消息|nocna | degreeJ_io?324575.0_2_1093245 68,031093-105 76.041013-105 64.051083-105 78.0E1076-166 79.0-5、

12、 查詢Score表中成績?yōu)?5, 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班或性別為女”的同學(xué)記錄select * from studentwhere class =95031 or ssex =女結(jié)果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結(jié)果二消息.|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二結(jié)果消息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班的學(xué)生人數(shù)select count ( sno ) from studentwhere class =95031口結(jié)果| J消慮氏列名|1冷iRkmuiuujisiiBJMiiBJM iul-10、查詢Score表中的最高分的學(xué)生學(xué)號和課程號select sno , eno , degree from score where degree in( select max (degree ) from score )結(jié)果消息1snoenodegree1i 103丨 3-10

16、59Z0where eno =3-105-11、查詢305 號課程的平均分select avg ( degree ) from score結(jié)果1山消息1氏列名1! 81.500000 1-12、查詢Score表中至少有5名學(xué)生選修的并 以3開頭的課程的平均分數(shù)。select avg (degree ) from score where eno like 3% and eno in (select eno from score group by eno having count ( eno ) 5)叵結(jié)果1消息1優(yōu)列名111 31.500000 !: -13、查詢最低分大于70，最高分小于90的

17、Sno 列。select sno from score where degreebetween 70 and 90-14、查詢所有學(xué)生的 Sname、Cno和Degree.sno 二score . sno列。selectsname , cno , degree from score , student where student|E1結(jié)果匕消息|sriameenodegree1風(fēng)君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、查詢所有學(xué)生的Sno、Cname 和 Degree列。selectcname , student . sno , degreefrom score , student,course where.eno =course . enostudent . sno =score . sno and score庫結(jié)果|由稍息|cnamesnodegree11038S02操作系統(tǒng)1057503操作系魏1094計算機導(dǎo)論10392.0.5計算機導(dǎo)論10583.06計算機導(dǎo)論10976.07計算機導(dǎo)論10164.0_e計算機導(dǎo)論10791.D9計算機導(dǎo)論1

19、0879.010數(shù)宇電路10195.011數(shù)宇電路10779012數(shù)宇電路10881 0-16、查詢所有學(xué)生的 Sname、Cname和 Degree 列selectsname , cname , degree from score , student , coursewherestudent . sno =score . sno and score . eno =course . eno丄結(jié)果IJ消息Icnamedegree18G.02匡明操作系統(tǒng)7503王芳操作系蜿G8.04陸君計豊機號論92.05匡明計算機導(dǎo)論88.08王芳計算機辱論760丁李軍計負機導(dǎo)論G408王麗計篦機導(dǎo)論91 0g

20、曾華計負機辱論78010李軍數(shù)字電路85.011壬麗數(shù)字電路79012曾華數(shù)字電路81.0-17、查詢“95033班所選課程的平均分select 平均分=avg (degree ) from course , student , score where class =95033and course . eno =score . eno and student. sno =score . sno結(jié)果消息平均分179.6666BE .-.J.-.-. .-18、假設(shè)使用如下命令建立了一個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)-現(xiàn)查詢所有同學(xué)的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” 號同學(xué)成績的所有同學(xué)的記錄。無關(guān)子查詢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結(jié)果血消息1snesbirthday沁冷cnocnam

23、edegree1103陸君男1974-06-03 00:00:00.000950313-105計算機導(dǎo)論9202105匡明男1975-1(X)2 00:0Q 00.000950313-105計篡機導(dǎo)論8803107王麗女1976-01-23 OOtO&OaOOO950333-105計亶機導(dǎo)論91.04103曾華男1977-09-01 OftOOOOOOO950333-105計算機導(dǎo)論780(select degree from scorewhere sno =109 and cno =3-105)-20、查詢score中選學(xué)多門課程的同學(xué)中分數(shù)為非最高分成績的記錄select sno , cn

24、o , degree from score where degree not in (select max( degree ) from score group by cno ) order by snoJ結(jié)果1消息|enocnadegree1*21C13-10564 01053-24575 031053-10538.041C73-10591.0_5_107616679.0108618681.01103-1057B.0Fs-1093-10576.091093-24563 0109、課程號為-21、查詢成績高于學(xué)號為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、查詢和學(xué)號為108的同學(xué)同年出生的所有 學(xué)生的 Sno、Sname 和 Sbirthday 列。select sno , sname , sbirthday from studentwhere year (sbirthda

26、y )=(select year ( sbirthday ) from studentwhere sno =108)3結(jié)果_j消息1snosname曲Mhday10al曾華1977-09-01 00:00:00.000-23、查詢張旭教師任課的學(xué)生成績select sno , score . eno , degree from score , course , teacher wherescore . eno =course . eno and course . tno =teacher . tno and tname =張旭二I結(jié)果匕消息-24、查詢選修某課程的同學(xué)人數(shù)多于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班全體學(xué)生的記錄。select * from studentwhere class =95033 union select * from studentwhere class =95031口結(jié)果.消息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二結(jié)果_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=計算機系結(jié)果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 =計算機系)曲結(jié)果匕消息|trame prof1 1李誠丨副教授hminrHmrHimai

31、ri*2 張旭講師305課程且成績至少-29、查詢選修編號為高于選修編號為“-245”的同學(xué)的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結(jié)果少息cnosnodegree13-10510332033-10510530-30、查詢

32、選修編號為“ -105 ”且成績高于選修 編號為“-245”課程的同學(xué)的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、查詢所有教師和同學(xué)的name、sex 和錯果血消息|erasno |dsgrse1| 310510392.02310510560from teacher union selectstude ntbirthday.selectname =

33、tname , sex =tsex , birthday =tbirthday name =sname , sex =ssex , birthday =sbirthday from二結(jié)舉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、查詢所有 女”教師和 女”同學(xué)的name、sex 和 birthday.selectname =tname , sex =tsex , birthday =tbirthday from teacher where tsex =女 union selectname =sname , sex =ssex , birthday =sbirthday from studentwhere ssex =女二1結(jié)果消息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、查詢成績比該課程平均成績低的同學(xué)的成績表select * from score where degree = 2結(jié)果_J消息clasoJ_295031hia an ibiui aid95033-37、查詢Student表中不姓 王”勺同學(xué)記錄J結(jié)果吉消息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表中每個學(xué)生的姓名和年) from stude ntselectsname , sage =( 2011 - year (sbirthdayJ結(jié)果 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結(jié)果_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結(jié)果J消息srbosnamessexclasssageJ_王麗女95033352101李軍男9503335310G曾華男95033344103吐君男35031375109王芳玄9503136E105匡明男950313G-41、查詢 男”教師及其所上的課程。selecttname , tsex,cname , depart fromteacher , course wherecourse. tno =teacher.tno and tsex=男結(jié)果“乩消息tsexcnamedepart1