TP2 : Manipuler des entrées

Consignes

💡 Dans ce TP, vous reprendrez la base utilisée au TP précédent.

⚠ Vous activerez l'option afin d'afficher le nombre de lignes modifiées lors des insertions/modifications/suppressions.

Modification des entrées de la table

1. En une seule requête SQL, insérez les chimistes suivant dans table :
2. Affichez les chimistes que vous venez d'insérer (leurs sont les plus grands).
3. Vous remarquerez que August Kekule () est mort avant d'être né...
   En une seule requête, échangez ses années de naissance et de mort.
4. Pourquoi, dans les requêtes /, utiliser au lieu de nom/prénom ?
5. Andrei Markov est un mathématicien, pas un chimiste, supprimez-le (de la table).

Quelques prétraitements

Comme vu dans le CM, il est possible de pré-traiter les données avant de les afficher ou de les manipuler. Pour rappel, lorsqu'une API est utilisée, il est souvent préférable de faire les prétraitements côté langage de programmation que côté SQL.

Concaténations

1. Affichez les nom (en majuscules) et prénom des chercheurs.

2. Affichez, dans une colonne "nom complet", le nom (en majuscules) et le prénom, séparés par un espace.

Assigner des valeurs à une catégorie (conditions)

Un pré-traitement possible est d'assigner une catégorie à un ensemble de valeurs.
Par exemple, associer à chaque chercheur une "époque", en :

• ajoutant 25 à son année de naissance ;
• effectuant la division entière du résultat par 100 ;
• re-multipliant le résultat par 100.

1. Affichez le nom, prénom, et époque, des chercheurs.

Lorsque la catégorie ne peut être calculée numériquement, il convient alors d'utiliser des conditions. Ces dernières s'écrivent sous la forme suivante :

2. Affichez le nom, prénom, et status (vivant ou mort), des chercheurs.

Formatter des dates

Il est fréquent qu'on souhaite extraire des informations d'une date, ou l'afficher différemment. Pour cela, on utilise la fonction ( pour SQLite). Comme en Python, le format est une chaîne de caractère qui défini les éléments à afficher :

• : la date, équivalant à .
  • : l'année
  • : le mois
  • : le jour
• : l'heure, équivalent à .
  • : l'heure
  • : les minutes
  • : les secondes

1. Exécutez la requête ci-dessous, puis décrivez le résultat.

Au TP précédent, pour calculer l'âge des chimistes encore vivant, nous avions noté l'année courante en dur. L'âge retourné ne sera ainsi plus correct l'année prochaine.

2. Utilisez pour obtenir l'année actuelle.
3. Affichez le nom, prénom, et âge des chimistes, calculé avec l'année actuelle.

💡 Vous constatez que l'expression permettant d'obtenir l'âge des chimistes est complexe. Vous verrez plus tard des manières permettant d'améliorer la lisibilité de telles requêtes, et d'éviter la recopie cette expression d'une requête à une autre :

• via des colonnes générées (Cf CM3).
• via des vues (cf BDR2).
• via des fonctions (cf BDR2?).

Formatter des nombres

Comme vous le remarquez, toutes les colonnes sont alignées à gauche, ce qui rend les nombres peu lisibles. Pour corriger cela, on peut formater les nombres via la fonction ( pour SQLite < 3.38), qui s'utilise de manière analogue à .

1. Exécutez les exemples suivants :

Le format suit la structure suivante :

• est le format d'affichage :
  • : entier.
  • : entier en hexadécimal.
  • : réel.
  • : réel en notation exponentielle.
  • : chaîne de caractères.
• est la longueur minimale (facultatif). Ajoute des espaces en début de chaîne.
• est le nombre de chiffres après la virgule à afficher (facultatif).

2. Affichez l'âge des chimistes avec une précision de deux chiffres après la virgule, et une longueur minimale telle que les âges soient correctement alignés.

Agrégat sur une colonne

Pour rappel, il est possible d'agréger plusieurs entrées au sein d'une même ligne grâce à la clause . Si une fonction d'agrégat est utilisée sans clause , toutes les entrées seront regroupées au sein d'une même ligne.

1. Exécutez la requête suivante :
2. Affichez le nombre de chimistes dans la table (i.e. compter le nombre d'entrées).
3. Affichez chaque pays avec son nombre de chimistes (colonne "nb").
4. Ordonnez le résultat par nombre de chimistes décroissant.
5. Pourquoi ajouter la clause produira une erreur ?
6. Ajoutez une clause pour n'afficher que les pays ayant au moins 5 chimistes.

Fonction d'agrégat sur une expression

Les fonctions d'agrégats peuvent prendre en paramètre une colonne, mais aussi des expressions. Dans ce cas, il évaluera l'expression pour chaque lignes, avant d'exécuter la fonction d'agrégation.

1. En repartant de la requête effectuée dans la section "Formatter des nombres", calculez l'âge moyen des chimistes dans la base dans une colonne "m(age)".
2. En ajoutant les clauses une à une, modifiez cette requête pour afficher le pays et l'âge moyen des ses chimistes, classés par ordre croissant d'âge moyen.
3. Reprenez la requête précédente, mais n'affichez que les lignes correspondant aux pays ayant un âge moyen supérieur à 65 ans.