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 Student.Name,SUM(StudentTest.Mark) AS TotalMark
FROM StudentTest INNER JOIN Student
ON Student.SID = StudentTest.SID
GROUP BY(StudentTest.SID);