Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

SQL Firebird Discussion :

Requête pour calculer la moyenne semestrielle


Sujet :

SQL Firebird

  1. #1
    Membre du Club
    Requête pour calculer la moyenne semestrielle
    salut
    dans ma base de donnes (firebird 2.5) j'ai les cinq tables suivantes :


    voila le script de création des table:

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    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
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
     
    CREATE TABLE ETUDIANTES (
      N_INSCRIPTION  INTEGER NOT NULL,
      NOM            VARCHAR(20),
      PRENOM         VARCHAR(20) CHARACTER SET NONE,
      /* Keys */
      CONSTRAINT ETUDIANTES_INDEX01
        PRIMARY KEY (N_INSCRIPTION)
    );
     
    CREATE TABLE MODULES (
      CODE_MODULE  VARCHAR(20) NOT NULL,
      NOM_MODULE   VARCHAR(20),
      COEFFICIENT  INTEGER,
      /* Keys */
      CONSTRAINT MODULES_INDEX01
        PRIMARY KEY (CODE_MODULE)
    );
     
    CREATE TABLE SEMESTRES (
      CODE_SEMESTRE  VARCHAR(2) NOT NULL,
      /* Keys */
      CONSTRAINT SEMESTRES_INDEX01
        PRIMARY KEY (CODE_SEMESTRE)
    );
     
    CREATE TABLE AFFECTATION_MODULES (
      CODE_MODULE    VARCHAR(20) NOT NULL,
      CODE_SEMESTRE  VARCHAR(2) NOT NULL,
      DUREE          INTEGER,
      /* Keys */
      CONSTRAINT AFFECTATION_MODULES_INDEX01
        PRIMARY KEY (CODE_MODULE, CODE_SEMESTRE),
      /* Foreign keys */
      CONSTRAINT AFFE_MODULES_FOREIGN_KEY01
        FOREIGN KEY (CODE_MODULE)
        REFERENCES MODULES(CODE_MODULE)
        ON DELETE CASCADE
        ON UPDATE CASCADE, 
      CONSTRAINT AFFE_MODULES_FOREIGN_KEY02
        FOREIGN KEY (CODE_SEMESTRE)
        REFERENCES SEMESTRES(CODE_SEMESTRE)
        ON DELETE CASCADE
        ON UPDATE CASCADE
    );
     
    CREATE TABLE RESULTATS (
      N_INSCRIPTION  INTEGER NOT NULL,
      CODE_MODULE    VARCHAR(20) NOT NULL,
      CODE_SEMESTRE  VARCHAR(2) NOT NULL,
      CONTROLE1      FLOAT,
      CONTROLE2      FLOAT,
      EXAMEN         FLOAT,
      OBSRVATION     VARCHAR(50),
      /* Keys */
      CONSTRAINT RESULTATS_INDEX01
        PRIMARY KEY (N_INSCRIPTION, CODE_MODULE, CODE_SEMESTRE),
      /* Foreign keys */
      CONSTRAINT RESULTATS_FOREIGN_KEY01
        FOREIGN KEY (N_INSCRIPTION)
        REFERENCES ETUDIANTES(N_INSCRIPTION)
        ON UPDATE CASCADE, 
      CONSTRAINT RESULTATS_FOREIGN_KEY02
        FOREIGN KEY (CODE_MODULE)
        REFERENCES MODULES(CODE_MODULE)
        ON UPDATE CASCADE, 
      CONSTRAINT RESULTATS_FOREIGN_KEY03
        FOREIGN KEY (CODE_SEMESTRE)
        REFERENCES SEMESTRES(CODE_SEMESTRE)
        ON UPDATE CASCADE
    );

    et l'insertion d'enregistrements :
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    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
     
    INSERT INTO ETUDIANTES (N_INSCRIPTION, NOM, PRENOM) VALUES (100, 'marin', 'david');
    INSERT INTO ETUDIANTES (N_INSCRIPTION, NOM, PRENOM) VALUES (101, 'mula', 'young');
    INSERT INTO ETUDIANTES (N_INSCRIPTION, NOM, PRENOM) VALUES (102, 'ford', 'ilisa');
    COMMIT;
    INSERT INTO MODULES (CODE_MODULE, NOM_MODULE, COEFFICIENT) VALUES ('1', 'ALGO', 4);
    INSERT INTO MODULES (CODE_MODULE, NOM_MODULE, COEFFICIENT) VALUES ('2', 'DELPHI', 4);
    INSERT INTO MODULES (CODE_MODULE, NOM_MODULE, COEFFICIENT) VALUES ('3', 'anglais', 1);
    INSERT INTO MODULES (CODE_MODULE, NOM_MODULE, COEFFICIENT) VALUES ('4', 'francais', 2);
    COMMIT;
    INSERT INTO SEMESTRES (CODE_SEMESTRE) VALUES ('S1');
    INSERT INTO SEMESTRES (CODE_SEMESTRE) VALUES ('S2');
    COMMIT;
    INSERT INTO AFFECTATION_MODULES (CODE_MODULE, CODE_SEMESTRE, DUREE) VALUES ('1', 'S1', 120);
    INSERT INTO AFFECTATION_MODULES (CODE_MODULE, CODE_SEMESTRE, DUREE) VALUES ('4', 'S1', 60);
    INSERT INTO AFFECTATION_MODULES (CODE_MODULE, CODE_SEMESTRE, DUREE) VALUES ('2', 'S2', 140);
    INSERT INTO AFFECTATION_MODULES (CODE_MODULE, CODE_SEMESTRE, DUREE) VALUES ('3', 'S2', 140);
    COMMIT;
    INSERT INTO RESULTATS (N_INSCRIPTION, CODE_MODULE, CODE_SEMESTRE, CONTROLE1, CONTROLE2, EXAMEN, OBSRVATION) VALUES (100, '2', 'S2', 12, 13, 11.5, NULL);
    INSERT INTO RESULTATS (N_INSCRIPTION, CODE_MODULE, CODE_SEMESTRE, CONTROLE1, CONTROLE2, EXAMEN, OBSRVATION) VALUES (100, '4', 'S1', 10, 15, 14, NULL);
    INSERT INTO RESULTATS (N_INSCRIPTION, CODE_MODULE, CODE_SEMESTRE, CONTROLE1, CONTROLE2, EXAMEN, OBSRVATION) VALUES (100, '3', 'S2', 17, 18, 19, NULL);
    INSERT INTO RESULTATS (N_INSCRIPTION, CODE_MODULE, CODE_SEMESTRE, CONTROLE1, CONTROLE2, EXAMEN, OBSRVATION) VALUES (101, '1', 'S1', 13, 14, 18, NULL);
    INSERT INTO RESULTATS (N_INSCRIPTION, CODE_MODULE, CODE_SEMESTRE, CONTROLE1, CONTROLE2, EXAMEN, OBSRVATION) VALUES (101, '2', 'S2', 14.5, 15.5, 16.5, NULL);
    INSERT INTO RESULTATS (N_INSCRIPTION, CODE_MODULE, CODE_SEMESTRE, CONTROLE1, CONTROLE2, EXAMEN, OBSRVATION) VALUES (101, '3', 'S2', 9.5, 5, 7, NULL);
    INSERT INTO RESULTATS (N_INSCRIPTION, CODE_MODULE, CODE_SEMESTRE, CONTROLE1, CONTROLE2, EXAMEN, OBSRVATION) VALUES (101, '4', 'S1', 8.5, 9.5, 10.5, NULL);
    INSERT INTO RESULTATS (N_INSCRIPTION, CODE_MODULE, CODE_SEMESTRE, CONTROLE1, CONTROLE2, EXAMEN, OBSRVATION) VALUES (102, '1', 'S1', 16, 17, 19, NULL);
    INSERT INTO RESULTATS (N_INSCRIPTION, CODE_MODULE, CODE_SEMESTRE, CONTROLE1, CONTROLE2, EXAMEN, OBSRVATION) VALUES (102, '2', 'S2', 17.5, 14.25, 19.5, NULL);
    INSERT INTO RESULTATS (N_INSCRIPTION, CODE_MODULE, CODE_SEMESTRE, CONTROLE1, CONTROLE2, EXAMEN, OBSRVATION) VALUES (102, '3', 'S2', 11.5, 16.5, 17, NULL);
    INSERT INTO RESULTATS (N_INSCRIPTION, CODE_MODULE, CODE_SEMESTRE, CONTROLE1, CONTROLE2, EXAMEN, OBSRVATION) VALUES (102, '4', 'S1', 10.75, 17.5, 16, NULL);
    COMMIT;


    J'ai besoin de sortir :
    1 - requête SQL pour afficher le nom d’étudiant Qui a un numéro: N_INSCRIPTION='100' et sa moyenne semestrielle du semestre1 (S1).
    sachant que:
    -la moyenne semestrielle= (la somme des(moyenne générale pour chaque module)*COEFFICIENT du module))/la somme des COEFFICIENTS
    -la moyenne générale pour chaque module = (CONTROLE1+CONTROLE2+(EXAMEN*2))/4

    2 - requête SQL pour afficher Tous les étudiants avec leur moyenne semestrielle du semestre1

    Merci de m'aiguiller pour résoudre ce problème !
    Delphi installés : RAD Studio 10.3 ..........Programme VCL
    SGBD : Firebird 2.5
    générateurs Etats : FastReport, QuickReport
    OS : Window 7 64bit

  2. #2
    Expert éminent sénior
    Bonjour,

    S'agit il d'un exercice scolaire ? La description des tables est elle imposée ?

    Parce que
    - choisir du varchar(2) est complètement : plus encombrant que du char(2) et également moins performant
    - choisir du varchar pour un code est également , à remplacer par du char court (4 ou 5 caractères doivent suffire pour un code) pour la même raison
    sans compter que le code module sur 20 est aussi long que le libellé du module... où est l'intérêt du code en ce cas
    - choisir du float pour des notes n'est pas adapté non plus, la partie décimale de la note sera approximative
    - choisir du varchar(20) pour les noms et prénoms sera souvent insuffisant
    - choisir de l'integer pour la durée et le coefficient ne convient que si l'un est l'autre sont des entiers

    Par ailleurs, votre modèle suppose que les étudiants peuvent avoir des résultats à des modules sans vérifier que les modules et trimestres correspondent à des périodes dans lesquelles les étudiants sont insrits

    Bref, avant de réfléchir au SQL, il y a urgence à revoir le modèle de données !

  3. #3
    Membre du Club
    Merci pour tous vos commentaires sur la conception ...
    mais je n'ai pas obtenu la réponse que je voulais (requêtes sql)
    Delphi installés : RAD Studio 10.3 ..........Programme VCL
    SGBD : Firebird 2.5
    générateurs Etats : FastReport, QuickReport
    OS : Window 7 64bit

  4. #4
    Rédacteur/Modérateur

    Citation Envoyé par escartefigue Voir le message

    S'agit il d'un exercice scolaire ? La description des tables est elle imposée ?
    Bref, avant de réfléchir au SQL, il y a urgence à revoir le modèle de données !
    Je plussoie, d'autant plus que cette même demande avec les mêmes noms de tables (ETUDIANTES m'était resté en mémoire) les mêmes structures a déjà été posée.
    Je me souviens d'y avoir répondu avec les mêmes remarques (ou peu s'en faut), reste à retrouver la discussion en utilisant la fonction recherche par contre était-ce ce forum ou celui de Delphi

    En tout cas : https://www.developpez.net/forums/d2...aisie-donnees/ => même structure en 5 mois cela n'a pas changé !
    La structure à été faite à partir de la grille et non la grille rempli par une requête
    La seule chose absolue dans un monde comme le nôtre, c'est l'humour. » Albert Einstein

    Delphi installés : D3,D7,D2010,XE4,XE7,D10 (Tokyo, Rio, Sidney) et peut être quelques autres
    SGBD : Firebird 2.5, 3, SQLite
    générateurs Etats : FastReport, Rave, QuickReport
    OS : Window Vista, Windows 10, Ubuntu, Androïd

  5. #5
    Rédacteur/Modérateur

    Requete 1
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    WITH MOY AS
    (SELECT N_INSCRIPTION,CODE_MODULE,CODE_SEMESTRE,(CONTROLE1+CONTROLE2+(EXAMEN*2))/4   AS MOYENNE 
     FROM RESULTATS R)
     
    SELECT SUM(moyenne)/COUNT(1) AS MS1_E100 FROM MOY 
    WHERE CODE_SEMESTRE='S1' 
    AND N_INSCRIPTION='100'


    Requete 2
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    WITH MOY AS
    (SELECT N_INSCRIPTION,CODE_MODULE,CODE_SEMESTRE,(CONTROLE1+CONTROLE2+(EXAMEN*2))/4   AS MOYENNE 
     FROM RESULTATS R)
     
    SELECT MOY.N_INSCRIPTION,MAX(E.NOM||' '||E.PRENOM) ELEVE,COUNT(1) NB_MODULES,SUM(moyenne)/COUNT(1) AS MS1_E100 
    FROM MOY JOIN ETUDIANTES E ON E.N_INSCRIPTION=MOY.N_INSCRIPTION 
    WHERE MOY.CODE_SEMESTRE='S1' 
    GROUP BY MOY.N_INSCRIPTION


    Mais j'ai la flemme de contrôler les résultats souhaités qui auraient dû nous être aussi fournis
    La seule chose absolue dans un monde comme le nôtre, c'est l'humour. » Albert Einstein

    Delphi installés : D3,D7,D2010,XE4,XE7,D10 (Tokyo, Rio, Sidney) et peut être quelques autres
    SGBD : Firebird 2.5, 3, SQLite
    générateurs Etats : FastReport, Rave, QuickReport
    OS : Window Vista, Windows 10, Ubuntu, Androïd

  6. #6
    Membre du Club
    merci SergioMaster Tu es le meilleur!!
    Merci pour vos commentaires sur mon ancien sujet ,,Plus tard, je ferai les changements nécessaires pour la conception de BD

    -Je note que vous n'avez pas utilisé les COEFFICIENTS pour calculer la moyenne

    dans la table MODULES il existe un champ 'COEFFICIENT' de type INTEGER

    Comment sera la requête après l'avoir ajoutée les COEFFICIENTS?
    Delphi installés : RAD Studio 10.3 ..........Programme VCL
    SGBD : Firebird 2.5
    générateurs Etats : FastReport, QuickReport
    OS : Window 7 64bit

  7. #7
    Rédacteur/Modérateur

    Inclure le coefficient des matières dans la CTE, j'ai l'impression que vous n'avez pas beaucoup cherché !

    Fait à la va-vite
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    WITH MOY AS
    (SELECT R.N_INSCRIPTION,R.CODE_MODULE,R.CODE_SEMESTRE,(R.CONTROLE1+R.CONTROLE2+(R.EXAMEN*2)/4)*M.COEFFICIENT AS MOYENNE_MODULE,M.COEFFICIENT 
     FROM RESULTATS R
     JOIN MODULE M ON M.CODE_MODULE=R.CODE_MODULE
    )
     
    SELECT SUM(moyenne)/SUM(COEFFICIENT) AS MS1_E100 FROM MOY 
    WHERE CODE_SEMESTRE='S1' 
    AND N_INSCRIPTION='100'
    La seule chose absolue dans un monde comme le nôtre, c'est l'humour. » Albert Einstein

    Delphi installés : D3,D7,D2010,XE4,XE7,D10 (Tokyo, Rio, Sidney) et peut être quelques autres
    SGBD : Firebird 2.5, 3, SQLite
    générateurs Etats : FastReport, Rave, QuickReport
    OS : Window Vista, Windows 10, Ubuntu, Androïd

  8. #8
    Membre du Club
    MERCI
    après la correction du 'JOIN MODULES) et SELECT SUM(MOYENNE_MODULE) la moyenne s'affiche: 32 ????
    pour quoi ?

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    WITH MOY AS
    (SELECT R.N_INSCRIPTION,R.CODE_MODULE,R.CODE_SEMESTRE,(R.CONTROLE1+R.CONTROLE2+(R.EXAMEN*2)/4)*M.COEFFICIENT AS MOYENNE_MODULE,M.COEFFICIENT 
     FROM RESULTATS R
     JOIN MODULES M ON M.CODE_MODULE=R.CODE_MODULE
    )
     
    SELECT SUM(MOYENNE_MODULE)/SUM(COEFFICIENT) AS MS1_E100 FROM MOY 
    WHERE CODE_SEMESTRE='S1' 
    AND N_INSCRIPTION='100'
    Delphi installés : RAD Studio 10.3 ..........Programme VCL
    SGBD : Firebird 2.5
    générateurs Etats : FastReport, QuickReport
    OS : Window 7 64bit

  9. #9
    Rédacteur/Modérateur

    Citation Envoyé par delphi2019 Voir le message

    après la correction du 'JOIN MODULES) et SELECT SUM(MOYENNE_MODULE) la moyenne s'affiche: 32 ????
    pourquoi ?
    Comme je l'avais bien spécifié, je n'ai absolument pas testé, j'avais juste inclus la jointure (et encore mal) !
    Ce n'est qu'une question de parenthèses manquantes, à vous de trouver
    La seule chose absolue dans un monde comme le nôtre, c'est l'humour. » Albert Einstein

    Delphi installés : D3,D7,D2010,XE4,XE7,D10 (Tokyo, Rio, Sidney) et peut être quelques autres
    SGBD : Firebird 2.5, 3, SQLite
    générateurs Etats : FastReport, Rave, QuickReport
    OS : Window Vista, Windows 10, Ubuntu, Androïd

  10. #10
    Membre du Club
    merci SergioMaster j'ai trouver parenthèses manquantes
    voila la requête

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
     
    WITH MOY AS
    (SELECT R.N_INSCRIPTION,R.CODE_MODULE,R.CODE_SEMESTRE,((R.CONTROLE1+R.CONTROLE2+(R.EXAMEN*2))/4)*M.COEFFICIENT AS MOYENNE_MODULE,M.COEFFICIENT 
     FROM RESULTATS R
     JOIN MODULES M ON M.CODE_MODULE=R.CODE_MODULE
    )
     
    SELECT SUM(MOYENNE_MODULE)/SUM(COEFFICIENT) AS MS1_E100 FROM MOY 
    WHERE CODE_SEMESTRE='S1' 
    AND N_INSCRIPTION='100'
    Delphi installés : RAD Studio 10.3 ..........Programme VCL
    SGBD : Firebird 2.5
    générateurs Etats : FastReport, QuickReport
    OS : Window 7 64bit