# 11/28 每日任務

## 需要必備的知識點

* 請觀看第三週直播錄影講解一對一、一對多、多對多的關係
* [JOIN 教學，看到 inner JOIN 即可](https://courses.hexschool.com/courses/20242/lectures/58295115)

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


### 資料表與模擬資料

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');

## 題目

### 題目一

查看所有科目


## 題目二
計算所有成績平均

## 題目三
找出 90 分以上的成績

## 題目四：會使用到 inner join

顯示小明的國文成績，篩選條件請用國文的 `subject_id = 1` 進行篩選


:::spoiler 解答

SELECT student.name, exam_score.score
FROM student
INNER JOIN exam_score ON student.id = exam_score.student_id
WHERE student.name = '小明' AND exam_score.subject_id = 1;

:::