標籤：

SQL學生選課場景練習

06-04
SQL學生選課場景練習

題目轉載自50道SQL練習題，簡單的學生選課場景。

我使用的是SQLite，與MYSQL除了函數用法不同外其他基本相同。

數據表

--1.學生表

Student(SId,Sname,Sage,Ssex)

--SId 學生編號,Sname 學生姓名,Sage 出生年月,Ssex 學生性別

--2.課程表

Course(CId,Cname,TId)

--CId 課程編號,Cname 課程名稱,TId 教師編號

--3.教師表

Teacher(TId,Tname)

--TId 教師編號,Tname 教師姓名

--4.成績表

SC(SId,CId,score)

--SId 學生編號,CId 課程編號,score 分數

創建測試數據

學生表 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 , 孫風 , 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

create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10))insert into Course values(01 , 語文 , 02)insert into Course values(02 , 數學 , 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 , 王五)

成績表 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)

練習題目

1. 查詢" 01 "課程比" 02 "課程成績高的學生的信息及課程分數

1.1 查詢同時存在" 01 "課程和" 02 "課程的情況

1.2 查詢存在" 01 "課程但可能不存在" 02 "課程的情況(不存在時顯示為 null )

1.3 查詢不存在" 01 "課程但存在" 02 "課程的情況

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

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

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

4.1 查有成績的學生信息

5. 查詢「李」姓老師的數量

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

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

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

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

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

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

12. 檢索" 01 "課程分數小於 60，按分數降序排列的學生信息

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

14. 查詢各科成績最高分、最低分和平均分：

以如下形式顯示：課程 ID，課程 name，最高分，最低分，平均分，及格率，中等率，優良率，優秀率

及格為>=60，中等為：70-80，優良為：80-90，優秀為：>=90

要求輸出課程號和選修人數，查詢結果按人數降序排列，若人數相同，按課程號升序排列

15. 按各科成績進行排序，並顯示排名， Score 重複時保留名次空缺

15.1 按各科成績進行排序，並顯示排名， Score 重複時合併名次

16. 查詢學生的總成績，並進行排名，總分重複時保留名次空缺

16.1 查詢學生的總成績，並進行排名，總分重複時不保留名次空缺

17. 統計各科成績各分數段人數：課程編號，課程名稱，[100-85]，[85-70]，[70-60]，[60-0] 及所佔百分比

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

19. 查詢每門課程被選修的學生數

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

21. 查詢男生、女生人數

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

23. 查詢同名同性學生名單，並統計同名人數

24. 查詢 1990 年出生的學生名單

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

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

27. 查詢課程名稱為「數學」，且分數低於 60 的學生姓名和分數

28. 查詢所有學生的課程及分數情況（存在學生沒成績，沒選課的情況）

29. 查詢任何一門課程成績在 70 分以上的姓名、課程名稱和分數

30. 查詢不及格的課程

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

32. 求每門課程的學生人數

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

34. 成績有重複的情況下，查詢選修「張三」老師所授課程的學生中，成績最高的學生信息及其成績

35. 查詢不同課程成績相同的學生的學生編號、課程編號、學生成績

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

37. 統計每門課程的學生選修人數（超過 5 人的課程才統計）。

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

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

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

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

42. 查詢本周過生日的學生

43. 查詢下周過生日的學生

44. 查詢本月過生日的學生

45. 查詢下月過生日的學生

1.查詢" 01 "課程比" 02 "課程成績高的學生的信息及課程分數

select sc1.Sid, sc1.score, sc2.scorefrom SC sc1 left join SC sc2 on sc1.SId=sc2.SIdwhere sc1.CId=01 and sc2.CId=02 and sc1.score>sc2.score

1.1 查詢同時存在" 01 "課程和" 02 "課程的情況

select sc1.Sid, sc1.score, sc2.scorefrom SC sc1 left join SC sc2 on sc1.SId=sc2.SIdwhere sc1.CId=01 and sc2.CId=02

1.2 查詢存在" 01 "課程但可能不存在" 02 "課程的情況(不存在時顯示為 null )

select sc1.SId, sc1.score, sc2.scorefrom SC sc1 left join SC sc2 on sc1.SId=sc2.SId and sc2.CId=02where sc1.CId=01

1.3 查詢不存在" 01 "課程但存在" 02 "課程的情況

select *from SCwhere SId not in (select SId from SC where CId=01) and CId=02

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

select c.SId, s.Sname, avg(c.score)from SC c inner join Student s on c.SId=s.SIdgroup by c.SIdhaving avg(c.score)>=60

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

select DISTINCT student.*from student ,scwhere student.SId=sc.SId

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

select s.SId, s.Sname, count(c.CId), sum(score)from Student s left join SC c on s.SId=c.SIdgroup by c.SId

4.1 查有成績的學生信息

select distinct s.*from Student s inner join SC con s.SId=c.SId

5. 查詢「李」姓老師的數量

select count(*)from Teacher twhere t.Tname like 李%

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

select distinct s.*from Student sinner join SC sc on s.SId=sc.SIdinner join Course c on sc.CId=c.CIdinner join Teacher t on c.TId=t.TIdwhere t.Tname=張三

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

select student.*from studentwhere student.SId not in ( select sc.SIdfrom scgroup by sc.SIdhaving count(*)=(select count(CId) from course))

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

select DISTINCT student.*from sc ,studentwhere sc.CId in (select CId from sc where sc.SId=01)and sc.SId=student.SId and sc.SId!=01

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

select s.*from sc, (select CId from SC where SId=01) s1, Student swhere sc.CId=s1.CId and s.SId=sc.SId and s.SId!=01group by sc.SIdhaving count(sc.CId)=3

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

select *from Student swhere s.SId not in(select distinct s.SIdfrom Student s left join sc on s.SId=sc.SIdleft join Course c on sc.CId=c.CIdleft join Teacher t on c.TId=t.TIdwhere t.Tname=張三)

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

select s.SId, s.Sname, avg(sc1.score)from (select * from sc where score<60) sc1, Student swhere sc1.SId=s.SIdgroup by sc1.SIdhaving count(sc1.CId)>1

12. 檢索" 01 "課程分數小於 60，按分數降序排列的學生信息

select s.*from Student s, scwhere sc.CId=01 and sc.score<60 and s.SId=sc.SIdorder by sc.score desc

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

select SId, max(case when CId=01 then score end) as 01, max(case when CId=02 then score end) as 02, max(case when CId=03 then score end) as 03, avg(score)from scgroup by SIdorder by avg(score) desc

課程非常多或動態變化時此方法不可行。

14. 查詢各科成績最高分、最低分和平均分：

以如下形式顯示：課程 ID，課程 name，最高分，最低分，平均分，及格率，中等率，優良率，優秀率

及格為>=60，中等為：70-80，優良為：80-90，優秀為：>=90

要求輸出課程號和選修人數，查詢結果按人數降序排列，若人數相同，按課程號升序排列

select sc.CId 課程號, max(sc.score) 最高分, min(sc.score) 最低分, round(avg(sc.score),2) 平均分, count(sc.score) 選修人數, round(count(case when sc.score>=60 then 1 else null end)*1.0/count(sc.score),2) 及格率, round(count(case when sc.score>=70 and sc.score<80 then 1 else null end)*1.0/count(sc.score),2) 中等率, round(count(case when sc.score>=80 and sc.score<90 then 1 else null end)*1.0/count(sc.score),2) 優良率, round(count(case when sc.score>=90 then 1 else null end)*1.0/count(sc.score),2) 優秀率from scgroup by sc.CIdorder by 選修人數 desc, 課程號

15. 按各科成績進行排序，並顯示排名， Score 重複時保留名次空缺

select sc.SId, sc.CId, sc.score, tp.rankfrom sc left join (select SId, CId, (select count(sc2.score)+1 from sc sc2 where sc1.CId=sc2.CId and sc2.score>sc1.score) rank from sc sc1) tpon sc.SId=tp.SId and sc.CId=tp.CIdorder by sc.CId, rank

15.1 按各科成績進行排序，並顯示排名， Score 重複時合併名次

select sc.SId, sc.CId, sc.score, tp.rankfrom sc left join (select SId, CId, (select count(distinct sc2.score)+1 from sc sc2 where sc1.CId=sc2.CId and sc2.score>sc1.score) rank from sc sc1) tpon sc.SId=tp.SId and sc.CId=tp.CIdorder by sc.CId, rank

16. 查詢學生的總成績，並進行排名，總分重複時保留名次空缺

select k1.*, count(k2.total) rankfrom (select SId, sum(score) total from sc group by SId) k1, (select sum(score) total from sc group by SId) k2where k2.total>=k1.totalgroup by k1.SIdorder by rank

16.1 查詢學生的總成績，並進行排名，總分重複時不保留名次空缺

select k1.*, count(distinct k2.total) rankfrom (select SId, sum(score) total from sc group by SId) k1, (select sum(score) total from sc group by SId) k2where k2.total>=k1.totalgroup by k1.SIdorder by rank

17. 統計各科成績各分數段人數：課程編號，課程名稱，[100-85]，[85-70]，[70-60]，[60-0] 及所佔百分比

select c.CId 課程編號, c.Cname 課程名稱, count(case when sc.score>=85 then 1 else null end) as [100,85]人數, round(count(case when sc.score>=85 then 1 else null end)*1.0/count(sc.score),2) as [100,85]佔比, count(case when sc.score>=70 and sc.score<85 then 1 else null end) as (85,70]人數, round(count(case when sc.score>=70 and sc.score<85 then 1 else null end)*1.0/count(sc.score),2) as (85,70]佔比, count(case when sc.score>=60 and sc.score<70 then 1 else null end) as (70,60]人數, round(count(case when sc.score>=60 and sc.score<70 then 1 else null end)*1.0/count(sc.score),2) as (70,60]佔比, count(case when sc.score<60 then 1 else null end) as (60,0]人數, round(count(case when sc.score<60 then 1 else null end)*1.0/count(sc.score),2) as (60,0]佔比from Course c left join scon c.CId=sc.CIdgroup by c.CId

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

select CId, SId, scorefrom scwhere (select count(*) from sc sc1 where sc1.CId=sc.CId and sc1.score>sc.score) <3order by CId, score desc

19. 查詢每門課程被選修的學生數

select c.CId, c.Cname, count(*)from Course c, scwhere c.CId=sc.CIdgroup by c.CId

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

select s.SId, s.Snamefrom Student s left join sc on s.SId=sc.SIdgroup by s.SIdhaving count(score)=2

21.查詢男生、女生人數

select Ssex, count(*)from Studentgroup by Ssex

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

select *from Studentwhere Sname like %風%

23.查詢同名同性學生名單，並統計同名人數

select s1.*, amountfrom Student s1inner join (select Sname, Ssex, count(*) amount from Student s2 group by Sname, Ssex having count(*)>1) non s1.Sname=n.Sname and s1.Ssex=n.Ssex

24.查詢 1990 年出生的學生名單

select *from studentwhere date(student.Sage) like 1990%

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

select CId, round(avg(score),2) averagefrom scgroup by CIdorder by average desc, CId

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

select s.SId, s.Sname, round(avg(score),2) averagefrom sc inner join Student son sc.SId=s.SIdgroup by sc.SIdhaving average>=85

27. 查詢課程名稱為「數學」，且分數低於 60 的學生姓名和分數

select Sname, scorefrom Course cinner join sc on c.Cname=數學 and c.CId=sc.CIdinner join Student s on sc.SId=s.SIdwhere score<60

28. 查詢所有學生的課程及分數情況（存在學生沒成績，沒選課的情況）

select s.SId, s.Sname, max(case when sc.CId=01 then score else null end) as 語文』, max(case when sc.CId=02 then score else null end) as 數學』, max(case when sc.CId=03 then score else null end) as 『英語 from Student s left join sc on s.SId=sc.SIdgroup by s.SId

29. 查詢任何一門課程成績在 70 分以上的姓名、課程名稱和分數

select s.Sname, c.Cname, sc.scorefrom Student s, sc, Course c, (select SId from sc group by SId having min(score)>70) nwhere n.SId=s.SId and sc.CId=c.CId and n.SId=sc.SId

30.查詢存在不及格的課程

select distinct SIdfrom scwhere score<60

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

select s.SId, s.Snamefrom sc, Student swhere sc.CId=01 and sc.Score>=80 and sc.SId=s.SId

32. 求每門課程的學生人數

select CId, count(*)from scgroup by CId

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

select s.*, sc.scorefrom Teacher tleft join Course c on t.Tname=張三 and t.TId=c.TIdleft join SC on c.CId=sc.CIdleft join Student s on sc.SId=s.SIdorder by sc.score desclimit 1

34. 成績有重複的情況下，查詢選修「張三」老師所授課程的學生中，成績最高的學生信息及其成績

select s.*, sc.scorefrom (select sc.CId, sc.score from sc, Course c, Teacher t where t.Tname=張三 and t.TId=c.TId and sc.CId=c.CId order by sc.score desc limit 1) ms, sc, Student swhere ms.score=sc.score and ms.CId=sc.CId and sc.SId=s.SId

35. 查詢不同課程成績相同的學生的學生編號、課程編號、學生成績

select distinct sc1.*from sc sc1 inner join sc sc2on sc1.SId=sc2.SId and sc1.score=sc2.score and sc1.CId!=sc2.CId

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

select *from scwhere (select count(*) from sc sc1 where sc1.CId=sc.CId and sc1.score>sc.score)<2order by CId

37.統計每門課程的學生選修人數（超過 5 人的課程才統計）

select CId, count(*)from scgroup by CIdhaving count(*)>5

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

select SIdfrom scgroup by SIdhaving count(*)>=2

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

select s.*from Student s left join sc on s.SId=sc.SIdgroup by s.SIdhaving count(*)=(select distinct count(*) from Course)

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

select SId as 學生編號, Sname as 學生姓名, date(now)-strftime(%Y,Sage) as 學生年齡from student

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

select student.SId as 學生編號, student.Sname as 學生姓名, date(now)-strftime(Sage) as 學生年齡from student

42.查詢本周過生日的學生

select *from studentwhere strftime(%W,Sage)=strftime(%W,date(now))

43. 查詢下周過生日的學生

select *from studentwhere cast(strftime(%W,Sage) as int)=cast(strftime(%W,date(now)) as int)+1

44.查詢本月過生日的學生

select *from studentwhere cast(strftime(%m,Sage) as int)=cast(strftime(%m,date(now)) as int)

45.查詢下月過生日的學生

select *from studentwhere cast(strftime(%m,Sage) as int)=cast(strftime(%m,date(now)) as int)+1

歡迎指正。


推薦閱讀：

※當計算欄位邂逅子查詢
※利用SQL進行用戶消費行為分析
※Cross Apply 與 Inner Join 的對抗
※什麼是最好的oracle sql 開發工具？
※Sqli labs系列-less-2 詳細篇

TAG:SQL |