說(shuō)明：以下五十個(gè)語(yǔ)句都按照測(cè)試數(shù)據(jù)進(jìn)行過(guò)測(cè)試食绿，最好每次只單獨(dú)運(yùn)行一個(gè)語(yǔ)句。
問(wèn)題及描述：
--1.學(xué)生表
Student(S#,Sname,Sage,Ssex) ? ?--S#學(xué)生編號(hào),Sname學(xué)生姓名,Sage出生年月,Ssex學(xué)生性別
--2.課程表
Course(C#,Cname,T#) ? ?--C# --課程編號(hào),Cname課程名稱(chēng),T#教師編號(hào)
--3.教師表
Teacher(T#,Tname) ? ? --T#教師編號(hào),Tname教師姓名
--4.成績(jī)表
SC(S#,C#,score) ? ? --S#學(xué)生編號(hào),C#課程編號(hào),score分?jǐn)?shù)
*/
--創(chuàng)建測(cè)試數(shù)據(jù)
create table Student(S# varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10))
insert into Student values('01', N'趙雷','1990-01-01', N'男')
insert into Student values('02', N'錢(qián)電','1990-12-21', N'男')
insert into Student values('03', N'孫風(fēng)','1990-05-20', N'男')
insert into Student values('04', N'李云','1990-08-06', N'男')
insert into Student values('05', N'周梅','1991-12-01', N'女')
insert into Student values('06', N'吳蘭','1992-03-01', N'女')
insert into Student values('07', N'鄭竹','1989-07-01', N'女')
insert into Student values('08', N'王菊','1990-01-20', N'女')
create table Course(C# varchar(10),Cname nvarchar(10),T# varchar(10))
insert into Course values('01', N'語(yǔ)文','02')
insert into Course values('02', N'數(shù)學(xué)','01')
insert into Course values('03', N'英語(yǔ)','03')
create table Teacher(T# varchar(10),Tname nvarchar(10))
insertintoTeachervalues('01', N'張三')
insertintoTeachervalues('02', N'李四')
insertintoTeachervalues('03', 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)
insertintoSCvalues('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)
insertintoSCvalues('06','03',34)
insertintoSCvalues('07','02',89)
insertintoSCvalues('07','03',98)
go
--1硫戈、查詢(xún)"01"課程比"02"課程成績(jī)高的學(xué)生的信息及課程分?jǐn)?shù)
--1.1、查詢(xún)同時(shí)存在"01"課程和"02"課程的情況
select a.*, b.score[課程'01'的分?jǐn)?shù)],c.score[課程'02'的分?jǐn)?shù)]from Student a , SC b , SC c
where a.S# = b.S# and a.S# = c.S# and b.C# = '01' and c.C# = '02' and b.score > c.score
--1.2、查詢(xún)同時(shí)存在"01"課程和"02"課程的情況和存在"01"課程但可能不存在"02"課程的情況(不存在時(shí)顯示為null)(以下存在相同內(nèi)容時(shí)不再解釋)
select a.*, b.score[課程"01"的分?jǐn)?shù)],c.score[課程"02"的分?jǐn)?shù)] from Student a
left join SC b on a.S# = b.S# and b.C# = '01'
left join SC c on a.S# = c.S# and c.C# = '02'
where b.score > isnull(c.score,0)
注：
LEFT JOIN 關(guān)鍵字從左表（table1）返回所有的行踩衩，即使右表（table2）中沒(méi)有匹配柴罐。如果右表中沒(méi)有匹配徽缚，則結(jié)果為 NULL。
對(duì)于left join丽蝎，不管on后面跟什么條件猎拨，左表的數(shù)據(jù)全部查出來(lái)膀藐，因此要想過(guò)濾需把條件放到where后面
對(duì)于inner join，滿(mǎn)足on后面的條件表的數(shù)據(jù)才能查出红省，可以起到過(guò)濾作用额各。也可以把條件放到where后 ? ? 面。
--2吧恃、查詢(xún)"01"課程比"02"課程成績(jī)低的學(xué)生的信息及課程分?jǐn)?shù)
--2.1虾啦、查詢(xún)同時(shí)存在"01"課程和"02"課程的情況
selecta.*, b.score[課程'01'的分?jǐn)?shù)],c.score[課程'02'的分?jǐn)?shù)]fromStudent a , SC b , SC c
wherea.S#=b.S#anda.S#=c.S#andb.C#='01'andc.C#='02'andb.score
--2.2、查詢(xún)同時(shí)存在"01"課程和"02"課程的情況和不存在"01"課程但存在"02"課程的情況
selecta.*, b.score[課程"01"的分?jǐn)?shù)],c.score[課程"02"的分?jǐn)?shù)]fromStudent a
leftjoinSC bona.S#=b.S#andb.C#='01'
leftjoinSC cona.S#=c.S#andc.C#='02'
whereisnull(b.score,0)
--3痕寓、查詢(xún)平均成績(jī)大于等于60分的同學(xué)的學(xué)生編號(hào)和學(xué)生姓名和平均成績(jī)
select a.S#, a.Sname,?cast?(avg(b.score) as decimal(18,2)) avg_score
from Student a , sc b
where a.S# = b.S#
group by a.S# , a.Sname
having cast(avg(b.score)as decimal(18,2))>=60
orderby a.S#
注：CAST函數(shù)用于將某種數(shù)據(jù)類(lèi)型的表達(dá)式顯式轉(zhuǎn)換為另一種數(shù)據(jù)類(lèi)型 ?(CAST (expression AS data_type))
? ??? ??group by:根據(jù)(by)一定的規(guī)則進(jìn)行分組(Group),它的作用是通過(guò)一定的規(guī)則將一個(gè)數(shù)據(jù)集劃分成若干個(gè)小的區(qū)域傲醉，然后針對(duì)若干個(gè)小區(qū)域進(jìn)行數(shù)據(jù)處理
? ??? ??聚合函數(shù)，例如SUM, COUNT, MAX, AVG等呻率。這些函數(shù)和其它函數(shù)的根本區(qū)別就是它們一般作用在多條記錄上硬毕。
????當(dāng)出現(xiàn)了 聚合函數(shù)的時(shí)候 一般情況下 我們就會(huì)用到group by來(lái)進(jìn)行分組，而且group by出現(xiàn)的字段一般都要在查詢(xún)中出現(xiàn)
--4礼仗、查詢(xún)平均成績(jī)小于60分的同學(xué)的學(xué)生編號(hào)和學(xué)生姓名和平均成績(jī)
--4.1吐咳、查詢(xún)?cè)趕c表存在成績(jī)的學(xué)生信息的SQL語(yǔ)句。
selecta.S# , a.Sname ,cast(avg(b.score)asdecimal(18,2)) avg_score
fromStudent a , sc b
wherea.S#=b.S#
groupbya.S# , a.Sname
havingcast(avg(b.score)asdecimal(18,2))<60
orderbya.S#
--4.2元践、查詢(xún)?cè)趕c表中不存在成績(jī)的學(xué)生信息的SQL語(yǔ)句韭脊。
selecta.S# , a.Sname ,isnull(cast(avg(b.score)asdecimal(18,2)),0) avg_score
fromStudent aleftjoinsc b
ona.S#=b.S#
groupbya.S# , a.Sname
havingisnull(cast(avg(b.score)asdecimal(18,2)),0)<60
orderbya.S#
--5、查詢(xún)所有同學(xué)的學(xué)生編號(hào)单旁、學(xué)生姓名沪羔、選課總數(shù)、所有課程的總成績(jī)
--5.1象浑、查詢(xún)所有有成績(jī)的SQL蔫饰。
selecta.S#[學(xué)生編號(hào)], a.Sname[學(xué)生姓名],count(b.C#)選課總數(shù),sum(score)[所有課程的總成績(jī)]
fromStudent a , SC b
wherea.S#=b.S#
groupbya.S#,a.Sname
orderbya.S#
--5.2、查詢(xún)所有(包括有成績(jī)和無(wú)成績(jī))的SQL融柬。
selecta.S#[學(xué)生編號(hào)], a.Sname[學(xué)生姓名],count(b.C#)選課總數(shù),sum(score)[所有課程的總成績(jī)]
fromStudent aleftjoinSC b
ona.S#=b.S#
groupbya.S#,a.Sname
orderbya.S#
--6死嗦、查詢(xún)"李"姓老師的數(shù)量
--方法1
selectcount(Tname)["李"姓老師的數(shù)量]fromTeacherwhereTnamelikeN'李%'
--方法2
selectcount(Tname)["李"姓老師的數(shù)量]fromTeacherwhereleft(Tname,1)=N'李'
/*
"李"姓老師的數(shù)量
-----------
1
*/
--7、查詢(xún)學(xué)過(guò)"張三"老師授課的同學(xué)的信息
selectdistinctStudent.*fromStudent , SC , Course , Teacher
whereStudent.S#=SC.S#andSC.C#=Course.C#andCourse.T#=Teacher.T#andTeacher.Tname=N'張三'
orderbyStudent.S#
--8粒氧、查詢(xún)沒(méi)學(xué)過(guò)"張三"老師授課的同學(xué)的信息
selectm.*fromStudent mwhereS#notin(selectdistinctSC.S#fromSC ,
Course , TeacherwhereSC.C#=Course.C#andCourse.T#=Teacher.T#andTeacher.Tname=N'張三')orderbym.S#
--9越除、查詢(xún)學(xué)過(guò)編號(hào)為"01"并且也學(xué)過(guò)編號(hào)為"02"的課程的同學(xué)的信息
--方法1
selectStudent.*fromStudent , SCwhereStudent.S#=SC.S#andSC.C#='01'andexists(Select1fromSC SC_2whereSC_2.S#=SC.S#andSC_2.C#='02')orderbyStudent.S#
--方法2
selectStudent.*fromStudent , SCwhereStudent.S#=SC.S#andSC.C#='02'andexists(Select1fromSC SC_2whereSC_2.S#=SC.S#andSC_2.C#='01')orderbyStudent.S#
--方法3
selectm.*fromStudent mwhereS#in
(
selectS#from
(
selectdistinctS#fromSCwhereC#='01'
unionall
selectdistinctS#fromSCwhereC#='02'
) tgroupbyS#havingcount(1)=2
)
orderbym.S#
--10、查詢(xún)學(xué)過(guò)編號(hào)為"01"但是沒(méi)有學(xué)過(guò)編號(hào)為"02"的課程的同學(xué)的信息
--方法1
selectStudent.*fromStudent , SCwhereStudent.S#=SC.S#andSC.C#='01'andnotexists(Select1fromSC SC_2whereSC_2.S#=SC.S#andSC_2.C#='02')orderbyStudent.S#
--方法2
selectStudent.*fromStudent , SCwhereStudent.S#=SC.S#andSC.C#='01'andStudent.S#notin(SelectSC_2.S#fromSC
SC_2whereSC_2.S#=SC.S#andSC_2.C#='02')orderbyStudent.S#
--11外盯、查詢(xún)沒(méi)有學(xué)全所有課程的同學(xué)的信息
--11.1摘盆、
selectStudent.*
fromStudent , SC
whereStudent.S#=SC.S#
groupbyStudent.S# , Student.Sname , Student.Sage ,
Student.Ssexhavingcount(C#)<(selectcount(C#)fromCourse)
--11.2
selectStudent.*
fromStudentleftjoinSC
onStudent.S#=SC.S#
groupbyStudent.S# , Student.Sname , Student.Sage ,
Student.Ssexhavingcount(C#)<(selectcount(C#)fromCourse)
--12、查詢(xún)至少有一門(mén)課與學(xué)號(hào)為"01"的同學(xué)所學(xué)相同的同學(xué)的信息
selectdistinctStudent.*fromStudent , SCwhereStudent.S#=SC.S#andSC.C#in(selectC#fromSCwhereS#='01')andStudent.S#<>'01'
--13饱苟、查詢(xún)和"01"號(hào)的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的信息
selectStudent.*fromStudentwhereS#in
(selectdistinctSC.S#fromSCwhereS#<>'01'andSC.C#in(selectdistinctC#fromSCwhereS#='01')
groupbySC.S#havingcount(1)=(selectcount(1)fromSCwhereS#='01'))
--14孩擂、查詢(xún)沒(méi)學(xué)過(guò)"張三"老師講授的任一門(mén)課程的學(xué)生姓名
selectstudent.*fromstudentwherestudent.S#notin
(selectdistinctsc.S#fromsc ,
course , teacherwheresc.C#=course.C#andcourse.T#=teacher.T#andteacher.tname=N'張三')
orderbystudent.S#
--15、查詢(xún)兩門(mén)及其以上不及格課程的同學(xué)的學(xué)號(hào)箱熬，姓名及其平均成績(jī)
selectstudent.S# , student.sname ,cast(avg(score)asdecimal(18,2)) avg_scorefromstudent , sc
wherestudent.S#=SC.S#andstudent.S#in(selectS#fromSCwherescore<60groupbyS#havingcount(1)>=2)
groupbystudent.S# , student.sname
--16类垦、檢索"01"課程分?jǐn)?shù)小于60狈邑，按分?jǐn)?shù)降序排列的學(xué)生信息
selectstudent.*, sc.C# , sc.scorefromstudent , sc
wherestudent.S#=SC.S#andsc.score<60andsc.C#='01'
orderbysc.scoredesc
--17、按平均成績(jī)從高到低顯示所有學(xué)生的所有課程的成績(jī)以及平均成績(jī)
--17.1 SQL 2000靜態(tài)
selecta.S#學(xué)生編號(hào), a.Sname學(xué)生姓名,
max(casec.CnamewhenN'語(yǔ)文'thenb.scoreelsenullend)[語(yǔ)文],
max(casec.CnamewhenN'數(shù)學(xué)'thenb.scoreelsenullend)[數(shù)學(xué)],
max(casec.CnamewhenN'英語(yǔ)'thenb.scoreelsenullend)[英語(yǔ)],
cast(avg(b.score)asdecimal(18,2))平均分
fromStudent a
leftjoinSC bona.S#=b.S#
leftjoinCourse conb.C#=c.C#
groupbya.S# , a.Sname
orderby平均分desc
--17.2 SQL 2000動(dòng)態(tài)
declare@sqlnvarchar(4000)
set@sql='select
a.S# '+N'學(xué)生編號(hào)'+'
, a.Sname '+N'學(xué)生姓名'
select@sql=@sql+',max(case c.Cname when
N'''+Cname+''' then b.score else null end) ['+Cname+']'
from(selectdistinctCnamefromCourse)ast
set@sql=@sql+' , cast(avg(b.score)
as decimal(18,2)) '+N'平均分'+'from Student a left join SC b on a.S# = b.S# left join Course c on b.C# = c.C#
group by a.S# , a.Sname order by '+N'平均分'+'
desc'
exec(@sql)
--17.3有關(guān)sql 2005的動(dòng)靜態(tài)寫(xiě)法參見(jiàn)我的文章《普通行列轉(zhuǎn)換(version 2.0)》或《普通行列轉(zhuǎn)換(version 3.0)》蚤认。
二米苹；
--18、查詢(xún)各科成績(jī)最高分砰琢、最低分和平均分：以如下形式顯示：課程ID蘸嘶，課程name，最高分陪汽，最低分训唱，平均分，及格率挚冤，中等率况增，優(yōu)良率，優(yōu)秀率
--及格為>=60你辣，中等為：70-80巡通，優(yōu)良為：80-90尘执，優(yōu)秀為：>=90
--方法1
selectm.C#[課程編號(hào)], m.Cname[課程名稱(chēng)],
max(n.score)[最高分],
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<80)*100.0/(selectcount(1)fromSCwhereC#=m.C#)asdecimal(18,2))[中等率(%)],
cast((selectcount(1)fromSCwhereC#=m.C#andscore>=80andscore<90)*100.0/(selectcount(1)fromSCwhereC#=m.C#)asdecimal(18,2))[優(yōu)良率(%)],
cast((selectcount(1)fromSCwhereC#=m.C#andscore>=90)*100.0/(selectcount(1)fromSCwhereC#=m.C#)asdecimal(18,2))[優(yōu)秀率(%)]
fromCourse m , SC n
wherem.C#=n.C#
groupbym.C# , m.Cname
orderbym.C#
--方法2
selectm.C#[課程編號(hào)], m.Cname[課程名稱(chēng)],
(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/(selectcount(1)fromSCwhereC#=m.C#)asdecimal(18,2))[及格率(%)],
cast((selectcount(1)fromSCwhereC#=m.C#andscore>=70andscore<80)*100.0/(selectcount(1)fromSCwhereC#=m.C#)asdecimal(18,2))[中等率(%)],
cast((selectcount(1)fromSCwhereC#=m.C#andscore>=80andscore<90)*100.0/(selectcount(1)fromSCwhereC#=m.C#)asdecimal(18,2))[優(yōu)良率(%)],
cast((selectcount(1)fromSCwhereC#=m.C#andscore>=90)*100.0/(selectcount(1)fromSCwhereC#=m.C#)asdecimal(18,2))[優(yōu)秀率(%)]
fromCourse m
orderbym.C#
--19舍哄、按各科成績(jī)進(jìn)行排序，并顯示排名
--19.1 sql 2000用子查詢(xún)完成
--Score重復(fù)時(shí)保留名次空缺
selectt.*, px=(selectcount(1)fromSCwhereC#=t.C#andscore>t.score)+1fromsc torderbyt.c# , px
--Score重復(fù)時(shí)合并名次
selectt.*, px=(selectcount(distinctscore)fromSCwhereC#=t.C#andscore>=t.score)fromsc torderbyt.c# , px
--19.2 sql 2005用rank,DENSE_RANK完成
--Score重復(fù)時(shí)保留名次空缺(rank完成)
selectt.*, px=rank()over(partitionbyc#orderbyscoredesc)fromsc torderbyt.C# , px
--Score重復(fù)時(shí)合并名次(DENSE_RANK完成)
selectt.*, px=DENSE_RANK(
三誊锭；
--24表悬、查詢(xún)學(xué)生平均成績(jī)及其名次
--24.1
查詢(xún)學(xué)生的平均成績(jī)并進(jìn)行排名，sql 2000用子查詢(xún)完成丧靡，分平均成績(jī)重復(fù)時(shí)保留名次空缺和不保留名次空缺兩種蟆沫。
select t1.* , px = (select count(1) from
(
select m.S# [
學(xué)生編號(hào)] ,
m.Sname [
學(xué)生姓名] ,
isnull(cast(avg(score) as decimal(18,2)),0) [
平均成績(jī)]
from Student m left join SC n on m.S# = n.S#
group by m.S# , m.Sname
) t2 where
平均成績(jī)> t1.平均成績(jī)) + 1 from
(
select m.S# [
學(xué)生編號(hào)] ,
m.Sname [
學(xué)生姓名] ,
isnull(cast(avg(score) as decimal(18,2)),0) [
平均成績(jī)]
from Student m left join SC n on m.S# = n.S#
group by m.S# , m.Sname
) t1
order by px
select t1.* , px = (select count(distinct
平均成績(jī)) from
(
select m.S# [
學(xué)生編號(hào)] ,
m.Sname [
學(xué)生姓名] ,
isnull(cast(avg(score) as decimal(18,2)),0) [
平均成績(jī)]
from Student m left join SC n on m.S# = n.S#
group by m.S# , m.Sname
) t2 where
平均成績(jī)>= t1.平均成績(jī)) from
(
select m.S# [
學(xué)生編號(hào)] ,
m.Sname [
學(xué)生姓名] ,
isnull(cast(avg(score) as decimal(18,2)),0) [
平均成績(jī)]
from Student m left join SC n on m.S# = n.S#
group by m.S# , m.Sname
) t1
order by px
--24.2
查詢(xún)學(xué)生的平均成績(jī)并進(jìn)行排名，sql 2005用rank,DENSE_RANK完成温治，分平均成績(jī)重復(fù)時(shí)保留名次空缺和不保留名次空缺兩種饭庞。
select t.* , px = rank() over(order by [
平均成績(jī)] desc) from
(
select m.S# [
學(xué)生編號(hào)] ,
m.Sname [
學(xué)生姓名] ,
isnull(cast(avg(score) as decimal(18,2)),0) [
平均成績(jī)]
from Student m left join SC n on m.S# = n.S#
group by m.S# , m.Sname
) t
order by px
select t.* , px = DENSE_RANK() over(order by [
平均成績(jī)] desc) from
(
select m.S# [
學(xué)生編號(hào)] ,
m.Sname [
學(xué)生姓名] ,
isnull(cast(avg(score) as decimal(18,2)),0) [
平均成績(jī)]
from Student m left join SC n on m.S# = n.S#
group by m.S# , m.Sname
) t
order by px
--25
、查詢(xún)各科成績(jī)前三名的記錄
--25.1
分?jǐn)?shù)重復(fù)時(shí)保留名次空缺
select m.* , n.C# , n.score from Student m, SC n where m.S# = n.S# and n.score in
(select top 3 score from sc where C# = n.C# order by score desc) order by n.C# , n.score desc
--25.2
分?jǐn)?shù)重復(fù)時(shí)不保留名次空缺熬荆，合并名次
--sql 2000
用子查詢(xún)實(shí)現(xiàn)
select * from (select t.* , px = (select count(distinct score) from SC where C# = t.C# and score >= t.score) from sc t) m where px between 1 and 3 order by m.c# , m.px
--sql 2005
用DENSE_RANK實(shí)現(xiàn)
select * from (select t.* , px = DENSE_RANK() over(partition by c# order by score desc) from sc t) m where px between 1 and 3 order by m.C# , m.px
--26
舟山、查詢(xún)每門(mén)課程被選修的學(xué)生數(shù)
select c# , count(S#)[
學(xué)生數(shù)] from sc group by C#
--27
、查詢(xún)出只有兩門(mén)課程的全部學(xué)生的學(xué)號(hào)和姓名
select Student.S# , Student.Sname
from Student , SC
where Student.S# = SC.S#
group by Student.S# , Student.Sname
having count(SC.C#) = 2
order by Student.S#
--28
卤恳、查詢(xún)男生累盗、女生人數(shù)
select count(Ssex) as
男生人數(shù)from Student where Ssex = N'男'
select count(Ssex) as
女生人數(shù)from Student where Ssex = N'女'
select sum(case when Ssex = N'
男' then 1 else 0 end) [男生人數(shù)],sum(case when Ssex = N'女' then 1 else 0 end) [女生人數(shù)] from student
select case when Ssex = N'
男' then N'男生人數(shù)' else N'女生人數(shù)' end [男女情況] , count(1) [人數(shù)] from student group by case when Ssex = N'男' then N'男生人數(shù)' else N'女生人數(shù)' end
--29
、查詢(xún)名字中含有"風(fēng)"字的學(xué)生信息
select * from student where sname like N'%
風(fēng)%'
select * from student where charindex(N'
風(fēng)' , sname) > 0
--30
突琳、查詢(xún)同名同性學(xué)生名單若债，并統(tǒng)計(jì)同名人數(shù)
select Sname [
學(xué)生姓名], count(*) [人數(shù)] from Student group by Sname having count(*) > 1
--31
、查詢(xún)1990年出生的學(xué)生名單(注：Student表中Sage列的類(lèi)型是datetime)
select * from Student where year(sage) = 1990
select * from Student where datediff(yy,sage,'1990-01-01') = 0
select * from Student where datepart(yy,sage) = 1990
select * from Student where convert(varchar(4),sage,120) = '1990'
--32
拆融、查詢(xún)每門(mén)課程的平均成績(jī)蠢琳，結(jié)果按平均成績(jī)降序排列啊终，平均成績(jī)相同時(shí)，按課程編號(hào)升序排列
select m.C# , m.Cname , cast(avg(n.score) as decimal(18,2)) avg_score
from Course m, SC n
where m.C# = n.C#
group by m.C# , m.Cname
order by avg_score desc, m.C# asc
--33
傲须、查詢(xún)平均成績(jī)大于等于85的所有學(xué)生的學(xué)號(hào)孕索、姓名和平均成績(jī)
select a.S# , a.Sname , cast(avg(b.score) as decimal(18,2)) avg_score
from Student a , sc b
where a.S# = b.S#
group by a.S# , a.Sname
having cast(avg(b.score) as decimal(18,2)) >= 85
order by a.S#
--34
、查詢(xún)課程名稱(chēng)為"數(shù)學(xué)"躏碳，且分?jǐn)?shù)低于60的學(xué)生姓名和分?jǐn)?shù)
select sname , score
from Student , SC , Course
where SC.S# = Student.S# and SC.C# = Course.C# and Course.Cname = N'
數(shù)學(xué)' and score< 60
--35
搞旭、查詢(xún)所有學(xué)生的課程及分?jǐn)?shù)情況；
select Student.* , Course.Cname , SC.C# , SC.score
from Student, SC , Course
where Student.S# = SC.S# and SC.C# = Course.C#
order by Student.S# , SC.C#
--36
菇绵、查詢(xún)?nèi)魏我婚T(mén)課程成績(jī)?cè)?0分以上的姓名肄渗、課程名稱(chēng)和分?jǐn)?shù)；
select Student.* , Course.Cname , SC.C# , SC.score
from Student, SC , Course
where Student.S# = SC.S# and SC.C# = Course.C# and SC.score >= 70
order by Student.S# , SC.C#
--37
咬最、查詢(xún)不及格的課程
select Student.* , Course.Cname , SC.C# , SC.score
from Student, SC , Course
where Student.S# = SC.S# and SC.C# = Course.C# and SC.score< 60
order by Student.S# , SC.C#
--38
翎嫡、查詢(xún)課程編號(hào)為01且課程成績(jī)?cè)?0分以上的學(xué)生的學(xué)號(hào)和姓名；
select Student.* , Course.Cname , SC.C# , SC.score
from Student, SC , Course
where Student.S# = SC.S# and SC.C# = Course.C# and SC.C# = '01' and SC.score >= 80
order by Student.S# , SC.C#
--39
永乌、求每門(mén)課程的學(xué)生人數(shù)
select Course.C# , Course.Cname , count(*) [
學(xué)生人數(shù)]
from Course , SC
where Course.C# = SC.C#
group by? Course.C# , Course.Cname
order by Course.C# , Course.Cname
--40
惑申、查詢(xún)選修"張三"老師所授課程的學(xué)生中，成績(jī)最高的學(xué)生信息及其成績(jī)
--40.1
當(dāng)最高分只有一個(gè)時(shí)
select top 1 Student.* , Course.Cname , SC.C# , SC.score
from Student, SC , Course , Teacher
where Student.S# = SC.S# and SC.C# = Course.C# and Course.T# = Teacher.T# and Teacher.Tname = N'
張三'
order by SC.score desc
--40.2
當(dāng)最高分出現(xiàn)多個(gè)時(shí)
select Student.* , Course.Cname , SC.C# , SC.score
from Student, SC , Course , Teacher
where Student.S# = SC.S# and SC.C# = Course.C# and Course.T# = Teacher.T# and Teacher.Tname = N'
張三' and
SC.score = (select max(SC.score) from SC , Course , Teacher where SC.C# = Course.C# and Course.T# = Teacher.T# and Teacher.Tname = N'
張三')
--41
翅雏、查詢(xún)不同課程成績(jī)相同的學(xué)生的學(xué)生編號(hào)圈驼、課程編號(hào)、學(xué)生成績(jī)
--
方法1
select m.* from SC m ,(select C# , score from SC group by C# , score having count(1) > 1) n
where m.C#= n.C# and m.score = n.score order by m.C# , m.score , m.S#
--
方法2
select m.* from SC m where exists (select 1 from (select C# , score from SC group by C# , score having count(1) > 1) n
where m.C#= n.C# and m.score = n.score) order by m.C# , m.score , m.S#
--42
望几、查詢(xún)每門(mén)功成績(jī)最好的前兩名
select t.* from sc t where score in (select top 2 score from sc where C# = T.C# order by score desc) order by t.C# , t.score desc
--43
绩脆、統(tǒng)計(jì)每門(mén)課程的學(xué)生選修人數(shù)（超過(guò)5人的課程才統(tǒng)計(jì)）。要求輸出課程號(hào)和選修人數(shù)橄抹，查詢(xún)結(jié)果按人數(shù)降序排列靴迫，若人數(shù)相同，按課程號(hào)升序排列
select Course.C# , Course.Cname , count(*) [
學(xué)生人數(shù)]
from Course , SC
where Course.C# = SC.C#
group by? Course.C# , Course.Cname
having count(*) >= 5
order by [
學(xué)生人數(shù)] desc , Course.C#
--44
楼誓、檢索至少選修兩門(mén)課程的學(xué)生學(xué)號(hào)
select student.S# , student.Sname
from student , SC
where student.S# = SC.S#
group by student.S# , student.Sname
having count(1) >= 2
order by student.S#
--45
玉锌、查詢(xún)選修了全部課程的學(xué)生信息
--
方法1根據(jù)數(shù)量來(lái)完成
select student.* from student where S# in
(select S# from sc group by S# having count(1) = (select count(1) from course))
--
方法2使用雙重否定來(lái)完成
select t.* from student t where t.S# not in
(
select distinct m.S# from
(
select S# , C# from student , course
) m where not exists (select 1 from sc n where n.S# = m.S# and n.C# = m.C#)
)
--
方法3使用雙重否定來(lái)完成
select t.* from student t where not exists(select 1 from
(
select distinct m.S# from
(
select S# , C# from student , course
) m where not exists (select 1 from sc n where n.S# = m.S# and n.C# = m.C#)
) k where k.S# = t.S#
)
--46
、查詢(xún)各學(xué)生的年齡--46.1只按照年份來(lái)算
select * , datediff(yy , sage , getdate()) [
年齡] from student
--46.2
按照出生日期來(lái)算疟羹，當(dāng)前月日<出生年月的月日則主守，年齡減一
select * , case when right(convert(varchar(10),getdate(),120),5)< right(convert(varchar(10),sage,120),5) then datediff(yy , sage , getdate()) - 1 else datediff(yy , sage , getdate()) end [
年齡] from student
--47
、查詢(xún)本周過(guò)生日的學(xué)生
select * from student where datediff(week,datename(yy,getdate()) + right(convert(varchar(10),sage,120),6),getdate()) = 0
--48
阁猜、查詢(xún)下周過(guò)生日的學(xué)生
select * from student where datediff(week,datename(yy,getdate()) + right(convert(varchar(10),sage,120),6),getdate()) = -1
--49
丸逸、查詢(xún)本月過(guò)生日的學(xué)生
select * from student where datediff(mm,datename(yy,getdate()) + right(convert(varchar(10),sage,120),6),getdate()) = 0
--50
、查詢(xún)下月過(guò)生日的學(xué)生
select * from student where datediff(mm,datename(yy,getdate()) + right(convert(varchar(10),sage,120),6),getdate()) = -1
drop table? Student,Course,Teacher,SC
