数据库系统概述

01
02
03
04
05
06
07
08
09
10
11
12CREATE TABLE enrollment (
  enrollment_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  student_id CHAR(10) NOT NULL REFERENCES student(student_id),
  course_id CHAR(12) NOT NULL REFERENCES course(course_id),
  semester_code VARCHAR(12) NOT NULL,
  final_score DECIMAL(5, 2) CHECK (final_score BETWEEN 0 AND 100),
  status VARCHAR(16) NOT NULL DEFAULT 'ENROLLED',
  CONSTRAINT uq_enrollment UNIQUE (student_id, course_id, semester_code)
);
SELECT s.student_id, s.name, e.course_id, e.semester_code, e.final_score, e.status
FROM student AS s JOIN enrollment AS e ON e.student_id = s.student_id
WHERE e.semester_code = '2026-FALL' AND e.status = 'ENROLLED' AND e.final_score >= 90;