非標準答案
-- 2.1 查詢出每門課都大于60分的學姓名


SELECT user_id FROM score WHERE score > 60 GROUP BY user_id HAVING COUNT(1) ?>2;

-- 解答

SELECT user_name FROM user WHERE user_id IN (SELECT user_id FROM score ?WHERE score > 60 GROUP BY user_id HAVING COUNT(1) >2);

-- 2.2 查詢出總分大于220分的 學生姓名，與總分數

SELECT user_id,SUM(score) FROM score ?GROUP BY user_id HAVING SUM(score) ?>220;

-- 解答

SELECT user_name FROM user WHERE user_id IN (SELECT user_id FROM score ? GROUP BY user_id HAVING SUM(score) >220);

-- 2.3 用sql語句把每個學生的總分數更新到User用戶表 的user_score 學生總分數 字段里

-- 解答

UPDATE `user` u ?SET user_score = (SELECT SUM(score) ?FROM score WHERE ?user_id = u.user_id ?GROUP BY user_id );

-- 2.4 查詢出學生的 班級名稱、姓名、課程名稱、分數

SELECT c.class_name,u.user_name,s.course,s.score FROM user u JOIN score s ?ON u.user_id = s.user_id JOIN class c ON c.class_id = u.class_id;

-- 2.5 查詢出每個學生的班級、姓名、平均分、最高分數、最低分數,保留兩位小數

SELECT user_id, ROUND(avg(score),2) ascore , max(score) xscore , min (score)iscore FROM score GROUP BY user_id;

-- 解答
SELECT c.class_name,u.user_name,a.ascore '平均分', a.xscore '最高分 數',a.iscore '最低分數'
FROM user u JOIN class c ON c.class_id = u.class_id
JOIN (
SELECT user_id, ROUND(avg(score),2) ascore , max(score) xscore , min (score)iscore FROM score GROUP BY user_id
) a
ON a.user_id = u.user_id;

-- 2.6 查詢出學生的 班級名稱、最高分的課程、最高分數

-- 查詢每個user_id 最高分數
SELECT user_id,max(score) xscore FROM ?score GROUP BY user_id;

-- 查詢每個user_id 最高分數 最高分的課程 ?s1
SELECT s.user_id,course,xscore FROM ?score s JOIN
(SELECT user_id,max (score) xscore FROM ?score GROUP BY user_id)a
ON a.user_id = s.user_id ?WHERE score = a.xscore;

-- 查詢出學生的 班級名稱 user_id ? ? ? ? ?s2
SELECT c.class_name,u.user_id,u.user_name FROM user u JOIN class c ON ?u.class_id = c.class_id;

-- 解答a
SELECT s2.class_name,s2.user_name,s1.course 最高分課程,s1.xscore 最高分 ?FROM
(
SELECT s.user_id,course,xscore FROM ?score s JOIN
(SELECT user_id,max (score) xscore FROM ?score GROUP BY user_id)a
ON a.user_id = s.user_id ?WHERE score = a.xscore
) s1
JOIN (
SELECT c.class_name,u.user_id,u.user_name FROM user u JOIN class c ON ?u.class_id = c.class_id
) s2
ON s1.user_id = s2.user_id;

-- 解答b
select b.class_name,b.user_name,b.d 最高分,g.course 最高分課程 from score g
join(
select c.class_name,a.user_name,a.d from class c
? ?join(
? ? select u.class_id,s.user_id,u.user_name,max(score) d from score s left join ?user u on s.user_id=u.user_id group by s.user_id
)a on a.class_id=c.class_id
)b

on b.d=g.score ;