CREATE TABLE Condidats(
    numero INT PRIMARY KEY,
    nom VARCHAR(20),
    prenom VARCHAR(20)
);

CREATE TABLE Matieres(
    Code VARCHAR(5),
    nom VARCHAR(20),
    coeff INT
);
CREATE TABLE Notes(
    numC INT,
    codM VARCHAR(5),
    note DECIMAL(2,2),
    FOREIGN KEY(numC) REFERENCES Condidats(numero) ON DELETE SET NULL
    FOREIGN KEY(codM) REFERENCES Matieres(code) ON DELETE SET NULL
);
INSERT INTO Condidats VALUES(416, 'Jafraoui', 'Hicham');
INSERT INTO Condidats VALUES(70, 'Hilal', 'Samira');
INSERT INTO Condidats VALUES(162, 'Senhaji', 'Amal');
INSERT INTO Condidats VALUES(23, 'Bakouri', 'Ahmed');


INSERT INTO Matieres VALUES('M', 'Mathematiques', 14);
INSERT INTO Matieres VALUES('P', 'Physique', 10);
INSERT INTO Matieres VALUES('SI', "Sciences de l'ingenieur", 6);
INSERT INTO Matieres VALUES('Ch', 'Chimie', 3);

INSERT INTO Notes VALUES(70, 'SI', 14.50);
INSERT INTO Notes VALUES(162, 'SI', 17.00);
INSERT INTO Notes VALUES(416, 'SI', 12.25);
INSERT INTO Notes VALUES(70, 'M', 16.00);
INSERT INTO Notes VALUES(162, 'M', 13.50);
INSERT INTO Notes VALUES(416, 'M', 08.35);
INSERT INTO Notes VALUES(70, 'P', 06.75);
INSERT INTO Notes VALUES(162, 'P', 11.05);
INSERT INTO Notes VALUES(416, 'P', 10.10);



SELECT C.numero, C.nom, C.prenom, SUM(N.note*M.coeff) AS total
FROM Condidats C 
JOIN Matieres M 
JOIN Notes N 
ON C.numero = N.numC AND M.Code = N.codM
WHERE C.numero NOT IN (
        SELECT C.numero 
        FROM Condidats C
        JOIN NOTES N 
        ON C.numero = N.numC
        GROUP BY C.numero
        HAVING MIN(N.note) < 5)
GROUP BY C.numero
HAVING total > 100
ORDER BY total DESC;