[資料庫]50道經典SQL練習題,使用MySQL5.7解答

資料表介紹

--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 分數

練習題目

1.查詢" 01 “課程比” 02 "課程成績高的學生的資訊及課程分數

-- 此處可以先考慮分別查詢 01和02課程的學員id和分數
select SId,score from SC where CId = '01';
select SId,score from SC where CId = '02';
-- 對比結果可以清楚的知道,兩個表中有些SId不對應,不對應就沒有可比性
-- 因此可以對兩個結果做join,條件就是SId要相等,
-- 並且01的成績要比02大
select s1.SId,s1.score
from 
(select SId,score from SC where CId = '01') s1
join
(select SId,score from SC where CId = '02') s2
on s1.SId = s2.SId
where s1.score > s2.score

-- 通過以上的sql得到了符合條件的學員id和分數,再到學員表中獲取學員資訊
select stu.SId,stu.Sname,s.score 
from student as stu 
right join (
        select s1.SId,s1.score
        from 
        (select SId,score from SC where CId = '01') s1
        join
        (select SId,score from SC where CId = '02') s2
        on s1.SId = s2.SId
        where s1.score > s2.score
    ) as s
on stu.SId = s.SId;

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

-- 本題和上一題內容相似,我們只需要把01和02的學員的SID作為連線條件就可以
select s1.*
from 
(select SId,score from SC where CId = '01') s1
join
(select SId,score from SC where CId = '02') s2
on s1.SId = s2.SId

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

select s1.SId,s2.score
from 
(select SId,score from SC where CId = '01') s1
left join
(select SId,score from SC where CId = '02') s2
on s1.SId = s2.SId

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

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

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

-- 此題主要考核 分組後的條件過濾
select SC.SId,Sname,round(avg(score),2) as avg_score
from SC,Student
where SC.SId = Student.SId
group by SC.SId,Sname having avg_score >= 60;

6.查詢在 SC 表存在成績的學生資訊

-- 此題主要考核 資料去重 distinct 
select distinct stu.* from Student stu join SC on SC.SId = stu.SId;

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

select stu.SId,stu.Sname,count(SC.CId) as total ,sum(SC.score) as sum_score
from Student as stu left join SC on stu.SId = SC.SId
group by stu.SId,stu.Sname;

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

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

9.查詢學過「張三」老師授課的同學的資訊

select stu.*
from Student as stu 
join SC on stu.SId = SC.SId
join course as C on SC.CId = C.CId
join Teacher as T on C.TId = T.TId
where T.Tname = '張三';

10.查詢沒有學全所有課程的同學的資訊

-- 排除法,找到所有學過全部課程的學生,
select * from Student where SId not in(
    select SId from SC group by SId having count(CId) = (select count(*) from course)
)

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

select distinct stu.* 
from student as stu 
left join sc on sc.SId = stu.SId 
where sc.CId in (select CId from sc where SId = '01');

12.查詢和" 01 "號的同學學習的課程 完全相同的其他同學的資訊

-- 注意要對比的是課程ID號,在對比課程數
select s2.SId
from SC as s1 join SC as s2
on s1.CId = s2.CId and s1.SId = '01' and s2.SId != '01'  
group by s2.SId 
having count(s1.SId) = (select count(*) from SC where SId = '01');

select stu.* 
from student as stu 
left join sc on sc.SId = stu.SId 
join ( select CId from sc where SId = '01') as t on t.CId = sc.CId 
group by stu.SId having count(sc.SId) = 5;

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

select SId,Sname from Student where SId not in 
(select SId from SC
    where CId = (
        select Course.Cid from Course 
        join Teacher on Teacher.TId = Course.TId 
        where Teacher.Tname = '張三'
        ))

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

select SC.SId,Student.Sname,round(avg(SC.score),2) as avg_sc 
from SC join Student on Student.SId = SC.SId
where SC.score < 60 group by SC.SId,Student.Sname 
having count(SC.CId) >=2;

15.檢索" 01 "課程分數小於 60，按分數降序排列的學生資訊

select SC.SId,Student.Sname,SC.score
from SC join Student  on SC.SId = Student.SId 
where SC.CId = '01' and SC.score < 60 
order by SC.score desc;

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

select SC.*,s2.avg_sc from SC 
join  (select SId, avg(score) as avg_sc from SC group by SId) as s2
on SC.SId = s2.SId
order by avg_sc desc, SC.SId;


--最佳解決方案 王宇鵬
select a.sname,b.score 語文,c.score 數學,d.score 英語,avg(e.score) 
from student a 
left join sc b on a.sid=b.sid and b.cid='01' 
left join sc c on a.sid=c.sid and c.cid='02' 
left join sc d on a.sid=d.sid and d.cid='03' 
left join sc e on a.sid=e.sid 
group by a.sname,語文,數學,英語
order by avg(e.score) desc;

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

select SC.CId,C.Cname,
max(SC.score) as '最高分',
min(SC.score) as '最低分',
avg(SC.score) as '平均分',
count(SC.CId) as '選修人數',
SUM(CASE WHEN SC.score >= 60 THEN 1 ELSE 0 END) / count(SC.CId) as '及格率',
SUM(CASE WHEN SC.score >= 70 and SC.score < 80 THEN 1 ELSE 0 END) / count(SC.CId) as '中等率',
SUM(CASE WHEN SC.score >= 80 and SC.score < 90 THEN 1 ELSE 0 END) / count(SC.CId) as '優良率',
SUM(CASE WHEN SC.score >= 90 THEN 1 ELSE 0 END) / count(SC.CId) as '優秀率'
from SC,Course as C
where SC.CId = C.CId
group by SC.CId,C.Cname
order by '選修人數' desc,SC.CId;

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

select s2.CId,s2.avg_sc,count(distinct s1.avg_sc) as Rank
from
(select  avg(score) as avg_sc from SC group by CId) as s1
join 
(select CId,avg(score) as avg_sc from SC group by CId) as s2
on s1.avg_sc >= s2.avg_sc
group by s2.CId order by Rank;

-- 解析過程
select s1.*,s2.*
from
(select  CId,avg(score) as avg_sc from SC group by CId) as s1
join 
(select CId,avg(score) as avg_sc from SC group by CId) as s2
on s1.avg_sc >= s2.avg_sc
group by s2.CId order by Rank;
-- 在這種情況下 去統計s1.平均分 還是去統計s2.平均分 的次數,資料中都有重複
-- 如果能把s1中的出現重複的平均分數去重,再去按照s1.平均分去統計次數就正常
    +------+----------+------+----------+
    | CId  | avg_sc   | CId  | avg_sc   |
    +------+----------+------+----------+
    | 01   | 64.50000 | 01   | 64.50000 |
    | 02   | 79.33333 | 01   | 64.50000 |
    | 03   | 70.00000 | 01   | 64.50000 |
    | 04   | 79.30000 | 01   | 64.50000 |
  --| 5    | 70.00000 | 01   | 64.50000 |
    | 02   | 79.33333 | 02   | 79.33333 |
    | 02   | 79.33333 | 03   | 70.00000 |
    | 03   | 70.00000 | 03   | 70.00000 |
    | 04   | 79.30000 | 03   | 70.00000 |
  --| 5    | 70.00000 | 03   | 70.00000 |
    | 02   | 79.33333 | 04   | 79.30000 |
    | 04   | 79.30000 | 04   | 79.30000 |
    | 02   | 79.33333 | 5    | 70.00000 |
    | 03   | 70.00000 | 5    | 70.00000 |
    | 04   | 79.30000 | 5    | 70.00000 |
  --| 5    | 70.00000 | 5    | 70.00000 |
    +------+----------+------+----------+

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

select b.CId,b.avg_sc,@i := @i+1 as Rank 
from (select @i := 0) a,
(select CId,round(avg(Score),2) as avg_sc from SC group by CId order by avg_sc desc) b;

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

select s2.SId,s2.avg_sc,count(distinct s1.avg_sc) as Rank
from
(select  sum(score) as avg_sc from SC group by SId) as s1
join 
(select SId,sum(score) as avg_sc from SC group by SId) as s2
on s1.avg_sc >= s2.avg_sc
group by s2.SId order by Rank;

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

select b.SId,b.avg_sc,@i := @i+1 as Rank 
from (select @i := 0) a,
(select SId,sum(Score) as avg_sc from SC group by sId order by avg_sc desc) b;

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

select Course.CId,Course.Cname,
sum(CASE WHEN SC.score >= 85 and SC.score <= 100 THEN 1 ELSE 0 END) '[100-85]',
concat(round(sum(CASE WHEN SC.score >= 85 and SC.score <= 100 THEN 1 ELSE 0 END)/count(SC.SId),2)*100,'%') as '百分比',
sum(CASE WHEN SC.score >= 70 and SC.score < 85 THEN 1 ELSE 0 END) '[85-70]',
concat(round(sum(CASE WHEN SC.score >= 70 and SC.score < 85 THEN 1 ELSE 0 END)/count(SC.SId),2)*100,'%') as '百分比',
sum(CASE WHEN SC.score >= 60 and SC.score < 70 THEN 1 ELSE 0 END) '[70-60]',
concat(round(sum(CASE WHEN SC.score >= 60 and SC.score < 70 THEN 1 ELSE 0 END)/count(SC.SId),2)*100,'%') as '百分比',
sum(CASE WHEN SC.score >= 0 and SC.score < 60 THEN 1 ELSE 0 END) '[60-0]',
concat(round(sum(CASE WHEN SC.score >= 0 and SC.score < 60 THEN 1 ELSE 0 END)/count(SC.SId),2)*100,'%') as '百分比'
from SC,Course
where SC.CId = Course.CId
group by Course.CId,Course.Cname;

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

(select CId,score from SC where CId = '01' order by score desc limit 3) 
union all
(select CId,score from SC where CId = '02' order by score desc limit 3) 
union all
(select CId,score from SC where CId = '03' order by score desc limit 3) 

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

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

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

select SC.SId,Student.Sname
from SC join Student on SC.SId = Student.SId
group by SC.SId,Student.Sname having count(SC.CId) = 2; 

26.查詢男生、女生人數

select Ssex,count(*) from Student group by Ssex;

27.查詢名字中含有「風」字的學生資訊

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

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

select s1.Sname,count(s1.Sname)
from Student s1 join Student s2 
on s1.Sname = s2.Sname and s1.Ssex = s2.Ssex and s1.SId != s2.SId
group by s1.Sname;

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

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

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

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

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

select SC.SId,Stu.Sname,round(avg(SC.score),2) as avg_sc 
from SC join Student as Stu on SC.SId = Stu.SId
group by SC.SId,Stu.Sname having avg_sc >= 85;

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

select C.Cname,Stu.Sname,SC.score
from Course as C join SC  on C.CId = SC.CId 
join Student as Stu on SC.SId = Stu.SId
where C.Cname = '數學' and SC.score < 60;

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

select Student.Sname,Course.Cname,SC.score
from Student left join SC on Student.SId = SC.SId
left join Course on SC.CId = Course.CId
order by Student.Sname;

-- 張波
select a.SId,a.sname,b.score 語文,c.score 數學,d.score 英語 
from student a 
left join sc b on a.sid=b.sid and b.cid='01' 
left join sc c on a.sid=c.sid and c.cid='02' 
left join sc d on a.sid=d.sid and d.cid='03' 
group by a.SId,a.sname,語文,數學,英語
order by a.Sid;

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

select Student.Sname,Course.Cname,SC.score
from Student,Course,SC 
where Student.SId = SC.SId and SC.CId = Course.CId
and SC.score > 70;

35.查詢不及格的課程

select Student.Sname,Course.Cname,SC.score
from Student,Course,SC 
where Student.SId = SC.SId and SC.CId = Course.CId
and SC.score < 60;

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

select SC.SId,Student.Sname
from SC join Student on SC.SId = Student.SId
where SC.Score > 80 and SC.CId = '01';

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

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

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

select Student.SId,Student.Sname,SC.score
from Student join SC on Student.SId = SC.SId
join Course on SC.CId = Course.CId
join Teacher on Teacher.TId = Course.TId
where Teacher.Tname = '張三';
order by SC.score desc limit 1;

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

-- 查詢張三老師授課的學生中最高分數,查詢所有等於最高分的

-- -- 先查詢最高分數
-- select max(SC.score) 
-- from SC join Course on SC.CId = Course.CId
-- join Teacher on Teacher.TId = Course.TId
-- where Teacher.Tname = '張三'

-- 追加 分數條件
select Student.SId,Student.Sname,SC.score
from Student join SC on Student.SId = SC.SId
join Course on SC.CId = Course.CId
join Teacher on Teacher.TId = Course.TId
where Teacher.Tname = '張三' 
and SC.score = (select max(SC.score) 
from SC join Course on SC.CId = Course.CId
join Teacher on Teacher.TId = Course.TId
where Teacher.Tname = '張三');

-- 王宇鵬
select a.sname,b.score 
from student a join sc b on a.sid=b.sid 
and b.cid in (select cid from course where tid in (select tid from teacher where tname='張三')) 
join (select cid,max(score) m from sc group by cid) c on b.cid=c.cid and b.score=c.m;


-- 張波
select stu.*,
max(SC.score) as 成績
from Student as stu 
join SC on stu.SId = SC.SId
join course as C on SC.CId = C.CId
join Teacher as T on C.TId = T.TId
where T.Tname = '張三';

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

select  s1.CId,s1.SId,s1.Score
from SC s1 join SC s2
on s1.CId != s2.CId and s1.score = s2.score and s1.SId = s2.SId
order by s1.Score;

select distinct s1.SId,s1.CId,s1.Score
from SC s1 join SC s2 
on s1.CId != s2.CId and s1.score = s2.score
group by  s1.SId,s1.CId,s1.Score

SELECT DISTINCT s1.sid,s1.cid,s1.score 
FROM sc s1,sc s2 WHERE s1.cid != s2.cid AND s1.score = s2.score

41.查詢每門課程成績最好的前兩名

(select CId,score from SC where CId = '01' order by score desc limit 2) 
union all
(select CId,score from SC where CId = '02' order by score desc limit 2) 
union all
(select CId,score from SC where CId = '03' order by score desc limit 2) 

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

select CId,count(SId) from SC group by CId having count(SId) > 5;

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

select SId,count(CId) as num from SC group by SId having num >=2;

44.查詢選修了全部課程的學生資訊

select SId from SC group by SId 
having count(CId) = (select count(*) from Course);

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

select Stu.SId,Stu.Sname,(year(now()) - year(Stu.Sage)) as '年齡' 
from Student as stu;

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

TIMESTAMPDIFF() 從日期時間表示式中減去間隔
https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html

select student.SId,student.Sname,
TIMESTAMPDIFF(YEAR,student.Sage,CURDATE()) from student

47.查詢本週過生日的學生

返回日期從範圍內的數字日曆星期1到53

select *from student 
where WEEKOFYEAR(student.Sage)=WEEKOFYEAR(CURDATE());

48.查詢下週過生日的學生

select *
from student 
where WEEKOFYEAR(student.Sage)=WEEKOFYEAR(CURDATE())+1;

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

select *
from student 
where MONTH(student.Sage)=MONTH(CURDATE());

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

select *
from student 
where MONTH(student.Sage)=MONTH(CURDATE())+1;

資料Data

--學生表 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-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' , '數學' , '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);