--q1--
CREATE TABLE RESULTAT(
    NUM_ET NUMBER(6,0), NOM_ET VARCHAR2(20), PRENOM_ET VARCHAR2(15), CODE VARCHAR(7), NOTE_GLOB NUMBER(4,2),
    CONSTRAINT DOM_NOTE_GLOB CHECK (NOTE_GLOB <= 20),
    CONSTRAINT CON_PKEY PRIMARY KEY (NUM_ET, CODE)
)

CREATE TABLE CLASSEMENT(
    NUM_ET NUMBER(6,0), NOM_ET VARCHAR2(20), PRENOM_ET VARCHAR2(15),NOTE_GENE NUMBER(4,2), RANG NUMBER(3,0),
    CONSTRAINT DOM_NOTE_GENE CHECK (NOTE_GENE <= 20),
    CONSTRAINT CON_PKEY_CLASSEMENT PRIMARY KEY (NUM_ET)
)

DECLARE
    CURSOR NOTE_ETUDIANT IS
        SELECT ETUDIANT.NUM_ET, NOM_ET, PRENOM_ET, MODULE.CODE,((NVL(MOY_CC,0)*COEFF_CC)+(NVL(MOY_TEST,0)*COEFF_TEST))/(COEFF_TEST + COEFF_CC) NOTE_GLOB
        FROM ETUDIANT, NOTATION, MODULE
        WHERE ETUDIANT.NUM_ET = NOTATION.NUM_ET AND NOTATION.CODE = MODULE.CODE AND ANNEE = 2;/*NOTE POUR UNE MATIERE*/

    CURSOR ETUDIANT_C IS
        SELECT NUM_ET, NOM_ET, PRENOM_ET, AVG(NOTE_GLOB) NOTE_GENE
        FROM RESULTAT
        GROUP BY NUM_ET, NOM_ET, PRENOM_ET ORDER BY NOTE_GENE DESC;/*MOYENNE GENERALE POUR UN ETUDIANT*/

    CURSOR ETUDIANT_NON_NOTER IS
        SELECT NUM_ET, NOM_ET, PRENOM_ET
        FROM ETUDIANT
        WHERE NUM_ET NOT IN (SELECT DISTINCT NUM_ET FROM NOTATION);/*ETUDIANT QUI ONT AUCUNE NOTE*/

    NOMBRE_ET NUMBER(3,0);
    RANG_NOTE NUMBER(3,0);
    MOYENNE_DE_TEST NUMBER(4,2);
    AUCUN_ETUDIANT EXCEPTION;
    UN_ETUDIANT ETUDIANT_C%ROWTYPE;

BEGIN
    COMMIT; /*INITIALISE LE TRAITEMENT*/
    SELECT COUNT(*) INTO NOMBRE_ET FROM ETUDIANT;
    IF NOMBRE_ET = 0 THEN RAISE AUCUN_ETUDIANT; END IF; /*test si il y a des relations ou pas*/

    FOR LIGNE_ET IN NOTE_ETUDIANT LOOP
        INSERT INTO RESULTAT VALUES (LIGNE_ET.NUM_ET, LIGNE_ET.NOM_ET, LIGNE_ET.PRENOM_ET, LIGNE_ET.CODE, LIGNE_ET.NOTE_GLOB);
    END LOOP;/*INSERT DANS RESULTAT LES ETUDIANT NOTÉ*/

    FOR ET_SANS_NOTE IN ETUDIANT_NON_NOTER LOOP
        INSERT INTO RESULTAT VALUES (ET_SANS_NOTE.NUM_ET, ET_SANS_NOTE.NOM_ET, ET_SANS_NOTE.PRENOM_ET,'INFO',0);
    END LOOP;/*INSERT ETUDIANT NON NOTÉ DANS RÉSULTAT*/

    RANG_NOTE := 0;
    MOYENNE_DE_TEST := 21;/*POUR LE PREMIER TOUR*/

    OPEN ETUDIANT_C;
    FOR ET_C IN 1..NOMBRE_ET LOOP
        FETCH ETUDIANT_C INTO UN_ETUDIANT;
        EXIT WHEN ETUDIANT_C%NOTFOUND;
        IF MOYENNE_DE_TEST > UN_ETUDIANT.NOTE_GENE THEN RANG_NOTE := ET_C; END IF;
        INSERT INTO CLASSEMENT VALUES(UN_ETUDIANT.NUM_ET, UN_ETUDIANT.NOM_ET, UN_ETUDIANT.PRENOM_ET, UN_ETUDIANT.NOTE_GENE, RANG_NOTE);
        MOYENNE_DE_TEST := UN_ETUDIANT.NOTE_GENE;
    END LOOP;

    CLOSE ETUDIANT_C;

    COMMIT;

    EXCEPTION
        WHEN AUCUN_ETUDIANT THEN
            DBMS_OUTPUT.PUT_LINE('Il n''y a pas d''etudiant');
END;