Chapitre 7 - Les requêtes

La manipulation des données se fait à l'aide de requêtes qui ciblent une ou plusieurs tables de la base de données et précisent les opérations à effectuer pour récupérer les données d'intérêt. Le moteur de la base de données interprète les requêtes pour récupérer rapidement les données et fournir le résultat.

Nous présentons dans ce chapitre la façon dont sont conçues les requêtes. Nous présentons d'abord les requêtes en utilisant l'algèbre relationnelle ce qui permet de bien comprendre leur sémantique. Pour chaque type de requêtes nous expliquons ensuite comment les coder en SQL pour qu'elles soient interprétables par les moteurs des bases de données.

Les requêtes mono-table

Une requête mono-table ne cible qu'une seule table. L'objectif est de récupérer certaines données contenues dans la table ciblée, ou de vérifier la non-existence de ces données.

Il existe deux opérations principales pour récupérer des données contenues dans une table : la projection (π) et la sélection (σ).

En prenant l'exemple de la table des auteurs, la requête π_prenom,nom(auteur) effectue une projection des attributs prenom et nom. Le résultat de l'exécution de cette requête donne le Tableau 7.1.

Tableau 7.1 : Résultat de la requête de projection.

En SQL, la projection est réalisée en utilisant le mot clé SELECT en précisant la table ciblée à l'aide du mot clé FROM. Le Code 7.1 présente la commande SQL permettant de récupérer les attributs prenom et nom de la table auteurs.

SELECT prenom, nom FROM auteurs;

Code 7.1 : Requête SQL de projection.

En prenant l'exemple de la table des auteurs, la requête σ_nom='Carjat'(auteurs) effectue une sélection des lignes dont le nom est égal à Carjat. Le résultat de l'exécution de cette requête donne le nuplet (1, 'Etienne', 'Carjat').

En SQL, la sélection est réalisée en ajoutant une clause WHERE contenant le critère de sélection. Notons que la clause WHERE doit être utilisée avec la projection SELECT. On peut notamment utiliser le caractère "*" pour récupérer toutes les colonnes. Le Code 7.2 présente la commande SQL permettant de récupérer les lignes dont le nom est égal à Carjat.

SELECT * FROM auteurs WHERE nom='Carjat';

Code 7.2 : Requête SQL de sélection.

SQL propose différents opérateurs pour exprimer les critères de sélection (=, <=, IN, LIKE, etc.). Il est de plus possible de combiner les critères de sélection en utilisant les opérateurs logiques AND, OR et NOT.

Les requêtes multi-tables

Les requêtes multi-tables récupèrent des données en exploitant les associations entre plusieurs tables. Elles sont utilisées lorsque les informations recherchées se trouvent dans des tables différentes et qu'il est nécessaire de les combiner.

L'opération de base pour réaliser des requêtes multi-tables est le produit cartésien. Cette opération combine les lignes de deux tables en créant une nouvelle table.

En SQL, il existe deux façons d'exprimer le produit cartésien. La première consiste à simplement identifier les tables dans la clause FROM. La deuxième consiste à utiliser la clause CROSS JOIN. Le Code 7.3 présente deux exemples de requêtes permettant de faire un produit cartésien entre les auteurs et les images.

SELECT * FROM auteurs, images;
SELECT * FROM auteurs CROSS JOIN images;

Code 7.3 : Requête SQL de produit cartésien.

Le résultat de l'exécution du produit cartésien auteurs X images est présenté dans le Tableau 7.2 . On voit que toutes les lignes des deux tables sont combinées dans le résultat.

Tableau 7.2 : Résultat de la requête auteurs X images.

Notons que le produit cartésien permet d'obtenir une table résultat contenant des colonnes qui ont le même nom. En effet, le produit cartésien auteurs X images donne une table avec les attributs id, prenom et nom venant de la table auteurs mais aussi id, nom, annee et auteurs venant de la table images. On a donc deux colonnes nommées id et deux colonnes nommées nom. Pour faire face à ce problème il faut utiliser l'opération de renommage des attributs (ρ).

Ainsi, en reprenant notre exemple, la requête auteurs X ρ_{id -> pid, nom -> pnom, auteur -> phtg_id}(images) effectue un produit cartésien entre les tables auteurs et images en renommant les attributs id, nom et auteur de la table images par pid, pnom et phtg_id. Le résultat de l'exécution de cette requête donne le Tableau 7.3.

Tableau 7.3 : Résultat de la requête de produit cartésien avec renommage.

En SQL, le renommage des attributs se fait en utilisant la clause AS. Le Code 7.4 présente l'expression du produit cartésien entre les auteurs et les images en utilisant le renommage des attributs.

SELECT p.id, p.prenom, p.nom, i.id AS pid, i.nom AS pnom, i.annee, i.auteur AS phtg\_id FROM auteurs AS p, images AS i;

Code 7.4 : Requête SQL avec renommage des attributs.

Le produit cartésien combine deux tables mais il ajoute des lignes qui n'ont pas beaucoup de sens surtout lorsque les deux tables sont associées par une clé étrangère. Par exemple, la deuxième ligne du produit cartésien auteurs X images associe l'auteur Etienne Cargat à l'image le baiser de l'hôtel de ville alors que la clé étrangère précise que c'est Robert Doisneau qui a pris cette image (voir Tableau 7.3). Pour éviter ce problème il faut supprimer du résultat du produit cartésien toutes les entités qui ont été mal associées. Cela peut se faire en utilisant l'opération mono-table de sélection (σ) et ne sélectionner que les lignes pour lesquelles la clé étrangère est bien égale à la clé primaire (id du auteur dans notre cas). Ainsi la requête σ_{images.auteur=auteur.id}(auteurs X images) ne garde que les entités qui sont cohérentes vis à vis des valeurs des clés primaires et étrangères.

Cette utilisation produit cartésien suivie d'une sélection est très souvent utilisée pour manipuler les données des tables qui sont liées par des clés étrangères. C'est ce qu'on appelle une opération de jointure. Une jointure combine les données de plusieurs tables en se basant sur une relation d'égalité entre les clés primaires et étrangères. L'opération de jointure interne est représentée par le symbole ⨝ en algèbre relationnelle. Elle effectue le produit cartésien des tables et filtre les lignes qui satisfont la condition d'égalité spécifiée.

En SQL, la jointure interne est obtenue en utilisant la clause JOIN avec la clause ON qui permet de spécifier la condition sur les clés. Le Code 7.5 présente la requête SQL qui permet de faire la jointure interne entre les auteurs et les images. Le résultat de cette requête est présenté dans le Tableau 7.4 . Nous avons montré l'effet de l'opérateur de sélection en rayant les lignes supprimées. On voit bien qu'il ne reste que les lignes pour lesquelles la clé étrangère (colonne auteur) correspond bien à la clé primaire (première colonne Id).

SELECT * FROM auteurs JOIN images ON auteurs.id = images.auteur;

Code 7.5 : Requête SQL de jointure interne.

Tableau 7.4 : Résultat de la requête auteurs ⨝_{auteurs.id = images.auteur} images.

Le Code 7.6 montre comment on peut faire une jointure et renommer les attributs des tables pour éviter les conflits de noms. On renomme l'attribut nom de la table images en titre et on ne garde que le nom et le prénom de l'auteur ainsi que l'année de l'image dans le résultat. Le résultat de cette requête est présenté dans le Tableau 7.5.

SELECT auteurs.prenom, auteurs.nom, images.nom as titre, images.annee FROM auteurs JOIN images ON auteurs.id = images.auteur;

Code 7.6 : Requête SQL de jointure avec renommage des attributs et projection pour simplifier le résultat.

Tableau 7.5 : Résultat d'une jointure avec renommage et projection des attributs.

Résultats des requêtes

Les requêtes SQL retournent des tables qui nécessitent souvent d'autres traitements (tri, calcul de moyenne, etc.) pour obtenir le résultat souhaité. Heureusement, SQL propose plusieurs opérations pour affiner les résultats. Nous présentons ici quelques unes de ces opérations.

La première opération est celle qui consiste à trier les résultats d'une requêtes en fonction d'un ou de plusieurs attributs. Cela se fait directement dans la requête en utilisant la clause ORDER BY. Par défaut, le classement est ascendant (du plus petit au plus grand). Il est possible de préciser l'ordre en utilisant la clause ASC ou DESC. Par exemple, le Code 7.7 présente la requête SQL qui permet de trier les résultats par année.

SELECT * FROM auteurs JOIN images ON auteurs.id = images.auteur ORDER BY annee;

Code 7.7 : Requête SQL de jointure avec tri.

Une autre opération permet de limiter la taille de la table résultat. Cela se fait directement dans la requête en utilisant la clause LIMIT. Par exemple, le Code 7.8 présente la requête SQL qui permet de trier les résultats par année et de ne retourner que le premier éléments.

SELECT * FROM auteurs JOIN images ON auteurs.id = images.auteur ORDER BY annee LIMIT 1;

Code 7.8 : Requête SQL de jointure avec tri et limite.

Enfin, les fonctions d'agrégation en SQL sont des opérations puissantes qui calculent des valeurs agrégées à partir de plusieurs entitées. Par exemple, la fonction COUNT() est utilisée pour compter le nombre de lignes qui correspondent à un critère spécifié. Pour illustrer son utilisation, considérons le cas où nous souhaitons déterminer le nombre total d'auteurs dans notre base de données. Dans ce cas, nous utiliserions COUNT(*) dans notre requête SQL (voir Code 7.9).

SELECT COUNT(*) FROM auteurs;

Code 7.9 : Requête SQL de comptage.

La fonction AVG() calcule la moyenne d'une colonne numérique spécifique. Supposons que nous voulons connaître l'âge moyen des auteurs. Dans ce cas, nous utiliserions AVG(age) (voir code 7.10).

SELECT AVG(age) FROM auteurs;

Code 7.10 : Requête SQL de calcul de moyenne.

SQL propose bien d'autres fonctions comme par exemple SUM() qui additionne toutes les valeurs d'une colonne numérique, MIN() qui est utilisée pour trouver la plus petite valeur d'une colonne spécifique ou MAX() qui est utilisée pour trouver la plus grande valeur d'une colonne spécifique.

Les requêtes imbriquées

Les résultats d'une requête SQL peuvent être utilisés dans d'autres requêtes, on appelle cela des requêtes imbriquées.

Un exemple assez classique est quand une première requête trouve la valeur maximum MAX() et qu'une deuxième requête cherche la ligne qui contient cette valeur maximum. Le Code 7.11 présente la requête SQL qui permet de trouver l'auteur qui a créé l'image qui a le plus de likes (on considère que la table des images contient une colonne pour stocker le nombre de likes).

SELECT * FROM auteurs JOIN images ON auteurs.id = images.auteur WHERE likes = (SELECT MAX(likes) FROM images);

Code 7.11 : Requête SQL de jointure avec requête imbriquée qui utilise la fonction MAX().

Une autre possibilité est d'utiliser les quantificateurs ALL et ANY. Le quantificateur ALL vérifie qu'une condition est vraie pour toutes les lignes d'une table alors que le quantificateur ANY vérifie qu'une condition est vraie pour au moins une ligne d'une table. Par exemple, la requête SQL du Code 7.12 utilise le quantificateur ALL et vérifie que tous les auteurs ont créé au moins une image.

SELECT * FROM auteurs WHERE id = ALL (SELECT auteur FROM images);

Code 7.12 : Requête SQL avec quantificateur ALL.

Pour s'exercer

Questions de cours

Exercice 1 - Compétitions sportives

Projet - mur d'images