--剛學習SQL?sever一周 ，網上好多都是?用的my sql ,絕大部分確實通用但是個別不一樣，在下使用的是SQL server 2019

47題沒找到最優(yōu)解，如有更好的方式?辛苦評論區(qū)?告知~

首先是這個題大家都用的表?

一、數(shù)據(jù)表

學生表 Student(SId,Sname,Sage,Ssex)–SId 學生編號,Sname 學生姓名,Sage 出生年月,Ssex 學生性別

復制以下內容；

create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));

insert into Student values('01' , '趙雷' , '1990-01-01' , '男');

insert into Student values('02' , '錢電' , '1990-12-21' , '男');

insert into Student values('03' , '孫風' , '1990-05-20' , '男');

insert into Student values('04' , '李云' , '1990-08-06' , '男');

insert into Student values('05' , '周梅' , '1991-12-01' , '女');

insert into Student values('06' , '吳蘭' , '1992-03-01' , '女');

insert into Student values('07' , '鄭竹' , '1989-07-01' , '女');

insert into Student values('09' , '張三' , '2017-12-20' , '女');

insert into Student values('10' , '李四' , '2017-12-25' , '女');

insert into Student values('11' , '李四' , '2017-12-30' , '女');

insert into Student values('12' , '趙六' , '2017-01-01' , '女');

insert into Student values('13' , '孫七' , '2018-01-01' , '女');

二、課程表 Course(CId,Cname,TId) – CId 課程編號,Cname 課程名稱,TId 教師編號

復制以下內容：

create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));

insert into Course values('01' , '語文' , '02');

insert into Course values('02' , '數(shù)學' , '01');

insert into Course values('03' , '英語' , '03');

三、教師表 Teacher(TId,Tname)–TId 教師編號,Tname 教師姓名

復制以下內容：

create table Teacher(TId varchar(10),Tname varchar(10));

insert into Teacher values('01' , '張三');

insert into Teacher values('02' , '李四');

insert into Teacher values('03' , '王五');

四、成績表 SC(SId,CId,score)–SId 學生編號,CId 課程編號,score 分數(shù)

復制以下內容：

create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));

insert into SC values('01' , '01' , 80);

insert into SC values('01' , '02' , 90);

insert into SC values('01' , '03' , 99);

insert into SC values('02' , '01' , 70);

insert into SC values('02' , '02' , 60);

insert into SC values('02' , '03' , 80);

insert into SC values('03' , '01' , 80);

insert into SC values('03' , '02' , 80);

insert into SC values('03' , '03' , 80);

insert into SC values('04' , '01' , 50);

insert into SC values('04' , '02' , 30);

insert into SC values('04' , '03' , 20);

insert into SC values('05' , '01' , 76);

insert into SC values('05' , '02' , 87);

insert into SC values('06' , '01' , 31);

insert into SC values('06' , '03' , 34);

insert into SC values('07' , '02' , 89);

insert into SC values('07' , '03' , 98);

然后是題目

1.?查詢" 01 “課程比” 02 “課程成績高的學生的信息及課程分數(shù)

2.?查詢同時存在” 01 “課程和” 02 “課程的情況

3.?查詢存在” 01 “課程但可能不存在” 02 “課程的情況(不存在時顯示為 null )

4.?查詢不存在” 01 “課程但存在” 02 "課程的情況

5.?查詢平均成績大于等于 60 分的同學的學生編號和學生姓名和平均成績

6.?查詢在 SC 表存在成績的學生信息

7.?查詢所有同學的學生編號、學生姓名、選課總數(shù)、所有課程的總成績(沒成績的顯示為 null )

8.?查詢「李」姓老師的數(shù)量

9.?查詢學過「張三」老師授課的同學的信息

10.?查詢沒有學全所有課程的同學的信息

11.?查詢至少有一門課與學號為" 01 "的同學所學相同的同學的信息

12.?查詢和" 01 "號的同學學習的課程 完全相同的其他同學的信息

13.?查詢沒學過"張三"老師講授的任一門課程的學生姓名

14.?查詢兩門及其以上不及格課程的同學的學號，姓名及其平均成績

15.?檢索" 01 "課程分數(shù)小于 60，按分數(shù)降序排列的學生信息

16.?按平均成績從高到低顯示所有學生的所有課程的成績以及平均成績

17.?查詢各科成績最高分、最低分和平均分

18.?按各科成績進行排序，并顯示排名， Score 重復時保留名次空缺

19.?按各科成績進行排序，并顯示排名， Score 重復時合并名次

20.?要求輸出課程號和選修人數(shù)，查詢結果按人數(shù)降序排列，若人數(shù)相同，按課程號升序排列

21.?查詢學生的總成績，并進行排名，總分重復時保留名次空缺

22.?查詢學生的總成績，并進行排名，總分重復時不保留名次空缺

23.?統(tǒng)計各科成績各分數(shù)段人數(shù)：課程編號，課程名稱，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

24.?查詢各科成績前三名的記錄

25.?查詢每門課程被選修的學生數(shù)

26.?查詢出只選修兩門課程的學生學號和姓名

27.?查詢男生、女生人數(shù)

28.?查詢名字中含有「風」字的學生信息

29.?查詢同名同性學生名單，并統(tǒng)計同名人數(shù)

30.?查詢 1990 年出生的學生名單

31.?查詢每門課程的平均成績，結果按平均成績降序排列，平均成績相同時，按課程編號升序排列

32.?查詢平均成績大于等于 85 的所有學生的學號、姓名和平均成績

33.?查詢課程名稱為「數(shù)學」，且分數(shù)低于 60 的學生姓名和分數(shù)

34.?查詢所有學生的課程及分數(shù)情況（存在學生沒成績，沒選課的情況）

35.?查詢任何一門課程成績在 70 分以上的姓名、課程名稱和分數(shù)

36.?查詢不及格的課程

37.?查詢課程編號為 01 且課程成績在 80 分及以上的學生的學號和姓名

38.?求每門課程的學生人數(shù)

39.?成績不重復，查詢選修「張三」老師所授課程的學生中，成績最高的學生信息及其成績

40.?查詢每門功成績最好的前兩名

41.?統(tǒng)計每門課程的學生選修人數(shù)（超過 5 人的課程才統(tǒng)計）。

42.?檢索至少選修兩門課程的學生學號

43.?查詢選修了全部課程的學生信息

44.?查詢各學生的年齡，只按年份來算

45.?按照出生日期來算，當前月日 < 出生年月的月日則，年齡減一

46.?查詢本周過生日的學生

47.?查詢下周過生日的學生

48.?查詢本月過生日的學生

49.?查詢下月過生日的學生

50.?以如下形式顯示：課程 ID，課程 name，最高分，最低分，平均分，及格率，中等率，優(yōu)良率，優(yōu)秀率 及格為>=60，中等為：70-80，優(yōu)良為：80-90，優(yōu)秀為：>=90 要求輸出課程號和選修人數(shù)，查詢結果按人數(shù)降序排列，若人數(shù)相同，按課程號升序排列

現(xiàn)在是答案?實測全對

--1.查詢" 01 “課程比” 02 “課程成績高的學生的信息及課程分數(shù)

? ? ?select s.sid,s.sage,s.sname,s.ssex,a.score as '01',b.score as '02'?

from student s ,

(select sid,score

? from sc

? where cid =01?

? )a,

? (select sid,score

? from sc

? where cid =02?

? )b

where? a.sid =b.sid and a.sid=s.sid and b.sid =s.sid and a.score > b.score

--2.查詢同時存在” 01 “課程和” 02 “課程的情況

select sc.sid from? sc ,

(select sid??

from sc

where? cid= 01)b

where sc.sid = b.sid? and cid=02

--3.查詢存在” 01 “課程但可能不存在” 02 “課程的情況(不存在時顯示為 null )

select * from?

(select *?

from sc?

where cid=01)a

left join

(select *?

from sc?

where cid=02)b

on? a.sid=b.sid

--4.查詢不存在” 01 “課程但存在” 02 "課程的情況

select * from?

(select *?

from sc?

where cid=02)a

left join

(select *?

from sc?

where cid=01)b

on? a.sid=b.sid

--5.查詢平均成績大于等于 60 分的同學的學生編號和學生姓名和平均成績

select? sc.sid,avg(sc.score)'平均成績',s.sname?

from student s , sc

where sc.sid =s.sid?

group by? sc.sid ,s.sname

having avg(sc.score) >60

--6.查詢在 sc 表存在成績的學生信息

select sc.sid ,s.sage,s.sname,s.ssex

from? sc, student? s

where? sc.sid = s.sid

group by sc.sid ,s.sage,s.sname,s.ssex

--7.查詢所有同學的學生編號、學生姓名、選課總數(shù)、所有課程的總成績(沒成績的顯示為 null )

select? s.* ,count (sc.score)from student s??

left join??

sc

on s.sid = sc.sid

group by? s.sid ,s.sage,s.sname,s.ssex

--8.查詢「李」姓老師的數(shù)量

select * ,count(tid)'數(shù)量'?

from? ?teacher

where? tname like '李%'

group by tid , tname

--9.查詢學過「張三」老師授課的同學的信息

? select *?

? from student?

? where sid in

(

select sid?

from sc?

where cid =

(

select cid?

from course?

where tid=

(select tid?

from teacher?

where tname = '張三'

)?

? )

? )

--10.查詢沒有學全所有課程的同學的信息

select * from student where? sid not in(select sid from sc )

--11.查詢至少有一門課與學號為" 01 "的同學所學相同的同學的信息

select * from student where? sid in (select sid from sc where cid in (select cid from sc where sid=01))

--12.查詢和" 01 "號的同學學習的課程 完全相同的其他同學的信息

select? st.*?

from student st?

inner join?

(

select sc.sid,count(distinct sc.cid)as'1'?

from sc?

left join

(

select cid?

from sc?

where sid=01

)a

on a.cid=sc.cid

group by sc.sid?

? ? ? ? ?having? count (distinct sc.cid) = (select count(distinct sc.cid)from sc where sid = 01)

)b

? on st.sid = b.sid

where? b.sid != 01

--13.查詢沒學過"張三"老師講授的任一門課程的學生姓名

select sname?

from student?

where sid not in

(

select sid?

from sc?

where cid in

(

select cid?

from course

where tid=

(

select tid?

from? teacher?

where tname= '張三'

)

? )

? )

--14.查詢兩門及其以上不及格課程的同學的學號，姓名及其平均成績

select st.sid as'學號',a.平均成績,st.sname

from? ?student st,

(

select sid ,count(score)'1',avg(score)'平均成績'?

from sc?

where? score<60?

group by sid?

having count(score)>=2? ?

)a

where a.sid = st.sid

--15.檢索" 01 "課程分數(shù)小于 60，按分數(shù)降序排列的學生信息

select st.* from student st , (select sid? from sc where cid=01 and score <60 )a

where st.sid =a.sid

--16.按平均成績從高到低顯示所有學生的所有課程的成績以及平均成績

select? sid,sum(score)'所有課程的成績',avg(score)'平均成績'?

from sc

group by sid

order by avg(score) desc

--17.查詢各科成績最高分、最低分和平均分

select? cid, max(score)'最高分',min(score)'最低分' ,avg(score)'平均成績'?

from sc

group by cid

--18.按各科成績進行排序，并顯示排名， score 重復時保留名次空缺

select? sid,cid,dense_rank()over (order by score) from sc where cid=01

union all

select? sid,cid,dense_rank()over (order by score) from sc where cid=02

union all

select? sid,cid,dense_rank()over (order by score) from sc where cid=03

--19.按各科成績進行排序，并顯示排名， score 重復時合并名次

select? sid,cid,rank()over (order by score) from sc where cid=01

union all

select? sid,cid,rank()over (order by score) from sc where cid=02

union all

select? sid,cid,rank()over (order by score) from sc where cid=03

--20.要求輸出課程號和選修人數(shù)，查詢結果按人數(shù)降序排列，若人數(shù)相同，按課程號升序排列

select cid, count(sid)'選修人數(shù)'?

from sc

group by? cid

order by cid? asc

--21.查詢學生的總成績，并進行排名，總分重復時保留名次空缺

select sid ,dense_rank()over(order by sum(score)desc)'排名',sum(score)'總成績'from sc

group by sid?

order by sum(score) desc

--22.查詢學生的總成績，并進行排名，總分重復時不保留名次空缺

select sid ,rank()over(order by sum(score)desc)'排名',sum(score)'總成績'from sc

group by sid?

order by sum(score) desc

--23.統(tǒng)計各科成績各分數(shù)段人數(shù)：課程編號，課程名稱，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

select sc.cid,c.cname,

sum(case when sc.score>=85 and score <=100then 1 else 0 end)*100/count(sc.sid)as'[100-85]',

sum(case when sc.score>=70 and sc.score<85 then 1 else 0 end)*100/count(sc.sid)as'[85-70]' ,

sum(case when sc.score>=60 and sc.score<70 then 1 else 0 end)*100/count(sc.sid) as'[70-60]',

sum(case when sc.score<60 then 1 else 0 end)*100/count(sc.sid)as'[60-0]'

from sc ,course c

where sc.cid= c.cid

group by sc.cid , c.cname

--24.查詢各科成績前三名的記錄

select sid ,cid ,rank1

from(select cid ,sid,rank()over(partition? by cid order by score desc)as rank1 from? sc)a

where rank1<=3? ?

--25.查詢每門課程被選修的學生數(shù)

? ?

? ? ? ? ? ? ? ? ??

? select? cid, count(sid)

? from sc?

? group by cid?

--26.查詢出只選修兩門課程的學生學號和姓名

select sc.sid ,count(sc.cid)'課程數(shù)量',st.sname from sc ,student st

where st.sid = sc.sid

group by? sc.sid ,st.sname

having? count(sc.cid)=2

--27.查詢男生、女生人數(shù)

select a.*,? b.* from?

(select count(ssex)'男'from student where ssex='男')a,

(select count(ssex)'女'from student where ssex='女')b

--28.查詢名字中含有「風」字的學生信息

select *?

from student?

where sname like '%風%'

--29.查詢同名同性學生名單，并統(tǒng)計同名人數(shù)

select sname ,count(sname)

from student

group by sname

having count(sname)>1

--30.查詢 1990 年出生的學生名單

select *?

from student

where? sage between'1990-01-31'and '1990-12-31'

--31.查詢每門課程的平均成績，結果按平均成績降序排列，平均成績相同時，按課程編號升序排列

select cid,avg(score)'平均成績'

from sc

group by cid?

order by avg(score) desc

--32.查詢平均成績大于等于 85 的所有學生的學號、姓名和平均成績

select sc.sid,sname,avg(score)'平均成績'

from student st,sc

where st.sid=sc.sid

group by sc.sid ,sname

having avg(score)>=85

--33.查詢課程名稱為「數(shù)學」，且分數(shù)低于 60 的學生姓名和分數(shù)

select sc.sid,sname? from sc ,student st

where sc.sid= st.sid and cid=(select cid from course where cname='數(shù)學') and score<60

--34.查詢所有學生的課程及分數(shù)情況（存在學生沒成績，沒選課的情況）

select * from student st

left join

sc

on st.sid=sc.sid

--35.查詢任何一門課程成績在 70 分以上的姓名、課程名稱和分數(shù)

select sc.* ,st.sname??

from sc,student st?

where sc.sid = st.sid and score>70

--36.查詢不及格的課程

select *??

from sc?

where score<60

--37.查詢課程編號為 01 且課程成績在 80 分及以上的學生的學號和姓名

select? *?

from sc??

where cid='01' and score >=80

--38.求每門課程的學生人數(shù)

select? cid ,count(sid )

from? sc??

group by cid

--39.成績不重復，查詢選修「張三」老師所授課程的學生中，成績最高的學生信息及其成績

select st.*,a.*?

from student st,

(

select top 1? sid,max(score)as '最高分'

from sc where cid=

(

select cid?

from course?

where tid=

(

select tid?

from teacher?

where tname='張三'

)

)

group by sid

order by max(score) desc )a

? where a.sid= st.sid

--40.查詢每門功成績最好的前兩名

? select *

? from

(

select cid,score, DENSE_RANK()over ( partition by cid order by score desc)'a'?

from sc

group by cid ,score

)a

? where a<=2

--41.統(tǒng)計每門課程的學生選修人數(shù)（超過 5 人的課程才統(tǒng)計）。

select? cid ,count(sid)'選修人數(shù)'

from sc?

group by cid

having count(sid)>5

--42.檢索至少選修兩門課程的學生學號

select sid,count(cid)

from sc

group by sid

having? count(cid)=2

--43.查詢選修了全部課程的學生信息

select sid, count(distinct cid) from sc

group by sid

having? count(distinct cid)=(

select top 1 count(distinct cid)'a'

from sc

group by sid)

--44.查詢各學生的年齡，只按年份來算

select? sid,datediff(yy,sage,getdate())

from student

--45.按照出生日期來算，當前月日 < 出生年月的月日則，年齡減一

select sid,floor(datediff(dd,sage,getdate())/365)

from student

--46.查詢本周過生日的學生

select * from student where datepart(week, sage) = datepart(week, getdate());

--47.查詢下周過生日的學生

select *?

from student?

where datediff(week,datename(yy,getdate()) + right(convert(varchar(10),sage,120),6),getdate()) = -1

--48.查詢本月過生日的學生

select *

from student?

where month (student.sage) = month(getdate())

--49.查詢下月過生日的學生

select *

from student?

where month (student.sage) = month(getdate())+1;

--50.以如下形式顯示：課程 ID，課程 name，最高分，最低分，平均分，及格率，中等率，優(yōu)良率，

--優(yōu)秀率 及格為>=60，中等為：70-80，優(yōu)良為：80-90，優(yōu)秀為：>=90 要求輸出課程號和選修人數(shù)，查詢結果按人數(shù)降序排列，若人數(shù)相同，按課程號升序排列

select sc.cid,c.cname,max(sc.score)'最高分',min(sc.score)'最低值',avg(sc.score)'平均分',

sum(case when score>=60 then 1? else 0 end)*100/count(sc.score)'及格率',

sum(case when score between 70 and 80 then 1? else 0 end)*100/count(sc.score)'中等率',

sum(case when score between 80 and 90 then 1? else 0 end)*100/count(sc.score)'優(yōu)良率',

sum(case when score>=90 then 1? else 0 end)*100/count(sc.score)'優(yōu)秀率'

from sc join course c

on sc.cid = c.cid

group by sc.cid ,c.cname