最近求職筆試遇到的SQL考題是：串三張表並且依據項目分組後取最大值，當時寫得不太完整，所以自己練習了請AI設計的題目。把題目、解答、自己容易犯錯的地方及優化的建議筆記起來，記錄在這篇。

語法回顧

GROUP BY 與 HAVING

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

CTE

WITH cte_name(自定義名稱) AS (
    SELECT ...
)
SELECT ...
FROM cte_name;

資料表格式

假設有4張資料表：

• Table sc

• Table student

• Table course

• Table teacher

題目

讀者也可以先寫寫看，再往下看解答。

1. 找出有超過兩門課成績不及格（< 60分）的學生學號（sid）。
2. 找出修課總分超過 200 分的學生學號（sid）與其總分。
3. 列出每位老師（tname）教過的課程數量，並只顯示教超過 2 門課的老師。
4. 找出平均分數高於 85 分的學生學號與其平均分。
5. 找出至少修了 3 門課的學生姓名（sname）與修課數。
6. 找出每門課（cid）被修過的次數（學生數），並只顯示有超過 5 位學生選修的課。
7. 找出平均分數小於 60 分的課程名稱（cname）與平均分。
8. 列出每位學生的最高成績，並只顯示最高成績超過 95 分的學生（顯示 sname 與分數）。
9. 找出有學生的平均成績達到 90 分以上的老師姓名（tname）。
10. 找出男生（ssex = 'M'）中修課總分超過 250 分的學生姓名與總分。
11. 找出修過「張三」老師課的學生平均分數，並只顯示平均分數高於 80 分的學生姓名（sname）與平均分。

解答

1.找出有超過兩門課成績不及格（< 60分）的學生學號（sid）。

select sid from sc
where score < 60 
group by sid
having count(*) > 2;

2.找出修課總分超過 200 分的學生學號（sid）與其總分。

select s.sid, sum(sc.score) as sum from student s inner join sc 
on sc.sid = s.sid
group by s.sid 
having sum(sc.score) > 200;

3.列出每位老師（tname）教過的課程數量，並只顯示教超過 2 門課的老師。

本題的題目其實不太恰當，因為教師姓名有可能完全一樣，合適的分組依據應為教師代碼。

如果寫成下述這樣在MySQL中是正確的，但在PostgreSQL則語法錯誤。

select t.tid, t.tname, count(c.cid) as cnt
from teacher t inner join course c
on t.tid = c.tid
group by t.tid
having count(c.cid) > 2;

因為除了聚合函數(count、sum、max、min、avg)外，查詢(select)的欄位必須全部出現在分組條件(group by)中，如下述：

select t.tid, t.tname, count(c.cid) as cnt
from teacher t inner join course c
on t.tid = c.tid
group by t.tid, t.tname
having count(c.cid) > 2;

4.找出平均分數高於 85 分的學生學號與其平均分。

select s.sid, avg(sc.score) as average
from student s inner join sc
on s.sid = sc.Sid
group by s.sid
having avg(sc.score) > 85;

5.找出至少修了 3 門課的學生姓名（sname）與修課數。

雖然是要查詢學生姓名，但合適的分組依據應為學號，因為學號才是完全不可能重複的。同樣地，要將查詢欄位都寫進分組條件中，確保SQL在PostgreSQL中也適用。

select s.sid, s.sname, count(sc.cid)
from student s inner join sc
on s.sid = sc.sid
group by s.sid, s.sname
having count(sc.cid) >= 3;

6.找出每門課（cid）被修過的次數（學生數），並只顯示有超過 5 位學生選修的課。

select sc.cid, count(distinct sc.sid) as cnt
from sc
group by sc.cid
having count(sc.sid) > 5;

7.找出平均分數小於 60 分的課程名稱（cname）與平均分。

雖然是要查詢課程名稱，但合適的分組依據應為不會重複的課程代號。此題也要將查詢欄位都寫進分組條件中，確保SQL在PostgreSQL中也適用。

select c.cid, c.cname, avg(sc.score) as cnt 
from sc
inner join course c
on c.cid = sc.cid
group by c.cid, c.cname
having avg(sc.score) < 60;

8.列出每位學生的最高成績，並只顯示最高成績超過 95 分的學生（顯示 sname 與分數）。

select s.sid, s.sname, max(sc.score) as maximum
from student s
inner join sc
on s.sid = sc.sid
group by s.sid, s.sname
having max(sc.score) > 95;

9.找出有學生的平均成績達到 90 分以上的老師姓名（tname）。

因為一個老師只會教一個課程，所以此題的邏輯是先將分數依序按照課程、教師代碼分類，為了符合postgreSQL的語法要求，group by後再加上教師名稱(tname)。

select t.tname
from teacher t
inner join course c
on t.tid = c.tid
inner join sc
on sc.cid = c.cid
group by sc.cid, t.tid, t.tname 
having avg(sc.score) >= 90;

10.找出男生（ssex = 'M'）中修課總分超過 250 分的學生姓名與總分。

select s.sid, s.sname, sum(sc.score) as sum_score
from student s
inner join sc
on sc.Sid = s.sid
where s.ssex = 'M'
group by s.sid, s.sname
having sum(sc.score) > 250;

11.找出修過「張三」老師課的學生平均分數，並只顯示平均分數高於 80 分的學生姓名（sname）與平均分。

這題目有點小瑕疵，因為中文姓名有可能重複，只有教師代碼是唯一的，不過這裡我們還是先將錯就錯完成練習。

select s.sid, s.sname, avg(sc.score) as average
from student s
inner join sc on s.sid = sc.sid
inner join course c on sc.cid = c.cid
inner join teacher t on c.tid = t.tid
where t.tname = '張三'
group by s.sid, s.sname
having avg(sc.score) > 80;

多表串連時，可以使用Common Table Expression(CTE)表示，這在製作報表時尤其實用。

with course_of_chang as (
    select cid
    from course c
    join teacher t on c.tid = t.tid
    where t.tname = '張三'
)
select s.sid, s.sname, avg(sc.score) as average
from student s
join sc on s.sid = sc.sid
join course_of_chang z on sc.cid = z.cid
group by s.sid, s.sname
having avg(sc.score) > 80;

但是CTE寫法只有 MySQL 8 才可使用，MySQL 5 則會顯示語法錯誤。

重點筆記

• 使用group by 時，建議select到的欄位名稱(除了聚合函數)都要一併寫在group by後，滿足postgreSQL的語法正確性。
• CTE寫法在mysql 8後才可使用。