# 11/29 每日任務

## 需要必備的知識點

* 觀看 [group by 分組資料](https://hackmd.io/gDseaambSbW1pP-H6Yek8g?view)章節

## 題目：學生國文、數學、英文成績


### 資料表與模擬資料

1. subject ：科目名稱
2. exam_score：科目成績
3. student：學生資料

-- 建立資料表
CREATE TABLE student (
 id serial PRIMARY KEY,
 name varchar(50) NOT NULL,
 class varchar(20) NOT NULL,
 created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE subject (
 id serial PRIMARY KEY,
 name varchar(50) NOT NULL,
 created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE exam_score (
 id serial PRIMARY KEY,
 student_id integer NOT NULL,
 subject_id integer NOT NULL,
 score integer NOT NULL,
 exam_date date NOT NULL,
 created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (student_id) REFERENCES student(id),
 FOREIGN KEY (subject_id) REFERENCES subject(id)
);

-- 插入測試資料
INSERT INTO student (name, class) VALUES
('小明', '三年一班'),
('小華', '三年一班'),
('小美', '三年一班'),
('小龍', '三年一班'),
('小智', '三年一班');

INSERT INTO subject (name) VALUES
('國文'),
('英文'),
('數學');

INSERT INTO exam_score (student_id, subject_id, score, exam_date) VALUES
(1, 1, 85, '2024-03-01'),
(1, 2, 92, '2024-03-01'),
(1, 3, 78, '2024-03-01'),
(2, 1, 76, '2024-03-01'),
(2, 2, 88, '2024-03-01'),
(2, 3, 95, '2024-03-01'),
(3, 1, 92, '2024-03-01'),
(3, 2, 85, '2024-03-01'),
(3, 3, 87, '2024-03-01'),
(4, 1, 78, '2024-03-01'),
(4, 2, 82, '2024-03-01'),
(4, 3, 90, '2024-03-01'),
(5, 1, 88, '2024-03-01'),
(5, 2, 79, '2024-03-01'),
(5, 3, 93, '2024-03-01');

## 題目

### 題目一：計算每個學生的總分


:::spoiler 小抄

SELECT student.name, SUM(exam_score.score) as total
FROM student
INNER JOIN exam_score ON student.id = exam_score.student_id
GROUP BY student.name;

:::

### 題目二：計算每科的平均分數並依分數排序


:::spoiler 小抄

SELECT subject.name, AVG(exam_score.score) as average
FROM subject
INNER JOIN exam_score ON subject.id = exam_score.subject_id
GROUP BY subject.name
ORDER BY average DESC;

:::

### 題目三：顯示英文成績排名

:::spoiler 小抄

SELECT student.name, exam_score.score
FROM student
INNER JOIN exam_score ON student.id = exam_score.student_id
WHERE exam_score.subject_id = 2
ORDER BY exam_score.score DESC;

:::

### 題目四：找出考最好的科目

:::spoiler 小抄

SELECT subject.name, MAX(exam_score.score) as highest
FROM subject
INNER JOIN exam_score ON subject.id = exam_score.subject_id
GROUP BY subject.name
ORDER BY highest DESC
LIMIT 1;

:::

### 題目五(稍難)：計算每位學生的總分和全班平均的差距，依總分排序


:::spoiler 小抄

SELECT 
    student.name,
    SUM(exam_score.score) as student_total,
    (SELECT AVG(total) FROM 
        (SELECT SUM(score) as total FROM exam_score GROUP BY student_id) t
    ) as class_avg,
    SUM(exam_score.score) - (SELECT AVG(total) FROM 
        (SELECT SUM(score) as total FROM exam_score GROUP BY student_id) t
    ) as difference
FROM student
INNER JOIN exam_score ON student.id = exam_score.student_id
GROUP BY student.name
ORDER BY student_total DESC;

:::