TP1 : Requêter une base de données

Consignes

SQLite

SQLite est un SGBD très léger dont chaque base de données est stockée sous la forme d'un fichier . Comme de nombreux SGDB, il permet de manipuler une base de données via plusieurs types d'interfaces :

• interface en lignes de commandes.
• interface graphique.
• interface de programmation (e.g. pour manipuler une base à partir de Python).

💡 Dans le cadre de ce TP, nous utiliserons SQLite via son interface CLI.

Usage de SQLite

SQLite se lance dans le terminal via la commande suivante :

Une fois lancé, vous pourrez ensuite écrire vos requêtes SQL après l'invite (), appuyer sur entrée pour les exécuter, puis visualiser les résultats.

💡 Vous pouvez aussi entrer des commandes préfixées par un :

• : quitter SQLite.
• : afficher l'aide de SQLite.

Explorer la base de données

1. Téléchargez la base de données "chimiste" via ce lien.
2. Dans un terminal, exécutez afin d'ouvrir le fichier téléchargé.
3. Exécutez la commande pour afficher la liste des tables de la base.
4. Affichez le contenu de la table grâce à la requête SQL suivante :
   

Formatter le résultat

Comme vous pouvez le constater, l'affichage des résultats est, par défaut, un peu austère. Plusieurs commandes SQLite (ou arguments de la commande ) permettent d'améliorer l'affichage :

• (ou ) : dans les résultats, affiche les noms des colonnes.
• (ou ) : change le format d'affichage des résultats :
  • pour des humains : , , .
  • pour un ordinateur : , , , .
  • pour les pages Web : , .

1. Activez l'affichage des noms des colonnes, puis re-exécutez la requête SQL.
2. Testez les formats , , et sur la requête SQL précédente.

Sélection des colonnes

Les tables SQL peuvent contenir de nombreuses colonnes. Afin de conserver des résultats succins et lisibles, il convient de n'afficher que celles qui nous intéressent.

1. Affichez uniquement les pays de naissance.
2. Affichez les pays de naissance, cette fois en supprimant les doublons.
3. Affichez le nom et le prénom de chaque chimistes.
4. À cette requête, ajoutez les années de naissance que vous nommerez "naissance".

Sélectionner des lignes

De la même manière que les tables SQL peuvent contenir de très nombreuses colonnes, elle peuvent aussi contenir de très nombreuses lignes. Ainsi, comme pour les colonnes, il convient de sélectionner les lignes qui nous intéressent.

1. Affichez le nom, prénom, et pays de naissance des chimistes nés en France.
2. Affichez le nom, prénom des chimistes dont le nom commence par "A".
3. Affichez le nom, prénom des chimistes encore vivant (i.e. sans année de mort).
4. Affichez le nom, prénom des chimistes morts (i.e. ayant une année de mort), avec leur date de mort.

Opérations mathématiques sur les colonnes.

Il est fréquent que certaines informations soient calculées à partir d'une ou plusieurs colonnes. Par exemple, l'âge est calculée comme la différence entre l'année de mort et l'année de naissance :

1. Exécutez la requête ci-dessus en nommant la 3ème colonne "age".
2. Pourquoi, dans la requête précédente, certains chimistes n'ont pas d'âge ?
3. Affichez le nom, prenom, année de naissance, et âge des chimistes encore vivants.
4. En vous aidant de , qui retourne si est nul, sinon , affichez le nom, prenom, et âge de tous les chimistes.
5. Affichez le nom, prenom, et âge des chimistes dont l'âge est supérieur à 100 ans.

Trier et limiter les entrées

Parfois, les entrées qui nous intéressent sont les e.g. 5 produits les plus chers/moins chers. Pour cela on va d'abord trier les lignes sur leur prix, puis limiter le nombre de lignes retournées.

1. Ordonnez le nom, prénom, et âge des chimistes par leur âge croissant.
2. Ordonnez le nom, prénom, et âge des chimistes par leur âge décroissant.
3. Affichez le nom, prénom, et âge des 5 chimistes les plus vieux.
4. Affichez le nom, prénom, et âge des 5 chimistes les plus jeunes.