目錄

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

二、練習(xí)題

1.?? ?查詢" 01 "課程比" 02 "課程成績(jī)高的學(xué)生的信息及課程分?jǐn)?shù)

2.?? ?查詢同時(shí)存在" 01 "課程和" 02 "課程的情況

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

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

5.?? ?查詢平均成績(jī)大于等于 60 分的同學(xué)的學(xué)生編號(hào)和學(xué)生姓名和平均成績(jī)

6.?? ?查詢?cè)?SC 表存在成績(jī)的學(xué)生信息

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

8.?? ?查有成績(jī)的學(xué)生信息

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

10.?? ?查詢學(xué)過「張三」老師授課的同學(xué)的信息

11.?? ?查詢沒有學(xué)全所有課程的同學(xué)的信息

12.?? ?查詢至少有一門課與學(xué)號(hào)為" 01 "的同學(xué)所學(xué)相同的同學(xué)的信

13.?? ?查詢和" 01 "號(hào)的同學(xué)學(xué)習(xí)的課程 完全相同的其他同學(xué)的信息

14.?? ?查詢沒學(xué)過"張三"老師講授的任一門課程的學(xué)生姓名

15.?? ?查詢兩門及其以上不及格課程的同學(xué)的學(xué)號(hào)，姓名及其平均成績(jī)

16.?? ?檢索" 01 "課程分?jǐn)?shù)小于 60，按分?jǐn)?shù)降序排列的學(xué)生信息

17.?? ?按平均成績(jī)從高到低顯示所有學(xué)生的所有課程的成績(jī)以及平均成績(jī)

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

19.?? ?按各科成績(jī)進(jìn)行排序，并顯示排名， Score 重復(fù)時(shí)保留名次空缺

20. 按各科成績(jī)進(jìn)行排序，并顯示排名， Score 重復(fù)時(shí)合并名次

21.?? ?查詢學(xué)生的總成績(jī)，并進(jìn)行排名，總分重復(fù)時(shí)保留名次空缺

22. 查詢學(xué)生的總成績(jī)，并進(jìn)行排名，總分重復(fù)時(shí)不保留名次空缺

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

24.?? ?查詢各科成績(jī)前三名的記錄

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

26.?? ?查詢出只選修兩門課程的學(xué)生學(xué)號(hào)和姓名

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

28.?? ?查詢名字中含有「風(fēng)」字的學(xué)生信息

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

30.?? ?查詢 1990 年出生的學(xué)生名單

31.?? ?查詢每門課程的平均成績(jī)，結(jié)果按平均成績(jī)降序排列，平均成績(jī)相同時(shí)，按課程編號(hào)升序排列

32.?? ?查詢平均成績(jī)大于等于 85 的所有學(xué)生的學(xué)號(hào)、姓名和平均成績(jī)

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

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

35.?? ?查詢?nèi)魏我婚T課程成績(jī)?cè)?70 分以上的姓名、課程名稱和分?jǐn)?shù)

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

37.?? ?查詢課程編號(hào)為 01 且課程成績(jī)?cè)?80 分以上的學(xué)生的學(xué)號(hào)和姓名

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

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

40.?? ?成績(jī)有重復(fù)的情況下，查詢選修「張三」老師所授課程的學(xué)生中，成績(jī)最高的學(xué)生信息及其成績(jī)

41.?? ?查詢不同課程成績(jī)相同的學(xué)生的學(xué)生編號(hào)、課程編號(hào)、學(xué)生成績(jī)

42.?? ?查詢每門功成績(jī)最好的前兩名

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

44.?? ?檢索至少選修兩門課程的學(xué)生學(xué)號(hào)

45.?? ?查詢選修了全部課程的學(xué)生信息

46.?? ?查詢各學(xué)生的年齡，只按年份來算

47.?? ?按照出生日期來算，當(dāng)前月日 < 出生年月的月日則，年齡減一

48.?? ?查詢本周過生日的學(xué)生

49.?? ?查詢下周過生日的學(xué)生

50.?? ?查詢本月過生日的學(xué)生

51.?? ?查詢下月過生日的學(xué)生

---

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

1.學(xué)生表 Student(SId,Sname,Sage,Ssex) --SId 學(xué)生編號(hào),Sname 學(xué)生姓名,Sage 出生年月,Ssex 學(xué)生性別

2.課程表 Course(CId,Cname,TId) --CId 課程編號(hào),Cname 課程名稱,TId 教師編號(hào)

3.教師表 Teacher(TId,Tname) --TId 教師編號(hào),Tname 教師姓名

4.成績(jī)表 SC(SId,CId,score) --SId 學(xué)生編號(hào),CId 課程編號(hào),score 分?jǐn)?shù)

學(xué)生表 Student
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' , '孫風(fēng)' , '1990-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吳蘭' , '1992-01-01' , '女');
insert into Student values('07' , '鄭竹' , '1989-01-01' , '女');
insert into Student values('09' , '張三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2012-06-06' , '女');
insert into Student values('12' , '趙六' , '2013-06-13' , '女');
insert into Student values('13' , '孫七' , '2014-06-01' , '女');科目表 Course
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10)
);
insert into Course values('01' , '語文' , '02');
insert into Course values('02' , '數(shù)學(xué)' , '01');
insert into Course values('03' , '英語' , '03');教師表 Teacher
create table Teacher(TId varchar(10),Tname varchar(10)
);
insert into Teacher values('01' , '張三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');成績(jī)表 SC
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);

二、練習(xí)題

1.?? ?查詢" 01 "課程比" 02 "課程成績(jī)高的學(xué)生的信息及課程分?jǐn)?shù)

select s.*, sc1.score, sc2.score
from student s
join SC sc1
on s.SId = sc1.SId
join SC sc2
on sc1.SId = sc2.CId and sc1.CId = "01" and sc2.CId = "02"
where sc1.score > sc2.score;

2.?? ?查詢同時(shí)存在" 01 "課程和" 02 "課程的情況

select s1.SId, s1.score,s2.score
from  (select SId, score from SC where CId ="01") s1, 
(select SId, score from SC where CId ="02") s2 
where s1.SId = s2.SId;

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

select * 
from SC a
left join SC b
on a.SId = b.SId and b.CId = "02"
where a.CId = "01";

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

select *
from (select * from SC s where s.CId = "02") as a
where a.SId not in (select SId from SC s where s.CId = "01");

5.?? ?查詢平均成績(jī)大于等于 60 分的同學(xué)的學(xué)生編號(hào)和學(xué)生姓名和平均成績(jī)

select SC.SId, s.Sname, AVG(score)
from student s, SC
where s.SId = SC.SId
group by SC.SId
having AVG(score) >= 60;

6.?? ?查詢?cè)?SC 表存在成績(jī)的學(xué)生信息

select * 
from student
where student.SId in (select SId from SC);

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

select s.SId, s.Sname, b.cnt, b.sum_s
from student s
left join (select SId, count(CId) as cnt, sum(score) as sum_sfrom SC group by SId) as b
on s.SId = b.SId;

8.?? ?查有成績(jī)的學(xué)生信息

select *
from student
where SId in (select SId from SC group by SId);

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

select count(*)
from teacher
where Tname like '李%';

10.?? ?查詢學(xué)過「張三」老師授課的同學(xué)的信息

select s.*, Tname
from student s
inner join(select SC.*, Tnamefrom SCinner join(select course.*, Tnamefrom courseinner join teacher as thon course.TId = th.TId) as aon SC.CId = a.CId) as b
on s.SId = b.SId
where Tname = '張三';

11.?? ?查詢沒有學(xué)全所有課程的同學(xué)的信息

select s.*
from student s
inner join(select SId, count(CId) as cntfrom SCgroup by SIdhaving cnt < (select count(CId) from course)) as a
on s.SId = a.SId;

12.?? ?查詢至少有一門課與學(xué)號(hào)為" 01 "的同學(xué)所學(xué)相同的同學(xué)的信

select distinct s.*
from student s
inner join (select *from SCwhere CId in(select CIdfrom SCwhere SId = "01")) as b
on s.SId = b.SId;

13.?? ?查詢和" 01 "號(hào)的同學(xué)學(xué)習(xí)的課程 完全相同的其他同學(xué)的信息

select s.*
from student s
inner join (select SIdfrom SCwhere SId not in (select SIdfrom SCwhere CId not in (select CIdfrom SCwhere SId = "01")) and SId != "01"group by SIdhaving count(CId) = (select count(CId) from SC where SId = "01")) as a
on s.SId = a.SId;

14.?? ?查詢沒學(xué)過"張三"老師講授的任一門課程的學(xué)生姓名

select Sname
from student s
where SId not in (select SIdfrom SCleft join course couon SC.CId = cou.CIdinner join teacher thon cou.TId = th.TIdwhere Tname = "張三");

15.?? ?查詢兩門及其以上不及格課程的同學(xué)的學(xué)號(hào)，姓名及其平均成績(jī)

select b.SId, s.Sname, avg_sc
from (select SC.SId, AVG(score) as avg_scfrom SCinner join(select SIdfrom SCwhere score < 60group by SIdhaving count(CId) >= 2) as aon SC.SId = a.SIdgroup by SC.SId) as b
left join student s
on b.SId = s.SId;

16.?? ?檢索" 01 "課程分?jǐn)?shù)小于 60，按分?jǐn)?shù)降序排列的學(xué)生信息

select s.*, a.score
from student s
right join(select SId, scorefrom SCwhere CId = "01" and score <60) as a
on s.SId = a.SId
order by a.score desc;

17.?? ?按平均成績(jī)從高到低顯示所有學(xué)生的所有課程的成績(jī)以及平均成績(jī)

select a.SId, a.CId, a.Score, avg_sc
from (select s.SId, SC.CId, SC.Scorefrom student sleft join SCon s.SId = SC.SId) as a
left join (select SId, AVG(score) as avg_scfrom SCgroup by SId) as b
on a.SId = b.SId
order by b.avg_sc desc;

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

select a.*, b.Cname
from(select CId,count(*) as 選修人數(shù),max(score) as 最高分,min(score) as 最低分,avg(score) as 平均分,sum(case when score >= 60 then 1 else 0 end) / count(*) as 及格率,sum(case when score >= 70 and score < 80 then 1 else 0 end) / count(*) as 中等率,sum(case when score >= 80 and score < 90 then 1 else 0 end) / count(*) as 優(yōu)良率,sum(case when score >= 90 then 1 else 0 end) / count(*) as 優(yōu)秀率from SCgroup by CIdorder by count(*) desc, CId asc) as a
left join course as b
on a.CId = b.CId;

19.?? ?按各科成績(jī)進(jìn)行排序，并顯示排名， Score 重復(fù)時(shí)保留名次空缺

select *, row_number() 
over (partition by CId order by score desc) as 排名
from SC;

20. 按各科成績(jī)進(jìn)行排序，并顯示排名， Score 重復(fù)時(shí)合并名次

select *, rank()
over (partition by CId order by score desc) as 排名
from SC;

21.?? ?查詢學(xué)生的總成績(jī)，并進(jìn)行排名，總分重復(fù)時(shí)保留名次空缺

select *, rank()
over (order by sum_s desc) as 排名
from (select SId, sum(score) as sum_sfrom SCgroup by SId) as a;

22. 查詢學(xué)生的總成績(jī)，并進(jìn)行排名，總分重復(fù)時(shí)不保留名次空缺

select *,dense_rank()
over (order by sum_sc desc) as 排名
from(select SId, sum(score) as sum_scfrom SCgroup by SId) as a;

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

select b.*, a.Cname
from course as a
right join(select CId,concat(sum(case when score > 0 and score < 60 then 1 else 0 end) / count(*) * 100, '%') as '[0-60]',concat(sum(case when score >= 60 and score < 70 then 1 else 0 end) / count(*) * 100, '%') as '[60-70]',concat(sum(case when score >= 70 and score < 85 then 1 else 0 end) / count(*) * 100, '%') as '[70-85]',concat(sum(case when score >= 85 and score <= 100 then 1 else 0 end) / count(*) * 100, '%') as '[85-100]'from SCgroup by CId) as b
on a.CId = b.CId;

24.?? ?查詢各科成績(jī)前三名的記錄

select *
from(select *,dense_rank()over (partition by CId order by score desc) as rnkfrom SC) as a
where rnk <= 3;

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

select CId, count(*) as 學(xué)生數(shù)
from SC
group by CId;

26.?? ?查詢出只選修兩門課程的學(xué)生學(xué)號(hào)和姓名

select a.SId, s.Sname
from student s
right join(select SId, count(*) as 課程數(shù)from SCgroup by SIdhaving 課程數(shù) = 2) as a
on s.SId = a.SId;

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

select Ssex, count(*) as num
from student
group by Ssex;

28.?? ?查詢名字中含有「風(fēng)」字的學(xué)生信息

select *
from student
where Sname like "%風(fēng)%";

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

select Sname, Ssex, count(*) as 同名人數(shù)
from (select s.*from student sinner join student aon s.Sname = a.Sname and s.Ssex = a.Ssex and s.SId != a.SId) as a
group by Sname, Ssex;

30.?? ?查詢 1990 年出生的學(xué)生名單

select *
from student
where year(Sage) = "1990";

31.?? ?查詢每門課程的平均成績(jī)，結(jié)果按平均成績(jī)降序排列，平均成績(jī)相同時(shí)，按課程編號(hào)升序排列

select CId, avg(score) as avg_sc
from SC
group by CId
order by avg_sc desc, CId asc;

32.?? ?查詢平均成績(jī)大于等于 85 的所有學(xué)生的學(xué)號(hào)、姓名和平均成績(jī)

select s.SId, s.Sname, avg_sc
from student s
right join(select SId, avg(score) as avg_scfrom SCgroup by SIdhaving avg_sc >= 85) as a
on s.SId = a.SId;

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

select s.SId, s.Sname, SC.CId, SC.score
from SC
left join student s
on SC.SId = s.SId
where CId = (select CIdfrom coursewhere Cname = "數(shù)學(xué)") and score < 60;

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

select *
from student s
left join SC
on s.SId = SC.SId;

35.?? ?查詢?nèi)魏我婚T課程成績(jī)?cè)?70 分以上的姓名、課程名稱和分?jǐn)?shù)

select s.Sname, c.Cname, SC.score
from SC
left join course as c
on SC.CId = c.CId
left join student s
on SC.SId = s.SId
where SC.score > 70;

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

select *
from course
where CId in (select distinct CIdfrom SCwhere score < 60);

37.?? ?查詢課程編號(hào)為 01 且課程成績(jī)?cè)?80 分以上的學(xué)生的學(xué)號(hào)和姓名

select s.SId, s.Sname
from student s
join SC
on s.SId = SC.SId
where SC.CId = "01" and SC.score > 80;

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

select CId, count(*)
from SC
group by CId;

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

select s.*, SC.score
from student s, teacher th, course c, SC
where th.TId = c.TId
and SC.SId = s.SId
and SC.CId = c.CId
and th.Tname = "張三"
order by score desc
limit 1;

40.?? ?成績(jī)有重復(fù)的情況下，查詢選修「張三」老師所授課程的學(xué)生中，成績(jī)最高的學(xué)生信息及其成績(jī)

select s.*, SC.score
from student s
join SC
on s.SId = SC.SId
join course c
on SC.CId = c.cId
join teacher th
on c.TId = th.TId
where th.Tname = "張三"
order by score desc
limit 0,1;

41.?? ?查詢不同課程成績(jī)相同的學(xué)生的學(xué)生編號(hào)、課程編號(hào)、學(xué)生成績(jī)

select distinct s.SId, s.Sname, a.CId, a.score, b.CId, b.score
from student s
join SC a
on a.SId = s.SId
join SC b 
on b.SId = s.SId
where a.CId != b.CId and a.score = b.score;

42.?? ?查詢每門功成績(jī)最好的前兩名

select s.*, a.*
from student s
left join (select SC.SId, SC.score, row_number()over (partition by SC.CId) ranksfrom SC) as a
on s.SId = a.SId
where a.ranks <= 2;

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

select SC.CId, count(*)
from SC
join course c
on SC.CId = c.CId
group by SC.CId
having count(*) > 5;

44.?? ?檢索至少選修兩門課程的學(xué)生學(xué)號(hào)

select s.SId
from SC
join student s
on SC.SId = s.SId
group by SC.SId
having count(*) >= 2;

45.?? ?查詢選修了全部課程的學(xué)生信息

select s.*
from SC
join student s
on SC.SId = s.SId
group by SC.SId
having count(*) = (select count(*) from course);

46.?? ?查詢各學(xué)生的年齡，只按年份來算

select s.*, year(now()) - year(s.Sage) 年齡
from student s;

47.?? ?按照出生日期來算，當(dāng)前月日 < 出生年月的月日則，年齡減一

select s.*, timestampdiff(year, s.Sage, curdate()) 年齡
from student s;

48.?? ?查詢本周過生日的學(xué)生

select s.*
from student s
where weekofyear(s.Sage) = weekofyear(now());

49.?? ?查詢下周過生日的學(xué)生

select s.*
from student s
where weekofyear(s.Sage) = weekofyear(now()) + 1;

50.?? ?查詢本月過生日的學(xué)生

select s.*
from student s
where month(s.Sage) = month(now());

51.?? ?查詢下月過生日的學(xué)生

select s.*
from student s
where month(s.Sage) = month(now()) + 1;