BDD1

Objectif : Champs calculés et utilisation de fonctions (agrégation, regroupement)

synthèse sur des éléments abordés :

SELECT  [DISTINCT] [nom_table.nom_champs, nom_table.nom_champs2.....] [champs calculés] [fonctions agrégation]
FROM
nom_table1, nom_table2 …. 
WHERE  [conditions, jointure … ]
[GROUP BY]
[HAVING]
[ORDER BY nom_table.nom_champs] [DESC];
[LIMIT]

Exemple champ calculé : [ nom_table.nom_champs]*3 AS nom_champ_calcule

Faire une opération avec un champ : SUM( ) COUNT( ) AVG( ) MIN( ) MAX( )

rajouter GROUP BY nom_du_champ_sur_lequel_on_fait_l_operation [champs calculés]

HAVING nom_champ_calcule ...

ATTENTION à l’ordre des instructions

ordre	ordre

Requêtes

requête 1.1 et 1.2 : requête avec un/des champ(s) calculé(s), utilisation d’ un « ALIAS »

Écrire une requête pour calculer la moyenne des étudiants ((note ue1 + note ue2) /2 ) et sélectionner uniquement les moyennes supérieures ou égales à 13 (trier par note puis par groupe)

Requête 2 : champs calculés ou concaténation d’éléments

Sur le même principe afficher le nom des étudiants et leur date de naissance au format JJ/MM/AAAA. Afficher uniquement les étudiants dont l’année de naissance est supérieure ou égale à 2005. Trier le résultat par date de naissance de façon décroissante.

requête 3 : utilisation de fonctions « d’agrégation » et d’un regroupement :

L’instruction « GROUP BY » est souvent utilisé avec les fonctions d’agrégation (COUNT, MAX, MIN, SUM, AVG) pour regrouper les résultats par une ou plusieurs colonnes. ( https://www.w3schools.com/SQL/sql_groupby.asp )

Tester la requête pour compter le nombre d’étudiants par groupe :

Requête 3.1 :

SELECT COUNT(Etudiant.idEtudiant) AS Nbre_etudiants, Etudiant.groupe_id 
FROM Etudiant 
GROUP BY Etudiant.groupe_id;

Modifier la requête et tester avec et sans le code barré :

Requête 3.2 :

les champs barrés, sont ils nécessaires ? Pourquoi ? Peut on mettre qu’un seul champ derrière le mot clé «GROUP BY » dans ce cas ?

Modifier la requête et tester avec et sans le code barré :

Requête 3.3 :

les champs barrés, sont ils nécessaires ? Pourquoi ? Peut on mettre qu’un seul champ derrière la fonction d’agrégation dans ce cas ?

Autre exemple avec beaucoup de fonction d’agrégation :

Requête 3.4 :

La documentation sur internet ne manque pas :

requête 4.1, 4.2, 4.3 : utilisation de fonctions « d’agrégation » et d’un regroupement

requête 5 : utilisation de fonctions « d’agrégation » et d’un regroupement

requête 6 : utilisation de fonctions « d’agrégation » et d’un regroupement

Sur le même principe calculer la moyenne des UE ainsi que la moyenne des 2 UE : pour chaque groupe et chaque semestre différents (trier par semestre puis par groupe).

requête 7 (ALTER TABLE puis UPDATE)

requête 8

Requête 8 :

Écrire une requête pour compter les étudiants qui sont majeurs. Commencer par tester la requête ci dessous :

SELECT date_naissance
,DATE_ADD(date_naissance,INTERVAL 18 YEAR) AS plus18
,DATEDIFF(curdate(),DATE_ADD(date_naissance,INTERVAL 18 YEAR)) AS diff_jours_majorite 
FROM Etudiant ;

requête 9 (Utiliser des conditions imbriqués)

Requête 9 :

Créer une requête pour afficher le nom des étudiants, leurs notes, la moyenne de leurs notes et un champ calculé avec le résultat du semestre : « DECISION ». Ce champ vaut (‘ADM’,‘UE1 validé – Jury’,‘UE2 validé – Jury’,’Jury’).

Ce champ a la valeur ‘ADM’ si les 2 UEs ont une note supérieure à 8 et la moyenne vaut plus de 10.
Ce champ a la valeur ‘UE1 validé – Jury’ ou ‘UE2 validé – Jury’ si une note de 2 UEs est >=10 mais la moyenne des 2 notes ne fait pas 10.
Sinon ce champ vaut ‘Jury’

Commencer par tester les 3 requêtes ci-dessous :

SELECT IF(note_ue1>=10 ,"UE1 validé - Jury","Jury") AS DECISION, nom
FROM Etudiant;
SELECT IF(note_ue2>=10 ,"UE2 validé","Jury") AS DECISION, nom
FROM Etudiant;
SELECT IF(note_ue1>=8 AND note_ue2 >=8 AND (note_ue1+note_ue2)>20,"ADM","Jury") AS DECISION, nom
FROM Etudiant;

objectif