802_2. Les requêtes avec regroupement

802_2_1 : Comptage

Ajoutez un nouvel étudiant dans la table tb_etudiant.

Testez les requêtes ci-dessous en utilisant la rédaction SQL et le mode ébauche pour la création de requête.

Nom de la requête SQL : rq_sql_802_2_1_nb_etudiants

Code SQL ci-dessous

Nom de la requête (mode ébauche) : rq_802_2_1_nb_etudiants

  • Sélectionner la table étudiant

  • Glisser l'attribut nom_et dans la première colonne.

  • Activer les totaux pour faire apparaître la ligne d'opération.

    On peut aussi cliquer droit dans la ligne identifiée champ pour activer l'option « Opérations ».

  • Choisir l'opération compte pour l'attribut nom_et.

1
SELECT count(*) as nb_et from Tb_etudiant

La requête en mode ébauche au final ressemble à l'image à droite

Le résultat en mode feuille

Le titre de la colonne n'est pas satisfaisant. Pour renommer une colonne avec un nom qui nous est plus familier, il suffit sur la ligne champ d'ajouter le nom souhaité (ici nb_notes) devant le nom du champ avec :

802_2_2 : Somme

Nom de la requête SQL  : rq_sql_802_2_2_somme_des_notes_sql

Question

Code SQL : select sum(Note) from Tb_note

1
SELECT sum(Note) FROM Tb_note

Par analogie avec la question précédente, utilisez le mode ébauche et sauvegardez la requête avec le nom rq_802_2_2_somme_des_notes

802_2_3 : Expressions

Utiliser des colonnes calculées pour par exemple calculer une moyenne à partir des 2 colonnes Nb de notes et somme des notes.

Question

Que pensez vous de la requête suivante (rq_802_2_3_1_test) : testez la requête, réfléchissez au résultat obtenu en fonction du code proposé, et demandez vous si le résultat est logique ?

1
select count(nom_et) as nb_et, sum(Note) as som_notes from tb_etudiant,tb_note

Indice

A la lecture du code, on pourrait supposer que l'on souhaite obtenir le cumul des notes obtenues pour chaque étudiant.

Solution

En 802_2_1, on compte simplement le nombre de lignes de la table tb_etudiant

En 802_2_2, on fait le cumul de la colonne Note sur les lignes de la table tb_note

Les deux tables n'ont pas le même nombre de ligne (cardinalité), les fonctions demandées peuvent fournir un résultat sur le nombre de lignes de la requête (ici, on a 6 étudiants * 15 notes donc 90 lignes). ATTENTION, le valeurs que je cite dépendent du nombre d'étudiants ou de notes dans vos tables

Si on ajoute la condition de jointure, on aura autant de lignes que de notes : compter ces lignes revient à calculer le nombre d'étudiants notés, la somme obtenue est la somme des notes

1
select count(nom_et) as nb_notes, sum(Note) as som_notes from tb_note INNER JOIN tb_etudiant ON tb_etudiant.id_et=tb_note.id_et;

Pour calculer la moyenne

1
select count(nom_et) as nb_notes, sum(Note) as som_notes, som_notes/nb_notes as moyenne from tb_note INNER JOIN tb_etudiant ON tb_etudiant.id_et=tb_note.id_et;

Pour faire en mode ébauche l'équivalent SQL ci-dessus, on notera que sur la ligne opération pour la colonne moyenne, il faut sélectionner Expression afin de faire un calcul, et ne pas afficher un attribut d'une table, ou faire une opération de comptage ou de somme sur un attribut d'un table.

802_2_4 : Calcul de moyenne par étudiant

Nous allons utiliser les fonctions COUNT et SUM pour compter le nombre de notes ainsi que la somme des notes pour chaque étudiant. ICI, on ne traitera pas le coefficient des matières, on se contentera de calculer un moyenne de toutes les notes de chaque étudiant.

Nous allons donc exploiter la notion de fonctions sur des regroupements de données dites identiques.

Si on se réfère à la requête rq_sql_02_aff_notes, nous avons les 15 notes renseignées par le nom de l'étudiant et la note obtenue : nous ne trouvons pas dans les colonnes concernées la matière notée : c'est ce que nous voulons car, pour calculer la moyenne par étudiant, nous allons omettre d'intégrer la matière.

Pour effectuer une opération de comptage ou de somme pour chaque étudiant, il faut regrouper par exemple les 3 lignes concernant l'étudiant Dupond : on en déduit que 3 lignes portent le nom de Dupond, et que la somme des 3 notes est de 39.

On doit donc obtenir le résultat ci-dessous

Complétez la requête en mode "ébauche" comme ci-dessous

La dernière colonne avec moyenne est la simple division des 2 colonnes ayant pour nom som_notes et nb_notes

Question

Observez le code SQL de la requête 802_2_4_SQL : Moyenne par étudiant[1]

Quel mot clé permet de regrouper les tuples selon les valeurs identiques d'un attribut ?

Solution

GROUP BY

2.5 Moyenne par matière

Exploiter le relation Matière-Note par analogie avec le contexte 2.4 (Etudiant-Note)

Question

Créer une requête qui calcule la moyenne par matière