1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32
| CREATE TABLE ENTITE
(ENT_ID INT NOT NULL PRIMARY KEY,
...)
CREATE TABLE MATIERE
(MTR_ID INT NOT NULL PRIMARY KEY,
...)
CREATE TABLE NOTE
(ENT_ID INT NOT NULL FOREIGN KEY ENTITE (ENT_ID),
MTR_ID INT NOT NULL FOREIGN KEY MATIERE (MTR_ID),
NOT_VALEUR FLOAT,
CONSTRAINT PRIMARY KEY (ENT_ID, MTR_ID))
INSERT INTO ENTITE VALUES (A)
INSERT INTO ENTITE VALUES (B)
INSERT INTO MATIERE VALUES (1)
INSERT INTO MATIERE VALUES (2)
INSERT INTO MATIERE VALUES (3)
INSERT INTO NOTE (A, 1, 3)
INSERT INTO NOTE (A, 2, 5)
INSERT INTO NOTE (A, 3, 8)
INSERT INTO NOTE (B, 1, 6)
INSERT INTO NOTE (B, 2, 7)
INSERT INTO NOTE (B, 3, 3)
-- les meilleures notes
SELECT ENT_ID, MAX(NOT_VALEUR) AS NOTE_MAX
FROM NOTE
GROUP BY ENT_ID |
Partager