Requête pour calculer la moyenne semestrielle

Version imprimable

1 pièce(s) jointe(s)

salut
dans ma base de donnes (firebird 2.5) j'ai les cinq tables suivantes :
Pièce jointe 573955

voila le script de création des table:

Code:

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:

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 !

29/06/2020, 09h08
escartefigue

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 :aie: : plus encombrant que du char(2) et également moins performant
- choisir du varchar pour un code est également :aie:, à 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 :aie:
- 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 :aie:

Bref, avant de réfléchir au SQL, il y a urgence à revoir le modèle de données !
29/06/2020, 13h21
delphi2019

Merci pour tous vos commentaires sur la conception ...
mais je n'ai pas obtenu la réponse que je voulais (requêtes sql)
30/06/2020, 07h50
SergioMaster

Citation:

Envoyé par escartefigue

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 :koi:

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

Requete 1

Code:

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:

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

01/07/2020, 10h37
delphi2019

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?

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

:alerte:Fait à la va-vite

Code:

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'

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

Code:

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'

03/07/2020, 07h16
SergioMaster

Citation:

Envoyé par delphi2019

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

merci SergioMaster j'ai trouver parenthèses manquantes
voila la requête

Code:

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'

salut SergioMaster

dans le code suivant:

Code:

MAX(E.NOM||' '||E.PRENOM) ELEVE

Je ne veux pas afficher le nom et le prénom dans un seul champ .... Je veux afficher le nom dans le seul champ et le nom dans le seul champ ... Quelle est la solution

J'ai essayé ça et ça n'a pas marché:
Code:

1 2 3 4 5 6 7 8 9 WITH MOY AS (SELECT N_INSCRIPTION,CODE_MODULE,CODE_SEMESTRE,(CONTROLE1+CONTROLE2+(EXAMEN*2))/4 AS MOYENNE FROM RESULTATS R) SELECT MOY.N_INSCRIPTION,E.NOM,E.PRENOM,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

Bonjour,

deux sous de jugeote suffisent ! J'ai l'impression que vous n'avez fait aucun effort.

Vous n'avez pas compris à quoi servait le MAX(E.NOM||' '||E.PRENOM) ELEVE ?
C'est une des techniques pour éviter grouper sur trop de colonnes, en effet dans le cas de la requête NOM et PRENOM sont logiquement unique à un identifiant (N_INSCRIPTION)

Deux solutions :
- la classique
Code:

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,E.NOM,E.PRENOM,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,E.NOM,E.PRENOM
Les colonnes n'utilisant pas d'aggrégats doivent être indiquées dans la clause GROUP BY, votre erreur

-moins conventionnelle mais plus "efficace"
Code:

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) NOM,MAX(E.PRENOM) PRENOM,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

16/07/2020, 11h58
delphi2019

meerci SergioMaster...