Aller plus loin en SQL

Dans ce classeur, nous poursuivrons l'étude de l'exemple de la base de données de livres de SF et nous verrons des requêtes de sélection avancées ainsi que la manière de fusionner plusieurs tables (Jointures).

Commencez par valider les cellules suivantes afin d'activer la fonctionnalité SQL de Jupyter et de ncharger la base du TP précédent.

%load_ext sql

%sql sqlite:///livres_db

Aller plus loin avec SELECT

Sélection simple

Supposons que l'on veuille lister seulement les noms et prénoms des auteurs nés avant 1900, on utilisera une clause WHERE condition :

%sql SELECT NomAuteur, PrenomAuteur from Auteurs WHERE AnneeNaissance < 1900;

Pour obtebir les auteurs prénommés Jules :

%sql SELECT NomAuteur, PrenomAuteur from Auteurs WHERE PrenomAuteur = 'Jules';

Vous voyez que les textes sont délimités par des quotes '...'.

Attention : essayez de remplacer Jules par jules et vous constaterez que la recherche est sensible aux majuscules.

Supposns maintenant que l'on recherche les auteurs dont le prénom commence par P : Nous utiliserons une clause LIKE de cette manière :

%sql SELECT NomAuteur, PrenomAuteur from Auteurs WHERE PrenomAuteur LIKE 'P%';

Sélection multiple

Il est possible de croiser plusieurs critères à l'aide d'opérateurs booleens : AND et OR. Voici la liste des auteurs français nés après 1900 :

%sql SELECT NomAuteur, PrenomAuteur from Auteurs WHERE IdLangue = 2 AND AnneeNaissance > 1900;

Compter le nombre de réponses d'une requête SELECT

Combien y a t-il d'auteurs nés entre 1900 et 1915 ? Vous verrez à l'occation l'utilisation de l'opérateur BETWEEN pour tester l'appartenance à un intervalle.

nbr = %sql SELECT COUNT(*) from Auteurs WHERE AnneeNaissance BETWEEN 1900 AND 1915;

print(nbr)
print("récupérer juste le nombre : ", nbr[0][0])

Trier les réponses

Nous allons lister tous les auteurs par ordre croissant d'année de naissance

%sql SELECT * from Auteurs ORDER BY AnneeNaissance;

et par ordre décroissant, on ajoute DESC à la fin de la requête

%sql SELECT * from Auteurs ORDER BY AnneeNaissance DESC;

Éviter les occurrences multiples

interrogeons la table Livres sur les années de publication, rangées par ordre croissant :

%sql SELECT AnneePubli from Livres ORDER BY AnneePubli;

On constate la présence de quelques doublons

Pour éviter les redondances dans les résultats, on peut rajouter le mot-clé DISTINCT juste après le SELECT :

%sql SELECT DISTINCT AnneePubli from Livres ORDER BY AnneePubli;

A vous de jouer

  1. Donner la liste de tous les titres des livres écrits entre 1920 et 1950.
  2. Combien y en a t-il ?
# Utilisez cette cellule en brouillon pour vos requetes 
# Utiliser la syntaxe courte 
# reponse_i = %sql VOTRE REQUETE pour i=1 ou i=2

# YOUR CODE HERE
raise NotImplementedError()
# Vérification des réponses

assert reponse_1[3][0] == 'Les Robots'
assert reponse_2[0][0] == 6

Requêtes portant sur plusieurs tables

Jusqu'à présent, nos requêtes ne portaient que sur une seule table. Néanmoins notre liste de livres comporte des donées en provenance de plusieurs tables simultanément. Nous allons voir comment effectuer des requêtes pour croiser des données en provenance de plusieurs tables.

%sql SELECT * FROM Langues, Auteurs

Comme on peut le constater cette requête est peu pertinente car elle affiche toutes les données de chacune des tables sans effectuer de correspondances. La clé de jointure apparaît pourtant ici clairement : il s'agit de id_langue qui doit permettre de recouper les informations entre les deux tables : il est en effet inutile d'afficher les données pour lesquelles les langues ne correspondent pas entre les deux tables.

Jointure

La jonture consiste à croiser les données de plusieurs tables pour les présenter sous forme d'un seul tableau. On va utiliser ce mécanisme pour afficher clairement la langue de l'auteur plutôt qu'un numéro qui n'est pas forcément très parlant. Nous utiliserons pour cela l'opérateur JOIN ... ON :

%sql SELECT NomAuteur, PrenomAuteur, Langue, AnneeNaissance  FROM Auteurs JOIN Langues ON Auteurs.IdLangue = Langues.IdLangue

Les champs sur lesquels faire la jointure ayant les mêmes noms dans les 2 tables, cette requête peut aussi être écrite plus simplement en utilisant le mot-clé USING ainsi :

%sql SELECT NomAuteur, PrenomAuteur, Langue, AnneeNaissance  FROM Auteurs JOIN Langues USING (IdLangue)

A vous de jouer

En croisant la table Livres avec la table Auteurs

  1. récupérer dans la variable reponse_1 une liste dont les attributs sont Titre, PrenomAuteur, NomAuteur et AnneePubli, triée du plus récent au plus ancien.
  2. récupérer dans la variable reponse_2 une liste dont les attributs sont Titre, PrenomAuteur, NomAuteur et AnneePubli écrits en français.
# Utiliser la syntaxe courte 
# reponse_i = %sql VOTRE REQUETE pour i=1 ou i=2

# YOUR CODE HERE
raise NotImplementedError()
# Utilisez cette cellule en brouillon pour vos requetes 
assert reponse_1[0] == ('Ubik', 'Philip', 'K. Dick', 1969)
assert reponse_2[0] == ('La nuit des temps', 'René', 'Barjavel', 1968)

Le cas des relations de n à n

Parfois il arrive que les données à collecter se trouvent dans plus que deux tables : c'est le cas des Thèmes pour les livres qui nécessitent l'analyse de 3 tables : Livres et Thèmes bien sûr, mais aussi la table de relation RelationsLivreTheme.

Observez et étudiez la requête suivante : Le principe est d'enchaîner deux jointures JOIN ... USING en utilisant la table de Relation au milieu. En effet, la requête se lit de la gauche vers la droite et on ne peut faire de jointure que si on a une clé externe en commun, ce qui n'est par exemple pas le cas entre Livres et Thèmes.

%sql SELECT Titre, Intitule FROM Livres JOIN RelationsLivreTheme USING (IdLivre) JOIN Themes USING (IdTheme)

A vous de jouer

Ecrire une requête permettant d'obtenir une liste dont les attributs sont Titre, NomAuteur et Langue triée par ordre croissant de date de naissance de l'auteur.

Vous stockerez le résultat dans une variable result

# Utiliser la syntaxe courte 
# reponse = %sql VOTRE REQUETE 

# YOUR CODE HERE
raise NotImplementedError()
# Utilisez cette cellule en brouillon pour vos requetes 
assert result[0] == ('De la Terre à la Lune', 'Verne', 'Français')