Sql語句基礎示例

1、說明：以下五十個語句都按照測試數(shù)據(jù)進行過測試，最好每次只單獨運行一個語句。問題及描述：- -1.學生表Student(S#,Sname,Sage,Ssex)-S#學生編號,Sname 學生姓名,Sage 出生年月,Ssex 學生性別- -2.課程表Course(C#,Cname,T#)-C#-課程編號，Cname 課程名稱,T#教師編號- -3.教師表Teacher(T#,Tname)-T#教師編號,Tname 教師姓名- -4.成績表SC(S#,C#,score)-S#學生編號,C#課程編號，score 分數(shù)- /- -創(chuàng)建測試數(shù)據(jù)createtableStudent(S#varchar(1

2、0),Snamenvarchar(10),Sagedatetime,Ssexnvarchar(10)insertintoStudentvalues(01,N趙雷insertintoStudentvalues(02,N錢電insertintoStudentvalues(03,N孫風insertintoStudentvalues(04,N李云insertintoStudentvalues(05,N周梅insertintoStudentvalues(06,N吳蘭insertintoStudentvalues(07,N關B竹insertintoStudentvalues(08,N王菊createtab

3、leCourse(C#varchar(10),Cnamenvarchar(10),T#varchar(10)insertintoCoursevalues(01,N語文,02)insertintoCoursevalues(02,N數(shù)學,01)insertintoCoursevalues(03,N英語,03)createtableTeacher(T#varchar(10),Tnamenvarchar(10)insertintoTeachervalues(01,N張三)insertintoTeachervalues(02,N李四)insertintoTeachervalues(03,N王五),199

4、0-01-01,N男),1990-12-21,NF),1990-05-20,N男),1990-08-06,N男),1991-12-01,N女),1992-03-01,N女),1989-07-01,N女),1990-01-20,N女)createtableSC(S#varchar(10),C#varchar(10),scoredecimal(18,1)insertintoSCvalues(01,01,80)insertintoSCvalues(01,02,90)insertintoSCvalues(01,03,99)insertintoSCvalues(02,01,70)insertintoSC

5、values(02,02,60)insertintoSCvalues(02,03,80)insertintoSCvalues(03,01,80)insertintoSCvalues(03,02,80)insertintoSCvalues(03,03,80)insertintoSCvalues(04,01,50)insertintoSCvalues(04,02,30)insertintoSCvalues(04,03,20)insertintoSCvalues(05,01,76)insertintoSCvalues(05,02,87)insertintoSCvalues(06,01,31)inse

6、rtintoSCvalues(06,03,34)insertintoSCvalues(07,02,89)insertintoSCvalues(07,03,98)go-1、查詢01課程比02課程成績高的學生的信息及課程分數(shù)-1.1、查詢同時存在01課程和02課程的情況selecta.*,b.score課程01的分數(shù),c.score課程02的分數(shù)fromStudenta,SCb,SCcwherea.S#=b.S#anda.S#=c.S#andb.C#=01andc.C#=02andb.scorec.score-1.2、 查詢同時存在01課程和02課程的情況和存在01課程但可能不存在02課程的情況(

7、不存在時顯示為 null)(以下存在相同內(nèi)容時不再解釋)selecta.*,b.score 課程01的分數(shù) ,c.score 課程02的分數(shù) fromStudentaleftjoinSCbona.S#=b.S#andb.C#=01leftjoinSCcona.S#=c.S#andc.C#=02whereb.scoreisnull(c.score,0)-2、查詢01課程比02課程成績低的學生的信息及課程分數(shù)-2.1、查詢同時存在01課程和02課程的情況selecta.*,b.score課程01的分數(shù),c.score課程02的分數(shù)fromStudenta,SCb,SCcwherea.S#=b.S#

8、anda.S#=c.S#andb.C#=01andc.C#=02andb.scorec.score-2.2、 查詢同時存在01課程和02課程的情況和不存在01課程但存在02課程的情況selecta.*,b.score 課程01的分數(shù) ,c.score 課程02的分數(shù) fromStudentaleftjoinSCbona.S#=b.S#andb.C#=01leftjoinSCcona.S#=c.S#andc.C#=02whereisnull(b.score,0)=60orderbya.S#- -4、查詢平均成績小于 60 分的同學的學生編號和學生姓名和平均成績- -4.1、查詢在 sc 表存在成

9、績的學生信息的 SQL 語句。selecta.S#,a.Sname,cast(avg(b.score)asdecimal(18,2)avg_scorefromStudenta,scbwherea.S#=b.S#groupbya.S#,a.Snamehavingcast(avg(b.score)asdecimal(18,2)60orderbya.S#- -4.2、查詢在 sc 表中不存在成績白學生信息的 SQL 語句。selecta.S#,a.Sname,isnull(cast(avg(b.score)asdecimal(18,2),0)avg_scorefromStudentaleftjoin

10、scbona.S#=b.S#groupbya.S#,a.Snamehavingisnull(cast(avg(b.score)asdecimal(18,2),0)60orderbya.S#- -5、查詢所有同學的學生編號、學生姓名、選課總數(shù)、所有課程的總成績- -5.1、查詢所有有成績的 SQL。selecta.S#學生編號,a.Sname學生姓名,count(b.C#)選課總數(shù)，sum(score)所有課程的總成績fromStudenta,SCbwherea.S#=b.S#groupbya.S#,a.Snameorderbya.S#- -5.2、查詢所有(包括有成績和無成績)的 SQL。se

11、lecta.S#學生編號,a.Sname學生姓名,count(b.C#)選課總數(shù)，sum(score)所有課程的總成績fromStudentaleftjoinSCbona.S#=b.S#groupbya.S#,a.Snameorderbya.S#- -6、查詢李姓老師的數(shù)量- -方法 1selectcount(Tname)李姓老師白數(shù)量fromTeacherwhereTnamelikeN李- -方法 2selectcount(Tname)李姓老師白數(shù)量fromTeacherwhereleft(Tname,1)=N李/*李姓老師的數(shù)量1*/- -7、查詢學過張三老師授課的同學的信息selectd

12、istinctStudent.*fromStudent,SC,Course,TeacherwhereStudent.S#=SC.S#andSC.C#=Course.C#andCourse.T#=Teacher.T#andTeacher.Tname=N張三orderbyStudent.S#- -8、查詢沒學過張三老師授課的同學的信息selectm.*fromStudentmwhereS#notin(selectdistinctSC.S#fromSC,Course,TeacherwhereSC.C#=Course.C#andCourse.T#=Teacher.T#andTeacher.Tname=

13、N張三)orderbym.S#-9、查詢學過編號為01并且也學過編號為02的課程的同學的信息- -方法 1selectStudent.*fromStudent,SCwhereStudent.S#=SC.S#andSC.C#=01andexists(Select1fromSCSC_2whereSC_2.S#=SC.S#andSC_2.C#=02)orderbyStudent.S#-方法 2selectStudent.*fromStudent,SCwhereStudent.S#=SC.S#andSC.C#=02andexists(Select1fromSCSC_2whereSC_2.S#=SC.S

14、#andSC_2.C#=01)orderbyStudent.S#- -方法 3selectm.*fromStudentmwhereS#in(selectS#from(selectdistinctS#fromSCwhereC#=01unionallselectdistinctS#fromSCwhereC#=02)tgroupbyS#havingcount(1)=2)orderbym.S#-10、查詢學過編號為01但是沒有學過編號為02”的課程的同學的信息- -方法 1selectStudent.*fromStudent,SCwhereStudent.S#=SC.S#andSC.C#=01andn

15、otexists(Select1fromSCSC_2whereSC_2.S#=SC.S#andSC_2.C#=02)orderbyStudent.S#- -方法 2selectStudent.*fromStudent,SCwhereStudent.S#=SC.S#andSC.C#=01andStudent.S#notin(SelectSC_2.S#fromSCSC_2whereSC_2.S#=SC.S#andSC_2.C#=02)orderbyStudent.S#-11、查詢沒有學全所有課程的同學的信息-11.1、selectStudent.*fromStudent,SCwhereStuden

16、t.S#=SC.S#groupbyStudent.S#,Student.Sname,Student.Sage,Student.Ssexhavingcount(C#)(selectcount(C#)fromCourse)-11.2selectStudent.*fromStudentleftjoinSConStudent.S#=SC.S#groupbyStudent.S#,Student.Sname,Student.Sage,Student.Ssexhavingcount(C#)(selectcount(C#)fromCourse)-12、查詢至少有一門課與學號為01的同學所學相同的同學的信息se

17、lectdistinctStudent.*fromStudent,SCwhereStudent.S#=SC.S#andSC.C#in(selectC#fromSCwhereS#=01)andStudent.S#01-13、查詢和01”號的同學學習的課程完全相同的其他同學的信息selectStudent.*fromStudentwhereS#in(selectdistinctSC.S#fromSCwhereS#01andSC.C#in(selectdistinctC#fromSCwhereS#=01)groupbySC.S#havingcount(1)=(selectcount(1)fromSC

18、whereS#=01)-14、查詢沒學過張三”老師講授的任一門課程的學生姓名selectstudent.*fromstudentwherestudent.S#notin(selectdistinctsc.S#fromsc,course,teacherwheresc.C#=course.C#andcourse.T#=teacher.T#andteacher.tname=N 張三)orderbystudent.S#- -15、查詢兩門及其以上不及格課程的同學的學號，姓名及其平均成績selectstudent.S#,student.sname,cast(avg(score)asdecimal(18,

19、2)avg_scorefromstudent,scwherestudent.S#=SC.S#andstudent.S#in(selectS#fromSCwherescore=2)groupbystudent.S#,student.sname-16、檢索01”課程分數(shù)小于 60,按分數(shù)降序排列的學生信息selectstudent.*,sc.C#,sc.scorefromstudent,scwherestudent.S#=SC.S#andsc.score=60,中等為：70-80,優(yōu)良為：80-90,優(yōu)秀為：=90- -方法 1selectm.C#課程編號,m.Cname課程名稱,max(n.sc

20、ore)最高分,min(n.score)最低分,cast(avg(n.score)asdecimal(18,2)平均分,cast(selectcount(1)fromSCwhereC#=m.C#andscore=60)*100.0/(selectcount(1)fromSCwhereC#=m.C#)asdecimal(18,2)及格率(%),cast(selectcount(1)fromSCwhereC#=m.C#andscore=70andscore=80andscore=90)*100.0/(selectcount(1)fromSCwhereC#=m.C#)asdecimal(18,2)優(yōu)

21、秀率(%)fromCoursem,SCnwherem.C#=n.C#groupbym.C#,m.Cnameorderbym.C#-方法 2selectm.C#課程編號,m.Cname課程名稱,(selectmax(score)fromSCwhereC#=m.C#)最高分,(selectmin(score)fromSCwhereC#=m.C#)最低分,(selectcast(avg(score)asdecimal(18,2)fromSCwhereC#=m.C#)平均分,cast(selectcount(1)fromSCwhereC#=m.C#andscore=60)*100.0/(selectc

22、ount(1)fromSCwhereC#=m.C#)asdecimal(18,2)及格率(%),cast(selectcount(1)fromSCwhereC#=m.C#andscore=70andscore=80andscore=90)*100.0/(selectcount(1)fromSCwhereC#=m.C#)asdecimal(18,2)優(yōu)秀率(%)fromCoursemorderbym.C#-19、按各科成績進行排序，并顯示排名-19.1sql2000 用子查詢完成-Score 重復時保留名次空缺selectt.*,px=(selectcount(1)fromSCwhereC#=t

23、.C#andscoret.score)+1fromsctorderbyt.c#,px-Score 重復時合并名次selectt.*,px=(selectcount(distinctscore)fromSCwhereC#=t.C#andscore=t.score)fromsctorderbyt.c#,px-19.2sql2005 用 rank,DENSE_RANK 完成-Score 重復時保留名次空缺(rank 完成)selectt.*,px=rank()over(partitionbyc#orderbyscoredesc)fromsctorderbyt.C#,px-Score 重復時合并名次(

24、DENSE_RANK 完成)selectt.*,px=DENSE_RANK()over(partitionbyc#orderbyscoredesc)fromsctorderbyt.C#,px-20、查詢學生的總成績并進行排名-20.1 查詢學生的總成績selectm.S#學生編號,m.Sname學生姓名,isnull(sum(score),0)總成績fromStudentmleftjoinSCnonm.S#=n.S#groupbym.S#,m.Snameorderby總成績desc-20.2 查詢學生的總成績并進行排名，sql2000 用子查詢完成，分總分重復時保留名次空缺和不保留名次空缺兩種

25、。selectt1.*,px=(selectcount(1)from(selectm.S#學生編號,m.Sname學生姓名,isnull(sum(score),0)總成績fromStudentmleftjoinSCnonm.S#=n.S#groupbym.S#,m.Sname)t2where 總成績t1.總成績)+1from(selectm.S#學生編號,m.Sname學生姓名,isnull(sum(score),0)總成績fromStudentmleftjoinSCnonm.S#=n.S#groupbym.S#,m.Sname)t1orderbypxselectt1.*,px=(select

26、count(distinct 總、成績)from(selectm.S#學生編號,m.Sname學生姓名,isnull(sum(score),0)總成績fromStudentmleftjoinSCnonm.S#=n.S#groupbym.S#,m.Sname)t2where 總成績=t1.總成績)from(selectm.S#學生編號,m.Sname學生姓名,isnull(sum(score),0)總成績fromStudentmleftjoinSCnonm.S#=n.S#groupbym.S#,m.Sname)t1orderbypx-20.3 查詢學生的總成績并進行排名， sql2005 用 r

27、ank,DENSE_RANK 完成，分總分重復時保留名次空缺和不保留名次空缺兩種。selectt.*,px=rank()over(orderby總、成績desc)from(selectm.S#學生編號,m.Sname學生姓名,isnull(sum(score),0)總成績fromStudentmleftjoinSCnonm.S#=n.S#groupbym.S#,m.Sname)torderbypxselectt.*,px=DENSE_RANK()over(orderby總成績desc)from(selectm.S#學生編號,m.Sname學生姓名,isnull(sum(score),0)總成績

28、fromStudentmleftjoinSCnonm.S#=n.S#groupbym.S#,m.Sname)torderbypx-21、查詢不同老師所教不同課程平均分從高到低顯示selectm.T#,m.Tname,cast(avg(o.score)asdecimal(18,2)avg_scorefromTeacherm,Coursen,SCowherem.T#=n.T#andn.C#=o.C#groupbym.T#,m.Tnameorderbyavg_scoredesc-22、查詢所有課程的成績第 2 名到第 3 名的學生信息及該課程成績-22.1sql2000 用子查詢完成-Score 重

29、復時保留名次空缺select*from(selectt.*,px=(selectcount(1)fromSCwhereC#=t.C#andscoret.score)+1fromsct)mwherepxbetween2and3orderbym.c#,m.px-Score 重復時合并名次select*from(selectt.*,px=(selectcount(distinctscore)fromSCwhereC#=t.C#andscore=t.score)fromsct)mwherepxbetween2and3orderbym.c#,m.px-22.2sql2005 用 rank,DENSE_R

30、ANK 完成-Score 重復時保留名次空缺(rank 完成)select*from(selectt.*,px=rank()over(partitionbyc#orderbyscoredesc)fromsct)mwherepxbetween2and3orderbym.C#,m.px-Score 重復時合并名次(DENSE_RANK 完成)select*from(selectt.*,px=DENSE_RANK()over(partitionbyc#orderbyscoredesc)fromsct)mwherepxbetween2and3orderbym.C#,m.px-23、統(tǒng)計各科成績各分數(shù)段

31、人數(shù)：課程編號，課程名稱,100-85,85-70,70-60,0-60及所占百分比-23.1 統(tǒng)計各科成績各分數(shù)段人數(shù)：課程編號，課程名稱,100-85,85-70,70-60,0-60- -橫向顯示selectCourse.C#課程編號,Cnameas課程名稱,sum(casewhenscore=85then1else0end)85-100,sum(casewhenscore=70andscore=60andscore70then1else0end)60-70,sum(casewhenscore=85then85-100whenn.score=70andn.score=60andn.sco

32、re=85then85-100whenn.score=70andn.score=60andn.score=85then85-100whenn.score=70andn.score=60andn.score=85then85-100whenn.score=70andn.score=60andn.score70then60-70else0-60end)orderbym.C#,m.Cname,分數(shù)段- -23.2 統(tǒng)計各科成績各分數(shù)段人數(shù)：課程編號，課程名稱,100-85,85-70,70-60,60及所占百分比- -橫向顯示selectm.C#課程編號，m.Cname 課程名稱，(selectco

33、unt(1)fromSCwhereC#=m.C#andscore60)0-60,cast(selectcount(1)fromSCwhereC#=m.C#andscore=60andscore=60andscore=70andscore=70andscore=85)85-100,cast(selectcount(1)fromSCwhereC#=m.C#andscore=85)*100.0/(selectcount(1)fromSCwhereC#=m.C#)asdecimal(18,2)百分比()fromCoursemorderbym.C#- -縱向顯示 1(顯示存在的分數(shù)段)selectm.C

34、#課程編號,m.Cname課程名稱,分數(shù)段=(casewhenn.score=85then85-100whenn.score=70andn.score=60andn.score=85then85-100whenn.score=70andn.score=60andn.score=85then85-100whenn.score=70andn.score=60andn.score=85then85-100whenn.score=70andn.score=60andn.scoret1.平均成績)+1from(selectm.S#學生編號,m.Sname學生姓名,isnull(cast(avg(scor

35、e)asdecimal(18,2),0)平均成績fromStudentmleftjoinSCnonm.S#=n.S#groupbym.S#,m.Sname)t1orderbypxselectt1.*,px=(selectcount(distinct 平均成績)from(selectm.S#學生編號,m.Sname學生姓名,isnull(cast(avg(score)asdecimal(18,2),0)平均成績fromStudentmleftjoinSCnonm.S#=n.S#groupbym.S#,m.Sname)t2where 平均成績=t1.平均成績)from(selectm.S#學生編號

36、,m.Sname學生姓名,isnull(cast(avg(score)asdecimal(18,2),0)平均成績fromStudentmleftjoinSCnonm.S#=n.S#groupbym.S#,m.Sname)t1orderbypx-24.2 查詢學生的平均成績并進行排名，sql2005 用 rank,DENSE_RANK 完成，分平均成績重復時保留名次空缺和不保留名次空缺兩種。selectt.*,px=rank()over(orderby平均成績desc)from(selectm.S#學生編號,m.Sname學生姓名,isnull(cast(avg(score)asdecimal

37、(18,2),0)平均成績fromStudentmleftjoinSCnonm.S#=n.S#groupbym.S#,m.Sname)torderbypxselectt.*,px=DENSE_RANK()over(orderby平均成績desc)from(selectm.S#學生編號,m.Sname學生姓名,isnull(cast(avg(score)asdecimal(18,2),0)平均成績fromStudentmleftjoinSCnonm.S#=n.S#groupbym.S#,m.Sname)torderbypx- -25、查詢各科成績前三名的記錄- -25.1 分數(shù)重復時保留名次空缺

38、selectm.*,n.C#,n.scorefromStudentm,SCnwherem.S#=n.S#andn.scorein(selecttop3scorefromscwhereC#=n.C#orderbyscoredesc)orderbyn.C#,n.scoredesc- -25.2 分數(shù)重復時不保留名次空缺，合并名次- -sql2000 用子查詢實現(xiàn)select*from(selectt.*,px=(selectcount(distinctscore)fromSCwhereC#=t.C#andscore=t.score)fromsct)mwherepxbetween1and3order

39、bym.c#,m.px- -sql2005 用 DENSE_RANK 實現(xiàn)select*from(selectt.*,px=DENSE_RANK()over(partitionbyc#orderbyscoredesc)fromsct)mwherepxbetween1and3orderbym.C#,m.px-26、查詢每門課程被選修的學生數(shù)selectc#,count(S#)學生數(shù)fromscgroupbyC#-27、查詢出只有兩門課程的全部學生的學號和姓名selectStudent.S#,Student.SnamefromStudent,SCwhereStudent.S#=SC.S#groupbyStudent.S#,Student.Snamehavingcount(SC.C#)=2orderbyStudent.S#-28、查詢男生、女生人數(shù)selectcount(Ssex)as 男生人數(shù) fromStudentwhereSsex=N男selectcount(Ssex)as 女生人數(shù) fromStude