最完整的sql練習+答案

練習題一createdatabasemydbgousemydbcreatetablestudent(--學號snovarchar(3)notnullprimarykey,snamevarchar(4)notnull,--性別ssexvarchar(2)notnull,--出生年月sbirthdaydatetime,--所在班級classvarchar(5))createtableteacher(--教工編號tnovarchar(3)notnullprimarykey,--教工tnamevarchar(4)notnull,--教工性別tsexvarchar(2)notnull,--教工出生日期tbirthdaydatetime,--職稱profvarchar(6),--所在部門departvarchar(10))createtablecourse(--課程號cnovarchar(5)notnullprimarykey,--課程名稱cnamevarchar(10)notnull,--教工編號tnovarchar(3)referencesteacher(tno))createtablescore（--學號snovarchar（3）notnullreferencesstudent（sno）,--課程號cnovarchar（5）notnullreferencescourse（cno）,--成績degreedecimal（4,1））insertintostudentvalues（'1O8','曾華','男','1977-09-01','95033'）insertintostudentvalues（'105'，'匡明','男','1975-10-02','95031'）insertintostudentvalues（'1O7','王麗','女'，‘1976-01-23','95033'）insertintostudentvalues（'1O1','軍','男','1976-02-20','95033'）insertintostudentvalues（'1O9','王芳','女'，‘1975-02-10','95031'）insertintostudentvalues（'1O3','陸君','男','1974-06-03','95031'）insertintoteachervalues（'8O4','誠','男','1958-12-02','副教授','計算機系'）insertintoteachervalues（'856','旭','男','1969-03-12','講師','電子工程系'）insertintoteachervalues（'825','王萍','女','1972-05-05','助教','計算機系'）insertintoteachervalues（'831','冰','女','1958-08-14','助教','電子工程系'）insertintocoursevalues（'3-105',計算機導論','825'）insertintocoursevalues（'3-245',操作系統(tǒng)','804'）insertintocoursevalues（'6-166','數(shù)字電路','856'）insertintocoursevalues（'9-888','高等數(shù)學','831'）

insertintoscorevalues('103','3-245','86')insertintoscorevalues('105','3-245','75')insertintoscorevalues('109','3-245','68')insertintoscorevalues('103','3-105','92')insertintoscorevalues('105','3-105','88')insertintoscorevalues('109','3-105','76')insertintoscorevalues('101','3-105','64')insertintoscorevalues('107','3-105','91')insertintoscorevalues('108','3-105','78')insertintoscorevalues('101','6-166','85')insertintoscorevalues('107','6-166','79')insertintoscorevalues('108','6-166','81')select*from口結果1匕消息Isno]sname|ssex|sbirthda'r1class1iioi李車另1976-02-2000:00:00.300350332103爭1374-D5-D3□□：□□：□□.linn350313105匡明爭1375-1D-DZ□□:DO:00.00035031|4111/-fru立1976-01-23HhlUH51IIH晉華1977-09-01IHrinrHhlUH￡mq土芳iq7F-n2-irinnnn-nnnnn弔npselect*fromteacher二I結果|-冷消息|tnotname|tsex|tbirthdayp「oFdepart1j804李誠期195S-12-0200:00:00.000副褻侵計算機樂2825干萍1972-05-0500:00:00.000助教計算機丟3C31劉冰195G001400:00:00.000電了工程樂|4tltiH張旭期iaba-uj-1^ju：ju：uu.uuu講帥

select*fromcourse--1、查詢Student表中的所有記錄的Sname、Ssex和Class列。selectsname,ssex,classfromstudent--2、查詢教師所有的單位即不重復的Depart列。selectdistinctdepartfromteacher--3、查詢Student表的所有記錄。--5、查詢Score表中成績?yōu)?5，86或88的記錄。select*fromscorewheredegree='85'ordegree='86'ordegree='88

--6、查詢Student表中“95031”班或性別為“女”的同學記錄。select*fromstudentwhereclass='95O31'orssex='女二］結果1.消息|snos=ri-3rne^birthdayclassJ_103男1974-0&衛(wèi)00:00:00.000950312._105匡明男1975-10-0200:00:00.030950313107王麗女1976-01-2300:00:00.030950334109王芳立1975-02-1000:00:00.00095031--7、以Class降序查詢Student表的所有記錄。select*fromstudentorderbyclassdescl貉果|占消息|snosnamessexsbirlhdayclass11miL李軍男1976-02-2000:00:00.000950332107王麗1976-01-2300:00:00.000950333108曾華男1977-09-0100:00:00.00095033H-109王芳1975-02-1000:00:00.000950315103陸君男1974-06-0300:00:00.000950316105匡明男1975-10-0200:00:00.00095031--8、以Cno升序、Degree降序查詢Score表的所有記錄。select*fromscoreorderbyo,degreedesc--9、查詢“95031”班的學生人數(shù)。selectcount(sno)fromstudentwhereclass='95031--10、查詢Score表中的最高分的學生學號和課程號。selectsno,cno,degreefromscorewheredegreein(selectmax(degree)fromscore)--11、查詢‘3-105'號課程的平均分。selectavg(degree)fromscorewhereo='3-105'--12、查詢Score表中至少有5名學生選修的并以3開頭的課程的平均分數(shù)。selectavg(degree)fromscorewhereolike'3%'andoin(selectofromscoregroupbyohavingcount(cno)>5)--13、查詢最低分大于70，最高分小于90的Sno列。selectsnofromscorewheredegreebetween70and90--14、查詢所有學生的Sname、Cno和Degree列。selectsname,cno,degreefromscore,studentwherestudent.sno=score.sno結果|占消息|snarriecriodegree13-24556.02匡明3-24575.0王芳3-24E68.0|4-陸君3-10592.05匡明3-105S8.0王芳3-10576.07于軍3-10564.0|8~王麗3-10591.0p曾華3-10578.0106-166S5.011王麗6-16679.012習華6-16681.0--15、查詢所有學生的Sno、Cname和Degree列。

selectame,student.sno,degreefromscore,student,coursewherestudent.sno=o=oJ結果I也消息Icnarnesnodegree1［操作丟統(tǒng)10386.0|2操作系統(tǒng)10575.0h操作系統(tǒng)10968.04計算機導論10392.0計算機導論10588.0|6計算機導論10976.07計算機導論10164.0h計算機導論10791.09計篦機導論10878.010數(shù)字電躋101S5.011數(shù)字電路10779.0數(shù)字電瞎10881.0--16、查詢所有學生的Sname、Cname和Degree列selectsname,cname,degreefromscore,student,coursewherestudent.sno=o=o=結果|歯消息丨?n-3mecriarnedegree1操作系統(tǒng)珈2匡明操作系■統(tǒng)75.03王芳操作乘統(tǒng)68.04陸君計算機導論92.05匡明計算機導論88.06王芳計算機導論7G.07李軍計算機導論64.08王麗計算機導論91.09曾華計算機導論?8.010李軍數(shù)字電路85.011王麗數(shù)宇電路79.012曾華數(shù)于電路81.0--17、查詢“95033班”所選課程的平均分。select平均分=avg(degree)fromcourse,student,scorewhereclass='95O33o=oandstudent.sno=score.sno--18、假設使用如下命令建立了一個grade表：--createtablegrade(lowint,uppint,rankvarchar(1))--insertintogradevalues(90,100,'A')

--insertintogradevalues(80,89,'B')--insertintogradevalues(70,79,'C')--insertintogradevalues(60,69,'D')--insertintogradevalues(0,59,'E')--現(xiàn)查詢所有同學的Sno、Cno和rank列。selectstudent.sno,cno,rankfromscore,student,gradewherestudent.sno=score.snoanddegreebetweenlowandupp二1皓果|也消息Isnoenorank1:1033-245E1053-245C31093-245D41033-105A51053-105E61093-105匚71013-105DS1073-105Ap-1083-105CW1016-166E111076-166C121086-166E--19、查詢選修“3-105”課程的成績高于“109”號同學成績的所有同學的記錄。//無關子查詢selectscore.sno,sname,ssex,sbirthday,class,o,cname,degreefromscore,student,coursewherestudent.sno=o=o='3-105'anddegree>(selectdegreefromscorewheresno='109'ando='3-105')乜結果|.消息IsriQfn^messe;-:sbirthda^ch??enocnarnedegree1103陸君男1974-06-03oa：oo：ao.ooo950313-105計算機導論92.02105匡明男1975-10-02oa：oo：ao.ooo950313-105計篦機導論88.02107王麗197G-01-22oa：oo：ao.ooo950333-105計算機導論91.0I4108曽華男1977-09-0100:00:30.000950333-105計篦機導論78.0--20、查詢score中選學多門課程的同學中分數(shù)為非最高分成績的記錄。selectsno,cno,degreefromscorewheredegreenotin(selectmax(degree)fromscoregroupbyo)orderbysno罔結果］..J］消息Isriocnodegree11013-10564.0E-1053-24575.031053-10588.01073-10591.051076-16679.0fc-1006-16681.011083-10578.081093-10576.0Ija-1093-24568.0--21、查詢成績高于學號為“109”、課程號為“3-105”的成績的所有記錄。select*fromscorewheredegree>(selectdegreefromscorewheresno='109'ando='3-105')--22、查詢和學號為108的同學同年出生的所有學生的Sno、Sname和Sbirthday列。selectsno,sname,sbirthdayfromstudentwhereyear(sbirthday)=(selectyear(sbirthday)fromstudentwheresno='108')--23、查詢“旭“教師任課的學生成績。selectsno,o,degreefromscore,course,o=oandcourse.tno=teacher.tnoandtname='旭'--24、查詢選修某課程的同學人數(shù)多于5人的教師。selecttnamefromteacher,coursewhereteacher.tno=oin(selectofromscoregroupbyohavingcount(sno)>5)--25、查詢95033班和95031班全體學生的記錄。select*fromstudentwhereclass='95033'unionselect*fromstudentwhereclass='95031'

二結果Jl消息snosnam已ssexsturthddiy1class1：101李軍男1976-02-2000:00:00.000950332107王麗女197S-01-2300:00:00.000950233108曾華男1977-09-0100:00:00.000950334103陸君男1974-06-0300:00:00.000950315105匡明男1975-10-0200:00:00.000950316109王芳女1975-02-1000:00:00.00095031--26、查詢存在有85分以上成績的課程Cno.selectdistinctofromscorewheredegree>85--27、查詢出“計算機系“教師所教課程的成績表。selectscore.sno,o,degreefromteacher,course,scorewhereteacher.tno=o=oanddepart='計算機系orderbysnoJ結果匕消息srocnodegreeJ_1013-10564.021033-24586.031033-105S2.041053-10588.0510E3-24575.061073-10531.0710E3-10578.081033-24568.091093-10576.0--28、查詢“計算機系”與“電子工程系“不同職稱的教師的Tname和Prof。selecttname,proffromteacherwheredepart='計算機系'andprofnotin(selectproffromteacherwheredepart='電子工程系')unionselecttname,proffromteacherwheredepart='電子工程系'andprofnotin(selectproffromteacherwheredepart='計算機系')--29、查詢選修編號為“3-105“課程且成績至少高于選修編號為“3-245”的同學的Cno、Sno和Degree，并按Degree從高到低次序排序。selecto,sno,degreefromscorewhereo='3-105'anddegree>any(selectdegreefromscorewhereo='3-245')orderbydegreedesc--30、查詢選修編號為“3-105”且成績高于選修編號為“3-245”課程的同學的Cno、Sno和Degree.selecto,sno,degreefromscorewhereo='3-105'anddegree>all(selectdegreefromscorewhereo='3-245')--31、查詢所有教師和同學的name、sex和birthday.selectname=tname,sex=tsex,birthday=tbirthdayfromteacherunionselectname=sname,sex=ssex,birthday=sbirthdayfromstudentJ結果j消息ridmesexbirthday1男1975-10-3200:00:00.0002李誠男1958-12-0200:00:00.0003李軍男1976-02-2000:00:00.0004劉冰1958-08-1400:00:00.0005旺君男1974-06-0300:00:00.0006王芳1975-02-1000:00:00.0007王麗1976-01-2300:00:00.0008壬萍1972-05-0500:00:00.0009曾華男1977-09-0100:00:00.00010張旭男1969-03-1200:00:00.000--32、查詢所有“女"教師和“女”同學的name、sex和birthday.selectname=tname,sex=tsex,birthday=tbirthdayfromteacherwheretsex=女'unionselectname=sname,sex=ssex,birthday=sbirthdayfromstudentwheressex='女'--33、查詢成績比該課程平均成績低的同學的成績表。select*fromscorewheredegree<any(selectavg(degree)fromscoregroupbyo)--34、查詢所有任課教師的Tname和Depart.selecttname,departfromteacher--35查詢所有未講課的教師的Tname和Depart.selecttname,departfromteacher,coursewhereteacher.tno=course.tnoandoin(selectofromcoursewherenotexists(select*fromscorewhereo=o))--36、查詢至少有2名男生的班號。selectclassfromstudentwheressex='男'groupbyclasshavingcount(ssex)>=2--37、查詢Student表中不姓“王”的同學記錄。select*fromstudentwheresnamenotlike'王%二1結果消息snosridmesse;-;sbirthdayclass巨wiJ李軍男197E-02-20oo：oa：oo.aoo950332103陸君男is?斗06-03oo：oa：oo.aoo950313105匡明男1975-10-02oo：oa：oo.aoo95031A1rm些華里1A77.nq.mnn-nn-nnnnri--38、查詢Student表中每個學生的和年齡。selectsname,sage=(2011-year(sbirthday))fromstudent--39、查詢Student表中最大和最小的Sbirthday日期值。selectmax(sbirthday)fromstudentunionselectmin(sbirthday)fromstudent--40、以班號和年齡從大到小的順序查詢Student表中的全部記錄。selectsno,sname,ssex,class,sage=(2011-year(sbirthday))fromstudentorderbyclassdesc,(2011-sbirthday)desc--41、查詢“男”教師及其所上的課程。selecttname,tsex,cname,departfromteacher,coursewherecourse.tno=teacher.tnoan