# 50道MySQL練習題
###### tags: `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
```sql=
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
```sql=
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
```sql=
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
``` sql=
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 "課程成績高的學生的資訊及課程分數
以子查詢的方式先搜尋出01,02課程的資料,進行成績大小的比較,在將名字補上
```sql=
use sqlpractice;
select Student.SId,Student.Sname,Student.Sage,Student.Ssex,c1.CId,c1.score,c2.CId,c2.score
from (SELECT * FROM SC where CId = "01") as c1
inner join (SELECT * FROM SC where CId = "02") as c2 on c1.SId = c2.SId
left join Student on c2.SId = Student.SId
where c1.score > c2.score;
```
- [x] 2. 查詢同時存在" 01 "課程和" 02 "課程的情況
首先搜尋出只有01課程和02課程再將他們Join
```sql=
Select a1.Sid as a1sid from
(SELECT * FROM sqlpractice.SC where Cid = "01") as a1
inner join
(select * From sqlpractice.SC where Cid = "02") as a2
on a1.Sid = a2.Sid;
```
- [x] 3. 查詢存在" 01 "課程但可能不存在" 02 "課程的情況(不存在時顯示為 null )
```sql=
use sqlpractice;
select * from
(select * from SC where Cid = "01") as t1
left join
(select * from SC where Cid = "02") as t2
on t1.SId = t2.Sid
where t2.Sid is null;
```
- [x] 4. 查詢不存在" 01 "課程但存在" 02 "課程的情況
```sql=
select
t1.SId as t1Sid,t1.CId as t1Cid,t1.score as t1score,
t2.SId as t2Sid,t2.CId as t2Cid,t2.score as t2Score
from
(select * from SC where CId = "02") as t1
left join
(select * from SC where CId = "01") as t2
on
t1.SId = t2.SId
where
t2.SId is NULL;
```
- [x] 5. 查詢平均成績大於等於 60 分的同學的學生編號和學生姓名和平均成績
```sql=
use sqlpractice;
SELECT
Student.SId, Student.Sname,STUDENT_SCORE_AVG.SCORE_AVG
FROM
Student
inner join
(SELECT SId,avg(score) as SCORE_AVG FROM sqlpractice.SC group by SId) as STUDENT_SCORE_AVG
on
STUDENT_SCORE_AVG.SId = Student.SId
where
STUDENT_SCORE_AVG.SCORE_AVG >=60;
```
- [x] 6. 查詢在 SC 表存在成績的學生資訊
```sql=
use sqlpractice;
SELECT
Student.SId, Student.Sname
FROM
Student
right join
(SELECT distinct SId FROM SC ) as STUDENT_SCORE
on
STUDENT_SCORE.SId = Student.SId;
```
- [x] 7. 查詢所有同學的學生編號、學生姓名、選課總數、所有課程的總成績(沒成績的顯示為 null )
```sql=
use sqlpractice;
SELECT
Student.SId, Student.Sname,STUDENT_SCORE.count_cid,STUDENT_SCORE.score_sum
FROM
Student
left join
(SELECT SId,sum(score) as score_sum,count(CId) as count_cid FROM SC group by SId) as STUDENT_SCORE
on
STUDENT_SCORE.SId = Student.SId;
```
- [x] 8. 查有成績的學生資訊
```sql=
use sqlpractice;
SELECT
Student.SId, Student.Sname,STUDENT_SCORE.count_cid,STUDENT_SCORE.score_sum
FROM
Student
right join
(SELECT SId,sum(score) as score_sum,count(CId) as count_cid FROM SC group by SId) as STUDENT_SCORE
on
STUDENT_SCORE.SId = Student.SId;
```
- [x] 9. 查詢「李」姓老師的數量
```sql=
use sqlpractice;
SELECT count(Tname),Tname FROM Teacher Where Tname like '李%';
```
- [x] 10. 查詢學過「張三」老師授課的同學的資訊
```sql=
select
Student.SId,Student.Sname
from
Student
inner join
(select
SId,score
from
SC
inner join
(select
CId
from
Course
inner join
(SELECT
TId
FROM
sqlpractice.Teacher
where
Tname = "張三"
) as
teacher
on
Course.TId = Teacher.TId
) as
course_teacher
on
SC.CId = course_teacher.CId
) as
teacher_SC
on
teacher_SC.SId = Student.SId;
```
- [x] 11. 查詢沒有學全所有課程的同學的資訊
```sql=
select
Student.SId,Student.Sname,Student.Sage,Student.Ssex
from
Student
where
Student.SId
not in
(select
SCTB.SId
from
(SELECT SId,count(CId) as numberI FROM SC group by SId)as
SCTB
inner join
(SELECT count(CId) as cidcount FROM Course) as
course_totle
on
SCTB.numberI = course_totle.cidcount);
```
```sql=
SELECT
Student.*
FROM
Student
WHERE
Student.SId
NOT IN
(SELECT
SId
FROM
SC
GROUP BY
SId
HAVING
COUNT(CId) = (SELECT COUNT(*) FROM Course));
```
- [x] 12. 查詢至少有一門課與學號為" 01 "的同學所學相同的同學的資訊
```sql=
Select
Student.*
from
Student
inner join
(SELECT distinct SId FROM sqlpractice.SC where Sid != "01" && CId in (SELECT CId FROM sqlpractice.SC where SId = "01"))as
studentIdTb
on
Student.Sid = studentIdTb.SId;
```
- [x] 13. 查詢和" 01 "號的同學學習的課程 完全相同的其他同學的資訊
```sql=
SELECT
SId
FROM
sqlpractice.SC
group by
SId
having
group_concat(CId ORDER BY CId)
=
(SELECT
group_concat(CId ORDER BY CId) as
str1
FROM
SC
where
SId = "01"
);
```
- [x] 14. 查詢沒學過"張三"老師講授的任一門課程的學生姓名
```sql=
use sqlpractice;
select
Student.Sname
from
Student
where
SId
not in
(SELECT
SC.SId
FROM
SC
inner join
(SELECT
Teacher.Tname, Course.CId
FROM
Teacher
left join
Course
on
Teacher.TId = Course.TId
where
Teacher.Tname = "張三")as
teacher_course
on
teacher_course.CId = SC.Cid
);
```
- [x] 15. 查詢兩門及其以上不及格課程的同學的學號，姓名及其平均成績
```sql=
select
Student.SId,Student.Sname,noSt.avg_score
from
Student
inner join
(select
SId,count(ss.SId)as num,avg(score)as avg_score
from
(select
SId,score
FROM
SC
where
score < 60)as ss
group by
ss.SId
having
num>=2)as noSt
on
Student.SId = noSt.SId;
```
- [x] 16. 檢索" 01 "課程分數小於 60，按分數降序排列的學生資訊
```sql=
SELECT
Student.SId,Student.Sname,Student.Sage,Student.Ssex,sct.score
from
Student
right join
(SELECT
SId,CId,score
FROM
sqlpractice.SC
where
CId = "01"
AND
score<60)as
sct
on
Student.SId = sct.SId
order by
sct.score
asc;
```
- [x] 17. 按平均成績從高到低顯示所有學生的所有課程的成績以及平均成績
```sql=
use sqlpractice;
select
Student.SId,Student.Sname,Student.Sage,
Student.Ssex,student_avg_score.avg_score,
sc01.score as sc01score,sc02.score as sc02score,
sc03.score as sc03score
from
Student
left join
(SELECT SId,avg(score)as avg_score FROM SC group by SId)as student_avg_score
on
Student.SId = student_avg_score.SId
left join
(SELECT SId,score FROM SC where CId = "01")as sc01
on
Student.SId = sc01.SId
left join
(SELECT SId,score FROM SC where CId = "02")as sc02
on
Student.SId = sc02.SId
left join
(SELECT SId,score FROM SC where CId = "03")as sc03
on
Student.SId = sc03.SId
order by
student_avg_score.avg_score DESC;
```
```sql=
use sqlpractice;
SELECT st.SId,GROUP_CONCAT(c.cname) 課程,GROUP_CONCAT(sc.score) 分數,AVG(sc.score) 平均分
FROM Student st
LEFT JOIN SC sc
ON st.SId=sc.SId
INNER JOIN Course c
ON sc.CId=c.CId
GROUP BY sc.SId
ORDER BY AVG(sc.score) DESC;
```
- [x] 18. 查詢各科成績最高分、最低分和平均分：
- 以如下形式顯示：
- 課程 ID，課程 name，最高分，最低分，平均分，及格率，中等率，優良率，優秀率
- 及格為>=60，中等為：70-80，優良為：80-90，優秀為：>=90
```sql=
use sqlpractice;
SELECT
Course.CId as "課程 ID",Course.Cname as "課程名稱",SC_A.maxscore as "最高分",
SC_A.minscore as "最低分",SC_A.avgscore as "平均分",SC_A.d as '及格率',
SC_A.c as '中等率',SC_A.b as '優良率',SC_A.a as '優秀率'
FROM
Course
LEFT JOIN
(SELECT
SC.CId,Max(SC.score) as 'maxscore',min(SC.score) as "minscore",AVG(SC.score) as "avgscore",
sum(case when SC.score >= 60 then 1 else 0 end)/count(*) as 'd',
sum(case when SC.score >= 70 AND SC.score < 80 then 1 else 0 end)/count(*) as 'c',
sum(case when SC.score >= 80 AND SC.score < 90 then 1 else 0 end)/count(*) as 'b',
sum(case when SC.score >= 90 then 1 else 0 end)/count(*) as 'a'
FROM SC GROUP BY SC.CId ) as
SC_A
ON
SC_A.CId=Course.CId;
```
- [x] 19. 要求輸出課程號和選修人數，查詢結果按人數降序排列，若人數相同，按課程號升序排列
```sql=
SELECT
CId,count(SId)as sid_count
FROM
sqlpractice.SC
group by
CId
order by
sid_count
ASC,
CId
desc;
```
- [x] 20. 按各科成績進行排序，並顯示排名， Score 重複時保留名次空缺
```sql=
use sqlpractice;
select
a.SId,a.CId,count(b.score)+1 as ranka
from
SC a
left join
SC b
on
a.CId = b.CId and (a.score < b.score or (a.score = b.score and a.sid > b.sid))
group by
a.SId,a.CId
order by
a.cid,ranka;
```
解題思路：
搜尋出同一門課程且成績比自身大的或者當同成績且代號比較大後，在計算數量就可以知道自己是第幾名。
實作方式：
使用Left join遍歷同一張SC表格取得成績大的有幾個，在使用group by配合count涵式計算名次,order by 排序
- [x] 21. 按各科成績進行排序，並顯示排名， Score 重複時合併名次
```sql=
select
a.sid,a.cid,a.score,count(b.score)+1 as ranka
from
sc a
left join
sc b
on
a.cid = b.cid AND a.score < b.score
group by a.sid,a.cid,a.score
order by a.cid ASC, ranka ASC;
```
- [x] 22. 查詢學生的總成績，並進行排名，總分重複時保留名次空缺
```sql=
SELECT
SId,sum(score)as aaa,
RANK() OVER (ORDER BY sum(score) DESC) as an
FROM
sqlpractice.SC
group by
SId;
```
- [x] 23. 查詢學生的總成績，並進行排名，總分重複時不保留名次空缺
```sql=
SELECT
SId,sum(score)as aaa,
RANK() OVER (ORDER BY sum(score) DESC) as an
FROM
sqlpractice.SC
group by
SId;
```
- [x] 24. 統計各科成績各分數段人數：
- 課程編號，課程名稱，[100-85]，[85-70]，[70-60]，[60-0] 及所佔百分比
```sql=
SELECT
Course.CId,
Course.Cname,
sum(case when SC.score >= 0 AND SC.score < 60 then 1 else 0 end) as sixty_folloew,
sum(case when SC.score >= 0 AND SC.score < 60 then 1 else 0 end)/count(*) as sixty_folloew_percentage,
sum(case when SC.score >= 60 AND SC.score < 70 then 1 else 0 end) as sixty_seventy,
sum(case when SC.score >= 60 AND SC.score < 70 then 1 else 0 end)/count(*) as sixty_seventy_percentage,
sum(case when SC.score >= 70 AND SC.score < 85 then 1 else 0 end) as seventy_eightyfive,
sum(case when SC.score >= 70 AND SC.score < 85 then 1 else 0 end)/count(*) as seventy_eightyfive_percentage,
sum(case when SC.score >= 85 AND SC.score < 100 then 1 else 0 end) as eightyfive_hundred,
sum(case when SC.score >= 85 AND SC.score < 100 then 1 else 0 end)/count(*) as eightyfive_hundred_percentage
FROM
sqlpractice.SC
left join
Course on SC.CId = Course.CId
group by
CId,Cname;
```
- [x] 25. 查詢各科成績前三名的記錄
```sql=
use sqlpractice;
Select
a.CId,a.SId,a.score,count(b.score)+1 as ranking
from
SC as a
left join
SC as b
on
a.CId = b.CId AND (a.score < b.score or(a.score = b.score and a.SId > b.SId))
group by
a.CId,a.SId,a.score
having
ranking <= 3
order by
CId ASC,ranking ASC;
```
- [x] 26. 查詢每門課程被選修的學生數
```sql=
use sqlpractice;
Select
Course.CId,Course.Cname,course_quantity.quantity
from
Course
left join
(SELECT
CId,count(*) as quantity
FROM
SC
Group by
CId) as
course_quantity
on
Course.CId = course_quantity.CId;
```
- [x] 27. 查詢出只選修兩門課程的學生學號和姓名
```sql=
use sqlpractice;
Select
Student.SId,Student.Sname,course_quantity.quantity
from
Student
right join
(SELECT
SId,count(*) as quantity
FROM
SC
Group by
SId
having
quantity = 2) as
course_quantity
on
Student.SId = course_quantity.SId;
```
- [x] 28. 查詢男生、女生人數
```sql=
use sqlpractice;
SELECT
Ssex , count(*) as quantity
FROM
Student
Group By
Ssex;
```
- [x] 29. 查詢名字中含有「風」字的學生資訊
```sql=
use sqlpractice;
SELECT
SId,Sname,Sage,Ssex
FROM
Student
where
Sname
like
'%風%';
```
- [x] 30. 查詢同名同性學生名單，並統計同名人數
```sql=
use sqlpractice;
SELECT
Sname,count(Sname) as quantity
FROM
Student
group by
Sname
having
quantity > 1;
```
- [x] 31. 查詢 1990 年出生的學生名單
```sql=
use sqlpractice;
SELECT
`Sage`, `SId`, `Sname`, `Ssex`
FROM
Student
where
Sage
between
"1990-01-01 00:00:00" AND "1990-12-31 23:59:00";
```
- [x] 32. 查詢每門課程的平均成績，結果按平均成績降序排列，平均成績相同時，按課程編號升序排列
```sql=
use sqlpractice;
SELECT
CId,avg(score) as avg_score
FROM
SC
group by
CId
order by
avg_score DESC, CId ASC;
```
- [x] 33. 查詢平均成績大於等於 85 的所有學生的學號、姓名和平均成績
```sql=
Select
Student.SId,Student.Sname,A.avg_score
from
Student
right join
(SELECT
SId,avg(score) as avg_score
FROM
SC
group by
SId
having
avg_score > 85)as
A
on
Student.SId = A.SId;
```
- [x] 34. 查詢課程名稱為「數學」，且分數低於 60 的學生姓名和分數
```sql=
SELECT
Cname_Math.CId,Cname_Math.Cname,Student.SId,Student.Sname,SC_low.score
FROM
Student
right join
(SELECT
SC.CId,SC.SId,SC.score
FROM
SC
where
score < 60) as
SC_low
on
SC_low.SId = Student.SId
right join
(SELECT
Course.CId,Course.Cname
FROM
Course
where
Cname = '數學')as
Cname_Math
on
SC_low.CId = Cname_Math.CId;
```
- [x] 35. 查詢所有學生的課程及分數情況（存在學生沒成績，沒選課的情況）
```sql=
SELECT Student.SId,Student.Sname,sc_course01.score,sc_course02.score,sc_course03.score FROM sqlpractice.Student
left join (SELECT SId,score FROM SC where CId = '01')as sc_course01 on Student.SId = sc_course01.SId
left join (SELECT SId,score FROM SC where CId = '02')as sc_course02 on Student.SId = sc_course02.SId
left join (SELECT SId,score FROM SC where CId = '03')as sc_course03 on Student.SId = sc_course03.SId;
```
```sql=
SELECT
Student.SId,Student.Sname,
MAX(CASE WHEN Course.Cname='語文' then SC.score else 0 END) as '語文'
,MAX(CASE WHEN Course.Cname='數學' then SC.score else 0 END) as '數學'
,MAX(CASE WHEN Course.Cname='英語' then SC.score else 0 END) as '英語'
FROM sqlpractice.Student
left join SC on Student.SId = SC.SId
left join Course on SC.CId = Course.CId
group by Student.SId,Student.Sname;
```
- [x] 36. 查詢任何一門課程成績在 70 分以上的姓名、課程名稱和分數
```sql=
use sqlpractice;
SELECT Student.SId,Student.Sname,Course.CId,Course.Cname,SC.score
FROM SC
join Student on Student.SId = SC.SId
join Course on Course.CId = SC.CId
where SC.score >= 70;
```
- [x] 37. 查詢不及格的課程
```sql=
use sqlpractice;
SELECT Student.SId,Student.Sname,Course.CId,Course.Cname,SC.score FROM SC
join Student on Student.SId = SC.SId
join Course on Course.CId = SC.CId
where SC.score <60;
```
- [x] 38. 查詢課程編號為 01 且課程成績在 80 分以上的學生的學號和姓名
```sql=
use sqlpractice;
SELECT Student.SId,Student.Sname,SC.score FROM SC
join Student on Student.SId = SC.SId
where SC.CId = 01 AND SC.score >= 80;
```
- [x] 39. 求每門課程的學生人數
```sql=
use sqlpractice;
SELECT Course.CId,Course.Cname,Course_Number.num FROM Course
join (select CId,count(CId) as num from SC Group By CId)as Course_Number
on Course.CId = Course_Number.CId;
```
- [x] 40. 成績不重複，查詢選修「張三」老師所授課程的學生中，成績最高的學生資訊及其成績
```sql=
explain SELECT Student.*,SC.CId,score
FROM Student JOIN SC ON Student.SId = SC.SId
JOIN Course ON SC.CId = Course.CId
JOIN Teacher ON Course.TId = Teacher.TId
WHERE Tname = '張三'
ORDER BY score DESC LIMIT 1
```
- [x] 41. 成績有重複的情況下，查詢選修「張三」老師所授課程的學生中，成績最高的學生資訊及其成績
```sql=
select Student.*,SC.score,Course.CId,Course.Cname,Teacher.TId,Teacher.Tname
FROM Student
right join SC on Student.SId = SC.SId
left join Course on SC.CId = Course.CId
left join Teacher on Course.CId = Teacher.TId
where Teacher.Tname = "張三" and SC.score = (
select max(SC.score)
from SC
left join Course on SC.CId = Course.CId
left join Teacher on Course.CId = Teacher.TId
Group by Teacher.Tname
having Teacher.Tname = "張三");
```
- [x] 42. 查詢不同課程成績相同的學生的學生編號、課程編號、學生成績
```sql=
SELECT SId,CId,score
FROM SC
where SId in (SELECT SId FROM SC group by score,SId having count(score) > 1 );
```
```sql=
SELECT distinct s1.SId,s1.CId,s1.score FROM SC as s1
left join SC as s2
on s1.sid = s2.sid
where s1.score = s2.score and s1.CId != s2.CId;
```
- [x] 43. 查詢每門功課成績最好的前兩名
```sql=
SELECT s1.* FROM SC s1 WHERE
(
SELECT COUNT(1) FROM SC s2 WHERE
s1.CId = s2.CId AND s2.score >= s1.score
)<=2
ORDER BY s1.CId,s1.score DESC;
```
- [x] 44. 統計每門課程的學生選修人數（超過 5 人的課程才統計）。
```sql
select Course.CId,Course.Cname,sc_num.num from Course right join
(SELECT CId,count(SId) as num FROM SC group by CId having num > 5)as
sc_num on Course.CId = sc_num.CId;
```
- [x] 45. 檢索至少選修兩門課程的學生學號
```sql=
Select SId,count(CId) as cid_num from SC group by SId having cid_num >=2;
```
- [x] 46. 查詢選修了全部課程的學生資訊
```sql=
Select SId,count(CId) as cid_num from SC group by SId having cid_num = (select count(*) from Course);
```
- [x] 47. 查詢各學生的年齡，只按年份來算·
```sql=
use sqlpractice;
SELECT abcTable.*,(abcTable.nowYear - abcTable.stuYear) as a FROM
(SELECT Student.*,
EXTRACT(YEAR FROM now())as nowYear,
EXTRACT(YEAR FROM Student.Sage)as stuYear
FROM Student) as
abcTable;
```
```sql=
SELECT Student.*,(EXTRACT(YEAR FROM now())-EXTRACT(YEAR FROM Student.Sage)) age FROM Student
```
- [x] 48. 按照出生日期來算，當前月日 < 出生年月的月日則，年齡減一
```sql=
use sqlpractice;
SELECT
STUDENT_AGE_TABLE.Sid,
STUDENT_AGE_TABLE.Sname,
STUDENT_AGE_TABLE.Ssex,
IF((STUDENT_AGE_TABLE.nowMonth > STUDENT_AGE_TABLE.studentMonth) ||
(STUDENT_AGE_TABLE.nowMonth = STUDENT_AGE_TABLE.studentMonth && STUDENT_AGE_TABLE.nowDay >= STUDENT_AGE_TABLE.studentDay),
STUDENT_AGE_TABLE.nowYear - STUDENT_AGE_TABLE.studentYear,
STUDENT_AGE_TABLE.nowYear - STUDENT_AGE_TABLE.studentYear - 1) as age
FROM
(SELECT
Student.Sid,
Student.Sname,
Student.Ssex,
EXTRACT(YEAR FROM Student.Sage)as studentYear,
EXTRACT(MONTH FROM Student.Sage)as studentMonth,
EXTRACT(DAY FROM Student.Sage)as studentDay,
EXTRACT(YEAR FROM NOW())as nowYear,
EXTRACT(MONTH FROM NOW())as nowMonth,
EXTRACT(DAY FROM NOW())as nowDay
FROM Student) as STUDENT_AGE_TABLE;
```
- [x] 49. 查詢本週過生日的學生
```sql=
USE sqlpractice;
SELECT T1.SId,T1.Sname,T1.Ssex,T1.Sage FROM Student T1
INNER JOIN (SELECT T2.SId,DATE_FORMAT(T2.Sage,'%m-%d') AS SAGE_MONTH_DAY FROM Student T2) AS T3
ON T1.SId = T3.SId
INNER JOIN (SELECT DATE_FORMAT(T4.NOW_WEEK_DATE,'%m-%d') AS NOW_WEEK_MONTH_DAY
FROM (SELECT (DATE_SUB(CURDATE(),INTERVAL DATE_FORMAT(CURDATE(),'%w') DAY)) AS NOW_WEEK_DATE
UNION SELECT DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DATE_FORMAT(CURDATE(),'%w') DAY),INTERVAL 1 DAY)
UNION SELECT DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DATE_FORMAT(CURDATE(),'%w') DAY),INTERVAL 2 DAY)
UNION SELECT DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DATE_FORMAT(CURDATE(),'%w') DAY),INTERVAL 3 DAY)
UNION SELECT DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DATE_FORMAT(CURDATE(),'%w') DAY),INTERVAL 4 DAY)
UNION SELECT DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DATE_FORMAT(CURDATE(),'%w') DAY),INTERVAL 5 DAY)
UNION SELECT DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DATE_FORMAT(CURDATE(),'%w') DAY),INTERVAL 6 DAY)) AS T4 ) AS T5
ON T3.SAGE_MONTH_DAY = T5.NOW_WEEK_MONTH_DAY;
```
- [x] 50. 查詢下週過生日的學生
```sql=
SELECT *
FROM
(SELECT SId,Sname,Ssex,Sage,date_format(Sage,'%m-%d') as monthAndDay FROM Student)as t3
inner join
(select date_format(date_time,'%m-%d') as week_time
from (SELECT date_add(curdate(),interval 7-date_format(curdate(),'%w') day) as date_time
UNION SELECT date_add(date_add(curdate(),interval 7-date_format(curdate(),'%w') day),interval 1 day)
UNION SELECT date_add(date_add(curdate(),interval 7-date_format(curdate(),'%w') day),interval 2 day)
UNION SELECT date_add(date_add(curdate(),interval 7-date_format(curdate(),'%w') day),interval 3 day)
UNION SELECT date_add(date_add(curdate(),interval 7-date_format(curdate(),'%w') day),interval 4 day)
UNION SELECT date_add(date_add(curdate(),interval 7-date_format(curdate(),'%w') day),interval 5 day)
UNION SELECT date_add(date_add(curdate(),interval 7-date_format(curdate(),'%w') day),interval 6 day))as t1)as t2
on t3.monthAndDay = t2.week_time
```
- [x] 51. 查詢本月過生日的學生
```sql=
SELECT * FROM Student where (EXTRACT(MONTH FROM now())) = EXTRACT(MONTH FROM Sage);
```
```sql=
SELECT abcTable.SId FROM (SELECT Student.*,EXTRACT(MONTH FROM now())as nowMonth,EXTRACT(MONTH FROM Student.Sage)as stuMonth FROM Student) as abcTable where abcTable.nowMonth = abcTable.stuMonth;
```
- [x] 52. 查詢下月過生日的學生
```sql=
SELECT * FROM Student where (EXTRACT(MONTH FROM now())+1) = EXTRACT(MONTH FROM Sage);
```
join 遍歷
group by 遍歷
Sign in with Wallet
Connect another wallet