编辑代码

-- 建立course表
CREATE TABLE IF NOT EXISTS course (
	course_num VARCHAR(32) NOT NULL,
	course_name VARCHAR(32) NOT NULL,
	credits int NOT NULL,
	course_type VARCHAR(32) NOT NULL,
	PRIMARY KEY (course_num)
);

CREATE TABLE IF NOT EXISTS department(
    depart_num VARCHAR(32) NOT NULL,
    depart_name VARCHAR(32) NOT NULL,
    PRIMARY KEY (depart_num)
);

CREATE TABLE IF NOT EXISTS student(
    stu_num VARCHAR(32) NOT NULL,
    stu_name VARCHAR(32) NOT NULL,
    stu_sex VARCHAR(32) NOT NULL,
    stu_birth VARCHAR(32) NOT NULL,
    PRIMARY KEY (stu_num)，
    FOREIGN KEY (depart_name) REFERENCES department(depart_num)
);
    course_num VARCHAR(32) NOT NULL,
    FOREIGN KEY (teach_num ) REFERENCES teacher(teach_num),

CREATE TABLE IF NOT EXISTS teacher(
    teach_num VARCHAR（32） NOT NULL,
    FOREIGN KEY (course_num) REFERENCES course(course_num) 
);

CREATE TABLE IF NOT EXISTS selectcourse(
    course_name VARCHAR(32) NOT NULL,
    stu_num VARCHAR NOT NULL,
    teach_num VARCHAR(32) NOT NULL,
    grades int NOT NULL,
    PRIMARY KEY (course_num,stu_num),
    FOREIGN KEY (course_num) REFERENCES course(course_name),
    FOREIGN KEY (stu_num) REFERENCES student(stu_num)
);

SELECT 
    c.course_name AS '课程名称',
    sc.grades AS '成绩',
    c.credits AS '学分',
    (SELECT SUM(c2.credits) FROM course c2 JOIN selectcourse sc2 ON c2.course_num = sc2.course_num WHERE sc2.stu_num = s.stu_num) AS '累计学分'
FROM 
    student s
JOIN 
    selectcourse sc ON s.stu_num = sc.stu_num
JOIN 
    course c ON sc.course_num = c.course_num
WHERE 
    s.stu_name = '张三';