今天的習題又來了

不過遺憾的是，本人技術不到家今天的部落格就找人代寫咯（我的師傅）。
本文為博主原創文章，遵循 CC 4.0 BY-SA 版權協議轉自：吮指原味雞毛毛
一個很棒的博主
網上關於這套練習題較多使用的是比較老的mysql版本，我使用的是 Server version: 8.0.15 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);
練習題目
查詢" 01 “課程比” 02 “課程成績高的學生的資訊及課程分數 1.1 查詢同時存在” 01 “課程和” 02 “課程的情況 1.2 查詢存在” 01 “課程但可能不存在” 02 “課程的情況(不存在時顯示為 null ) 1.3 查詢不存在” 01 “課程但存在” 02 “課程的情況
查詢平均成績大於等於 60 分的同學的學生編號和學生姓名和平均成績
查詢在 SC 表存在成績的學生資訊
查詢所有同學的學生編號、學生姓名、選課總數、所有課程的總成績(沒成績的顯示為 null ) 4.1 查有成績的學生資訊
查詢「李」姓老師的數量
查詢學過「張三」老師授課的同學的資訊
查詢沒有學全所有課程的同學的資訊
查詢至少有一門課與學號為” 01 “的同學所學相同的同學的資訊
查詢和” 01 “號的同學學習的課程 完全相同的其他同學的資訊
查詢沒學過"張三"老師講授的任一門課程的學生姓名
查詢兩門及其以上不及格課程的同學的學號，姓名及其平均成績
檢索” 01 “課程分數小於 60，按分數降序排列的學生資訊
按平均成績從高到低顯示所有學生的所有課程的成績以及平均成績
查詢各科成績最高分、最低分和平均分： 以如下形式顯示：課程 ID，課程 name，最高分，最低分，平均分，及格率，中等率，優良率，優秀率 及格為>=60，中等為：70-80，優良為：80-90，優秀為：>=90 要求輸出課程號和選修人數，查詢結果按人數降序排列，若人數相同，按課程號升序排列
按各科成績進行排序，並顯示排名， Score 重複時保留名次空缺 15.1 按各科成績進行排序，並顯示排名， Score 重複時合併名次
查詢學生的總成績，並進行排名，總分重複時保留名次空缺 16.1 查詢學生的總成績，並進行排名，總分重複時不保留名次空缺
統計各科成績各分數段人數：課程編號，課程名稱，[100-85]，[85-70]，[70-60]，[60-0] 及所佔百分比
查詢各科成績前三名的記錄
查詢每門課程被選修的學生數
查詢出只選修兩門課程的學生學號和姓名
查詢男生、女生人數
查詢名字中含有「風」字的學生資訊
查詢同名同性學生名單，並統計同名人數
查詢 1990 年出生的學生名單
查詢每門課程的平均成績，結果按平均成績降序排列，平均成績相同時，按課程編號升序排列
查詢平均成績大於等於 85 的所有學生的學號、姓名和平均成績
查詢課程名稱為「數學」，且分數低於 60 的學生姓名和分數
查詢所有學生的課程及分數情況（存在學生沒成績，沒選課的情況）
查詢任何一門課程成績在 70 分以上的姓名、課程名稱和分數
查詢不及格的課程
查詢課程編號為 01 且課程成績在 80 分以上的學生的學號和姓名
求每門課程的學生人數
成績不重複，查詢選修「張三」老師所授課程的學生中，成績最高的學生資訊及其成績
成績有重複的情況下，查詢選修「張三」老師所授課程的學生中，成績最高的學生資訊及其成績
查詢不同課程成績相同的學生的學生編號、課程編號、學生成績
查詢每門功成績最好的前兩名
統計每門課程的學生選修人數（超過 5 人的課程才統計）。
檢索至少選修兩門課程的學生學號
查詢選修了全部課程的學生資訊
查詢各學生的年齡，只按年份來算
按照出生日期來算，當前月日 < 出生年月的月日則，年齡減一
查詢本週過生日的學生
查詢下週過生日的學生
查詢本月過生日的學生
查詢下月過生日的學生
參考答案
–1. 查詢” 01 “課程比” 02 "課程成績高的學生的資訊及課程分數

select * from
(select SC.SId, SC.score from SC where SC.CId = ‘01’) as t1 inner join
(select SC.SId, SC.score from SC where SC.CId = ‘02’) as t2 on t1.SId = t2.SId
where t1.score > t2.score

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

select * from
(select SC.SId, SC.score from SC where SC.CId = ‘01’) as t1 inner join
(select SC.SId, SC.score from SC where SC.CId = ‘02’) as t2 on t1.SId = t2.SId

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

select * from
(select SC.SId, SC.score from SC where SC.CId = ‘01’) as t1 left join
(select SC.SId, SC.score from SC where SC.CId = ‘02’) as t2 on t1.SId = t2.SId

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

select *
from sc
where SId not in (select SId from sc where CId = ‘01’)
and CId = ‘02’

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

select t1.sid, t1.sname, t2.avgscore
from student as t1 inner join (
select sc.sid, avg(sc.score) as avgscore from sc group by sc.sid
having avgscore >= 60) as t2
on t1.sid = t2.sid

– 3. 查詢在 SC 表存在成績的學生資訊 注意⚠️DISTINCT⚠️關鍵字

select DISTINCT student.*
from student, sc
where student.sid = sc.sid

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

select student.sid, student.sname, t1.coursecount, t1.sumscore
from student inner join (
select sc.sid, count(sc.cid) as coursecount, sum(sc.score) as sumscore from sc group by sc.sid) as t1
on student.sid = t1.sid

– 4.1 查有成績的學生資訊 注意⚠️EXISTS⚠️關鍵字

select student.*
from student
where student.sid in (
select DISTINCT sc.sid from sc)

select *
from student
where EXISTS(select * from sc where student.SId=sc.SId)

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

select count(*)
from teacher
where teacher.Tname like ‘李%’

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

select student.*
from student, sc
where student.sid = sc.sid
and sc.cid in (select cid from course, teacher where course.tid = teacher.tid and Tname = ‘張三’)

– 同時連線多張表
select student.*
from teacher ,course ,student, sc
where teacher.Tname=‘張三’
and teacher.TId=course.TId
and course.CId=sc.CId
and sc.SId=student.SId

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

– 這種解法不包括什麼課都沒選的學生
select student.* from student, (select sid, count(cid) as coursecount from sc group by sid) as t1
where student.sid = t1.sid
and t1.coursecount<>3

select student.*
from sc ,student
where sc.SId=student.SId
GROUP BY sc.SId
Having count()<(select count() from course)

– 利用笛卡爾積可以把什麼課都沒選的同學查詢出來
select DISTINCT student.*
from
(select student.SId,course.CId
from student,course ) as t1 LEFT JOIN (SELECT sc.SId,sc.CId from sc)as t2 on t1.SId=t2.SId and t1.CId=t2.CId,student
where t2.SId is null
and t1.SId=student.SId

select student.* from student
where student.sid not in (
select student.sid from sc, student
where sc.sid = student.sid
group by sid
having count() = (select count() from course))

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

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

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

select DISTINCT student.*
from (
select student.SId, t.CId
from student ,(select sc.CId from sc where sc.SId=‘01’) as t)
as t1 LEFT JOIN sc on t1.SId=sc.SId and t1.CId=sc.CId,student
where sc.SId is not null
and t1.SId=student.SId
and t1.SId <>‘01’

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

– solution1
select *
from student
where student.SId not in (
select student.SId from (
select student.SId, t.CId from (
select course.CId from course, teacher
where course.TId = teacher.TId and teacher.Tname = ‘張三’) as t, student) as t1
right join sc on t1.SId=sc.SId and t1.CId=sc.CId, student
where t1.SId=student.SId)

– solution2
select *
from student
where student.SId not in
(
select student.SId
from student left join sc on student.SId=sc.SId
where EXISTS
(select *
from teacher ,course
where teacher.Tname=‘張三’
and teacher.TId=course.TId
and course.CId=sc.CId))

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

select student.SId, student.Sname, t2.avgscore
from student, (select sc.SId, avg(sc.score) as avgscore from sc group by sc.SId) as t2
where student.SId = t2.SId
and student.SId in (
select t1.SId from
(select * from sc
where sc.score < 60) as t1
group by t1.SId
having count(*) >= 2)

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

select student.*, t1.score from student, (
select * from sc
where sc.CId = ‘01’ and sc.score < 60) as t1
where student.SId = t1.SId
order by t1.score DESC

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

select sc.*, t1.avgscore from sc left join
(select sc.SId, avg(sc.score) as avgscore
from sc group by sc.SId) as t1
on sc.SId = t1.SId
order by t1.avgscore DESC

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

select sc.CId, max(sc.score) as ‘最高分’, min(sc.score) as ‘最低分’, avg(sc.score) as ‘平均分’,
count() as ‘選修人數’, sum(case when sc.score >= 60 then 1 else 0 end)/count() as ‘及格率’,
sum(case when sc.score >= 70 and sc.score < 80 then 1 else 0 end)/count() as ‘中等率’,
sum(case when sc.score >= 80 and sc.score < 90 then 1 else 0 end)/count() as ‘優良率’,
sum(case when sc.score >= 90 then 1 else 0 end)/count() as ‘優秀率’
from sc
group by sc.CId
order by count() DESC, sc.CId ASC

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

SELECT *, RANK() OVER(PARTITION BY sc.cid ORDER BY sc.score DESC)排名
FROM sc;

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

SELECT *, DENSE_RANK() OVER(PARTITION BY sc.cid ORDER BY sc.score DESC)排名
FROM sc;

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

SELECT sc.SId, RANK() OVER(ORDER BY sum(sc.score) DESC)排名, sum(sc.score) as sumscore

FROM sc

GROUP BY sc.SId

ORDER BY sumscore DESC;

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

SELECT sc.SId, DENSE_RANK() OVER(ORDER BY sum(sc.score) DESC)排名, sum(sc.score) as sumscore

FROM sc

GROUP BY sc.SId

ORDER BY sumscore DESC;

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

select sc.CId, sum(case when sc.score >=85 then 1 else 0 end) as num_100_85,
sum(case when sc.score >=70 and sc.score <85 then 1 else 0 end) as num_85_70,
sum(case when sc.score >= 60 and sc.score < 70 then 1 else 0 end) as num_70_60,
sum(case when sc.score < 60 then 1 else 0 end) as num_60_0,
sum(case when sc.score >=85 then 1 else 0 end)/count() as p_100_85,
sum(case when sc.score >=70 and sc.score <85 then 1 else 0 end) /count() as p_85_70,
sum(case when sc.score >= 60 and sc.score < 70 then 1 else 0 end)/count() as p_70_60,
sum(case when sc.score < 60 then 1 else 0 end)/count() as p_60_0
from sc
group by sc.CId

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

SELECT *

FROM(

SELECT *, RANK() OVER (PARTITION BY sc.CId ORDER BY sc.score) as r

FROM sc) as A

WHERE A.r <= 3

– 19. 查詢每門課程被選修的學生數
SELECT sc.CId, COUNT(*)

FROM sc

GROUP BY(sc.CId)

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

SELECT DISTINCT student.SId, student.Sname

FROM student, (

SELECT sc.SId, COUNT(*) as a

FROM sc

GROUP BY(sc.SId)) as A

WHERE student.SId = A.SId

AND A.a = 2

SELECT Distinct student.Sid, student.Sname

FROM student

where student.SId in (

SELECT sc.SId

FROM sc

GROUP BY sc.SId

having count(*)=2)

– 21. 查詢男生、女生人數

SELECT Ssex, count(*)

FROM student

GROUP BY student.Ssex

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

SELECT *

FROM student

WHERE student.sname LIKE ‘%風%’;

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

SELECT Sname, count(*)

FROM student

GROUP BY Sname

HAVING COUNT(*) >1;

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

SELECT *

FROM student

WHERE sage LIKE ‘1990-%’;

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

SELECT sc.CId, avg(sc.score) as avgscore

FROM sc

GROUP BY sc.CId

ORDER BY avg(sc.score) DESC, sc.CId

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

SELECT student.Sid, student.Sname, t.avgscore

FROM student, (

SELECT sc.SId, avg(sc.score) as avgscore

FROM sc

GROUP BY sc.SId) as t

WHERE student.SId = t.SId

AND t.avgscore >= 85

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

SELECT student.Sname, t.score

FROM student, (

SELECT sc.SId, sc.score

FROM sc 

WHERE sc.score < 60

AND sc.CId = (SELECT CId FROM course WHERE course.Cname='數學')) as t

WHERE student.SId = t.SId

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

SELECT st.*, sc.CId, sc.score

FROM student AS st

LEFT JOIN sc

ON st.SId = sc.SId

ORDER BY st.SId, sc.CId;

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

SELECT student.Sname, course.Cname, sc.score

FROM student, course, sc

WHERE student.SId = sc.SId

AND course.CId = sc.CId

AND sc.score > 70

ORDER BY sc.CId

– 30.查詢不及格的課程

SELECT sc.CId, sc.score

FROM sc

WHERE sc.score <60

ORDER BY sc.CId;

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

SELECT student.SId, student.Sname

FROM student, sc

WHERE student.SId = sc.SId

AND sc.CId = ‘01’

AND sc.score > 80

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

SELECT sc.CId, count(sc.SId)

FROM sc

GROUP BY sc.CId

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

SELECT student.*, sc.score, sc.CId

FROM student, sc

WHERE sc.CId = (SELECT course.CId

FROM course, teacher

WHERE course.TId = teacher.TId

AND teacher.Tname = ‘張三’)

AND student.SId = sc.SId

ORDER BY sc.score DESC

LIMIT 1

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

SELECT * FROM(

SELECT student.*, sc.score, sc.CId, DENSE_RANK() OVER (ORDER BY sc.score DESC)排名

FROM student, sc

WHERE sc.CId = (SELECT course.CId

FROM course, teacher

WHERE course.TId = teacher.TId

AND teacher.Tname = ‘張三’)

AND student.SId = sc.SId) as t

WHERE t.排名=‘1’

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

SELECT sc.SId, sc.CId, sc.score

FROM sc

WHERE sc.score in (

SELECT score FROM(

SELECT sc.score, count(sc.score) as count_people

FROM sc

GROUP BY sc.score) A

WHERE A.count_people > 1)

SELECT s,c,score

FROM

(

SELECT max(score),avg(score),COUNT(score),student.s,c,score

FROM student

LEFT JOIN sc

ON student.s=sc.s

GROUP BY student.s

HAVING max(score)=avg(score) AND COUNT(score)>=2

) A;

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

SELECT *
FROM (SELECT sc.SId, sc.CId, sc.score, row_number() over (PARTITION BY sc.CId ORDER BY sc.score DESC)排名
FROM sc
ORDER BY sc.score DESC) A
WHERE 排名 <= 2
ORDER BY A.CId, A.排名

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

SELECT *

FROM (

SELECT sc.CId, count(sc.SId) as count_people

FROM sc

GROUP BY sc.CId) A

WHERE A.count_people > 5

SELECT sc.CId,COUNT(sc.CId)

FROM sc

GROUP BY sc.CId

HAVING COUNT(sc.CId)>5 ;

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

SELECT sc.SId, COUNT(sc.SId)

FROM sc

GROUP BY sc.SId

HAVING COUNT(sc.SId) >= 2

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

SELECT SId, Sname, (year(curdate())-year(Sage)) as age

FROM student

ORDER BY age

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

SELECT SId, Sname,timestampdiff(year,Sage,curdate())年齡

FROM student

ORDER BY 年齡;

– 41. 查詢本週過生日的學生

SET @day =8-dayofweek(curdate());

SELECT *

FROM student

WHERE date_format(Sage, ‘%m%d’)

BETWEEN date_format(curdate(),’%m%d’)

AND date_format (date_add(curdate(),interval @day day), ‘%m%d’);

– 42.查詢下週過生日的學生

set @day =9-dayofweek(curdate());

SELECT *

FROM student

WHERE date_format(sage, ‘%m%d’)

BETWEEN date_format (date_add(curdate(),interval @day day), ‘%m%d’)

AND date_format (date_add(curdate(),interval @day+6 day), ‘%m%d’);

– 43.查詢本月過生日的學生

SELECT *

FROM student

WHERE date_format(Sage,’%m’)=date_format(curdate(),’%m’);

– 44.查詢下月過生日的學生

SELECT *

FROM student

WHERE date_format(Sage,’%m’)=date_format(date_add(curdate(),interval 1 month),’%m’);