三表聯(lián)查經(jīng)典50題

1、一個(gè)題目涉及到的50個(gè)Sql語(yǔ)句-（下面表的結(jié)構(gòu)以給出，自己在數(shù)據(jù)庫(kù)中建立表并且添加相應(yīng)的數(shù)據(jù)，數(shù)據(jù)要全面些.其中Student表中,Sid為學(xué)生的ID）-表結(jié)構(gòu)-學(xué)生表 tbIStudent (編號(hào) Stu Id v 姓名 StuName、年齡 Stu Age x 性別 StuSex)-課程表tbICourse (課程編號(hào)Courseld、課程名稱CourseName、教師編號(hào)Teald)-成績(jī)表tbIScore (學(xué)生編號(hào)Stuld、課程編號(hào)Courseld、成績(jī)Score)-教師表tbITeacher (教師編號(hào)Teald、姓名TeaName) -1、查詢“ 001 課程比“ 002”課

2、程成績(jī) 高的所有學(xué)生的學(xué)號(hào)；Select Stu Id From tbIStude nt s1Where (Select Score From tbIScore t1 Where t1 .Stuld=s1 .stuld And t1 .Courseld=001 *) (Select Score From tbIScore t2 Where t2.Stuld=s1 .stuld And t2.Courseld=002)-2、查詢平均成績(jī)大于 60分的同學(xué)的學(xué)號(hào)和平均成績(jī)；Select Stuld,Avg(Score) as AvgScore From tbIScoreGroup By Stuld

3、Havi ng Avg(Score)60-3、查詢所有同學(xué)的學(xué)號(hào)、姓名、選課數(shù)、總成績(jī)；Select Stuld,StuName,SelCourses=(Select Cou nt(Courseld) From tbIScore t1 Where t1 .Stuld=s1 .Stuld), SumScore=(Select Sum(Score) From tbIScore t2 Where t2.Stuld=s1 .Stuld) From tbIStude nt s1-4、查詢姓“李”的老師的個(gè)數(shù)；Select Count(*) From tbITeacher Where TeaName li

4、ke 李 %-5、查詢沒(méi)學(xué)過(guò)“葉平”老師課的同學(xué)的學(xué)號(hào)、姓名；Select Stuld,StuName From tbIStude ntWhere Stuld Not In(Select StulD From tbIScore scInner Joi n tbICourse cu ON sc.Courseld=cu.CourseldInner Join tbITeacher tc ON cu.Teald=tc.TealdWhere tc.TeaName=,H+平)-6、查詢學(xué)過(guò)“001”并且也學(xué)過(guò)編號(hào)“ 002”課程的同學(xué)的學(xué)號(hào)、姓名；Select Stuld,StuName From tbI

5、Stude nt stWhere (Select Cou nt(*) From tbIScore si Where s1 .Stuld=st.Stuld And s1 .Courseld=001 )0And(Select Cou nt(*) From tbIScore s2 Where s2.Stuld=st.Stuld And s2.Courseld=002*)07、查詢學(xué)過(guò)“葉平”老師所教的所有課的同學(xué)的學(xué)號(hào)、姓名；Select Stuld,StuName From tbIStude nt st Where n ot exists(Select CourselD From tbICours

6、e cu Inner Join tbITeacher tc On cu.TealD=tc.TealDWhere tc.TeaName=n+平 And CourselD not in(Select CourselD From tbIScore Where StulD=st.StulD)8、查詢課程編號(hào)s的成績(jī)比課程編號(hào)u 001 ”課程低的所有同學(xué)的學(xué)號(hào)、姓名；Select Stuld,StuName From tbIStude nt siWhere (Select Score From tbIScore t1 Where t1 .Stuld=s1 .stuld And t1 .Courseld

7、=*001)(Select Score From tbIScore t2 Where t2.Stuld=s1 .stuld And t2.Courseld=,002,)9、查詢所有課程成績(jī)小于分的同學(xué)的學(xué)號(hào)、姓名；Select Stuld,StuName From tbIStude nt stWhere Stuld Not IN(Select Stuld From tbIScore sc Where st.Stuld=sc.Stuld And Score60)-10.查詢沒(méi)有學(xué)全所有課的同學(xué)的學(xué)號(hào)、姓名；Select Stuld,StuName From tbIStude nt stWhere

8、 (Select Cou nt(*) From tbIScore sc Where st.Stuld=sc.Stuld)(Select Cou nt(*) From tbICourse)-1K查詢至少有一門課與學(xué)號(hào)為u 1001 的同學(xué)所學(xué)相同的同學(xué)的學(xué)號(hào)和姓名；-運(yùn)用連接查詢Select Dist In ct st.Stuld,StuName From tbIStude nt stInner Join tbIScore sc ON st.Stuld=sc.StuldWhere sc.Courseld IN (Select Courseld From tbIScore Where Stuld=

9、1001)嵌套子查詢Select Stuld,StuName From tbIStude ntWhere Stuld In(Select Distinct Stuld From tbIScore Where Courseld In (Select Courseld From tbIScore Where Stuld/001)-12v查詢至少學(xué)過(guò)學(xué)號(hào)為“1001 n同學(xué)所有課程的其他同學(xué)學(xué)號(hào)和姓名；Select Stuld,StuName From tbIStude ntWhere Stuld In(Select Dist in ct Stuld From tbIScore Where Cour

10、seld Not In (Select Courseld From tbIScore Where Stuld=1001)-13.把“ SC表中“葉平”老師教的課的成績(jī)都更改為此課程的平均成績(jī)；(從子查詢中獲取父查詢中的表名，這樣也行？ ？ ？ ？)創(chuàng)建測(cè)試表Select * Into Sc From tbIScoregoUpdate Sc Set Score=(Select Avg(Score) From tbIScore si Where s1 .Courseld=sc.Courseld) Where Courseld IN(Select Courseld From tbICourse cs

11、 INNER JOIN tbITeacher tc ON cs.TealD=tc.TealD WHERE TeaName =平十平)14、查詢和u 1002”號(hào)的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)學(xué)號(hào)和姓名；Select StulD,StuName From tbIStude nt stWhere Stuld ,1002,AndNot Exists(Select * From tbIScore sc Where sc.Stuld=st.Stuld And Courseld Not In (Select Courseld From tbIScore Where Stuld=1002)AndNot

12、Exists(Select * From tbIScore Where Stuld=1002* And Courseld Not In (Select Courseld From tbIScore sc Where sc.Stuld=st.Stuld)15、刪除學(xué)習(xí)“葉平”老師課的SC表記錄；Delete From tbIScore Where Courseld IN(Select Courseld From tbICourse cs INNER JOIN tbITeacher tc ON cs.Teald=tc.Teald Where tc.TeaName=O+平)-16.向SC表中插入一些

13、記錄，這些記錄要求符合以下條件：沒(méi)有上過(guò)編號(hào)“003”課程的同學(xué)學(xué)號(hào)、“號(hào)課的平均成績(jī)；In sert Into tbIScore (Stuld,Courseld,Score)Select Stuld,002,(Select Avg(Score) From tbIScore Where Courseld=002,) From tbIScore WhereStuld Not In (Select Stuld From tbIScore Where Courseld=003*)-17.按平均成績(jī)從高到低顯示所有學(xué)生的“數(shù)據(jù)庫(kù)” 、“企業(yè)管理”、“英語(yǔ)”三門的課程成績(jī)， 按如下形式顯示：學(xué)生ID,數(shù)

14、據(jù)庫(kù)，企業(yè)管理，英語(yǔ),有效課程數(shù)，有效平均分Select Stuld，數(shù)據(jù)庫(kù)(Select Score From tbIScore sc Inner Join tbICourse cs Onsc.Courseld=cs.Courseld Where CourseName=數(shù)據(jù)庫(kù)And sc.StulD=st.Stuld)，企業(yè)管理=(Select Score From tbIScore sc Inner Join tbICourse cs Onsc.Courseld=cs.Courseld Where CourseName=企業(yè)管理And sc.StulD=st.Stuld)，英語(yǔ)=(Sele

15、ct Score From tbIScore sc Inner Join tbICourse cs On sc.Courseld=cs.CourseldWhere CourseName=英語(yǔ)And sc.StulD=st.Stuld)，有效課程數(shù)=(Select Cou nt(Score) From tbIScore sc Irin er Joi n tbICourse cs Onsc.Courseld=cs.Courseld Where (CourseName=數(shù)據(jù)庫(kù)or CourseName=企 業(yè)管理or CourseName=英 語(yǔ))And sc.StulD=st.Stuld)，有效平

16、均分=(Select Avg(Score) From tbIScore sc Inn er Joi n tbICourse cs Onsc.Courseld=cs.Courseld Where (CourseName=數(shù)據(jù)庫(kù)or CourseName=企 業(yè)管理or CourseName=英 語(yǔ))And sc.StulD=st.Stuld)From tbIStude nt stOrder by有效平均分Desc查詢各科成績(jī)最高和最低的分：以如下形式顯示：課程ID,最高分，最低分Select Courseld as 課程 ID,最高分=(Select Max(Score) From tbISco

17、re sc Wheresc.Courseld=cs.Courseld ),最低分=(Select Mi n(Score) From tbIScore sc Where sc.Courseld=cs.Courseld ) From tbICourse cs-19、按各科平均成績(jī)從低到高和及格率的百分?jǐn)?shù)從高到低順序(百分?jǐn)?shù)后如何格式化為兩位小數(shù)?)Select課程ID,平均分，及格率From(Select Courseld as 課程 ID,平均分=(Select Avg(Score) From tbIScore sc Where sc.Courseld=cs.Courseld ),及格率=Con

18、vert(varchar(10),(Select Count(*) From tbIScore sc Wheresc.Courseld=cs.Courseld And sc.Score=60)*10000/(Select Count(*) From tbIScore sc Where sc.Courseld=cs.Courseld)/100)+,%From tbIScore cs) as tmpGroup by課程ID,平均分，及格率Order by 平均分，Convert(float,substring(及格率,1,len(及格率)1) Desc-20v查詢?nèi)缦抡n程平均成績(jī)和及格率的百分?jǐn)?shù)(

19、用”1行”顯示):企業(yè)管理(001),馬克思(002),OO&UML ( 003),數(shù)據(jù)庫(kù)(004)Select 課程 ID=sc.Courseld,課程名稱=cs.CourseName,平均成績(jī)=Avg(Score)，及格率=Convert(varchar(10),(Select Count(Score) From tbIScore WhereCourseld=sc.Courseld And Score=60)*10000/Cou nt(Score)/100.0)+%From tbIScore scInner Joi n tbICourse cs ON sc.Courseld=cs.Cour

20、seldWhere sc.Courseld like *001234Group By sc.Courseld,cs.CourseName-21、查詢不同老師所教不同課程平均分從高到低顯示Select 課程 ID=Courseld,課程名稱=CourseName,授課教師=TeaName,平均成績(jī)=(SelectAvg(Score) From tbIScore Where Courseld=cs.Courseld)From tbICourse csInner Join tbITeacher tc ON cs.Teald=tc.TealdOrder by平均成績(jī)Desc-22x查詢?nèi)缦抡n程成績(jī)第3

21、名到第6名的學(xué)生成績(jī)單：企業(yè)管理(001),馬克思(002), UML(003),數(shù)據(jù)庫(kù)(004)格式：學(xué)生ID,學(xué)生姓名，企業(yè)管理，馬克思，UML,數(shù)據(jù)庫(kù)，平均成績(jī) Select * From(Select Top 6 學(xué)生 ID=Stuld,學(xué)生姓名=StuName，企業(yè)管理=(Select Score From tbIScore sc Inner Join tbICourse cs Onsc.Courseld=cs.Courseld Where CourseName=企業(yè)管理And sc.StulD=st.Stuld)，馬克思=(Select Score From tbIScore sc

22、 Inner Join tbICourse cs Onsc.Courseld=cs.Courseld Where CourseName馬克思And sc.StulD=st.Stuld),UML=(Select Score From tbIScore sc Inner Join tbICourse cs Onsc.Courseld=cs.Courseld Where CourseName=UML, A nd sc.StulD=st.Stuld)，數(shù)據(jù)庫(kù)=(Select Score From tbIScore sc Inner Join tbICourse cs Onsc.Courseld=cs.

23、Courseld Where CourseName=(據(jù)庫(kù)And sc.StulD=st.Stuld),平均成績(jī)=(Select Avg(Score) From tbIScore sc Inner Join tbICourse cs Onsc.Courseld=cs.Courseld Where (CourseName數(shù)據(jù)庫(kù)or CourseName企 業(yè)管理orCourseName=UMLor CourseName=馬克思)And sc.StulD=st.Stuld)，排名=Row_Number() Over(Order by(Select Avg(Score) From tbIScore

24、sc Inner Join tbICourse cs On sc.Courseld=cs.Courseld Where (CourseName=數(shù)據(jù)庫(kù)or CourseName=企業(yè)管理 or CourseName=,UML,or CourseName=馬克思)And sc.StulD=st.Stuld) DESC)From tbIStude nt stOrder by 排名)as tmpWhere 排名 between 3 And 6-23v統(tǒng)計(jì)列印各科成績(jī)，各分?jǐn)?shù)段人數(shù)：課程ID,課程名稱，10085,8570,7060,v60 Select課程 ID=Courseld,課程名稱=Cour

25、seName,100-85=(Select Count(*) From tbIScore sc Where Courseld=cs.Courseld And Score between 85 And 100),85-70=(Select Count(*) From tbIScore sc Where Courseld=cs.Courseld And Score between 70 And 84),70-60=(Select Count(*) From tbIScore sc Where Courseld=cs.Courseld And Score between 60 And 69),60=

26、(Select Cou nt(*) From tbIScore sc Where Courseld=cs.Courseld And Score =2-31 v 1981年出生的學(xué)生名單(注：Student表中Sage列的類型是datetime)Select * From tbIStude nt Where Year(Sage)=1981-32.查詢每門課程的平均成績(jī)，結(jié)果按平均成績(jī)升序排列，平均成績(jī)相同時(shí)，按課程號(hào)降序排列 Select 課程 ID=Courseld課 程名稱=CourseName,平 均成績(jī)=(Select Avg(Score) From tbIScore Where Cou

27、rseld=cs.Courseld)From tbICourse csOrder by 平均成績(jī),Courseld Desc-33.查詢平均成績(jī)大于 85的所有學(xué)生的學(xué)號(hào)、姓名和平均成績(jī)Select 學(xué)號(hào)=Stuld,姓名=StuName,平均成績(jī)=(Select Avg(Score) From tbIScore WhereStuld=st.Stuld) From tbIStude nt stWhere (Select Avg(Score) From tbIScore Where Stuld=st.Stuld)85-34x查詢課程名稱為“數(shù)據(jù)庫(kù)”,且分?jǐn)?shù)低于60的學(xué)生姓名和分?jǐn)?shù)Select 姓

28、名=StuName,分?jǐn)?shù)=Score From tbIScore scInner Join tbIStude nt st On sc.Stuld=st.StuldInner Join tbICourse cs On sc.Courseld=cs.CourseldWhere CourseName=數(shù)據(jù)庫(kù)And Score=70-37.查詢不及格的課程，并按課程號(hào)從大到小排列Select * From tbIScore Where Score=80)39、求選了課程的學(xué)生人數(shù)Select 選了課程的學(xué)生人數(shù)=Cou nt(*) From tbIStude nt st Where Stuld IN

29、(Select Stu ID From tbIScore)-40、查詢選修“葉平”老師所授課程的學(xué)生中，成績(jī)最高的學(xué)生姓名及其成績(jī)Select Courseld,CourseName,該科最高學(xué)生=(Select StuName From tbIStudent Where Stuld in (Select Top 1 Stu ID From tbIScore Where Courseld=cs.Courseld Order by Score Desc), 成績(jī)=(Select Top 1 Score From tbIScore Where Courseld=cs.Courseld Order

30、by Score Desc)From tbICourse cs Inner Join tbITeacher tc ON cs.Teald=tc.TealdWhere TeaName=0+平-41、查詢各個(gè)課程及相應(yīng)的選修人數(shù)Select 課程 ID=Courseld,選修人數(shù)=(Select Count(*) From (Select Distinct Stuld From tbIScore Where Courseld=cs.Courseld) as tmp)From tbICourse cs-42.查詢不同課程成績(jī)相同的學(xué)生的學(xué)號(hào)、課程號(hào)、學(xué)生成績(jī)Select 學(xué)號(hào)=Stuld,課程號(hào)=C

31、ourseld,成績(jī)=Score From tbIScore scWhere Exists (Select * From tbIScore Where Score=sc.Score And Stuld=sc.Stuld And Courseld osc.Courseld)Order by學(xué)號(hào)，成績(jī)-43、查詢每門功成績(jī)最好的前兩名Select 課程號(hào)=Courseld,第 1 名=(Select Top 1 Stuld From tbIScore Where Courseld=cs.Courseld Order by Score DESC),第 2 名=(Select Top 1 StulD

32、From (Select Top 2 Stuld,Score From tbIScore Where Courseld=cs.Courseld Order by Score DESC) as tmp Order by Score)From tbICourse cs-44、統(tǒng)計(jì)每門課程的學(xué)生選修人數(shù)(超過(guò)10人的課程才統(tǒng)計(jì))。要求輸出課程號(hào)和選修人數(shù)，查詢結(jié)果 按人數(shù)降序排列，若人數(shù)相同，按課程號(hào)升序排列Select 課程 ID=Courseld,選修人數(shù)=(Select Count(*) From (Select Distinct Stuld From tbIScore Where Cours

33、eld=cs.Courseld) as tmp)From tbICourse csWhere (Select Cou nt(*) From (Select Dist inct Stuld From tbIScore WhereCourseld=cs.Courseld) as tmp)=10Order by選修人數(shù)DESC,課程ID-45.檢索至少選修兩門課程的學(xué)生學(xué)號(hào)Select Stuld from tbIScore Group by Stuid having Cou nt(*)=2沒(méi)有重復(fù)課程數(shù)據(jù)時(shí)可用此方法有重復(fù)課程時(shí)用此方法(如補(bǔ)考)Select Stuld from tbIStude

34、 nt st Where(Select Cou nt(*) From (Select Dist inct Courseld From tbIScore Where Stuld=st.Stuld) astmp)=2建庫(kù)建表建約束，插入測(cè)試數(shù)據(jù)46、查詢?nèi)繉W(xué)生都選修的課程的課程號(hào)和課程名Select Courseld,CourseName From tbICourse csWhere Not Exists(Select * From tbIStudent Where Stuld Not In沒(méi) 學(xué)過(guò)本課程的學(xué)生是否存在(Select Stuld From tbIScore Where Cours

35、eld=cs.Courseld)-47、查詢沒(méi)學(xué)過(guò)5十平”老師講授的任一門課程的學(xué)生姓名Select Stuld,StuName From tbIStude ntWhere Stuld Not In(Select StulD From tbIScore scInner Joi n tbICourse cu ON sc.Courseld=cu.CourseldInner Join tbITeacher tc ON cu.Teald=tc.TealdWhere tc.TeaName=Ht平)-4&查詢兩門以上不及格課程的同學(xué)的學(xué)號(hào)及其平均成績(jī)Select Stu ID as 學(xué)號(hào),Avg( Sco

36、re) as 平均成績(jī) From tbIScore scWhere (Select Cou nt(J From tbIScore si Where s1 .Stuld=sc.Stuld And Score=2Group By Stuld-49、檢索“ 004 ”課程分?jǐn)?shù)小于60,按分?jǐn)?shù)降序排列的同學(xué)學(xué)號(hào)(ok)Select StulD,Score From tbIScore Where Courseld=004 A nd Score60 Order by Score Desc-50v刪除u 002”同學(xué)的“ 001 ”課程的成績(jī)Delete From SC Where Stuld=1002 A

37、 nd Courseld=001.SC為刪除數(shù)據(jù)臨時(shí)表Select * INTO SC From tbIScoreSelect * from sc Where stuld= 1018*In sert Sc(Stuid,courseld,Score) Select StulD,009,74 From tbIStude nt/it* ifUse mastergoif db_id(,MySchool,) is n ot null Drop Database MySchoolCreate Database MySchool goUse MySchool gocreate table tbIStude

38、ntStuld varchar(5) primary key,StuName n varchar(10) n ot null,StuAge int,StuSex nchar(1) not null)create table tbITeacher(Teald varchar(3) primary key,TeaName varchar(10) not n ull)create table tbICourse(Courseld varchar(3) primary key,CourseName n varchar(20) not n ull,Teald varchar(3) not n ull f

39、oreig n key ref ere nces tblTeacher(teald)create table tbIScore(Stu Id varchar(5) not n ull foreig n key ref ere nces tbIStude nt(stuld),Courseld varchar(3) not null foreig n key references tblCourse(Courseld), Score float)-表結(jié)構(gòu)-學(xué)生表 tbIStude nt (編號(hào) Stuld、姓名 Stu name、年齡 Stuage、性別 Stusex)課程表tbICourse (

40、課程編號(hào)Courseld、課程名稱CourseNamev教師編號(hào)Teald) 成績(jī)表tbIScore (學(xué)生編號(hào)Stuldx課程編號(hào)CourseldX成績(jī)Score)-教師表tbITeacher (教師編號(hào)Teald、姓名TeaName)-插入數(shù)據(jù) -in sert into tbIStude ntselect 1000,張無(wú)忌,18,男unionselect *1001*,周芷若;19,女unionselect *1002*,楊過(guò)19,男unionselect *1003*,趙敏：18,女unionselect 1004,小龍女,17,女unionselect1005,張三豐,18,男uni

41、onselect *1006T令狐沖19,Runionselect *1007*,任盈盈,20,女unionselect *1008*,岳靈珊9,女unionselect *1009T韋小寶8,男,unionselect *101 O,康敏；17,女unionselect 1011蕭峰,19,男unionselect *101 黃蓉8,女unionselect 1013,郭靖,19,男unionselect 1014,周伯通男unionselect *1015*；瑛姑：20,女unionselect*1016,李秋水,21女union selects 017丁黃藥師8,男union selec

42、t *1018,李莫愁8,女union select 1019,馮默風(fēng)男union select102073E重陽(yáng)：17,男union select *1021*,郭襄：18,女 goin sert into tbITeacherselect 001T 姚明union select 002：葉平union select 003,葉開(kāi),unio n select 004,孟星魂union select 005, 獨(dú)孤求敗union select 006,裘千仞union select 0077裘千尺union select *008,趙志敬union select *009, 阿紫union s

43、elect 010,W芙蓉union select *011,佟湘玉union select 012*,白展堂union select 013,呂輕 侯union select 014,*李大嘴union select 015*/花無(wú)缺union select 016*,金不換union select 017,*喬丹go in sert into tbICourseselect 001,企業(yè)管理,002 union select 002：馬克思,008 union select 003,UML7006 un io n select 004；數(shù)據(jù)庫(kù),007 union select 0057邏輯

44、電路,006 union select 006,英語(yǔ),003 un io n select 0077電 子電路：005 union select 008；毛澤東思想概論,004 union select 009,西方哲學(xué)史,012 union select 010,線性代數(shù)70171 union select *01V；計(jì)算機(jī)基礎(chǔ)7013* union select *012*,AUTO CAD 制圖7015* union select 013,平面設(shè)計(jì)*,011 union select *014,Flash 動(dòng)漫：001 union select 015Java 開(kāi)發(fā),009 union select *01670# 基礎(chǔ)7002* un io n select 0