如有問題請及時指正 select version(); 數據準備 -- 1.學生表 -- S# 學生編號,Sname 學生姓名,Sage 出生年月,Ssex 學生性別 CREATE TABLE `Student` ( `S#` varchar(10) NOT NULL, `Sname` varcha ...

如有問題請及時指正

select version();

數據準備

-- 1.學生表
-- S# 學生編號,Sname 學生姓名,Sage 出生年月,Ssex 學生性別
CREATE TABLE `Student` (
  `S#` varchar(10) NOT NULL,
  `Sname` varchar(100) NOT NULL,
  `Sage` datetime NOT NULL,
  `Ssex` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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('08' , '王菊' , '1990-01-20' , '女');

-- 2.課程表 
-- C# --課程編號,Cname 課程名稱,T# 教師編號
CREATE TABLE `Course` (
  `C#` varchar(10) NOT NULL,
  `Cname` varchar(10) NOT NULL,
  `T#` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

insert into Course values('01' , '語文' , '02');
insert into Course values('02' , '數學' , '01');
insert into Course values('03' , '英語' , '03');

-- 3.教師表 
-- T# 教師編號,Tname 教師姓名
CREATE TABLE `Teacher` (
  `T#` varchar(10) NOT NULL,
  `Tname` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

insert into Teacher values('01' , '張三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

-- 4.成績表 
-- S# 學生編號,C# 課程編號,score 分數
CREATE TABLE `SC` (
  `S#` varchar(10) NOT NULL,
  `C#` varchar(10) NOT NULL,
  `score` decimal(18,1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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 "課程成績高的學生的信息及課程分數

-- 自連接
SELECT s.`S#` ,s.`C#` ,s.score,s2.`C#` ,s2.score ,s3.Sname ,s3.Sage ,s3.Ssex  
from SC s ,SC s2,Student s3 where s.`C#` ='01' and s2.`C#` ='02'
and s.`S#` = s2.`S#` 
and s.score > s2.score and s.`S#` = s3.`S#`;
-- 左連接
select A.*,B.`C#`,B.score,C.Sname ,C.Sage ,C.Ssex 
from Student C ,(select * from SC where `C#`='01')A 
left join(select * from SC where `C#`='02')B 
on A.`S#`=B.`S#` 
where A.score>B.score and C.`S#` = A.`S#`;

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

-- 自連接
SELECT * from SC s,SC s2  where s.`C#` ='01' and s2.`C#` ='02' and s.`S#` = s2.`S#`;
-- 左連接
SELECT * from (select * from SC where `C#`='01')A 
left join (select * from SC where `C#`='02')B on A.`S#`=B.`S#`
where B.`S#` is not null;

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

SELECT * FROM (select * from SC where `C#`='01') s
LEFT JOIN (select * from SC where `C#`='02') s2
on s.`S#` = s2.`S#`;

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

SELECT * from SC s  where s.`C#` ='02' 
and s.`S#` not in(SELECT s2.`S#` FROM SC s2 where s2.`C#`='01');

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

select A.`S#`,B.Sname,A.dc from(select `S#`,AVG(score)dc from SC group by `S#`)A
left join Student B on A.`S#`=B.`S#` where A.dc>=60;

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

select * from Student where `S#` in (select distinct `S#` from SC);

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

select B.`S#`,B.Sname,A.選課總數,A.總成績 from
(select `S#`,COUNT(`C#`)選課總數,sum(score)總成績 from SC group by `S#`)A
right join Student B on A.`S#`=B.`S#`;

4.1 查有成績的學生信息

select B.`S#`,B.Sname,A.選課總數,A.總成績 from
(select `S#`,COUNT(`C#`)選課總數,sum(score)總成績 from SC group by `S#`)A
LEFT join Student B on A.`S#`=B.`S#`;

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

select COUNT(*)李姓老師數量 from Teacher where Tname like '李%';

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

select * from Student s 
where s.`S#` in ( SELECT s2.`S#` from SC s2 
                 WHERE s2.`C#` IN (SELECT c.`C#`  from Course c 
                  WHERE c.`T#` IN (SELECT t.`T#`  from Teacher t
                 WHERE t.Tname='張三')));

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

select * from Student
where `S#` in(select `S#` from SC group by `S#` having COUNT(`C#`)<3);

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

select * from Student 
where `S#` in(select distinct `S#` from SC 
              where `C#` in(select `C#` from SC where `S#`='01'));

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

select * from Student 
where `S#` in(select `S#` from SC where `C#` in(select distinct `C#` from SC where `S#`='01') and `S#`<>'01'
group by `S#`
having COUNT(`C#`)>=3)

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

select * from Student 
where `S#` not in(select `S#` from SC 
where `C#` in(select c.`C#` from Course c 
where c.`T#` in (SELECT t.`T#` from Teacher t where t.Tname='張三')));

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

select A.`S#`,A.Sname,B.平均成績 from Student A right join
(select `S#`,AVG(score)平均成績 from SC where score<60 group by `S#` having COUNT(score)>=2)B
on A.`S#`=B.`S#`;

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

SELECT * FROM Student s2 
where s2.`S#` in(SELECT s.`S#` from SC s 
                 where s.`C#` ='01' and s.score <60 ORDER by score desc);

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

select`S#`,max(case `C#` when '01' then score else 0 end)'01',
MAX(case `C#` when '02' then score else 0 end)'02',
MAX(case `C#` when '03' then score else 0 end)'03',AVG(score)平均分 from SC
group by `S#` order by 平均分 desc;

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

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

select 
	`C#` ,
	count(`S#`) as '選修人數', 
	max(score) as '最高分',
	min(score) as '最低分',
	avg(score) as '平均分',
	concat( round( 100 * (sum( case  when score >= 60 then 1 else 0 end ) / count(score) ), 2 ), '%' )  as '及格率',
	concat( round( 100 * (sum( case  when score >= 70 and score < 80 then 1 else 0 end ) / count(score) ), 2 ), '%' )  as '中等率',
	concat( round( 100 * (sum( case  when score >= 80 and score < 90 then 1 else 0 end ) / count(score) ), 2 ), '%' )  as '優良率',
	concat( round( 100 * (sum( case  when score > 90 then 1 else 0 end ) / count(score) ), 2 ), '%' )  as '優秀率'
from 
	SC 
group by 
	`C#` 
order by
	選修人數 desc,
	`C#`;

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

select *,RANK()over(order by score desc)排名 from SC;

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

select *,DENSE_RANK()over(order by score desc)排名 from SC;

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

select *,RANK()over(order by 總成績 desc)排名 from(
select `S#`,SUM(score)總成績 from SC group by `S#`)A;

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

select *,DENSE_RANK()over(order by 總成績 desc)排名 from(
select `S#`,SUM(score)總成績 from SC group by `S#`)A;

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

select
b.Cname ,
a.*
from
(select
s.`C#` ,
sum(case when score >=85 and score <100 then 1 else 0 end) as '[85_100]人數' ,
concat(cast(100*sum(case when score >=85 and score <100 then 1 else 0 end)/COUNT(*) as decimal(4,2)) , '%') as '[85_100]占比',

sum(case when score >=70 and score <85 then 1 else 0 end) as '[70_85]人數' ,
concat(cast(100*sum(case when score >=70 and score <85 then 1 else 0 end)/COUNT(*) as decimal(4,2)) , '%') as '[70_85]占比',

sum(case when score >=60 and score <70 then 1 else 0 end) as '[60_70]人數' ,
concat(cast(100*sum(case when score >=60 and score <70 then 1 else 0 end)/COUNT(*) as decimal(4,2)) , '%') as '[60_70]占比',

sum(case when score >=0 and score <60 then 1 else 0 end) as '[0_60]人數' ,
concat(cast(100*sum(case when score >=0 and score <60 then 1 else 0 end)/COUNT(*) as decimal(4,2)) , '%') as '[0_60]占比'
from 
SC s
group by s.`C#`) a left join Course b on a.`C#` = b.`C#`;

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

-- 方法一：
select * from SC a where (select COUNT(*)from SC where `C#`=a.`C#` and score>a.score)<3
order by a.`C#`,a.score desc;
-- 方法二：
select a.`S#`,a.`C#`,a.score from SC a 
left join SC b on a.`C#`=b.`C#` and a.score<b.score
group by a.`S#`,a.`C#`,a.score
having COUNT(b.`S#`)<3
order by a.`C#`,a.score desc;
-- 方法三：
-- select * from(select *,rank()over (partition by `C#` order by score desc)A from SC)B where B.A<=3;

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

select `C#`,COUNT(`S#`)學生數 from SC group by `C#`

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

select `S#`,Sname from Student 
where `S#` in(select `S#` from(select `S#`,COUNT(`C#`)課程數 from SC group by `S#`)A 
              where A.課程數=2);

21. 查詢男生、女生人數

select Ssex,COUNT(Ssex)人數 from Student group by Ssex;

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

select * from Student where Sname like '%風%';

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

select A.*,B.同名人數 from Student A
left join (select Sname,Ssex,COUNT(*)同名人數 from Student group by Sname,Ssex)B 
on A.Sname=B.Sname and A.Ssex=B.Ssex
where B.同名人數>1;

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

select * from Student where YEAR(Sage)=1990;

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

select `C#`,AVG(score)平均成績 from SC group by `C#` order by 平均成績 desc,`C#`;

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

select A.`S#`,A.Sname,B.平均成績 from Student A
left join (select `S#`,AVG(score)平均成績 from SC group by `S#`)B on A.`S#`=B.`S#`
where B.平均成績>85;

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

select B.Sname,A.score from(select * from SC 
                            where score<60 and `C#`=(select `C#` from Course 
                                                     where Cname='數學'))A
left join Student B on A.`S#`=B.`S#`;

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

select A.`S#`,B.`C#`,B.score from Student A left join SC B on A.`S#`=B.`S#`;

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

select A.Sname,D.Cname,D.score from 
(select B.*,C.Cname from(select * from SC where score>70)B left join Course C on B.`C#`=C.`C#`)D 
left join Student A on D.`S#`=A.`S#`;

30. 查詢不及格的課程

SELECT * from Course c where c.`C#` in (select s.`C#` from SC s where score<60);

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

select A.`S#`,B.Sname from (select * from SC where score>80 and `C#`='01')A
left join Student B on A.`S#`=B.`S#`;

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

同19題

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

SELECT * from Student st 
where st.`S#` =(select s.`S#`  from SC s 
                where s.`C#`=(select c.`C#` from Course c 
                              where c.`T#`=(select t.`T#` from Teacher t 
                                            where t.Tname='張三'))
order by s.score desc limit 1);

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

SELECT * from Student st 
where st.`S#` =(select `S#`  from(select *,DENSE_RANK()over (order by score desc)A 
from SC 
where `C#`=(select `C#` from Course 
            where `T#`=(select `T#` from Teacher where Tname='張三')))B
where B.A=1);

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

select C.`S#`,max(C.`C#`)`C#`,max(C.score)score from SC C 
left join(select `S#`,avg(score)A from SC group by `S#`)B 
on C.`S#`=B.`S#`
where C.score=B.A
group by C.`S#`
having COUNT(0)=(select COUNT(0)from SC where `S#`=C.`S#`);

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

select * from
(select *,ROW_NUMBER()over(partition by `C#` order by score desc)A from SC)B
where B.A<3;

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

select `C#`,COUNT(`S#`)選修人數 from SC 
group by `C#`
having COUNT(`S#`)>5
order by 選修人數 desc,`C#`;

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

select `S#` from SC
group by `S#`
having COUNT(`C#`)>=2;

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

select `S#` from SC 
group by `S#` 
having count(`C#`)=(select distinct COUNT(0)a from Course);

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

select s.Sname , year(now())-year(s.Sage) as age from Student s

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

select s.Sname , timestampdiff(year , s.Sage, now()) as age from Student s

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

select *
from Student s  
where WEEKOFYEAR(s.Sage)=WEEKOFYEAR(NOW());

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

select *
from Student s 
where WEEKOFYEAR(s.Sage)=WEEKOFYEAR(NOW())+1;

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

select *
from Student s 
where MONTH(s.Sage)=MONTH(current_date());

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

select *
from Student s 
where MONTH(s.Sage)=MONTH(current_date())+1;

經典SQL練習題_MySQL

如有問題請及時指正

數據準備

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. 查詢各科成績最高分、最低分和平均分：

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. 查詢下月過生日的學生