编辑代码

CREATE DATABASE Exam;
USE Exam;

CREATE TABLE Student(
    SID CHARACTER(4),
    Name VARCHAR(20),
    Age CHARACTER(2),
    PRIMARY KEY(SID)
);
CREATE TABLE Test(
    TID CHARACTER(4),
    Subject VARCHAR(20),
    FullMark VARCHAR(3),
    PRIMARY KEY(TID)
);
CREATE TABLE StudentTest(
    SID CHARACTER(4),
    TID CHARACTER(4),
    Mark INTEGER,
    FOREIGN KEY(SID) REFERENCES Student(SID),
    FOREIGN KEY(TID) REFERENCES Test(TID),
    PRIMARY KEY(SID,TID)
);

INSERT INTO Student VALUES ('0001','Jack',15);
INSERT INTO Student VALUES ('0002','Jimmy',16);
INSERT INTO Student VALUES ('0003','Tommy',14);
INSERT INTO Test VALUES('1001','CS',75);
INSERT INTO Test VALUES('1002','Math',100);
INSERT INTO Test VALUES('1003','CN',100);
INSERT INTO StudentTest VALUES('0001','1001',68);
INSERT INTO StudentTest VALUES('0002','1001',65);
INSERT INTO StudentTest VALUES('0001','1002',98);
INSERT INTO StudentTest VALUES('0002','1002',68);
INSERT INTO StudentTest VALUES('0002','1003',59);

/*SELECT SID,SUM(Mark) AS TotalMark
FROM StudentTest
GROUP BY(SID);*/

SELECT Student.Name,SUM(StudentTest.Mark) AS TotalMark
FROM StudentTest INNER JOIN Student
ON Student.SID = StudentTest.SID
GROUP BY(StudentTest.SID);

/*SELECT * FROM Student;
SELECT * FROM Test;
SELECT * FROM StudentTest;*/