SQL-练习题

--1、查询Student表中的所有记录的Sname、Ssex和Class列。usestudbgoselectSname,Ssex,ClassfromSTUDENT--2、查询教师所有的单位即不重复的Depart列。usestudbgoselectDistinctDepartfromTEACHER--3、查询Student表的所有记录。usestudbgoselect*fromSTUDENT--4、查询Score表中成绩在到之间的所有记录。usestudbgoselect*fromSCOREwhereDEGREEbetween60and80--5、查询Score表中成绩为，或的记录。usestudbgoselect*fromSCOREwhereDEGREEin(85,86,88)--6、查询Student表中“”班或性别为“女”的同学记录。usestudbgoselect*fromSTUDENTwhereCLASS='95007'orSSEX='女'--7、以Class降序查询Student表的所有记录。usestudbgoselect*fromSTUDENTorderbyCLASSdesc--8、以Cno升序、Degree降序查询Score表的所有记录。usestudbgoselect*fromSCOREorderbyCNO,DEGREEdesc--9、查询“”班的学生人数。usestudbgoselect(COUNT(SNO))fromSTUDENTwhereclass='95031'--10、查询Score表中的最高分的学生学号和课程号。usestudbgoselectsno,cnofromscorewhereDEGREE=(selectMAX(DEGREE)fromSCORE)--11、查询‘-105’号课程的平均分。usestudbgoselectAVG(DEGREE)fromSCOREwhereCNO='3-105'--12、查询Score表中至少有名学生选修的并以开头的课程的平均分数。usestudbgoselectAVG(DEGREE)fromSCOREwherecnoin(selectcNOfromSCOREgroupbyCNOhavingCOUNT(sno)=5)andCNOlike'3%'--13、查询最低分大于，最高分小于的Sno列。usestudbgoselectsnofromSCOREwhereSNOin(selectSNOfromscoregroupbySNOhavingMIN(DEGREE)70andMAX(DEGREE)90)--14、查询所有学生的Sname、Cno和Degree列。usestudbgoselectsname,cno,DEGREEfromSCOREscjoinstudentsonsc.SNO=s.SNO--15、查询所有学生的Sno、Cname和Degree列。usestudbgoselects.Sno,Cname,DegreefromstudentsjoinSCOREscons.SNO=sc.SNOjoinCOURSEconsc.CNO=c.CNO--16、查询所有学生的Sname、Cname和Degree列。usestudbgoselectSname,Cname,DegreefromstudentsjoinSCOREscons.SNO=sc.SNOjoinCOURSEconsc.CNO=c.CNO--17、查询“”班所选课程的平均分。usestudbgoselectAVG(DEGREE)fromSCOREscjoinstudentsonsc.SNO=s.SNOwhereclass='95033'--18、假设使用如下命令建立了一个grade表：--createtablegrade(lownumber(3,0),uppnumber(3),rankchar(1));--insertintogradevalues(90,100,’A’);--insertintogradevalues(80,89,’B’);--insertintogradevalues(70,79,’C’);--insertintogradevalues(60,69,’D’);--insertintogradevalues(0,59,’E’);--commit;--现查询所有同学的Sno、Cno和rank列。usestudbgocreatetablegrade(lownumber(3,0),uppnumber(3),rankchar(1));insertintogradevalues(90,100,'A');insertintogradevalues(80,89,'B');insertintogradevalues(70,79,'C');insertintogradevalues(60,69,'D');insertintogradevalues(0,59,'E');--19、查询选修“-105”课程的成绩高于“”号同学成绩的所有同学的记录。usestudbgoselectx.CNO,x.SNO,x.DEGREEfromSCOREx,SCOREywherex.CNO='3-105'andx.DEGREEy.DEGREEandy.SNO='109'andy.CNO='3-105'--20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。USEstudbGOselect*fromscorejwherej.SNOin(selectSNOfromSCOREgroupbySNOhavingcount(*)=2)andj.DEGREE(selectmax(DEGREE)fromSCOREbwhereb.CNO=j.CNO);--21、查询成绩高于学号为“”、课程号为“-105”的成绩的所有记录。usestudbgoselectx.CNO,x.SNO,x.DEGREEfromSCOREx,SCOREywherex.CNO='3-105'andx.DEGREEy.DEGREEandy.SNO='109'andy.CNO='3-105'--22、查询和学号为的同学同年出生的所有学生的Sno、Sname和Sbirthday列。usestudbgoselectSNO,SNAME,SBIRTHDAYfromSTUDENTwhereyear(SBIRTHDAY)=(selectyear(SBIRTHDAY)fromSTUDENTwhereSNO='108')--23、查询“张旭“教师任课的学生成绩。usestudbgoselecta.SNO,a.DEGREEfromSCOREainnerjoincoursebona.CNO=b.CNOinnerjoinTEACHERconb.TNO=c.TNOwheretname='张旭'--24、查询选修某课程的同学人数多于人的教师姓名。usestudbgoselectTNAMEfromTEACHERwhereTNOin(selectx.TNOfromCOURSExinnerjoinSCOREyonx.CNO=y.CNOgroupbyx.TNOhavingcount(x.TNO)5)--25、查询班和班全体学生的记录。usestudbgoselect*fromSTUDENTwhereCLASS='95033'orCLASS='95031'--26、查询存在有分以上成绩的课程Cno.usestudbgoselectCNOfromSCOREwhereDEGREE=85--27、查询出“计算机系“教师所教课程的成绩表。usestudbgoselect*fromSCOREwhereCNOin(selectCOURSE.CNOfromCOURSE,TEACHERwhereTEACHER.TNO=COURSE.TNOandTEACHER.DEPART='计算机系')--28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。usestudbgoselectTNAME,PROFfromTEACHERwhereDEPART='计算机系'andPROFnotin(selectPROFfromTEACHERwhereDEPART='电子工程系')--29、查询选修编号为“-105“课程且成绩至少高于选修编号为“-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。usestudbgoselect*fromSCOREwhereCNO='3-105'andDEGREEany(selectDEGREEfromSCOREwhereCNO='3-245')orderbyDEGREEdesc--30、查询选修编号为“-105”且成绩高于选修编号为“-245”课程的同学的Cno、Sno和Degree.usestudbgoselect*fromSCOREwhereCNO='3-105'andDEGREEall(selectDEGREEfromSCOREwhereCNO='3-245')--31、查询所有教师和同学的name、sex和birthday.usestudbgoselectTNAME,TSEX,TBIRTHDAYfromTEACHERunionselectSNAME,SSEX,SBIRTHDAYfromstudent--32、查询所有“女”教师和“女”同学的name、sex和birthday.usestudbgoselectTNAME,TSEX,TBIRTHDAYfromTEACHERwhereTSEX='女'unionselectSNAME,SSEX,SBIRTHDAYfromstudentwhereSSEX='女'--33、查询成绩比该课程平均成绩低的同学的成绩表。usestudbgoselect*fromSCOREawhereDEGREE(selectavg(DEGREE)fromSCOREbwherea.CNO=b.CNO)--34、查询所有任课教师的Tname和Depart.usestudbgoselectTNAME,DEPARTfromTEACHERwhereexists(select*fromCOURSEwhereTEACHER.TNO=COURSE.TNO)--35查询所有未讲课的教师的Tname和Depart.usestudbgoselectTNAME,DEPARTfromTEACHERwherenotexists(select*fromCOURSEwhereTEACHER.TNO=COURSE.TNO)--36、查询至少有名男生的班号。usestudbgoselectCLASSfromstudentwhereSSEX='男'groupbyCLASShavingCOUNT(*)=2--37、查询Student表中不姓“王”的同学记录。usestudbgoselect*fromstudentwhereSNAMEnotlike'王%'--38、查询Student表中每个学生的姓名和年龄。usestudbgoselectSNAMEas姓名,year(GETDATE())-year(SBIRTHDAY)as年龄fromstudent--39、查询Student表中最大和最小的Sbirthday日期值。usestudbgoselectSNAME,SBIRTHDAYfromSTUDENTwhereSBIRTHDAY=(selectmin(SBIRTHDAY)fromSTUDENT)unionselectSNAME,SBIRTHDAYfromSTUDENTwhereSBIRTHDAY=(selectmax(SBIRTHDAY)fromSTUDENT)--40、以班号和年龄从大到小的顺序查询Student表中的全部记录。usestudbgoselect*fromSTUDENTorderbyCLASSdesc,year(G