# 学生表
CREATE TABLE STUDENT (
STUDENT_ID VARCHAR(32) NOT NULL,
STUDENT_NO VARCHAR(50) NOT NULL,
NAME VARCHAR(100) NOT NULL,
GENDER VARCHAR(10),
BIRTH_DATE DATE,
ID_CARD VARCHAR(18),
NATIONALITY VARCHAR(50),
ETHNICITY VARCHAR(50),
POLITICAL_STATUS VARCHAR(20),
ADDRESS VARCHAR(500),
PHONE VARCHAR(20),
EMAIL VARCHAR(100),
GRADE VARCHAR(20),
CLASS_ID VARCHAR(20),
MAJOR_ID VARCHAR(20),
DEPT_ID VARCHAR(20),
ENROLL_DATE DATE,
GRADUATE_DATE DATE,
STUDENT_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_STUDENT PRIMARY KEY (STUDENT_ID)
) ORGANIZE BY ROW;

# 教师表
CREATE TABLE TEACHER (
TEACHER_ID VARCHAR(32) NOT NULL,
TEACHER_NO VARCHAR(50) NOT NULL,
NAME VARCHAR(100) NOT NULL,
GENDER VARCHAR(10),
BIRTH_DATE DATE,
ID_CARD VARCHAR(18),
TITLE VARCHAR(50),
DEGREE VARCHAR(20),
DEPT_ID VARCHAR(20),
PHONE VARCHAR(20),
EMAIL VARCHAR(100),
TEACHER_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_TEACHER PRIMARY KEY (TEACHER_ID)
) ORGANIZE BY ROW;

# 课程表
CREATE TABLE COURSE (
COURSE_ID VARCHAR(32) NOT NULL,
COURSE_CODE VARCHAR(50) NOT NULL,
COURSE_NAME VARCHAR(200) NOT NULL,
COURSE_TYPE VARCHAR(20),
CREDITS DECIMAL(3, 1),
HOURS INTEGER,
DEPT_ID VARCHAR(20),
COURSE_DESC CLOB,
COURSE_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_COURSE PRIMARY KEY (COURSE_ID)
) ORGANIZE BY ROW;

# 创建索引
CREATE UNIQUE INDEX IDX_STUDENT_NO ON STUDENT(STUDENT_NO);
CREATE INDEX IDX_STUDENT_CLASS ON STUDENT(CLASS_ID, STUDENT_STATUS);
CREATE INDEX IDX_STUDENT_DEPT ON STUDENT(DEPT_ID, STUDENT_STATUS);
CREATE UNIQUE INDEX IDX_TEACHER_NO ON TEACHER(TEACHER_NO);
CREATE UNIQUE INDEX IDX_COURSE_CODE ON COURSE(COURSE_CODE);
CREATE INDEX IDX_COURSE_DEPT ON COURSE(DEPT_ID, COURSE_STATUS);

# 院系表
CREATE TABLE DEPARTMENT (
DEPT_ID VARCHAR(20) NOT NULL,
DEPT_CODE VARCHAR(20) NOT NULL,
DEPT_NAME VARCHAR(100) NOT NULL,
PARENT_DEPT_ID VARCHAR(20),
DEPT_LEVEL INTEGER,
DEPT_TYPE VARCHAR(20),
DEPT_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_DEPARTMENT PRIMARY KEY (DEPT_ID)
) ORGANIZE BY ROW;

# 专业表
CREATE TABLE MAJOR (
MAJOR_ID VARCHAR(20) NOT NULL,
MAJOR_CODE VARCHAR(20) NOT NULL,
MAJOR_NAME VARCHAR(100) NOT NULL,
DEPT_ID VARCHAR(20) NOT NULL,
MAJOR_DESC VARCHAR(500),
MAJOR_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_MAJOR PRIMARY KEY (MAJOR_ID)
) ORGANIZE BY ROW;

# 班级表
CREATE TABLE CLASS (
CLASS_ID VARCHAR(20) NOT NULL,
CLASS_CODE VARCHAR(20) NOT NULL,
CLASS_NAME VARCHAR(100) NOT NULL,
GRADE VARCHAR(20),
MAJOR_ID VARCHAR(20),
DEPT_ID VARCHAR(20),
HEAD_TEACHER_ID VARCHAR(32),
CLASS_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_CLASS PRIMARY KEY (CLASS_ID)
) ORGANIZE BY ROW;

# 创建索引
CREATE UNIQUE INDEX IDX_DEPT_CODE ON DEPARTMENT(DEPT_CODE);
CREATE UNIQUE INDEX IDX_MAJOR_CODE ON MAJOR(MAJOR_CODE);
CREATE INDEX IDX_MAJOR_DEPT ON MAJOR(DEPT_ID, MAJOR_STATUS);
CREATE UNIQUE INDEX IDX_CLASS_CODE ON CLASS(CLASS_CODE);
CREATE INDEX IDX_CLASS_MAJOR ON CLASS(MAJOR_ID, CLASS_STATUS);

# 学期表
CREATE TABLE SEMESTER (
SEMESTER_ID VARCHAR(20) NOT NULL,
SEMESTER_CODE VARCHAR(20) NOT NULL,
SEMESTER_NAME VARCHAR(100) NOT NULL,
START_DATE DATE NOT NULL,
END_DATE DATE NOT NULL,
SEMESTER_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_SEMESTER PRIMARY KEY (SEMESTER_ID)
) ORGANIZE BY ROW;

# 开课表
CREATE TABLE COURSE_OFFERING (
OFFERING_ID VARCHAR(32) NOT NULL,
COURSE_ID VARCHAR(32) NOT NULL,
TEACHER_ID VARCHAR(32),
SEMESTER_ID VARCHAR(20) NOT NULL,
CLASS_TIME VARCHAR(200),
CLASSROOM VARCHAR(100),
MAX_STUDENTS INTEGER,
ENROLLED_COUNT INTEGER DEFAULT 0,
OFFERING_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_COURSE_OFFERING PRIMARY KEY (OFFERING_ID)
) ORGANIZE BY ROW;

# 选课表
CREATE TABLE COURSE_SELECTION (
SELECTION_ID VARCHAR(32) NOT NULL,
STUDENT_ID VARCHAR(32) NOT NULL,
OFFERING_ID VARCHAR(32) NOT NULL,
SELECTION_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
SELECTION_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_COURSE_SELECTION PRIMARY KEY (SELECTION_ID)
) ORGANIZE BY ROW;

# 创建索引
CREATE UNIQUE INDEX IDX_SEMESTER_CODE ON SEMESTER(SEMESTER_CODE);
CREATE INDEX IDX_COURSE_OFFERING_COURSE ON COURSE_OFFERING(COURSE_ID, SEMESTER_ID);
CREATE INDEX IDX_COURSE_OFFERING_TEACHER ON COURSE_OFFERING(TEACHER_ID, SEMESTER_ID);
CREATE UNIQUE INDEX IDX_COURSE_SELECTION_STUDENT ON COURSE_SELECTION(STUDENT_ID, OFFERING_ID);
CREATE INDEX IDX_COURSE_SELECTION_OFFERING ON COURSE_SELECTION(OFFERING_ID);

# 选课存储过程
CREATE OR REPLACE PROCEDURE SP_SELECT_COURSE(
IN p_student_id VARCHAR(32),
IN p_offering_id VARCHAR(32),
OUT p_result_code VARCHAR(10),
OUT p_result_msg VARCHAR(200)
)
LANGUAGE SQL
BEGIN
DECLARE v_max_students INTEGER;
DECLARE v_enrolled_count INTEGER;
DECLARE v_count INTEGER;

SET p_result_code = ‘SUCCESS’;
SET p_result_msg = ‘选课成功’;

SELECT MAX_STUDENTS, ENROLLED_COUNT INTO v_max_students, v_enrolled_count
FROM COURSE_OFFERING
WHERE OFFERING_ID = p_offering_id
WITH RS USE AND KEEP UPDATE LOCKS;

SELECT COUNT(*) INTO v_count
FROM COURSE_SELECTION
WHERE STUDENT_ID = p_student_id AND OFFERING_ID = p_offering_id;

IF v_count > 0 THEN
SET p_result_code = ‘FAIL’;
SET p_result_msg = ‘已选择该课程’;
RETURN;
END IF;

IF v_enrolled_count >= v_max_students THEN
SET p_result_code = ‘FAIL’;
SET p_result_msg = ‘课程人数已满’;
RETURN;
END IF;

INSERT INTO COURSE_SELECTION (
SELECTION_ID, STUDENT_ID, OFFERING_ID, SELECTION_STATUS
) VALUES (
‘SEL’ || TO_CHAR(CURRENT TIMESTAMP, ‘YYYYMMDDHH24MISSFF6’),
p_student_id,
p_offering_id,
‘SELECTED’
);

UPDATE COURSE_OFFERING
SET ENROLLED_COUNT = ENROLLED_COUNT + 1,
UPDATE_TIME = CURRENT TIMESTAMP
WHERE OFFERING_ID = p_offering_id;

END;

# 退课存储过程
CREATE OR REPLACE PROCEDURE SP_DROP_COURSE(
IN p_student_id VARCHAR(32),
IN p_offering_id VARCHAR(32),
OUT p_result_code VARCHAR(10),
OUT p_result_msg VARCHAR(200)
)
LANGUAGE SQL
BEGIN
DECLARE v_count INTEGER;

SET p_result_code = ‘SUCCESS’;
SET p_result_msg = ‘退课成功’;

SELECT COUNT(*) INTO v_count
FROM COURSE_SELECTION
WHERE STUDENT_ID = p_student_id AND OFFERING_ID = p_offering_id;

IF v_count = 0 THEN
SET p_result_code = ‘FAIL’;
SET p_result_msg = ‘未选择该课程’;
RETURN;
END IF;

DELETE FROM COURSE_SELECTION
WHERE STUDENT_ID = p_student_id AND OFFERING_ID = p_offering_id;

UPDATE COURSE_OFFERING
SET ENROLLED_COUNT = ENROLLED_COUNT – 1,
UPDATE_TIME = CURRENT TIMESTAMP
WHERE OFFERING_ID = p_offering_id;

END;

# 成绩表
CREATE TABLE SCORE (
SCORE_ID VARCHAR(32) NOT NULL,
STUDENT_ID VARCHAR(32) NOT NULL,
OFFERING_ID VARCHAR(32) NOT NULL,
USUAL_SCORE DECIMAL(5, 2),
MIDTERM_SCORE DECIMAL(5, 2),
FINAL_SCORE DECIMAL(5, 2),
TOTAL_SCORE DECIMAL(5, 2) NOT NULL,
GRADE VARCHAR(10),
GPA DECIMAL(3, 2),
SCORE_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_SCORE PRIMARY KEY (SCORE_ID)
) ORGANIZE BY ROW;

# 创建索引
CREATE UNIQUE INDEX IDX_SCORE_STUDENT_OFFERING ON SCORE(STUDENT_ID, OFFERING_ID);
CREATE INDEX IDX_SCORE_OFFERING ON SCORE(OFFERING_ID);

# 成绩统计MQT
CREATE TABLE MQT_SCORE_STAT AS (
SELECT
OFFERING_ID,
COUNT(*) AS STUDENT_COUNT,
MIN(TOTAL_SCORE) AS MIN_SCORE,
MAX(TOTAL_SCORE) AS MAX_SCORE,
AVG(TOTAL_SCORE) AS AVG_SCORE,
STDDEV(TOTAL_SCORE) AS STDDEV_SCORE
FROM SCORE
GROUP BY OFFERING_ID
) DATA INITIALLY DEFERRED REFRESH DEFERRED;

REFRESH TABLE MQT_SCORE_STAT;

# 学生成绩查询
SELECT
s.STUDENT_ID,
s.STUDENT_NO,
s.NAME,
c.COURSE_NAME,
t.NAME AS TEACHER_NAME,
sc.TOTAL_SCORE,
sc.GRADE,
sc.GPA
FROM SCORE sc
INNER JOIN STUDENT s ON sc.STUDENT_ID = s.STUDENT_ID
INNER JOIN COURSE_OFFERING o ON sc.OFFERING_ID = o.OFFERING_ID
INNER JOIN COURSE c ON o.COURSE_ID = c.COURSE_ID
LEFT JOIN TEACHER t ON o.TEACHER_ID = t.TEACHER_ID
WHERE s.STUDENT_ID = ‘S001’
ORDER BY c.COURSE_NAME;

# 课程成绩排名
SELECT
s.STUDENT_ID,
s.STUDENT_NO,
s.NAME,
sc.TOTAL_SCORE,
RANK() OVER (ORDER BY sc.TOTAL_SCORE DESC) AS RANK
FROM SCORE sc
INNER JOIN STUDENT s ON sc.STUDENT_ID = s.STUDENT_ID
WHERE sc.OFFERING_ID = ‘O001’
ORDER BY RANK;

# 班级成绩统计
SELECT
c.CLASS_ID,
c.CLASS_NAME,
COUNT(*) AS STUDENT_COUNT,
AVG(sc.TOTAL_SCORE) AS AVG_SCORE,
MIN(sc.TOTAL_SCORE) AS MIN_SCORE,
MAX(sc.TOTAL_SCORE) AS MAX_SCORE
FROM SCORE sc
INNER JOIN STUDENT s ON sc.STUDENT_ID = s.STUDENT_ID
INNER JOIN CLASS c ON s.CLASS_ID = c.CLASS_ID
WHERE sc.OFFERING_ID = ‘O001’
GROUP BY c.CLASS_ID, c.CLASS_NAME
ORDER BY AVG_SCORE DESC;