11/29 每日任務

需要必備的知識點

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

截圖 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-28 下午9.38.12

小抄 
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;

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

截圖 2024-11-28 下午9.39.51

小抄 
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;

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

截圖 2024-11-28 下午9.45.16

小抄 
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;

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

截圖 2024-11-28 下午9.46.54

小抄 
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;

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

截圖 2024-11-28 下午9.52.53

小抄 
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;
Select a repo