11/28 每日任務

需要必備的知識點

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

截圖 2024-11-27 下午11.27.56

資料表與模擬資料

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

題目

題目一

查看所有科目

截圖 2024-11-27 下午11.31.43

題目二

計算所有成績平均
截圖 2024-11-27 下午11.32.01

題目三

找出 90 分以上的成績
截圖 2024-11-27 下午11.32.24

題目四：會使用到 inner join

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

截圖 2024-11-27 下午11.42.30

解答 
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;
Select a repo